首先将数据下载成集合对象,存储在ApiResponse中
然后设置excel表格的格式
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.3.0</version>
</dependency>
import org.apache.poi.ss.usermodel.*;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
private void downloadExcel(ApiResponse<List<Student>> data, HttpServletResponse response) {
// 导出参数配置
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
try {
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("temp.xlsx", "utf-8"));
} catch (UnsupportedEncodingException e) {
log.error("urlencode 错误:", e);
}
ExportParams exportParams = new ExportParams("表名", "Sheet1", ExcelType.XSSF);
List<StudentExcelVo> list = JSONObject.parseArray(JSON.toJSONString(data.getData()), StudentExcelVo.class);
// 获得Excel对象
Workbook sheets = ExcelExportUtil.exportExcel(exportParams, StudentExcelVo.class, list);
//开始,设置某一行的背景色
Sheet sheet1 = sheets.getSheetAt(0);
Sheet sheet = sheets.createSheet();
//这个map集合是由于,给第三行设置了填充色,但是将本来的数据给覆盖掉了,所以又加上的数据
Map<Integer,String> stringMap=new HashMap<>();
stringMap.put(0,"xxxxx");
stringMap.put(1,"11111");
stringMap.put(2,"2222");
stringMap.put(3,"3333");
......
Row row = sheet1.createRow(2);//第几行
//加上填充色的列数,不然会造成整行都会有填充色,样式会很难看
for (int j = 0; j <4; j++) {
//设置该行的行高和列宽
row.setHeightInPoints(99.7f);
//单元格的格式
CellStyle style = sheets.createCellStyle();
//垂直居中
style.setVerticalAlignment(style.getVerticalAlignmentEnum().CENTER);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
style.setFillForegroundColor((short)(5));//填充色,数字代表的是色号
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setWrapText(true);//自动换行
//获得列数
Cell cell = row.createCell(j);
//由于原有的数据被覆盖了,所以在此又加上去了,根据每列对应加上去
cell.setCellValue(stringMap.get(j));
//给每列加上格式
cell.setCellStyle(style);
}
//结束,设置某一行的背景色
// 输出
try (ServletOutputStream outputStream = response.getOutputStream()) {
// 写入文件
sheets.write(outputStream);
} catch (IOException e) {
log.error("下载错误: ", e);
}
}
颜色