需要用poi调用excel中的数据输入到数据库db2中,代码如下
import java.sql.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.model.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.hssf.usermodel.*;import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
import java.io.FileInputStream;
import java.io.*;
public class test{
static{
try{
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
System.out.println("Success loading DB2Driver...");
}
catch(Exception e){
System.out.println("Error loading DB2 Type2 Driver");
e.printStackTrace();
}
}
public static void main(String[] args){
dbconnection DBcon=new dbconnection("jdbc:db2:loaddata","db2admin","1");
Connection conn=DBcon.makeconnection();
Statement stmt1=DBcon.makestatement(conn);
POIFSFileSystem fs=null;
HSSFWorkbook wb=null;
try{
fs=new POIFSFileSystem(new FileInputStream("F:\\za\\book1.xls"));
wb=new HSSFWorkbook(fs);
System.out.println("Success make fs wb");
}
catch(IOException e){
e.printStackTrace();
System.out.println("Erorr connect Excel");
}
HSSFSheet sheet=wb.getSheetAt(0);
HSSFRow row=null;
HSSFCell cell=null;
int rowNum,cellNum,i,j;
String cellvalue="";
rowNum=sheet.getLastRowNum();
row=sheet.getRow(0);
cellNum=row.getLastCellNum();
String name=null;
for(i=1;i<=rowNum;i++){
String str="insert into administrator.load010203 values(";
for(j=1;j<=cellNum;j++){
row=sheet.getRow(i);
cell=row.getCell((short)j);
switch(cell.getCellType()){
case 0:{
Integer num=new Integer((int)cell.getNumericCellValue());
cellvalue=String.valueOf(num);
str=str+","+cellvalue;
break;}
case 1:{
cellvalue=cell.getStringCellValue();
str=str+"'"+cellvalue+"'";
break;
}
default:{
cellvalue="";
break;
}
}
}
str=str+")";
try{
stmt1.executeUpdate(str);
}
catch(Exception e){
e.printStackTrace();
}
}
System.out.println("sucess");
}
}
数据库可以连上,但执行时提示switch(cell.getCellType()){行出错,高手指教啊,
Success loading DB2Driver...
Success make connection
Success make statement
Success make fs wb
Exception in thread "main" java.lang.NullPointerException
at test.main(test.java:56)
import java.sql.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.model.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.hssf.usermodel.*;import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
import java.io.FileInputStream;
import java.io.*;
public class test{
static{
try{
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
System.out.println("Success loading DB2Driver...");
}
catch(Exception e){
System.out.println("Error loading DB2 Type2 Driver");
e.printStackTrace();
}
}
public static void main(String[] args){
dbconnection DBcon=new dbconnection("jdbc:db2:loaddata","db2admin","1");
Connection conn=DBcon.makeconnection();
Statement stmt1=DBcon.makestatement(conn);
POIFSFileSystem fs=null;
HSSFWorkbook wb=null;
try{
fs=new POIFSFileSystem(new FileInputStream("F:\\za\\book1.xls"));
wb=new HSSFWorkbook(fs);
System.out.println("Success make fs wb");
}
catch(IOException e){
e.printStackTrace();
System.out.println("Erorr connect Excel");
}
HSSFSheet sheet=wb.getSheetAt(0);
HSSFRow row=null;
HSSFCell cell=null;
int rowNum,cellNum,i,j;
String cellvalue="";
rowNum=sheet.getLastRowNum();
row=sheet.getRow(0);
cellNum=row.getLastCellNum();
String name=null;
for(i=1;i<=rowNum;i++){
String str="insert into administrator.load010203 values(";
for(j=1;j<=cellNum;j++){
row=sheet.getRow(i);
cell=row.getCell((short)j);
switch(cell.getCellType()){
case 0:{
Integer num=new Integer((int)cell.getNumericCellValue());
cellvalue=String.valueOf(num);
str=str+","+cellvalue;
break;}
case 1:{
cellvalue=cell.getStringCellValue();
str=str+"'"+cellvalue+"'";
break;
}
default:{
cellvalue="";
break;
}
}
}
str=str+")";
try{
stmt1.executeUpdate(str);
}
catch(Exception e){
e.printStackTrace();
}
}
System.out.println("sucess");
}
}
数据库可以连上,但执行时提示switch(cell.getCellType()){行出错,高手指教啊,
Success loading DB2Driver...
Success make connection
Success make statement
Success make fs wb
Exception in thread "main" java.lang.NullPointerException
at test.main(test.java:56)
for(j=1;j<=cellNum;j++){这里的循环是不是应该从0---cellNum-1;row的也是for(i=1;i<=rowNum;i++){也应该0---rowNum-1;
当j = cellNum的时候,已经到了cellNum + 1 列 了 所以
cell取道的是null.
NullPointerException