ps:该工具类支持导出excel,分页,下拉框,合并单元格
Hutool 简介
Hutool是一个轻量级的Java工具包,提供了许多实用的功能,包括字符串处理、日期时间操作、文件操作、加密解密、Excel操作等等。它的设计简洁而高效,使得开发者能够更专注于业务逻辑而不是底层实现细节。
步骤一:导入Hutool依赖
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.x.x</version> <!-- 使用最新版本 -->
</dependency>
步骤二:创建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;
}
2,创建下拉框实体dropDown
@Data
public class dropDown {
// 设置下拉框的起始行
int firstRow;
// 设置下拉框的结束行
int lastRow;
// 设置下拉框的起始列
int firstCol;
int lastCol;
// 设置下拉框的值
String[] dataValidationValues;
}
3,创建合并单元格实体mergeCell
@Data
public class mergeCell {
int firstRow;
// 设置合并单元格的结束行
int lastRow;
// 设置合并单元格的起始列
int firstCol;
int lastCol;
// 设置合并单元格的值
String mergeCellInfo;
}
4,创建分页实体pageBO
@Data
public class pageBO {
// 设置sheet页码
int pageNameRow;
// 设置sheet页名称
String pageNameValue;
}
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());
}
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());
}
步骤三:创建测试类
//测试生成文档
@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);
}
步骤四:postman模拟
结论
通过 Hutool 提供的强大功能,我们可以轻松创建一个通用的导出 Excel 工具类,实现数据导出到 Excel 文件的功能。这种做法不仅提高了开发效率,还使得代码更加清晰和可维护。
希望本文对你有所帮助,如有任何问题或建议,请随时留言!