Java解析Excel
Java解析Excel可以说在日常工作中必不可少的一个需求。解析Excel有很多方式，主流有poi，easyexcel等技术，本文使用springboot+poi技术来实现解析excel文件。其中包含解析本地文件和web端导入导出文件，且支持2003和2007及以上版本。
1.依赖导入
依赖版本不同可能会出现报错，整篇文章使用以下依赖版本进行编写。
org.apache.poi poi 3.10-FINAL org.apache.poi poi-ooxml 3.10-FINAL org.apache.poi poi-ooxml-schemas 3.10-FINAL 2.本地导入导出
定义要读取和存放Excel文件的路径
// 要读取的本地文件String localExcelPath = "/Users/jeckwu/Desktop/test/test.xlsx";// 把数据写入excel放到本地路径String writeDateToExcelPath = "/Users/jeckwu/Desktop/test/";解析本地excel：解析指定目录中的excel数据
// 读取本地excel数据 @Test public void getLocalExcelData() throws IOException { // 获取文件流 FileInputStream fileInputStream = new FileInputStream(localExcelPath); // 解析数据 List> maps = xlsxImportExcel(fileInputStream, 0, 1, 0); assert maps != null; log.info("size:{}", maps.size()); log.info("data:{}", JSON.toJSONString(maps)); } 这里简单的封装了一下，操作本地文件只针对xlsx格式文件，web端的处理包括xls类型。
static List> xlsxImportExcel(FileInputStream file, Integer titleNo, Integer dataNo, Integer sheetNo) throws IOException { log.info("excel2007及以上版本"); // 读取流中的excel数据 这里用到的是poi中的XSSFWorkbook 用来处理xlsx(版本在2007以后的excel文件) HSSFWorkbook对象用来处理xls类型 版本为2003的文件 XSSFWorkbook xwb = new XSSFWorkbook(file); //获取excel工作簿 XSSFSheet xssfSheet = xwb.getSheetAt(sheetNo); //获取excel的sheet if (xssfSheet == null) { return null; } // 防止数据列错乱 List> mapList = new ArrayList<>(); Row rowTitle = xssfSheet.getRow(titleNo); //循环获取excel每一行 for (int rowNum = dataNo; rowNum < xssfSheet.getLastRowNum() + 1; rowNum++) { XSSFRow xssfRow = xssfSheet.getRow(rowNum); if (xssfRow == null) { continue; } LinkedHashMap map = new LinkedHashMap<>(); //循环获取excel每一行的每一列 for (int cellNum = 0; cellNum < rowTitle.getLastCellNum(); cellNum++) { XSSFCell xssCell = xssfRow.getCell(cellNum); if (xssCell == null) { // 防止单元格为空 map.put(rowTitle.getCell(cellNum).toString(), ""); continue; } try { map.put(rowTitle.getCell(cellNum).toString(), getXSSFValue(xssCell)); } catch (Exception e) { log.error(""); } } mapList.add(map); //将excel每一行的数据封装到map对象,并将map对象添加到list } return mapList; } 处理excel各种类型值
public static Object getXSSFValue(XSSFCell hssfCell) { Object result = null; int cellType = hssfCell.getCellType(); switch (hssfCell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: //数字 if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {// 需要对日期这一列进行设置样式，否则无法识别是日期类型还是数值类型// 默认不支持中文日期类型，需要设置成纯英语日期类型，不要包含年月日等汉字// 最好是使用这种格式 2019/10/10 0:00 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); result = sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue())); break; } result = hssfCell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: //Boolean result = hssfCell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //故障 result = hssfCell.getErrorCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA: //公式 result = hssfCell.getCellFormula(); break; case HSSFCell.CELL_TYPE_BLANK: result = ""; break; default: //字符串 result = hssfCell.getStringCellValue(); } return result; }Excel下载到本地：根据指定数据集生成excel到指定目录中
这里用到了上面的excel数据作为测试 实际根据自己项目封装即可。
// 把数据集写入到excel中 放到本地服务器 @Test public void writeDataToLocalExcel() throws IOException { // 获取文件流-就拿本地数据源 FileInputStream fileInputStream = new FileInputStream(localExcelPath); List> maps = xlsxImportExcel(fileInputStream, 0, 1, 0); assert maps != null; // 取10条写入到其他文件 List> maps1 = maps.subList(0, 10); // 新建工作簿 XSSFWorkbook wb = new XSSFWorkbook(); // 新建Sheet XSSFSheet xssfSheet = wb.createSheet("testSheet"); // 获取标题 前面用的是linkedhashmap 直接获取map中的标题 且和读取的excel列相同 List titleList = maps1.get(0).keySet().stream().map(s -> s).collect(Collectors.toList()); //标题行 XSSFRow titleRow = xssfSheet.createRow(0); for (int i = 0; i < maps1.get(0).keySet().size(); i++) { XSSFCell titleCell = titleRow.createCell(i); titleCell.setCellValue(titleList.get(i)); } // 数据行 for (int i = 0; i < maps1.size(); i++) { XSSFRow xssfRow = xssfSheet.createRow(i + 1); Map stringObjectMap = maps1.get(i); for (int i1 = 0; i1 < titleList.size(); i1++) { XSSFCell xssfCell = xssfRow.createCell(i1); xssfCell.setCellValue(stringObjectMap.get(titleList.get(i1)).toString()); } } log.info("size:{}", maps1.size()); log.info("data:{}", JSON.toJSONString(maps1)); // 写入本地文件中 将文件保存到指定的位置 try { FileOutputStream fos = new FileOutputStream(writeDateToExcelPath+"写入本地excel.xlsx"); wb.write(fos); fos.close(); } catch (IOException e) { e.printStackTrace(); } } 3.Web端导入导出
导入Excel：在网页上导入Excel，然后处理业务逻辑
下面我们编写测试代码，导入数据Excel之后做一下基本的判断，就开始业务处理
// 根据参数选择解析文件格式数据 public ApiResult analysisPcsData(PcsDataIMportParamModel pcsDataIMportParamModel){ if (pcsDataIMportParamModel.getMultipartFile() == null) return new ApiResult(true,"请上传文件"); log.info("{}-导入PCS数据,参数：{}",DateUtil.format(new Date(),"yyyy-MM-dd HH:mm:ss"),pcsDataIMportParamModel.toString()); // 根据参数选择解析 String originalFilename = pcsDataIMportParamModel.getMultipartFile().getOriginalFilename(); assert originalFilename != null; // 解析的数据 List> = new ArrayList<>(); // 入库的数据 List pcsDataModelList = new ArrayList<>(); try { String expandName = originalFilename.substring(originalFilename.lastIndexOf(".") + 1, originalFilename.length()); if ((expandName.equals("xls") || expandName.equals("xlsx") ) && "EP".equals(pcsDataIMportParamModel.getCustomer_factory())){ mapList = ImportExcel.importExcel(pcsDataIMportParamModel.getMultipartFile(),1,2,0); pcsDataModelList.addAll(analysisExcel(mapList,pcsDataIMportParamModel)); }else { return new ApiResult(false,"上传的文件格式不正确，只支持xls，xlsx，csv!或者参数选择错误！"); } if (CollectionUtils.isEmpty(mapList)) return new ApiResult(false,"传入的文件数据为空，请检查后再上传！！"); log.info("导入PCS解析数据数量:{}",mapList.size()); log.info("PCS入库数据数量:{}",pcsDataModelList.size()); return new ApiResult(true,"导入数据成功！！文件名："+originalFilename+"，成功条数："+i); }catch (Exception e){ log.error("导入PCS数据解析失败！！！原因：{}",e.getMessage(),e); return new ApiResult(false,"导入PCS数据解析失败!!!"); } 这里区分了Excel版本
// 判断excel版本 public static List> importExcel(MultipartFile file,Integer titleNo,Integer dataNo,Integer sheetNo) throws IOException { String fileName = file.getOriginalFilename(); //获得上传的excel文件名 assert fileName != null; String fileSuffix = fileName.substring(fileName.lastIndexOf(".") + 1); //获取上传的excel文件名后缀 List> mapList = null; if ("xlsx".equals(fileSuffix)) { mapList = xlsxImportExcel(file,titleNo,dataNo,sheetNo); } else if ("xls".equals(fileSuffix)) { mapList = xlsImportExcel(file,titleNo,dataNo,sheetNo); } return mapList; } Excel2007版本处理
static List> xlsxImportExcel(MultipartFile file,Integer titleNo,Integer dataNo,Integer sheetNo) throws IOException { log.info("excel2007及以上版本"); XSSFWorkbook xwb = new XSSFWorkbook(file.getInputStream()); //获取excel工作簿 XSSFSheet xssfSheet = xwb.getSheetAt(sheetNo); //获取excel的sheet if (xssfSheet == null) { return null; } List> mapList = new ArrayList<>(); Row rowTitle = xssfSheet.getRow(titleNo); //循环获取excel每一行 for (int rowNum = dataNo; rowNum < xssfSheet.getLastRowNum() + 1; rowNum++) { XSSFRow xssfRow = xssfSheet.getRow(rowNum); if (xssfRow == null) { continue; } Map map = new HashMap<>(); //循环获取excel每一行的每一列 for (int cellNum = 0; cellNum < rowTitle.getLastCellNum(); cellNum++) { XSSFCell xssCell = xssfRow.getCell(cellNum); if (xssCell == null) { continue; } try { map.put(rowTitle.getCell(cellNum).toString(),getXSSFValue(xssCell)); }catch (Exception e){ log.error(""); } } mapList.add(map); //将excel每一行的数据封装到map对象,并将map对象添加到list } return mapList; } Excel2003版本处理
static List> xlsImportExcel(MultipartFile file,Integer titleNo,Integer dataNo,Integer sheetNo) throws IOException { log.info("excel2003版本"); Workbook wb = new HSSFWorkbook(file.getInputStream()); //获取excel工作簿 Sheet sheet = wb.getSheetAt(sheetNo); //获取excel的sheet if (sheet == null) { return null; } List> list = new ArrayList<>(); Row rowTitle = sheet.getRow(titleNo); //循环获取excel每一行 for (int rowNum = dataNo; rowNum < sheet.getLastRowNum() + 1; rowNum++) { Row row = sheet.getRow(rowNum); if (row == null) { continue; } Map map = new HashMap<>(); //循环获取excel每一行的每一列 for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) { Cell cell = row.getCell(cellNum); if (cell == null) { continue; } map.put(rowTitle.getCell(cellNum).toString(),getValue(cell)); } list.add(map); //将excel每一行的数据封装到map对象,并将map对象添加到list } return list; } Excel中各种类型值处理，包括2003和2007及以上的版本的
public static Object getXSSFValue(XSSFCell hssfCell) { Object result = null; int cellType = hssfCell.getCellType(); switch (hssfCell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: //数字 if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {// 需要对日期这一列进行设置样式，否则无法识别是日期类型还是数值类型// 默认不支持中文日期类型，需要设置成纯英语日期类型，不要包含年月日等汉字// 最好是使用这种格式 2019/10/10 0:00 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); result = sdf.format(HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue())); break; } result = hssfCell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: //Boolean result = hssfCell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: //故障 result = hssfCell.getErrorCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA: //公式 result = hssfCell.getCellFormula(); break; case HSSFCell.CELL_TYPE_BLANK: result = ""; break; default: //字符串 result = hssfCell.getStringCellValue(); } return result; } public static Object getValue(Cell cell){ //判断是否为null或空串 if (cell== null || cell.toString().trim().equals( "" )) { return null ; } String cellValue; int cellType=cell.getCellType(); switch (cellType) { case Cell.CELL_TYPE_STRING: cellValue= cell.getStringCellValue().trim(); cellValue= StringUtils.isEmpty(cellValue) ? "" : cellValue; break ; case Cell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break ; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { cellValue = DateUtil.formatDateByFormat(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss"); } else { cellValue = new DecimalFormat( "#.######" ).format(cell.getNumericCellValue()); } break ; default : cellValue = null ; break ; } return cellValue == null ? null : cellValue.replaceAll("\\xa0", ""); }导出Excel：在网页上导出Excel
// 导出excel public void invoicePartHisExcelExport(QueryParamModel queryParamModel, HttpServletResponse response){ long startTime = System.currentTimeMillis(); log.info("----开始导出Excel:"+startTime); //导出全部数据 queryParamModel.setPageSize(-1); queryParamModel.setPageSize(-1); //根据条件查询业务数据 List invoicePartHis = findInvoicePartHis(queryParamModel); String excelName = "test数据.xlsx"; String sheetName = "testSheet"; List titleList = new ArrayList<>(); titleList.add("单据号"); titleList.add("行号"); titleList.add("订单状态"); titleList.add("客户【描述】"); titleList.add("订单类型【描述】"); // 整理数据data List> datasList = new ArrayList<>(); if (invoicePartHis.size()>0){ for (TRPAInvoicePartHisVO partHisModel : invoicePartHis) { List list = new ArrayList<>(); list.add(Strings.isNotBlank(partHisModel.getInvoice_no())?partHisModel.getInvoice_no():""); list.add(Strings.isNotBlank(partHisModel.getLine_no())?partHisModel.getLine_no():""); list.add(Strings.isNotBlank(partHisModel.getOrder_status_desc())?partHisModel.getOrder_status_desc():""); list.add(Strings.isNotBlank(partHisModel.getCustomer_name())?partHisModel.getCustomer_name():""); list.add(Strings.isNotBlank(partHisModel.getOrder_type_desc())?partHisModel.getOrder_type_desc():""); datasList.add(list); } } ExportExcel.createSheetExcel(excelName,sheetName,titleList,datasList,response); log.info("----导出Excel结束,所用时间："+(System.currentTimeMillis()-startTime)); } 这里可以设置Excel的样式
public static void createSheetExcel(String excelName,String sheetName,List title, List> datas, HttpServletResponse response) { try{ int rowNum = datas.size() + 1; int colNum = title.size(); //创建工作簿 XSSFWorkbook wb = new XSSFWorkbook(); //创建一个sheet XSSFSheet sheet = wb.createSheet(); if(sheetName!=null||!"".equals(sheetName)){ wb.setSheetName(0, sheetName); } sheet.setDefaultColumnWidth(16); // 创建单元格样式 XSSFCellStyle style1 = wb.createCellStyle(); style1.setFillForegroundColor((short) 1); //设置要添加表背景颜色 style1.setFillPattern(FillPatternType.SOLID_FOREGROUND); //solid 填充 style1.setAlignment(XSSFCellStyle.ALIGN_CENTER); //文字水平居中 style1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//文字垂直居中 style1.setBorderBottom(BorderStyle.THIN); //底边框加黑 style1.setBorderLeft(BorderStyle.THIN); //左边框加黑 style1.setBorderRight(BorderStyle.THIN); // 有边框加黑 style1.setBorderTop(BorderStyle.THIN); //上边框加黑 //为单元格添加背景样式 sheet=setTitleStyle(sheet,rowNum,colNum,style1,null); //tian入数据 sheet=setSheetData(sheet,rowNum,colNum,title, datas); //将数据写入文件 writeData(excelName,response,wb); }catch (Exception e){ log.error("excel下载单个sheet报错:"+e); throw GlobalException.serverErrException(500,"下载出错"); } } 最后把流放进响应中，浏览器自动解析流，并且下载Excel
public static void writeData(String excelName, HttpServletResponse response, XSSFWorkbook wb){ OutputStream outputStream = null; try{ excelName = new String(excelName.getBytes(), StandardCharsets.UTF_8); response.setCharacterEncoding("utf-8"); response.setContentType("application/vnd.ms-excel;charset=UTF-8"); response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");//允许前端获取 response.setHeader("Content-Disposition", "attachment;filename="+ excelName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); outputStream = response.getOutputStream(); wb.write(outputStream); outputStream.flush(); outputStream.close(); }catch (Exception e){ log.error("输出excel报错:"+e); throw GlobalException.serverErrException(500,"下载出错"); }finally { if(outputStream!=null){ try{ outputStream.close(); }catch (Exception e){ log.error("流关闭报错:"+e); throw GlobalException.serverErrException(500,"下载出错"); } } } }最后实际对Excel操作，都可以根据自己的业务来封装。
来源地址：https://blog.csdn.net/Jeck_wu/article/details/128040333
