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(); } } }