使用 hutool包进行excel导入导出
依赖
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.5.6</version>
</dependency>
<!--Export as Excel，确保 POI 版本 ≥4.1.2，否则可能无法正确计算公式-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
导入
/**
* excel导入
*/
@PostMapping("/upload")
@ApiOperation("excel导入")
public Result upload(@RequestParam("file") MultipartFile file) throws IOException {
// 通过文件获取输入流
InputStream in = file.getInputStream();
// 借助hutool读取
ExcelReader reader = ExcelUtil.getReader(in);
List<List<Object>> list = reader.read(1); // 从第二行开始读
// 创建一个List集合
List<User> users = CollUtil.newArrayList();
List<List<Object>> data = new ArrayList<>(0);
// 遍历
for (List<Object> row : list) {
User user = new User();
// 转换成字符串
user.setUsername(row.get(0).toString());
user.setPassword(row.get(1).toString());
user.setNickname(row.get(2).toString());
user.setEmail(row.get(3).toString());
user.setPhone(row.get(4).toString());
user.setAddress(row.get(5).toString());
// 遍历完一个添加一个
users.add(user);
data.add(row);
}
// 调用mapper添加到数据库
boolean flag = userService.addAll(users);
// 输出到本地文件
ExcelWriter writer = ExcelUtil.getWriter(new File("C:\\Users\\zg\\Downloads\\export.xlsx"));
writer.writeHeadRow(Arrays.asList("账号", "密码", "用户名称", "邮箱", "电话"));
for (List<Object> row : data) {
// 写入整行
writer.writeRow(row);
}
// 关闭流
writer.flush();
writer.close();
return flag ? Result.ok() : Result.failed();
}
使用 List<List<Object>> list = reader.read(1); 导入时候，excel 如果有多个相邻都为空的会识别成一个空的问题
解决：使用 reader.readAll(); 方法
List<Map<String, Object>> maps = reader.readAll();
map读取为：{姓名=李四, 年龄=null, 性别=null}
使用实体类，表头和字段名一致即可
List<User> list = reader.readAll(User.class);
使用实体类注解导入
创建自定义注解，用于标记实体类字段对应的 Excel 列名
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Excel {
String name(); // Excel 列名（支持中文）
}
在实体类字段上添加 @Excel注解，指定对应的 Excel 列名
@Data
public class User {
@Excel(name = "姓名")
private String name;
@Excel(name = "年龄")
private Integer age;
@Excel(name = "邮箱")
private String email;
}
编写工具类，通过反射解析注解并生成标题别名映射
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ExcelMapper {
/**
* 自动映射 Excel 列到实体类字段
* @param reader ExcelReader 对象
* @param clazz 实体类类型
* @param <T> 实体类泛型
* @return 映射后的 ExcelReader
*/
public static <T> ExcelReader autoMap(ExcelReader reader, Class<T> clazz) {
// 遍历实体类字段，解析注解
for (Field field : clazz.getDeclaredFields()) {
if (field.isAnnotationPresent(Excel.class)) {
Excel excel = field.getAnnotation(Excel.class);
String columnName = excel.name(); // Excel 列名
String fieldName = field.getName(); // 实体字段名
// 设置标题别名
reader.addHeaderAlias(columnName,fieldName);
}
}
return reader;
}
}
使用
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import java.util.List;
@RestController
public class UserController {
@PostMapping("/importUsers")
public Result importUsers(@RequestParam("file") MultipartFile file) {
try (InputStream is = file.getInputStream()) {
// 创建 ExcelReader
ExcelReader reader = ExcelUtil.getReader(is);
// 自动映射注解
ExcelMapper.autoMap(reader, User.class);
// 读取数据到实体列表
List<User> userList = reader.readAll(User.class);
// 保存到数据库
userService.saveBatch(userList);
return Result.success("导入成功，共 " + userList.size() + " 条数据");
} catch (Exception e) {
log.error("导入失败", e);
return Result.failed("导入失败：" + e.getMessage());
}
}
}
导出
根据注解导出
注解：
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel {
String name() default ""; // 表头名称
int orderNum() default 0; // 排序序号
String headerColor() default "#D3D3D3"; // 表头背景色（十六进制）
String dateFormat() default "yyyy-MM-dd"; // 格式化时间
}
实体
public class User {
@Excel(name = "用户ID", orderNum = 1, headerColor = "#D3D3D3")
private Long userId;
@Excel(name = "用户名", orderNum = 2)
private String userName;
@Excel(name = "创建时间", orderNum = 3, dateFormat = "yyyy-MM-dd")
private LocalDateTime createTime;
}
导出工具类
public class ExcelExporter {
public static <T> void export(HttpServletResponse response, List<T> data, Class<T> clazz) throws IOException {
ExcelWriter writer = ExcelUtil.getWriter(true);
Sheet sheet = writer.getSheet();
sheet.setSheetName("Sheet1"); // 修改 Sheet 名称
// 获取所有带@Excel注解的字段并按顺序排序
List<Field> fields = Arrays.stream(clazz.getDeclaredFields())
.filter(f -> f.isAnnotationPresent(Excel.class))
.sorted(Comparator.comparingInt(f -> f.getAnnotation(Excel.class).orderNum()))
.collect(Collectors.toList());
// 创建表头行
Row headerRow = sheet.createRow(0);
XSSFCellStyle headerStyle = createHeaderStyle(writer); // 创建表头样式
for (int i = 0; i < fields.size(); i++) {
Field field = fields.get(i);
Excel excel = field.getAnnotation(Excel.class);
Cell cell = headerRow.createCell(i);
// 设置表头文本
cell.setCellValue(excel.name());
cell.setCellStyle(headerStyle); // 应用表头样式
// 设置表头背景色
XSSFCellStyle style = (XSSFCellStyle) writer.createCellStyle();
int r = Integer.parseInt(hexColor.substring(1, 3), 16);
int g = Integer.parseInt(hexColor.substring(3, 5), 16);
int b = Integer.parseInt(hexColor.substring(5, 7), 16);
XSSFColor color = new XSSFColor(
new byte[]{
(byte) (r & 0xFF),
(byte) (g & 0xFF),
(byte) (b & 0xFF)
},
new DefaultIndexedColorMap()
);
style.setFillForegroundColor(color);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(style);
}
// 写入数据
List<Map<String, Object>> mapList = convertToMapList(data,clazz);
writer.write(mapList, true);
// 设置数据行样式
XSSFCellStyle dataStyle = createDataStyle(writer);
for (Row row : sheet) {
if (row.getRowNum() == 0) continue; // 跳过表头
for (Cell cell : row) {
cell.setCellStyle(dataStyle);
}
}
// 调整列宽
adjustColumnWidth(writer);
// 输出响应
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment;filename=data.xlsx");
writer.flush(response.getOutputStream(), true);
writer.close();
}
// 自定义列宽调整方法
private static void adjustColumnWidth(ExcelWriter writer) {
Sheet sheet = writer.getSheet();
int columnCount = writer.getColumnCount();
// 当数据量很大时，可以计算前100列的最大宽度
for (int i = 0; i < columnCount; i++) {
// 自动调整列宽（支持中文）
sheet.autoSizeColumn(i);
// 或使用增强版方法（解决中文显示不全问题）
int width = calculateColumnWidth(sheet, i);
sheet.setColumnWidth(i, width * 256);
}
}
// 计算列宽（含中文适配）
private static int calculateColumnWidth(Sheet sheet, int columnIndex) {
int maxWidth = 0;
for (Row row : sheet) {
Cell cell = row.getCell(columnIndex);
if (cell != null) {
String text = cell.getStringCellValue();
int length = text.getBytes(StandardCharsets.UTF_8).length;
maxWidth = Math.max(maxWidth, length);
}
}
// 加20%缓冲空间，避免截断
return (int) (maxWidth * 1.2);
}
// 将实体列表转为Map列表
private static <T> List<Map<String, Object>> convertToMapList(List<T> data, Class<T> clazz) {
if (CollUtil.isEmpty(data)) {
return Collections.emptyList();
}
// 获取记录字段的声明顺序
Map<Field, Integer> declarationOrder = new HashMap<>();
List<Field> declaredFields = Arrays.asList(clazz.getDeclaredFields());
for (int i = 0; i < declaredFields.size(); i++) {
declarationOrder.put(declaredFields.get(i), i);
}
// 获取所有带@Excel注解的字段并按规则排序
List<Field> fields = Arrays.stream(clazz.getDeclaredFields())
.filter(f -> f.isAnnotationPresent(Excel.class))
.sorted((f1, f2) -> {
Excel excel1 = f1.getAnnotation(Excel.class);
Excel excel2 = f2.getAnnotation(Excel.class);
// 优先处理设置了orderNum的字段
if (excel1.orderNum() != 0 && excel2.orderNum() != 0) {
return Integer.compare(excel1.orderNum(), excel2.orderNum());
}
if (excel1.orderNum() != 0) {
return -1; // 设置了orderNum的字段排前面
}
if (excel2.orderNum() != 0) {
return 1; // 未设置的字段排后面
}
// 均未设置orderNum时，按声明顺序排序
return Integer.compare(declarationOrder.get(f1), declarationOrder.get(f2));
})
.collect(Collectors.toList());
// 遍历数据对象并转换为Map
return data.stream().map(item -> {
Map<String, Object> map = new LinkedHashMap<>();
for (Field field : fields) {
try {
field.setAccessible(true);
Object value = field.get(item);
// 处理特殊类型（如日期格式化）
Excel excel = field.getAnnotation(Excel.class);
if (excel.dateFormat() != null && value instanceof Date) {
value = DateUtil.format((Date) value, excel.dateFormat());
}
map.put(excel.name(), value);
} catch (IllegalAccessException e) {
throw new RuntimeException("字段值获取失败：" + field.getName(), e);
}
}
return map;
}).collect(Collectors.toList());
}
/**
* 创建表头边框
*/
private static XSSFCellStyle createHeaderStyle(ExcelWriter writer) {
XSSFCellStyle style = (XSSFCellStyle) writer.createCellStyle();
// 设置边框
setBorders(style);
// 设置文本居中
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* 创建数据边框
*/
private static XSSFCellStyle createDataStyle(ExcelWriter writer) {
XSSFCellStyle style = (XSSFCellStyle) writer.createCellStyle();
setBorders(style); // 设置边框
style.setAlignment(HorizontalAlignment.LEFT);
return style;
}
/**
* 设置边框样式
*/
private static void setBorders(CellStyle style) {
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// 设置边框颜色
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
}
}
调用
List<User> userList = userService.getAllUsers();
ExcelExporter.export(response, userList, User.class);
设置样式
官网自定义样式
ExcelWriter writer = ...;
// 定义单元格背景⾊
StyleSet style = writer.getStyleSet();
// 第⼆个参数表⽰是否也设置头部单元格背景
style.setBackgroundColor(IndexedColors.RED, false);
//设置内容字体
Font font = writer.createFont();
font.setBold(true);
font.setColor(Font.COLOR_RED);
font.setItalic(true);
//第⼆个参数表⽰是否忽略头部样式
writer.getStyleSet().setFont(font, true);
以上⾃定义样式是针对单元格集合的，划分为：
头部样式 headCellStyle
普通单元格样式 cellStyle
数字单元格样式 cellStyleForNumber
⽇期单元格样式 cellStyleForDate
有时候我们只需要修改单个单元格样式，其他单元格样式采⽤默认样式，参考如下：
@GetMapping("downloadExampleExcel")
public Response downloadExampleExcel(HttpServletResponse response) {
logger.info("downloadExampleExcel response start。。。");
List<Title> titles = titleService.selectTitles();
ExcelWriter writer = ExcelUtil.getWriter();
for (int i = 0, j = 0; i < titles.size(); i++) {
Title title = titles.get(i);
writeCell(writer, j ++, title.getFieldDescC(), title.getRequiredFlag());
}
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + "⽂件名" + ".xls");
ServletOutputStream out = null;
try {
out = response.getOutputStream();
writer.flush(out, true);
} catch (Exception e) {
log.error("downloadExampleExcel response exception", e);
return ResponseHelper.buildFail(e.getMessage());
} finally {
writer.close();
if (out != null) {
IoUtil.close(out);
}
logger.info("downloadExampleExcel response end。。。");
}
return ResponseHelper.buildOk();
}
/**
* 输⼊标题到excel
* @param writer excel对象
* @param column 当前列位置
* @param cellValue 标题内容
* @param requiredFlag 是否标红
*/
private void writeCell(ExcelWriter writer, int column, String cellValue, String requiredFlag){
// 根据x,y轴设置单元格内容
writer.writeCellValue(column , 0, cellValue);
Font font = writer.createFont();
font.setColor(Font.COLOR_RED);
if (Constants.NUMBER_TWO.equals(requiredFlag)){
// 根据x,y轴获取当前单元格样式 x 列数，从 0 开始 y 行数，从 0 开始 （包括标题栏）
CellStyle cellStyle = writer.createCellStyle(column, 0);
// 内容⽔平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 内容垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
// 字体颜⾊标红
cellStyle.setFont(font);
}
}
效果：
由于
autoSizeColumnAll()自动设置列宽方法只对数字、字母生效，对中文不生效
针对中文设置列宽
ExcelWriter writer = ExcelUtil.getWriter(true);
//写数据 writer.write(data, true);
StyleSet style = writer.getStyleSet();
Font font = writer.createFont();
font.setColor(IndexedColors.VIOLET.index);
font.setBold(true);
font.setFontHeightInPoints((short) 12);
//重点，设置中文字体
font.setFontName("宋体");
style.getHeadCellStyle().setFont(font);
int columnCount = writer.getColumnCount();
for (int i = 0; i < columnCount; ++i) {
double width = SheetUtil.getColumnWidth(writer.getSheet(), i, false);
if (width != -1.0D) {
width *= 256.0D;
//此处可以适当调整，调整列空白处宽度
width += 220D;
writer.setColumnWidth(i, Math.toIntExact(Math.round(width / 256D)));
}
}
默认导出
/**
* 文件导出 excel
*/
public void exportFile(HttpServletResponse response) throws IOException {
ExcelWriter writer = ExcelUtil.getWriter();
String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
// 设置表名
String excelName="导出文件"+time+".xls";
// 设置表头
List<Object> header= CollectionUtil.newArrayList("编号","姓名","年龄","生日","性别");
// 使用 mybatis-plus 快速查询数据
Collection<UserEntity> userList = userMapper.selectList(new QueryWrapper<UserEntity>());
// 输出数据设置
List<List<Object>> rows=userList.stream().map(a->{
List<Object> list=new ArrayList<>(0);
list.add(a.getId());
list.add(a.getUsername());
list.add(a.getAge());
list.add(a.getBirth());
list.add(a.getGender());
return list;
}).collect(Collectors.toList());
// 数据导出
writer.writeHeadRow(header).write(rows==null?new ArrayList<List<Object>>():rows);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName, "utf-8"));
ServletOutputStream out = response.getOutputStream();
// 关闭连接
writer.flush(out).close();
}
/**
* 导出数据到excel文件
*/
public void exportExcelFile(List<User> list){
String dir="E:\\";
String filename="test.xlsx";
ExcelWriter writer=new ExcelWriter(dir+filename);
writer.addHeaderAlias("username","姓名");
writer.addHeaderAlias("gender","性别");
writer.addHeaderAlias("age","年龄");
writer.addHeaderAlias("create_time","创建时间");
writer.setOnlyAlias(true);
writer.write(list,true);
writer.close();
}
自定义导出
- 工具类
package com.zl.util;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.SheetUtil;
/**
* hutool excel 工具类
*/
public class LocalExcelUtil {
/**
* 设置 excel 单元格颜色
* @param writer ExcelWriter
* @param x 列
* @param y 行
* @param color 颜色
*/
public static void setColor(ExcelWriter writer,int x,int y,short color){
Font font = writer.createFont();
font.setColor(color);
CellStyle cellStyle = writer.createCellStyle(x, y);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setFont(font);
}
/**
* 设置 excel 自动列宽
* @param writer ExcelWriter
* @param fontName 字体
* @param bold 加粗
* @param fontColor 字体颜色
*/
public static void setAuthColWidth(ExcelWriter writer,String fontName,Boolean bold,short fontColor){
writer.autoSizeColumnAll();
StyleSet style = writer.getStyleSet();
Font font = writer.createFont();
font.setColor(fontColor);
// 加粗
font.setBold(bold);
font.setFontHeightInPoints((short) 12);
//重点，设置中文字体
font.setFontName(fontName);
style.getHeadCellStyle().setFont(font);
int columnCount = writer.getColumnCount();
for (int i = 0; i < columnCount; ++i) {
double width = SheetUtil.getColumnWidth(writer.getSheet(), i, false);
if (width != -1.0D) {
width *= 256.0D;
//此处可以适当调整，调整列空白处宽度
width += 220D;
writer.setColumnWidth(i, Math.toIntExact(Math.round(width / 256D)));
}
}
}
}
- 导出
/**
* 导出数据
* @param queryUserVO 查询参数
* @param response 响应
*/
@Override
public void exportUserInfo(QueryUserVO queryUserVO, HttpServletResponse response) throws IOException {
ExcelWriter writer = ExcelUtil.getWriter();
StyleSet styleSet=writer.getStyleSet();
// 第二个参数表示是否也设置头部单元格背景
styleSet.setBackgroundColor(IndexedColors.WHITE,true);
String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd %T"));
// 设置表名
String excelName="用户数据"+time+".xls";
String titleName="用户数据";
// 查询数据
QueryWrapper<UserEntity> queryWrapper=new QueryWrapper<>();
Object[] prams={queryUserVO.getStartTime(),queryUserVO.getEndTime()};
queryWrapper.apply("DATE_FORMAT(birth,'%Y-%m-%d %T') BETWEEN {0} and {1}",prams);
queryWrapper.orderByAsc("birth");
List<UserEntity> data = list(queryWrapper);
// 查询性别 0 女 1 男
queryWrapper.eq("gender",queryUserVO.getGender());
switch (queryUserVO.getGender()){
case 0:
titleName= titleName+"（女）";
break;
case 1:
titleName= titleName+"（男）";
break;
}
// 设置表头
List<Object> header= CollectionUtil.newArrayList("姓名","性别","年龄","生日");
writer.merge(header.size()-1, titleName);
writer.writeHeadRow(header);
// 数据导出
IntStream.range(0,data.size()).forEach(i->{
// x 表示列 y 表示行
writer.writeCellValue(0,i+2,data.get(i).getUsername());
writer.writeCellValue(1,i+2,data.get(i).getGender());
writer.writeCellValue(2,i+2,data.get(i).getAge());
// 当年龄大于55岁时显示红色
if(data.get(i).getAge()>55){
LocalExcelUtil.setColor(writer,2,i+2,Font.COLOR_RED);
}
writer.writeCellValue(3,i+2,new SimpleDateFormat("yyyy/MM/dd").format(data.get(i).getBirth()));
});
// 设置默认列宽
LocalExcelUtil.setAuthColWidth(writer,"宋体",false,IndexedColors.BLACK.index);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName, "utf-8"));
ServletOutputStream out = response.getOutputStream();
// 关闭连接
writer.flush(out).close();
}
根据业务相应的改动就行
参考链接：
https://wenku.baidu.com/view/547d8a39b4360b4c2e3f5727a5e9856a561226ff.html
浙公网安备 33010602011771号