Excel的数据导入到数据库中出现的两个问题及解决方案

     今天要把Excel中的数据导入到数据库中，写了段代码，出现两个问题，解决方法如下。

问题一：程序报错，报错如下，原因是excel中用了筛选，且有标题头，去掉即可。

import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;public class ImportExcel {    public static String filePath = "D://20130128.xls";      public static void main(String[] args) {            try {          // 创建对Excel工作簿文件的引用                FileInputStream file = new FileInputStream(filePath);          HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));                  // 在Excel文档中，第一张工作表的缺省索引是0                  // 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);                  HSSFSheet sheet = wookbook.getSheetAt(0);                  //获取到Excel文件中的所有行数                  int rows = sheet.getPhysicalNumberOfRows();                  //遍历行                  for (int i = 0; i < rows; i++) {                        // 读取左上端单元格                        HSSFRow row = sheet.getRow(i);                        // 行不为空                        if (row != null) {                              //获取到Excel文件中的所有的列                              int cells = row.getPhysicalNumberOfCells();                              String value = "";                              //遍历列                              for (int j = 1; j < 7; j++) {                                    //获取到列的值                                    HSSFCell cell = row.getCell((short)j);                                    if (cell != null) {                                          switch (cell.getCellType()) {                                                case HSSFCell.CELL_TYPE_FORMULA:                                                break;                                                case HSSFCell.CELL_TYPE_NUMERIC:                                                    value += cell.getNumericCellValue() + ",";                                                break;                                                case HSSFCell.CELL_TYPE_STRING:                                                    value += cell.getStringCellValue() + ",";                                                break;                                                default:                                                    value += cell.getStringCellValue() + " ";                                                break;                                    }                              }                        }                              System.out.println(i+"   "+value);                  }             }      } catch (FileNotFoundException e) {            e.printStackTrace();      } catch (IOException e) {            e.printStackTrace();      }   }}

 

java.lang.reflect.InvocationTargetException
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
 at java.lang.reflect.Constructor.newInstance(Unknown Source)
 at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java:224)
 at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.java:160)
 at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:163)
 at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:210)
 at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:191)
 at com.comtop.log.ImportExcel.main(ImportExcel.java:20)
Caused by: java.lang.ArrayIndexOutOfBoundsException
 at java.lang.System.arraycopy(Native Method)
 at org.apache.poi.hssf.record.UnknownRecord.<init>(UnknownRecord.java:62)
 at org.apache.poi.hssf.record.SubRecord.createSubRecord(SubRecord.java:57)
 at org.apache.poi.hssf.record.ObjRecord.fillFields(ObjRecord.java:99)
 at org.apache.poi.hssf.record.Record.fillFields(Record.java:90)
 at org.apache.poi.hssf.record.Record.<init>(Record.java:55)
 at org.apache.poi.hssf.record.ObjRecord.<init>(ObjRecord.java:61)
 ... 10 more
Exception in thread "main" org.apache.poi.hssf.record.RecordFormatException: Unable to construct record instance, the following exception occured: null
 at org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java:237)
 at org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.java:160)
 at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:163)
 at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:210)
 at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:191)
 at com.comtop.log.ImportExcel.main(ImportExcel.java:20)
问题二：原本是800条数据，导到512条就导不进去了，读取excel的数据打印出来看到有几条数据是乱码。随后检查excel中的内容，看到有些字段内容后面有空格，把空格去掉后，导入正常。