ps:该工具类支持导出excel,分页,下拉框,合并单元格
Hutool 简介
Hutool是一个轻量级的Java工具包,提供了许多实用的功能,包括字符串处理、日期时间操作、文件操作、加密解密、Excel操作等等。它的设计简洁而高效,使得开发者能够更专注于业务逻辑而不是底层实现细节。
步骤一:导入Hutool依赖
步骤二:创建Excel导出工具类
一,创建通用导出实体CommonDTO
@Data
@Schema(description = "通用导出")
public class CommonDTO {
// @NotEmpty(message = "表头不能为空")
@Schema(description = "表头(字段-名称对应关系)")
Map<String,String> header;
// @NotEmpty(message = "数据不能为空")
@Schema(description = "数据(字段-值对应关系)")
List<Map<String, Object>> data;
// @NotBlank(message = "文件名不能为空")
@Schema(description = "文件名")
String name;
@Schema(description = "下拉框")
List<dropDown> dropDown;
//设置需要合并单元格信息
@Schema(description = "合并单元格")
List<mergeCell> mergeCell;
//分页
@Schema(description = "分页")
List<pageBO> pageBo;
}
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
2,创建下拉框实体dropDown
3,创建合并单元格实体mergeCell
4,创建分页实体pageBO
5,创建导出到一页工具类
@SneakyThrows
public void export(@RequestBody @Valid CommonDTO commonDTO, HttpServletResponse response) {
ExcelWriter writer = ExcelUtil.getWriter();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
writer.renameSheet(0, "学生表");
writer.renameSheet(0, "班级表");
//选定sheet页
writer.setSheet("学生表");
//设置表头样式
writer.setHeaderAlias(commonDTO.getHeader());
String[] headString = new String[]{};
//遍历 commonDTO.header.keySet()将数据添加到headString
for (String key : commonDTO.getHeader().keySet()) {
headString = ArrayUtils.add(headString, commonDTO.getHeader().get(key));
}
List<String> headerList = CollUtil.newArrayList(headString);
writer.writeHeadRow(headerList);
//设置表头样式
writer.getHeadCellStyle().setFont(StyleUtil.createFont(writer.getWorkbook(), (short) 0, (short) 12, "微软雅黑"));
//设置内容样式
writer.getStyleSet().setFont((short) 0, (short) 10, "微软雅黑", Boolean.TRUE);
// 合并单元格
if (commonDTO.getName() != null) {
//设置响应头
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(commonDTO.getName(), "UTF-8") + ".xls");
} else {
//设置响应头
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode("默认文件名", "UTF-8") + ".xls");
}
//合并单元格
if (commonDTO.getMergeCell() != null && commonDTO.getMergeCell().size() > 0) {
for (int i = 0; i < commonDTO.getMergeCell().size(); i++) {
writer.merge(commonDTO.getMergeCell().get(i).getFirstRow(),
commonDTO.getMergeCell().get(i).getLastRow(),
commonDTO.getMergeCell().get(i).getFirstCol(),
commonDTO.getMergeCell().get(i).getLastCol(),
commonDTO.getMergeCell().get(i).getMergeCellInfo(), Boolean.TRUE);
}
}
//设置下拉框
Sheet sheet = writer.getSheet();
if (commonDTO.getDropDown() != null && commonDTO.getDropDown().size() > 0) {
for (int i = 0; i < commonDTO.getDropDown().size(); i++) {
dropDown dropDown = commonDTO.getDropDown().get(i);
CellRangeAddressList addressList = new CellRangeAddressList(dropDown.getFirstRow(), dropDown.getLastRow(), dropDown.getFirstCol(), dropDown.getLastCol()); // 指定单元格范围,这里是第一行第一列
// 创建下拉框数据
DataValidationHelper validationHelper = sheet.getDataValidationHelper();
DataValidationConstraint dvConstraint = validationHelper.createExplicitListConstraint(dropDown.dataValidationValues); // 设置下拉框选项
DataValidation dataValidation = validationHelper.createValidation(dvConstraint, addressList); // 创建数据验证对象
sheet.addValidationData(dataValidation); // 添加验证对象到工作表
}
}
//写入数据
writer.write(commonDTO.getData(), true);
//设置自适应列宽
writer.autoSizeColumnAll();
writer.flush(response.getOutputStream(), true);
// 关闭writer,释放内存
writer.close();
//此处记得关闭输出Servlet流
IoUtil.close(response.getOutputStream());
}
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
6,创建分页工具类
因为分页每一页分页数据存放不一样,暂时未做出通用的
@SneakyThrows
public void export2(@RequestBody @Valid CommonDTO commonDTO, HttpServletResponse response) {
ExcelWriter writer = ExcelUtil.getWriter();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
List<pageBO> pageBo1 = commonDTO.getPageBo();
for (int j = 0; j < pageBo1.size(); j++) {
pageBO pageBO = pageBo1.get(j);
int sheetIndex = j;
//选定sheet页
writer.setSheet(pageBo1.get(sheetIndex).getPageNameValue());
//设置表头样式
writer.setHeaderAlias(commonDTO.getHeader());
String[] headString = new String[]{};
//遍历 commonDTO.header.keySet()将数据添加到headString
for (String key : commonDTO.getHeader().keySet()) {
headString = ArrayUtils.add(headString, commonDTO.getHeader().get(key));
}
List<String> headerList = CollUtil.newArrayList(headString);
writer.writeHeadRow(headerList);
//设置表头样式
writer.getHeadCellStyle().setFont(StyleUtil.createFont(writer.getWorkbook(), (short) 0, (short) 12, "微软雅黑"));
//设置内容样式
writer.getStyleSet().setFont((short) 0, (short) 10, "微软雅黑", Boolean.TRUE);
// 合并单元格
if (commonDTO.getName() != null) {
// writer.merge(0,0,0,1, "基本信息", Boolean.TRUE);
// writer.merge(0,0,2,3, "信息", Boolean.TRUE);
//设置响应头
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode(commonDTO.getName(), "UTF-8") + ".xls");
} else {
//设置响应头
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + URLEncoder.encode("默认文件名", "UTF-8") + ".xls");
}
//合并单元格
if (commonDTO.getMergeCell() != null && commonDTO.getMergeCell().size() > 0) {
for (int i = 0; i < commonDTO.getMergeCell().size(); i++) {
writer.merge(commonDTO.getMergeCell().get(i).getFirstRow(),
commonDTO.getMergeCell().get(i).getLastRow(),
commonDTO.getMergeCell().get(i).getFirstCol(),
commonDTO.getMergeCell().get(i).getLastCol(),
commonDTO.getMergeCell().get(i).getMergeCellInfo(), Boolean.TRUE);
}
}
//设置下拉框
Sheet sheet = writer.getSheet();
if (commonDTO.getDropDown() != null && commonDTO.getDropDown().size() > 0) {
for (int i = 0; i < commonDTO.getDropDown().size(); i++) {
dropDown dropDown = commonDTO.getDropDown().get(i);
CellRangeAddressList addressList = new CellRangeAddressList(dropDown.getFirstRow(), dropDown.getLastRow(), dropDown.getFirstCol(), dropDown.getLastCol()); // 指定单元格范围,这里是第一行第一列
// 创建下拉框数据
DataValidationHelper validationHelper = sheet.getDataValidationHelper();
DataValidationConstraint dvConstraint = validationHelper.createExplicitListConstraint(dropDown.dataValidationValues); // 设置下拉框选项
DataValidation dataValidation = validationHelper.createValidation(dvConstraint, addressList); // 创建数据验证对象
sheet.addValidationData(dataValidation); // 添加验证对象到工作表
}
}
List<Map<String, Object>> data = commonDTO.getData();
List<Map<String, Object>> sheetData = new ArrayList<>();
for (Map<String, Object> datum : data) {
String sex = datum.get("sex") + "";
if (pageBO.getPageNameValue().equals(sex)) {
sheetData.add(datum);
}
}
writer.write(sheetData, true);
//设置自适应列宽
writer.autoSizeColumnAll();
}
//删除sheet1,因为sheet1为空
writer.getWorkbook().removeSheetAt(0);
writer.flush(response.getOutputStream(), true);
// 关闭writer,释放内存
writer.close();
//此处记得关闭输出Servlet流
IoUtil.close(response.getOutputStream());
}
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
步骤三:创建测试类
//测试生成文档
@PostMapping(value = "/test1", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
public void test1(HttpServletResponse response) {
CommonDTO commonDTO = new CommonDTO();
commonDTO.setName("测试");
LinkedHashMap<String, String> StringHashMap = new LinkedHashMap<>();
StringHashMap.put("name", "姓名");
StringHashMap.put("age", "年龄");
StringHashMap.put("sex", "性别");
StringHashMap.put("address", "地址");
commonDTO.setHeader(StringHashMap);
ArrayList<Map<String, Object>> arrayList = new ArrayList<>();
for (int i = 0; i < 5; i++) {
Map<String, Object> HashMap = new HashMap<>();
HashMap.put("name", "张三" + i);
HashMap.put("age", "20" + i);
HashMap.put("sex", "男");
HashMap.put("address", "北京" + i);
arrayList.add(HashMap);
}
for (int i = 0; i < 5; i++) {
Map<String, Object> HashMap = new HashMap<>();
HashMap.put("name", "李四" + i);
HashMap.put("age", "20" + i);
HashMap.put("sex", "女");
HashMap.put("address", "北京" + i);
arrayList.add(HashMap);
}
// for (int i = 0; i < 1; i++) {
// Map<String, Object> HashMap = new HashMap<>();
// HashMap.put("name","");
//
// HashMap.put("age","");
//
// HashMap.put("sex","");
//
// HashMap.put("address","");
//
// arrayList.add(HashMap);
// }
// 设置下拉框值
List<dropDown> dropDownList = new ArrayList<>();
dropDown dropDown = new dropDown();
dropDown.firstRow = 1;
// 设置下拉框的结束行
dropDown.lastRow = 500;
// 设置下拉框的起始列
dropDown.firstCol = 2;
dropDown.lastCol = 2;
dropDown.dataValidationValues = new String[]{"男", "女"};
dropDownList.add(dropDown);
dropDown dropDown2 = new dropDown();
dropDown2.firstRow = 1;
// 设置下拉框的结束行
dropDown2.lastRow = 500;
// 设置下拉框的起始列
dropDown2.firstCol = 3;
dropDown2.lastCol = 3;
dropDown2.dataValidationValues = new String[]{"北京", "上海"};
dropDownList.add(dropDown2);
commonDTO.setDropDown(dropDownList);
//设置合并单元格
List<mergeCell> mergeCellList = new ArrayList<>();
mergeCell mergeCell = new mergeCell();
mergeCell.firstRow = 0;
mergeCell.lastRow = 0;
mergeCell.firstCol = 0;
mergeCell.lastCol = 1;
mergeCell.mergeCellInfo = "合并单元格标题1";
mergeCellList.add(mergeCell);
mergeCell mergeCell2 = new mergeCell();
mergeCell2.firstRow = 0;
mergeCell2.lastRow = 0;
mergeCell2.firstCol = 2;
mergeCell2.lastCol = 3;
mergeCell2.mergeCellInfo = "合并单元格标题2";
mergeCellList.add(mergeCell2);
commonDTO.setMergeCell(mergeCellList);
commonDTO.setData(arrayList);
List<pageBO> pageBOList = new ArrayList<>();
pageBO pageBO = new pageBO();
pageBO.setPageNameRow(0);
pageBO.setPageNameValue("男");
pageBOList.add(pageBO);
pageBO pageBO2 = new pageBO();
pageBO2.setPageNameRow(1);
pageBO2.setPageNameValue("女");
pageBOList.add(pageBO2);
commonDTO.setPageBo(pageBOList);
// export(commonDTO,response);
export2(commonDTO, response);
}
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
- 108.
- 109.
- 110.
- 111.
- 112.
- 113.
- 114.
- 115.
- 116.
- 117.
- 118.
- 119.
- 120.
步骤四:postman模拟
结论
通过 Hutool 提供的强大功能,我们可以轻松创建一个通用的导出 Excel 工具类,实现数据导出到 Excel 文件的功能。这种做法不仅提高了开发效率,还使得代码更加清晰和可维护。
希望本文对你有所帮助,如有任何问题或建议,请随时留言!