前提需求

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

代码实现

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

效果如下：

示例代码实现：

    public void exportCureList(HttpServletResponse response,@RequestBody CureListRo ro) throws Exception{
        //查找需要填充的数据list
        List 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刷新到OutPut
        writer.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();

    }