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