excel表导出

dto:查询条件所在的类

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

service方法

  @Transactionalpublic 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<Map<String, Object>> resultList = query.getResultList();List<List<Object>> rows = new ArrayList<>();SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");int i = 0; //显示在台账上的序号Integer j = 0; //判断是不是同一个numberfor (Map<String, Object> objectMap : resultList) {List<Object> list = new ArrayList<>();//例： list.add(objectMap.get("name") == null ? "" : objectMap.get("name").toString());list.add(i); list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString().substring(0, 7)); list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString());  list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString());  list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString());  list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString());  list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString());  list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString());  list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString());  list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString());  list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString());  list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString());  list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString());  list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString());  list.add(objectMap.get("字段名") == null ? "" : objectMap.get("字段名").toString()); ....rows.add(list);}//数据从第几行开始渲染writer.setCurrentRow(4);writer.write(rows, false);response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");response.setHeader("Content-Disposition", "attachment;filename=file.xlsx");ServletOutputStream out = response.getOutputStream();file.deleteOnExit();writer.flush(out, true);IoUtil.close(out);}