1.所需依赖
注意:hutool、poi版本不匹配会导致异常
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.12.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.4</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.2</version>
</dependency>
2.Excel文件创建、内容导入
@Data
public class User {
private String id;
private String name;
private Integer age;
private Date birthday;
public User() {
}
public User(String id, String name, Integer age, Date birthday) {
this.id = id;
this.name = name;
this.age = age;
this.birthday = birthday;
}
}
#控制列顺序,需要使用LinkedHashMap,不能使用HashMap
Map<String, String> headers = new LinkedHashMap<>();
headers.put("id","编号");
headers.put("name","姓名");
headers.put("age","年龄");
headers.put("birthday","出生日期");
ExcelWriter writer = ExcelUtil.getWriter("d:/excelTest.xls");
#设置表格头部别名
writer.setHeaderAlias(headers);
writer.setOnlyAlias(true);
List<User> userList = new ArrayList<>();
userList.add(new User("1","小明",20,new Date()));
userList.add(new User("2","小华",20,new Date()));
writer.write(userList,true);
#必须使用close或flush文件才能被创建、数据才能被导入
writer.close();
3.表格样式设置
Workbook workbook = writer.getWorkbook();
#设置全局样式
StyleSet styleSet = new StyleSet(workbook);
#设置字体样式
Font font = workbook.createFont();
#是否加粗
font.setBold(true);
#颜色
font.setColor(Font.COLOR_RED);
#字体
font.setFontName("微软雅黑");
#斜体
font.setItalic(true);
styleSet.setFont(font,true);
#设置全局行样式
CellStyle cellStyle = styleSet.getCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
writer.setStyleSet(styleSet);
#头部样式
CellStyle headCellStyle = styleSet.getHeadCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
headCellStyle.setFont(font);