在之前的项目中，使用到了Java后台读取excel文件数据的功能点，本想着该功能点已经做过了，这一类的应该都大差不离，不过在刚结束的一个项目中，现实给我深深的上了一课，特此编写此片博客，以作记录，并给自己提个醒，Java真的是浩瀚如海呀，任何时候其实自己都是小白，懂得越多越发谨慎。
Java后台读取excel文件数据该功能点一般与Java导出excel文件这个功能点配合使用。实际上此次的问题与之前的最大区别在于，之前导出excel文件时，明确知道导出的数据每一列的字段详情，导入excel文件数据时，数据格式便是固定的，即一个excel文件中每一列的数据，都是提前已经了解的，都是导出时所使用的字段，因此在读取到excel文件数据后，对数据的处理便简单粗暴一些，以代码为例，如下：
controller控制层：
/*** @author gr* @version V1.0.0* @Description 公告接口* @ClassName PublicController* @Copyright (c) All Rights Reserved, .* @since /08/27 15:19*/@Api(tags = "公共接口")@RestController@RequestMapping("/qwc")public class PublicController {@Autowiredprivate PublicService publicSvc;/*** 从EXCEL文件读取数据返回前台* @param file 要读取的EXCEL文件* @return 文件读取结果*/@ApiOperation("读取excel文件数据")@PostMapping("/readExcel")public ResponseEntity<DataResult<List<AttYsjEntity>>> readExcel(@RequestParam("file") MultipartFile file){List<AttYsjEntity> result = publicSvc.readExcel(file);return new ResponseEntity<>(ResultUtils.resultObject(result), HttpStatus.OK);}}
service服务层：
/*** @author gr* @version V1.0.0* @Description 公共接口服务层* @ClassName PublicService* @Copyright (c) All Rights Reserved, .* @since /08/27 15:24*/public interface PublicService {/*** 从EXCEL文件读取数据返回前台* @param file 要读取的EXCEL文件* @return 文件读取结果*/List<AttYsjEntity> readExcel(MultipartFile file);}
serviceImpl服务层实现类:
/*** @author 郭瑞* @version V1.0.0* @Description 公共接口服务层实现类* @ClassName PublicServiceImpl* @Copyright (c) All Rights Reserved, .* @since /08/27 15:25*/@Servicepublic class PublicServiceImpl implements PublicService {/*** 从EXCEL文件读取数据返回前台* @param file 要读取的EXCEL文件* @return 文件读取结果*/@Overridepublic List<AttYsjEntity> readExcel(MultipartFile file){String filename = file.getOriginalFilename();List<AttYsjEntity>attYsjList = new ArrayList<>();assert filename != null;if (!filename.matches("^.+\\.(?i)(xls)$") && !filename.matches("^.+\\.(?i)(xlsx)$")){throw new RuntimeException("上传文件格式不正确");}boolean isExcel = true;if (filename.matches("^.+\\.(?i)(xlsx)$")) {isExcel = false;}try {InputStream iss = file.getInputStream();Workbook wbk;if (isExcel) {wbk = new HSSFWorkbook(iss);} else {wbk = new XSSFWorkbook(iss);}Sheet sheet = wbk.getSheetAt(0);for (int r = 3; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象if (row == null){continue;}AttYsjEntity attYsj = new AttYsjEntity();String mlid = getCell(row,2,String.class);String mc = getCell(row,3,String.class);String lx = getCell(row,4,String.class);String xdlj = getCell(row,5,String.class);Integer zt = getCell(row,6,Integer.class);Integer ysjjxid = getCell(row,7,Integer.class);Integer xqid = getCell(row,8,Integer.class);String featuretype = getCell(row,9,String.class);String featuretable = getCell(row,10,String.class);String rkr = getCell(row,11,String.class);Date rksj = getCell(row,12,Date.class);String rkdw = getCell(row,13,String.class);String xgr = getCell(row,14,String.class);Date xgsj = getCell(row,15,Date.class);String sjlx = getCell(row,16,String.class);Integer nd = getCell(row,17,Integer.class);String scdw = getCell(row,18,String.class);String blc = getCell(row,20,String.class);String fbl = getCell(row,21,String.class);Integer yl = getCell(row,22,Integer.class);String xq = getCell(row,24,String.class);Float tlX = getCell(row,27,Float.class);Float tlY = getCell(row,28,Float.class);Float rbX = getCell(row,29,Float.class);Float rbY = getCell(row,30,Float.class);String dirname = getCell(row,31,String.class);Integer state = getCell(row,32,Integer.class);String ssdq = getCell(row,35,String.class);// String datasource = getCell(row,36,String.class);String province = getCell(row,37,String.class);//完整的循环一次 就组成了一个对象attYsj.setMlid(mlid);attYsj.setMc(mc);attYsj.setLx(lx);attYsj.setXdlj(xdlj);attYsj.setZt(zt);attYsj.setYsjjxid(ysjjxid);attYsj.setXqid(xqid);attYsj.setFeaturetype(featuretype);attYsj.setFeaturetable(featuretable);attYsj.setRkr(rkr);attYsj.setRksj((java.sql.Date) rksj);attYsj.setRkdw(rkdw);attYsj.setXgr(xgr);attYsj.setXgsj((java.sql.Date) xgsj);attYsj.setSjlx(sjlx);attYsj.setNd(nd);attYsj.setScdw(scdw);attYsj.setBlc(blc);attYsj.setFbl(fbl);attYsj.setYl(yl);attYsj.setXq(xq);attYsj.setTl_x(tlX);attYsj.setTl_y(tlY);attYsj.setRb_x(rbX);attYsj.setRb_y(rbY);attYsj.setDirname(dirname);attYsj.setState(state);attYsj.setSsdq(ssdq);// attYsj.setDatasource(datasource);attYsj.setProvince(province);attYsjList.add(attYsj);}} catch (IOException e) {e.printStackTrace();}return attYsjList;}private <T> T getCell(Row row, int cellNum, Class<T> clazz) {Cell cell = row.getCell(cellNum);if (cell != null) {String stringCellValue = cell.getStringCellValue();boolean hasValue = StringUtils.isNotBlank(stringCellValue);if (Double.class.equals(clazz)) {return (T) (hasValue ? Double.valueOf(stringCellValue) : new Double(0));} else if (Integer.class.equals(clazz)){return (T) (hasValue ? Integer.valueOf(stringCellValue) : new Integer(0));} else if (Float.class.equals(clazz)){return (T) (hasValue ? Float.valueOf(stringCellValue) : new Float(0));} else if (String.class.equals(clazz)){return (T) stringCellValue;} else if (Date.class.equals(clazz)){SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");Date date;try {date = hasValue ? sdf.parse(stringCellValue.substring(0,19)) : null;} catch (ParseException e) {e.printStackTrace();throw new RuntimeException(e);}return (T) date;} else {return (T) stringCellValue;}} else {return null;}}}
如此实现导入excel文档的功能，是可以用的，但是具有很多的局限性，excel文档中的数据以列显示时，必须对映AttYsjEntity 该实体类的字段排序以及字段类型，不然就会出现数据不对应的错误。
这次项目中需要实现的excel文档的功能地点，因为导出时数据不是通过后端导出，直接从前端导出的页面数据，并且会导出不同页面的数据，因此数据字段是否存在不确定，字段排序不确定，字段类型不确定。通过参考各种导入方式，实现了功能的解决方案为：在导出时，不仅仅只是导出数据，如同正常的excel表格数据一样，需要在首行先导出表头，即规定了该列的字段信息，先读取各字段即表头，再读取相关数据即可，上代码：
将数据读取的功能封装了一个工具类：
package com.ma.utils;import org.apache.poi.ss.usermodel.*;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import java.io.InputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/*** @Author gr* @Version V1.0.0* @Description excel文件处理工具类* @ClassName ExcelUtils* @Copyright (c) All Rights Reserved, .* @Since /05/15 22:08*/public class ExcelUtils {private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);@SuppressWarnings("deprecation")public static List<Map<String,Object>> excelToShopIdList(InputStream inputStream) {List<Map<String,Object>> list = new ArrayList<>();Workbook workbook;try {workbook = WorkbookFactory.create(inputStream);inputStream.close();//工作表对象Sheet sheet = workbook.getSheetAt(0);//总行数int rowLength = sheet.getLastRowNum() + 1;//工作表的列Row row = sheet.getRow(0);//总列数int colLength = row.getLastCellNum();//得到指定的单元格Cell cell;for (int i = 1; i < rowLength; i++) {Map<String,Object> ydxxInfo = new HashMap<>();//工作表的列Row rowHead = sheet.getRow(0);row = sheet.getRow(i);for (int j = 0; j < colLength; j++) {Cell cellHead = rowHead.getCell(j);String dataHead = null;String data = null;if(cellHead!=null){cellHead.setCellType(Cell.CELL_TYPE_STRING);dataHead = cellHead.getStringCellValue();dataHead = dataHead.trim();}cell = row.getCell(j);if (cell!=null){cell.setCellType(Cell.CELL_TYPE_STRING);data = cell.getStringCellValue();data = data.trim();}ydxxInfo.put(dataHead,data);}list.add(ydxxInfo);}} catch (Exception e) {LOGGER.error("parse excel file error :", e);}return list;}}
controller控制层代码：
/*** 控制器* @author luyifa* 工单办理*/@RestController@RequestMapping("/gdbl")public class GdblController {private final static Logger logger = (Logger) LoggerFactory.getLogger(TestController.class);/*** 从任意excel文件读取数据返回前台(任意excel文件导入)* 当前接口可导入携带表头的任意excel表格文件数据* @param file 要读取的excel文件* @return 文件读取结果* @author gr 0516*/@RequestMapping("/readExcel2")public ResponseEntity<Object> readExcel(@RequestParam("file") MultipartFile file) throws IOException{String name = file.getOriginalFilename();if (name.length() < 6 || !name.substring(name.length() - 5).equals(".xlsx")) {return new ResponseEntity<Object>("文件格式错误", HttpStatus.INTERNAL_SERVER_ERROR);}//TODO 业务逻辑，通过file.getInputStream()，处理Excel文件List<Map<String,Object>> result = ExcelUtils.excelToShopIdList(file.getInputStream());logger.info("导入成功");return new ResponseEntity<Object>(result,HttpStatus.OK);}}
导入的数据可以返回前端了，把接口返回前端的数据拿出来看看
到了这一步已经把该功能点的重点完成的差不多了，接下来需要将解析的数据拿到，更新数据库，更改controller中接口如下：
/*** 从任意excel文件读取数据* 并更新数据库中间表YDXXINFO后返回前台(任意excel文件导入)* 当前接口可导入携带表头的任意excel表格文件数据* @param file 要读取的excel文件* @return 文件读取结果* @author gr 0517*/@RequestMapping("/readExcel")public ResponseEntity<Object> readExcelAndUpdateBase(@RequestParam("file") MultipartFile file) throws IOException{String name = file.getOriginalFilename();if (name.length() < 6 || !name.substring(name.length() - 5).equals(".xlsx")) {return new ResponseEntity<Object>("文件格式错误", HttpStatus.INTERNAL_SERVER_ERROR);}//TODO 业务逻辑，通过file.getInputStream()，处理Excel文件List<Map<String,Object>> result = ExcelUtils.excelToShopIdList(file.getInputStream());List<Map<String,Object>> ydxxIndoUpdateLists = new ArrayList<>();for(Map<String,Object> ydxxInfos:result ){Map<String,Object> ydxxIndoUpdate = new HashMap<String,Object>();String ydxxId = "null";String finish = "null";String validYd = "null";String remedyDl = "null";String remedyDf = "null";String causeYd = "null";if(ydxxInfos.get("异动唯一标识")!=null&&StringUtils.isNotBlank(ydxxInfos.get("异动唯一标识").toString())){ydxxId = ydxxInfos.get("异动唯一标识").toString();}if(ydxxInfos.get("是否办结")!=null&&StringUtils.isNotBlank(ydxxInfos.get("是否办结").toString())){if(ydxxInfos.get("是否办结").equals("是")){finish = "1";}else {finish = "0";}}if(ydxxInfos.get("是否有效异动")!=null&&StringUtils.isNotBlank(ydxxInfos.get("是否有效异动").toString())){if(ydxxInfos.get("是否有效异动").equals("是")){validYd = "1";}else {validYd = "0";}}if(ydxxInfos.get("追补电量")!=null&&StringUtils.isNotBlank(ydxxInfos.get("追补电量").toString())){remedyDl = ydxxInfos.get("追补电量").toString();}if(ydxxInfos.get("追补电费")!=null&&StringUtils.isNotBlank(ydxxInfos.get("追补电费").toString())){remedyDf = ydxxInfos.get("追补电费").toString();}if(ydxxInfos.get("异动原因")!=null&&StringUtils.isNotBlank(ydxxInfos.get("异动原因").toString())){switch (ydxxInfos.get("异动原因").toString()) {case "档案问题（无电量追补）":causeYd = "01";break;case "用户责任":causeYd = "02";break;case "企业责任":causeYd = "03";break;case "用户企业均有责任":causeYd = "04";break;}}ydxxIndoUpdate.put("finish", finish);ydxxIndoUpdate.put("validYd", validYd);ydxxIndoUpdate.put("remedyDl", remedyDl);ydxxIndoUpdate.put("remedyDf", remedyDf);ydxxIndoUpdate.put("causeYd", causeYd);ydxxIndoUpdate.put("ydxxId", ydxxId);ydxxIndoUpdateLists.add(ydxxIndoUpdate);}int updateResult = iGdblService.updataSheetInfoDept2(ydxxIndoUpdateLists);logger.info("导入成功");return new ResponseEntity<Object>(result,HttpStatus.OK);}
ok，大功告成！在返回前端数据之前，将重要数据提取，调用批量更新的方法，对这部分数据做数据库更新，至此该功能点完成所有需求。
先到这里，去撸代码了！
