目录一. 引出问题二. 准备数据及数据导出逻辑三. 解决方案3.1 方法一数据库查询时原生转化2.2 方法二程序代码处理转化逻辑2.3 方法三使用阿里巴巴 EasyExcel 依赖依赖包一. 引出问题众所周知程序处理 Excel 数据或者导出数据到 Excel 文件是一个比较常见的系统功能此外在将数据导入到 excel 文件时我们常常还会做一些脱敏加密操作将不宜直接展示的数据加密处理例如我们常常见到的手机号很多都是中间四位加密为 *例如136****6789再比如用户张三常常加密为张*那么这些效果是如何实现的本篇文章我们就来简单探讨一下。二. 准备数据及数据导出逻辑如下 user 用户表结构如下所示create table user ( id bigint not null comment 主键 primary key, no varchar(10) not null comment 账号, username varchar(100) null comment 名字, password varchar(20) null comment 密码, age varchar(3) null comment 年龄, sex varchar(1) null comment 性别,1-男0-女, phone varchar(20) null comment 手机号, valid_status varchar(1) null comment 是否有效1有效0无效, create_by bigint null comment 创建人, create_at datetime null comment 创建时间, update_by bigint null comment 修改人, update_at datetime null comment 修改时间 ) charset utf8mb3;存储部分数据如下所示DML如下INSERT INTO user (id, no, username, password, age, sex, phone, valid_status, create_by, create_at, update_by, update_at) VALUES (1, U001, 张三, zhangsan123, 28, 1, 13800138001, 1, 1, 2023-10-01 09:00:00, 1, 2023-10-01 09:00:00), (2, U002, 李四, lisi456, 32, 1, 13800138002, 1, 1, 2023-10-02 10:15:00, 1, 2023-10-02 10:15:00), (3, U003, 王芳, wangfang789, 25, 0, 13800138003, 1, 2, 2023-10-03 11:30:00, 2, 2023-10-03 11:30:00), (4, U004, 赵敏, zhaomin111, 29, 0, 13800138004, 0, 2, 2023-10-04 14:20:00, 2, 2023-10-04 14:20:00), (5, U005, 刘强, liuqiang222, 35, 1, 13800138005, 1, 1, 2023-10-05 15:45:00, 1, 2023-10-05 15:45:00), (6, U006, 孙悦, sunyue333, 27, 0, 13800138006, 1, 3, 2023-10-06 16:00:00, 3, 2023-10-06 16:00:00), (7, U007, 周杰, zhoujie444, 31, 1, 13800138007, 1, 2, 2023-10-07 17:10:00, 2, 2023-10-07 17:10:00), (8, U008, 吴婷, wuting555, 26, 0, 13800138008, 0, 1, 2023-10-08 18:30:00, 1, 2023-10-08 18:30:00), (9, 1872708361, 张阳光, 123, 18, 1, 111, 1, NULL, NULL, NULL, NULL), (10, 1862583653, 李保民, 123456, 20, 1, 18625836538, 1, NULL, NULL, NULL, NULL);假设现在我要将用户表数据导入到 Excel 文件中并且导出的数据转化逻辑就遵循上面我们说到的性别sex 值为1转化为男sex值为0转化为女用户名称除了展示姓氏名称一律用 * 替换手机号中间四位用 * 替换三. 解决方案3.1 方法一数据库查询时原生转化这种方法最易理解简单来说我们要把数据导出到 Excel 文件肯定要先从数据库把数据查出来所以我们干脆就在数据库层面提前对数据做处理转化为我们希望的数据格式这样就可以直接将查询后的数据写入到 Excel 文件中了转化查询SQL如下所示select no, username, age, case when sex 1 then 男 when sex 0 then 女 else end as sex, concat(left(phone,3),****,right(phone,4)) as phone, case when valid_status 1 then 有效 when valid_status 0 then 失效 else end as valid_status from user执行上述脚本我们就可以得到如下结果可以看到数据库对查询出来的数据已经做了转化但这里有一个点需要注意程序代码在接受结果时要考虑到数据类型。举个例子sex 性别字段valid_status 状态字段在原始数据库存储为01等数据现在转化为字符串所以代码中应该用 String 接收如果数据库字段类型为 INT那么对应的 DTO 则应该改为 String 否则可能会爆出数据类型转化异常。DAO层接口比较简单这里省略Service 业务层方法如下/** * 导出数据到 Excel 文件 * */ public void exportUserDataToExcel1(HttpServletResponse response){ // 获取所有用户数据 ListUser users userMapper.selectAll(); // 创建 Excel 工作簿xlsx 格式 Workbook workbook new XSSFWorkbook(); Sheet sheet workbook.createSheet(用户数据); // 创建表头 Row headerRow sheet.createRow(0); headerRow.createCell(0).setCellValue(账号); headerRow.createCell(1).setCellValue(姓名); headerRow.createCell(2).setCellValue(年龄); headerRow.createCell(3).setCellValue(性别); headerRow.createCell(4).setCellValue(手机号); headerRow.createCell(5).setCellValue(有效状态); // 填充数据 for (int i 0; i users.size(); i) { User user users.get(i); // 从第二行开始填充 Row row sheet.createRow(i 1); row.createCell(0).setCellValue(user.getNo()); row.createCell(1).setCellValue(user.getUsername()); row.createCell(2).setCellValue(user.getAge()); row.createCell(3).setCellValue(user.getSex()); row.createCell(4).setCellValue(user.getPhone()); row.createCell(5).setCellValue(user.getValidStatus()); } // 设置响应头 String fileName 用户数据_ System.currentTimeMillis() .xlsx; fileName URLEncoder.encode(fileName, StandardCharsets.UTF_8).replaceAll(\\, %20); response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setHeader(Content-disposition, attachment;filename fileName); response.setCharacterEncoding(utf-8); try (OutputStream outputStream response.getOutputStream()){ // 写入数据 workbook.write(outputStream); // 刷新缓冲区 outputStream.flush(); // 关闭工作簿 workbook.close(); } catch (IOException e) { throw new RuntimeException(e); } }Controller 层如下/** * 导出文件 * */ GetMapping(/exportUserDataToExcel1) public void exportUserDataToExcel1(HttpServletResponse response){ excelService.exportUserDataToExcel1(response); }然后运行项目因为前端没有写直接在浏览器输入请求路径访问可以看到文件导出成功打开文件检查一下可以发现数据完美的导出成功。2.2 方法二程序代码处理转化逻辑这个方法和方法一其实没有本质区别只不过是处理的顺序变了方法一中我们是在数据库层面进行数据处理得到的就是最终数据那么方法二中我们这里就是直接查询原生数据库存储的数据然后在代码层面进行逻辑转化在代码中可以转化的地方有很多。我们可以在DAO接口查询后立即进行转化也可以在数据集将写入 sheet 文件中通过 if 判断转化总之方法是各种各样的。我就以DAO接口查询后立即转化为例对上方 Service 方法略加修改即可验证过程就不重复展示了/** * 导出数据到 Excel 文件 * */ public void exportUserDataToExcel1(HttpServletResponse response){ // 获取所有用户数据 ListUser users userMapper.selectAll2(); // 创建 Excel 工作簿xlsx 格式 Workbook workbook new XSSFWorkbook(); Sheet sheet workbook.createSheet(用户数据); // 创建表头 Row headerRow sheet.createRow(0); headerRow.createCell(0).setCellValue(账号); headerRow.createCell(1).setCellValue(姓名); headerRow.createCell(2).setCellValue(年龄); headerRow.createCell(3).setCellValue(性别); headerRow.createCell(4).setCellValue(手机号); headerRow.createCell(5).setCellValue(有效状态); // 填充数据 for (int i 0; i users.size(); i) { User user users.get(i); // 从第二行开始填充 Row row sheet.createRow(i 1); row.createCell(0).setCellValue(user.getNo()); row.createCell(1).setCellValue(user.getUsername()); row.createCell(2).setCellValue(user.getAge()); if (1.equals(user.getSex())){ row.createCell(3).setCellValue(男); } else if (0.equals(user.getSex())){ row.createCell(3).setCellValue(女); } else { row.createCell(3).setCellValue(); } if (user.getPhone() ! null user.getPhone().length() 11){ row.createCell(4).setCellValue(user.getPhone().substring(0, 3) **** user.getPhone().substring(7)); } else { row.createCell(4).setCellValue(user.getPhone()); } if (1.equals(user.getValidStatus())){ row.createCell(5).setCellValue(有效); } else if (0.equals(user.getSex())) { row.createCell(5).setCellValue(无效); } else { row.createCell(5).setCellValue(); } } // 设置响应头 String fileName 用户数据_ System.currentTimeMillis() .xlsx; fileName URLEncoder.encode(fileName, StandardCharsets.UTF_8).replaceAll(\\, %20); response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setHeader(Content-disposition, attachment;filename fileName); response.setCharacterEncoding(utf-8); try (OutputStream outputStream response.getOutputStream()){ // 写入数据 workbook.write(outputStream); // 刷新缓冲区 outputStream.flush(); // 关闭工作簿 workbook.close(); } catch (IOException e) { throw new RuntimeException(e); } }2.3 方法三使用阿里巴巴 EasyExcel 依赖依赖包关于对 Excel 数据的文件导入和导出阿里巴巴提供了一个封装好的 pom 依赖包里面集成了各种各样的关于对 Excel 文件的导入导出数据处理操作的各种API下面我们简单的学习一下它的数据导出功能。首先导入 pom 依赖!-- EasyExcel -- dependency groupIdcom.alibaba/groupId artifactIdeasyexcel/artifactId version3.3.2/version /dependency导入依赖之后我们就可以使用它提供的注解了对于数据导出暂时只了解下面两个就可以。ExcelProperty1value 值就是导出到 Excel 文件中的列名就是这列数据的标头2index 表示第几列从0开始0表示第一列以此类推3converter 是转换器值为一个转换类。就是将数据库的码值转换为实际含义的描述或者将实际含义的描述转化为码值可以互相转化对应一个转化器类ExcelIgnore忽略此字段不会将此字段导出到 Excel 文件Data ApiModel(valueUser表, description) public class User implements Serializable { private static final long serialVersionUID 1L; ExcelIgnore private Long id; ExcelProperty(value 账号, index 0) private String no; ExcelProperty(value 名字, index 1, converter UsernameConverter.class) private String username; ExcelIgnore private String password; ExcelProperty(value 年龄, index 2) private String age; ExcelProperty(value 性别, index 3, converter SexConverter.class) private String sex; ExcelProperty(value 电话, index 4) private String phone; ExcelIgnore private String validStatus; private Long createBy; private Date createAt; private Long updateBy; private Date updateAt; }上面提到的转换器 converter 转换器。指的就是对应的类我们可以为每个字段都定义一个转换规则类可以把对数据的可种各样的操作全部集中定义。public class PhoneConverter implements ConverterString { Override public Class? supportJavaTypeKey() { return String.class; } Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } Override public String convertToJavaData(ReadConverterContext? context) { // 读取Excel时的转换这里简单返回原始值 return context.getReadCellData().getStringValue(); } Override public WriteCellData? convertToExcelData(WriteConverterContextString context) { // 数据库数据转化为Excel数据手机号中间四位加密为**** String phone context.getValue(); if (phone null || phone.trim().isEmpty()) { return new WriteCellData(); } if (phone.length() ! 11) { return new WriteCellData(phone); } return new WriteCellData(phone.substring(0, 3) **** phone.substring(7)); } }public class SexConverter implements ConverterString { Override public Class? supportJavaTypeKey() { return String.class; } Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } Override public String convertToJavaData(ReadConverterContext? context) { // 读取Excel时的转换Excel中的男/女转回1/0 String excelValue context.getReadCellData().getStringValue(); if (男.equals(excelValue)) { return 1; } else if (女.equals(excelValue)) { return 0; } return excelValue; } Override public WriteCellData? convertToExcelData(WriteConverterContextString context) { // 将数据库数据转化为Excel数据 String javaValue context.getValue(); if (1.equals(javaValue)) { return new WriteCellData(男); } else if (0.equals(javaValue)) { return new WriteCellData(女); } return new WriteCellData(javaValue ! null ? javaValue : ); } }public class UsernameConverter implements ConverterString { Override public Class? supportJavaTypeKey() { return String.class; } Override public CellDataTypeEnum supportExcelTypeKey() { return CellDataTypeEnum.STRING; } Override public String convertToJavaData(ReadConverterContext? context) { // 读取Excel时的转换这里简单返回原始值 // 实际可能需要逆向处理姓* - 完整姓名 return context.getReadCellData().getStringValue(); } Override public WriteCellData? convertToExcelData(WriteConverterContextString context) { // 写入excel文件时只显示姓不显示名字用*替换 String value context.getValue(); if (value null || value.trim().isEmpty()) { return new WriteCellData(); } else if (value.length() 1) { // 如果只有1个字姓直接显示 return new WriteCellData(value); } else { // 显示姓, 不显示名字, 用*替换名字 return new WriteCellData(value.charAt(0) *.repeat(value.length() - 1)); } } }通常情况下我们会重写转换器的四个方法方法一supportJavaTypeKey支持哪种Java数据类型实体类中定义的是String所以这里也是String如果是数值类型可以选择对应的Java数据类型方法二supportExcelTypeKey支持那种Excel 文本类型在将数据导出到Excel时也会对数据类型做要求常见的都是String字符串也有日期类型Date或者浮点数金额可以使用提供的枚举值Number方法三convertToJavaDataExcel 文件中的数据转化为Java程序中的数据转化逻辑即 文字描述——》码值方法四convertToExcelDataJava程序中的码值导出到Excel 文件中代表的实际含义码值——》文字描述这种做法相比于前两种其好处是1避免了编写过于复杂的SQL更重要的一点是可以查询数据库原生数据这样一来就避免了后续我们要获取原生数据时进行二次查询2避免了在 Service 业务层写大量的字段转化逻辑提高了业务代码的简介度降低了复杂度做好了一切准备之后我们就可以编写 Service 业务层方法了这里我们就饿可以使用 EasyExcel 封装好的API接口直接链式调用即可搞定非常简单高效// 功能创建 Excel 写入器ExcelWriter此方法可以重载可以传递多种不同的参数 EasyExcel.write() // 创建一个工作表Sheet就是我们Excel文件中常见的sheet页 .sheet() // 指定Excel文件的表头表头字段在User类中已经标注直接作为参数传递进去即可 .head() // 正式执行数据写入的方法将查询到的 List 集合数据写入 Excel 文件 .doWrite()/** * 数据库数据导出到 Excel 文件 * */ public void exportUserDataToExcel(HttpServletResponse response){ // 查询所有用户数据 ListUser users userMapper.selectAll2(); response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet); response.setCharacterEncoding(utf-8); String fileName 用户数据_ System.currentTimeMillis() .xlsx; // 编码文件名防止浏览器解析错误 String encodedFileName URLEncoder.encode(fileName, StandardCharsets.UTF_8).replaceAll(\\, %20); response.setHeader(Content-disposition, attachment;filename encodedFileName); try { EasyExcel.write(response.getOutputStream(), User.class) .sheet(用户数据) .head(User.class) .doWrite(users); } catch (IOException e) { throw new RuntimeException(e); } }编写完毕代码重新编译运行项目到浏览器调用 Controller 接口文件下载成功打开文件再次确认发现数据均已按照加密转义规则经过了处理。