Apache POI实现EXcel的数据导入数据库

1.当然是导包

2.写一个读取EXcel的帮助类ExcelHelper

package com.zking.Test;import java.io.File;  import java.io.FileInputStream;  import java.io.IOException;  import java.io.InputStream;  import java.util.ArrayList;  import java.util.List;  import org.apache.poi.hssf.usermodel.HSSFWorkbook;  import org.apache.poi.ss.usermodel.Cell;  import org.apache.poi.ss.usermodel.CellValue;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.FormulaEvaluator;  import org.apache.poi.ss.usermodel.Row;  import org.apache.poi.ss.usermodel.Sheet;  import org.apache.poi.ss.usermodel.Workbook;import com.zking.entity.Production;  public abstract class ExcelHelper {      /**      * Excel 2003      */      private final static String XLS = "xls";      /**      * 由Excel文件的Sheet导出至List      *       * @param file      * @param sheetNum      * @return      */      public static List<Production> exportListFromExcel(File file, int sheetNum)              throws IOException {          return exportListFromExcel(new FileInputStream(file), sheetNum);      }      /**      * 由Excel流的Sheet导出至List      *       * @param is      * @param extensionName      * @param sheetNum      * @return      * @throws IOException      */      public static List<Production> exportListFromExcel(InputStream is,              int sheetNum) throws IOException {          Workbook workbook = null;              workbook = new HSSFWorkbook(is);          return exportListFromExcel(workbook, sheetNum);      }      /**      * 由指定的Sheet导出至List      *       * @param workbook      * @param sheetNum      * @return      * @throws IOException      */      private static List<Production> exportListFromExcel(Workbook workbook,              int sheetNum) {          Sheet sheet = workbook.getSheetAt(sheetNum);          // 解析公式结果          FormulaEvaluator evaluator = workbook.getCreationHelper()                  .createFormulaEvaluator();          List<Production> list = new ArrayList<Production>();          int minRowIx = sheet.getFirstRowNum();          int maxRowIx = sheet.getLastRowNum();          for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {              Row row = sheet.getRow(rowIx);              StringBuilder sb = new StringBuilder();              short minColIx = row.getFirstCellNum();              short maxColIx = row.getLastCellNum();             String value = "";              for (short colIx = minColIx; colIx <= maxColIx; colIx++) {                  Cell cell = row.getCell(new Integer(colIx));                  CellValue cellValue = evaluator.evaluate(cell);                  if (cellValue == null) {                      continue;                  }                  // 经过公式解析，最后只存在Boolean、Numeric和String三种数据类型，此外就是Error了                  // 其余数据类型，根据官方文档，完全可以忽略http://poi.apache.org/spreadsheet/eval.html                  switch (cellValue.getCellType()) {                  case Cell.CELL_TYPE_BOOLEAN:                        value +=cellValue.getBooleanValue()+ ",";                        break;                  case Cell.CELL_TYPE_NUMERIC:                      // 这里的日期类型会被转换为数字类型，需要判别后区分处理                       if (DateUtil.isCellDateFormatted(cell)) {                           value +=cell.getDateCellValue()+ ",";                      } else {                          value += cellValue.getNumberValue()+ ",";                      }                      break;                  case Cell.CELL_TYPE_STRING:                      value += cellValue.getStringValue()+ ",";                     break;                  case Cell.CELL_TYPE_FORMULA:                      break;                  case Cell.CELL_TYPE_BLANK:                      break;                  case Cell.CELL_TYPE_ERROR:                      break;                  default:                      break;                  }              }              String[] val = value.split(",");            Production pd=new Production();           // pd.setProduct_id(Integer.parseInt(val[0]));            pd.setProductName(val[1]);            pd.setSpec(val[2]);            pd.setUnit(val[3]);            list.add(pd);          }          return list;      }  }  

3.测试

package com.zking.Test;import java.io.File;  import java.io.IOException;  import java.util.List;  import static org.junit.Assert.*;  import org.junit.Test;import com.zking.entity.Production;  public class ExcelHelperTest {      @Test      public void test() {          String path = "D:\\test1.xls";          List<Production> list = null;          try {              list = ExcelHelper.exportListFromExcel(new File(path), 0);            //  System.out.println(list);            assertNotNull(list);          } catch (IOException e) {              fail();          }      }  }