excel表导出

dto:查询条件所在的类

  @GetMapping(value ="/downloadProject")
  @ApiOperation("导出台账数据")
  @AnonymousAccess
  public void queryDownload(Dto dto, HttpServletResponse response) throws IOException, ParseException {
    service.queryDownload(byPageDto, response);
  }

service方法

  @Transactional
  public void queryDownload(Dto dto, HttpServletResponse response) throws IOException, ParseException {
    String tempPath = System.getProperty("java.io.tmpdir") + IdUtil.fastSimpleUUID() + ".xlsx";
    File file = new File(tempPath);
    MyExcelWriter writer = MyExcelWriter.getBigWriter();
    //重构字体格式等
    Font font = writer.createFont();
    font.setFontHeightInPoints((short) 15);
    font.setBold(true);
    CellStyle headCellStyle = writer.getHeadCellStyle();
    headCellStyle.setFont(font);
    //表头样式 参数：都是从0开始算 首行 尾行 首列 尾列 内容 是否使用样式
    writer.merge(32, "表名称");
    writer.merge(1, 3, 0, 0, "第一列", true);
    writer.merge(1, 3, 1, 1, "第二列", true);
    writer.merge(1, 3, 2, 2, "第三列", true);
    writer.merge(1, 3, 3, 3, "第四列", true);
    writer.merge(1, 1, 4, 8, "第五列", true);
    writer.merge(2, 3, 4, 4, "第六列", true);
    writer.merge(2, 3, 5, 5, "第七列", true);
    writer.merge(2, 3, 6, 6, "第八列", true);
    writer.merge(2, 3, 7, 7, "第九列", true);
    writer.merge(2, 3, 8, 8, "第十列", true);
    writer.merge(1, 1, 9, 13, "第十一列", true);
    writer.merge(2, 3, 9, 9, "第十二列", true);
    writer.merge(2, 3, 10, 10, "第十三列", true);
    writer.merge(2, 3, 11, 11, "第十四列", true);
    writer.merge(2, 3, 12, 12, "第十五列", true);
    writer.merge(2, 3, 13, 13, "第十六列", true);
    writer.merge(1, 3, 14, 14, "第十七列", true);
    writer.merge(1, 3, 15, 15, "第十八列", true);
    writer.merge(1, 3, 16, 16, "第十九列", true);
    writer.merge(1, 3, 17, 17, "第二十列", true);
    writer.merge(1, 1, 18, 19, "第二十一列", true);
    writer.merge(2, 3, 18, 18, "第二十二列", true);
    writer.merge(2, 3, 19, 19, "第二十三列", true);
    writer.merge(1, 1, 20, 26, "第二十四列", true);
    writer.merge(2, 3, 20, 20, "第二十五列", true);
    writer.merge(2, 3, 21, 21, "第二十六列", true);
    writer.merge(2, 3, 22, 22, "第二十七列", true);
    writer.merge(2, 2, 23, 26, "第二十八列", true);
    writer.writeCellValue(23, 3, "第二十九列"); //单个没法合并，用这种方法定义
    writer.writeCellValue(24, 3, "第三十列"); //单个没法合并，用这种方法定义
    writer.writeCellValue(25, 3, "第三十一列"); //单个没法合并，用这种方法定义
    writer.writeCellValue(26, 3, "第三十二列"); //单个没法合并，用这种方法定义
    writer.merge(1, 3, 27, 27, "第三十三列", true);
    writer.merge(1, 3, 28, 28, "第三十四列", true);
    writer.merge(1, 3, 29, 29, "第三十五列", true);
    writer.merge(1, 3, 30, 30, "第三十六列", true);
    writer.merge(1, 3, 31, 31, "第三十七列", true);
    writer.merge(1, 3, 32, 32, "第三十八列", true);
 
    //使用writeCellValue方法无法和merge一样传递参数true进行和表头同一样式,通过下面方法可以实现
    writer.getCell(23, 3).setCellStyle(headCellStyle);
    writer.getCell(24, 3).setCellStyle(headCellStyle);
    writer.getCell(25, 3).setCellStyle(headCellStyle);
    writer.getCell(26, 3).setCellStyle(headCellStyle);
 
    // 调整列宽
    writer.autoSizeColumnAll();
 
    Query query = getQuery(dto);
    List> resultList = query.getResultList();
 
    List> rows = new ArrayList<>();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    int i = 0; //显示在台账上的序号
    Integer j = 0; //判断是不是同一个number
    for (Map objectMap : resultList) {
      List