前提需求

最近公司要求写一些记录的表格，并且带有导出功能。再深入学习后，表格的底层其实就是list遍历塞值，导出功能的话可以由前端，后端实现，但技多不压身嘛，这里我自己就写了后端的导出功能，和大家分享一下。

代码实现

基础版：只有列的表头，没有合并单元格的情况。

效果如下：

示例代码实现：

public void exportCureList(HttpServletResponse response,@RequestBody CureListRo ro) throws Exception{//查找需要填充的数据listList list=...;if (ObjectUtil.isEmpty(list)){throw new BusinessException("数据为空无法导出EXCEL");}List<List<Object>> rows = new ArrayList<>();//固定第一行表头List<Object> row = new ArrayList<>();row.add("患者姓名");row.add("手机号");row.add("门诊号");row.add("开单科室");row.add("开单医生");row.add("治疗师");row.add("治疗项目");row.add("是否签名");row.add("治疗开始");row.add("治疗完成");row.add("治疗项目");row.add("合计金额");rows.add(row);//填充剩余数据list.forEach(a->{List<Object> data= new ArrayList<>();data.add(a.getName());data.add(a.getPhone());data.add(a.getOutpatientId());data.add(a.getDepartmentName());data.add(a.getDoctor());data.add(a.getTherapists());data.add(a.getItems());data.add(a.getIsSign());data.add(a.getCureStartTime());data.add(a.getCureEndTime());data.add(a.getItems());data.add(a.getTotalPrice());rows.add(data);});//在内存操作，写到浏览器ExcelWriter writer= ExcelUtil.getWriter(true);//默认配置writer.write(rows,true);response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("治疗记录", "UTF8") + ".xlsx");ServletOutputStream outputStream= response.getOutputStream();//将Writer刷新到OutPutwriter.flush(outputStream,true);outputStream.close();writer.close();}

高阶版：表头存在斜杠，并且分层级有合并单元格。

示例代码实现：

public void exposeGetPerDepartmentOpenBillListNew(HttpServletResponse response,String startTime, String endTime, String departmentId, String doctorId, String tsName) throws IOException {//查询出的数据Vo vo=....;XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet("sheetName");CellRangeAddress range = new CellRangeAddress(0, 1, 0, 0);sheet.addMergedRegion(range);sheet.setColumnWidth(0, 5000);Row row1 = sheet.createRow(0);row1.setHeight((short) 1000);
//画线(由左上到右下的斜线)  在A1的第一个cell（单位  分类）加入一条对角线Row row = sheet.getRow(0);Cell cell0 = row.createCell(0);cell0.setCellValue("开单医生    治疗项目");XSSFRow row2 = sheet.createRow(1);List<BuAchievementsHeaderVo> header = vo.getHeader();Map<String,Integer> indexMap =new HashMap<>();int index =0 ;for (BuAchievementsHeaderVo m:header){indexMap.put(m.getName(),index);index++;}for (int i = 0;i<header.size();i++){sheet.addMergedRegion(new CellRangeAddress(0, 0, i*3+1, i*3+3));row.createCell(i*3+1).setCellValue(header.get(i).getName());row2.createCell(i*3+1).setCellValue("开");row2.createCell(i*3+2).setCellValue("做");row2.createCell(i*3+3).setCellValue("剩");}CreationHelper helper = workbook.getCreationHelper();XSSFDrawing xssfDrawing = sheet.createDrawingPatriarch();ClientAnchor anchor = helper.createClientAnchor();
//设置斜线的开始位置,我这里是从第0行到第1行，从第0列到第1列anchor.setCol1(0);anchor.setCol2(1);anchor.setRow1(0);anchor.setRow2(2);XSSFSimpleShape simpleShape = xssfDrawing.createSimpleShape((XSSFClientAnchor) anchor);
//设置形状类型未线型simpleShape.setShapeType(ShapeTypes.LINE);
//设置线宽simpleShape.setLineWidth(0.5);
//设置线的风格simpleShape.setLineStyle(0);
//设置线的颜色simpleShape.setLineStyleColor(0,0,0);
//撒数据//int[] xys1 = { 112, 83 };//drawLine(sheet, row, 1, 3, 110, 83, xys1);List<BuAchievementsDataVo> data = vo.getData();for (int i = 0;i<data.size();i++){XSSFRow row3 = sheet.createRow(i + 2);row3.createCell(0).setCellValue(data.get(i).getDoctor());List<BuAchievementsItemVo> list = data.get(i).getList();for (int j = 0 ; j<list.size();j++){Integer ind = indexMap.get(list.get(j).getName());row3.createCell(ind*3+1).setCellValue(list.get(j).getCount().toString());row3.createCell(ind*3+2).setCellValue(list.get(j).getTreCount().toString());row3.createCell(ind*3+3).setCellValue(list.get(j).getSurCount().toString());}}response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("治疗记录", "UTF8") + ".xlsx");OutputStream outputStream= response.getOutputStream();workbook.write(outputStream);outputStream.flush();outputStream.close();}