import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
public class PoiUtil {
public static FormulaEvaluator evaluator;
public static String getCellValue(Cell cell) {
if (cell==null) {
return "isNull";
}
String cellValue = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue=cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
cellValue=String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cellValue=getCellValue(evaluator.evaluate(cell));
break;
default:
break;
}
return cellValue;
}
private static String getCellValue(CellValue cell) {
String cellValue = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue=cell.getStringValue();
break;
case Cell.CELL_TYPE_NUMERIC:
cellValue=String.valueOf(cell.getNumberValue());
break;
case Cell.CELL_TYPE_FORMULA:
break;
default:
break;
}
return cellValue;
}
}
@RequestMapping("importExcel")
public void importExcel(@RequestParam(value = "file", required = false) MultipartFile file,
HttpServletResponse response,HttpServletRequest request) throws Exception{
Workbook wb0 = new HSSFWorkbook(file.getInputStream());
FormulaEvaluator evaluator=wb0.getCreationHelper().createFormulaEvaluator();
PoiUtil.evaluator=evaluator;
//获取Excel文档中的第一个表单
Sheet sht0 = wb0.getSheetAt(0);
//对Sheet中的每一行进行迭代
for (Row r : sht0) {
//从第三行开始
if(r.getRowNum()<3){
continue;
}
//创建实体类
Testentity entity= new Testentity();
try {
if(PoiUtil.getCellValue(r.getCell(0))!=null)
entity.setSortNum(new BigDecimal(PoiUtil.getCellValue(r.getCell(0))));
entity.setDevNo(PoiUtil.getCellValue(r.getCell(1)));
entity.setDevName(PoiUtil.getCellValue(r.getCell(2)));
entity.setDevInfo(PoiUtil.getCellValue(r.getCell(3)));
entity.setDevUnit(PoiUtil.getCellValue(r.getCell(4)));
if(PoiUtil.getCellValue(r.getCell(5))!=null)
entity.setDevCount(new BigDecimal(PoiUtil.getCellValue(r.getCell(5))));
if(PoiUtil.getCellValue(r.getCell(6))!=null)
entity.setBidUnitPrice(new BigDecimal(PoiUtil.getCellValue(r.getCell(6))));
if(PoiUtil.getCellValue(r.getCell(7))!=null)
entity.setBidSumPrice(new BigDecimal(PoiUtil.getCellValue(r.getCell(7))));
if(PoiUtil.getCellValue(r.getCell(8))!=null)
entity.setBidTempPrice(new BigDecimal(PoiUtil.getCellValue(r.getCell(8))));
if(PoiUtil.getCellValue(r.getCell(9))!=null)
entity.setCostUnitPrice(new BigDecimal(PoiUtil.getCellValue(r.getCell(9))));
if(PoiUtil.getCellValue(r.getCell(10))!=null)
entity.setCostSumPrice(new BigDecimal(PoiUtil.getCellValue(r.getCell(10))));
} catch (NumberFormatException e) {
}
}
}