poi导入excel解析为实体类集合
JSON用的 fastjson-1.2.70.jar
poi用的3.17
import com.alibaba.fastjson.JSONObject; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; /** * @ClassName: POIUtils * @Description: poi的一些工具*/ public class POIUtils { /** * @Title: readExcel * @Description: 解析excel文件 * @Param: [workBook * titleMap excel中表头和实体类中字段的对应 * requiredTitle 必填的表头 * options 在新增或编辑中需要去选择的字段(一般是下拉选) * { * '表头名':{ * '选项名':选项内容, * '选项名':选项内容 * }, * '表头名':{ * '选项名':选项内容, * '选项名':选项内容 * }, * } * titleRowIndex 表头所在行,读取的数据从下一行开始 * clazz 实体类的泛型 * ] * @return: java.util.List<T> **/ public static <T> List<T> readExcel(Workbook workBook, Map<String,String> titleMap, List<String> requiredTitle,Map<String,Map<String,Object>> options, int titleRowIndex, Class<T> clazz) throws Exception{ List<T> result = new ArrayList<>(); if(workBook == null){ return result; } Sheet sheet = workBook.getSheetAt(0); int totalRowNum = sheet.getLastRowNum() + 1;//获取总行数 Row titleRow = sheet.getRow(titleRowIndex);//获取表头行 int cellLength = titleRow.getPhysicalNumberOfCells();//获取表头列数 //判断表头是否对应 if (cellLength != titleMap.size()) { throw new Exception("标题行不匹配"); } List<String> titleList = new ArrayList<>(); for (int i = 0; i < cellLength; i++) { String titleName = titleRow.getCell(i).getStringCellValue(); if (titleMap.get(titleName) == null || titleMap.get(titleName) == "") { throw new Exception("不存在的标题:" + titleName); } titleList.add(titleName); } //开始解析 for(int i = titleRowIndex + 1; i < totalRowNum; i++){//从表头行下一行开始 Row thisRow = sheet.getRow(i); if(thisRow == null){ continue; } JSONObject json = new JSONObject(); for(int j = 0;j < titleList.size(); j++){//列 Cell cell = thisRow.getCell(j); String thisCellValue = getValue(cell);//获取单元格值 //必填验证 if(thisCellValue == null || thisCellValue.length() == 0){ if(requiredTitle.contains(titleList.get(j))){ throw new Exception("必填项不能为空,在第"+(i+1)+"行第"+(j+1)+"列"); }else{ continue; } } //可选验证 if(options.containsKey(titleList.get(j))){ Map<String,Object> option = options.get(titleList.get(j)); if(option.containsKey(thisCellValue)){ json.put(titleMap.get(titleList.get(j)),option.get(thisCellValue)); }else{ throw new Exception("不存在的" + titleList.get(j) + ":"+thisCellValue+",在第"+(i+1)+"行第"+(j+1)+"列"); } }else{ json.put(titleMap.get(titleList.get(j)),thisCellValue); } } T item = JSONObject.toJavaObject(json,clazz); result.add(item); } return result; } /** * @Title: getValue * @Description: 对表格中数值进行格式化 * @Param: [cell] * @return: java.lang.String **/ public static String getValue(Cell cell) { String value = ""; if(null==cell){ return value; } switch (cell.getCellTypeEnum()) { case NUMERIC://数值型 if (HSSFDateUtil.isCellDateFormatted(cell)) { //如果是date类型则 ,获取该cell的date值 Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()); SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd"); value = format.format(date);; }else {// 纯数字 BigDecimal big=new BigDecimal(cell.getNumericCellValue()); value = big.toString(); //解决1234.0 去掉后面的.0 if(null!=value&&!"".equals(value.trim())){ String[] item = value.split("[.]"); if(1<item.length&&"0".equals(item[1])){ value=item[0]; } } } break; case STRING://字符串类型 value = cell.getStringCellValue().toString(); break; case FORMULA:// 公式类型 //读公式计算值 value = String.valueOf(cell.getNumericCellValue()); if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串 value = cell.getStringCellValue().toString(); } break; case BLANK://空白单元格类型 break; case BOOLEAN:// 布尔类型 value = " "+ cell.getBooleanCellValue(); break; case ERROR://错误单元格类型 break; default: value = cell.getStringCellValue().toString(); break; } if("null".endsWith(value.trim())){ value=""; } return value; } }