【Hutool】:使用hutool实现复杂excel导出功能
【Hutool】:使用hutool实现复杂excel导出功能
转自:https://blog.csdn.net/weixin_45511500/article/details/118884656
引入依赖
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
<scope>provided</scope>
</dependency>
编写pojo类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
/**
* 学号
*/
private String sno;
/**
* 姓名
*/
private String name;
/**
* 年龄
*/
private Integer age;
/**
* 性别
*/
private String gender;
/**
* 籍贯
*/
private String nativePlace;
/**
* 入学时间
*/
@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
private Date enrollmentTime;
private BigDecimal money;
}
编写实现类
@PostMapping("user_export_excel")
@ResponseBody
public void exportExcel(HttpServletResponse response) throws UnsupportedEncodingException, ParseException {
// 设置响应类型
response.setContentType("application/vnd.ms-excel");
// 设置字符编码
response.setCharacterEncoding("utf-8");
// 设置响应头信息
response.setHeader("Content-disposition",
"attachment;filename*=utf-8''" + URLEncoder.encode("学生花名册", "UTF-8") + ".xlsx");
List<Student> studentList = new ArrayList<Student>() {
{
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
add(new Student("1001", "张三", 23, "男", "陕西西安", dateFormat.parse("2020-09-01"), BigDecimal.valueOf(22.33)));
add(new Student("1002", "李四", 22, "女", "陕西渭南", dateFormat.parse("2020-09-01"),BigDecimal.valueOf(11.99)));
}
};
// 写入文件
ExcelWriter writer = ExcelUtil.getWriter();
// 设置合并单元格
// writer.merge(11, "BOM报价单", false);
// writer.merge(3,"您上传的BOM", false);
// writer.merge(1, 1 ,4,9,"商城报价单", false);
// 设置标题一样式
CellStyle cellStyle1 = writer.createCellStyle();
cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle1.setAlignment(HorizontalAlignment.CENTER);
cellStyle1.setBorderLeft(writer.getCellStyle().getBorderLeft());
cellStyle1.setBorderRight(writer.getCellStyle().getBorderRight());
cellStyle1.setBorderBottom(writer.getCellStyle().getBorderBottom());
Font font = writer.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 12);
font.setFontName("微软雅黑");
cellStyle1.setFont(font);
// 设置“总价” 单元格样式
CellStyle cellStyle = writer.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GOLD.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置垂直居中和水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
//设置边框
cellStyle.setBorderRight(writer.getCellStyle().getBorderRight());
cellStyle.setBorderBottom(writer.getCellStyle().getBorderBottom());
// 标题一
writer.merge(0,0,0,11, "BOM报价单", cellStyle1);
// 标题二
writer.merge(1,1,0,3,"您上传的BOM", cellStyle1);
writer.merge(1, 1 ,4,9,"商城报价单", cellStyle1);
writer.merge(1,1,10,11,"总价(含税): "+ " 99.99",cellStyle);
// 跳过前面两行(为了设置标题样式)原始的设置标题方法,不可以,例:merge(3, "标题名", false);
writer.passRows(2);
// 设置高度(行row)
writer.setRowHeight(0, 50);
writer.setRowHeight(1, 30);
// 设置导出信息的表头
writer.addHeaderAlias("sno", "学号");
writer.addHeaderAlias("name", "姓名");
writer.addHeaderAlias("age", "年龄");
writer.addHeaderAlias("gender", "性别");
writer.addHeaderAlias("nativePlace", "籍贯");
writer.addHeaderAlias("enrollmentTime", "入学时间");
writer.addHeaderAlias("money", "金钱");
// 设置列宽(Colum)
writer.setColumnWidth(5, 20);
writer.setColumnWidth(10, 10);
writer.setColumnWidth(11, 10);
// 设置导出表头样式,但不包括合并单元格
CellStyle headCellStyle = writer.getHeadCellStyle();
headCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIME.getIndex());
headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 写入
writer.write(studentList, true);
// 主要针对自定义表头设置 行高(必须要写入完成后才可以设置)
for (int i = 2; i < studentList.size(); i++) {
writer.setRowHeight(i,20);
}
try {
writer.flush(response.getOutputStream(), true);
} catch (IOException e) {
e.printStackTrace();
} finally {
writer.close();
}
}
效果图


浙公网安备 33010602011771号