一、背景
公司运营的同事有个任务，提供一个数据文件给我，然后从数据库中找出对应的加密串再导出来给他。这个活不算是很难，但时不时就会有需求。
同事给我的文件有时是给excel表格，每一行有4列，逗号隔开，合并成一列数据，这类文件需要把所有数据复制到文本编辑器进行处理，把逗号替换成空格，再使用列块编辑模式复制2、3、4列替换原来的excel数据。有时是给.DAT的文件，这类文件需要手动修改后缀为csv，修改后就跟普通的excel表格一样打开，去掉第一列。最后添加一行表头，再对第一列进行筛选去重。
去重后准备导入到数据库的临时表，在此之前需要手动清空临时表的历史数据。导入后再执行一段sql语句，然后把查询结果导出为excel文件给到同事。
这样的工作重复重复再重复，确实挺无趣的，何不鼓捣一个工具给同事自己去处理？
二、步骤
2.1 项目搭建
项目结构如下图：
创建项目，使用springboot 2.5.14、poi 4.1.2、mybatis，前端使用 thymeleaf + layui-v2.6.8 。
具体看maven配置
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.5.14</version> </parent> <modelVersion>4.0.0</modelVersion> <groupId>com.xxx</groupId> <artifactId>test</artifactId> <version>1.0</version> <properties> <maven.compiler.source>8</maven.compiler.source> <maven.compiler.target>8</maven.compiler.target> </properties> <dependencies> <!-- Spring框架基本的核心工具 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context-support</artifactId> </dependency> <!-- SpringBoot Web容器 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <exclusions> <exclusion> <groupId>org.apache.tomcat.embed</groupId> <artifactId>tomcat-embed-el</artifactId> </exclusion> <exclusion> <groupId>org.apache.tomcat.embed</groupId> <artifactId>tomcat-embed-websocket</artifactId> </exclusion> </exclusions> </dependency> <!-- thymeleaf --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> <exclusions> <exclusion> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> </exclusion> </exclusions> </dependency> <!-- 阿里数据库连接池 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.2.16</version> </dependency> <!-- Mysql驱动包 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!--常用工具类 --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> </dependency> <!-- io常用工具类 --> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.11.0</version> </dependency> <!-- excel工具 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> <exclusions> <exclusion> <groupId>org.apache.commons</groupId> <artifactId>commons-math3</artifactId> </exclusion> <exclusion> <groupId>org.zaxxer</groupId> <artifactId>SparseBitSet</artifactId> </exclusion> </exclusions> </dependency> <!-- servlet包 --> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> </dependency> <!-- lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <scope>provided</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.1</version> <configuration> <source>${java.version}</source> <target>${java.version}</target> <encoding>${project.build.sourceEncoding}</encoding> </configuration> </plugin> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <version>2.7.3</version> <executions> <execution> <goals> <goal>repackage</goal> </goals> </execution> </executions> <configuration> <mainClass>com.xxx.AdminApplication</mainClass> </configuration> </plugin> </plugins> </build> </project>
为了节省jar包体积，尽可能把不需要的依赖给排除。
2.2 后端处理逻辑
Controller内容
import com.xxx.domain.Result; import com.xxx.domain.CellItem; import com.xxx.domain.HelmetConfig; import com.xxx.service.HelmetService; import com.xxx.utils.file.DatUtil; import com.xxx.utils.poi.ExcelUtil; import org.apache.commons.io.FilenameUtils; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.multipart.MultipartFile; import javax.annotation.Resource; import javax.servlet.http.HttpServletResponse; import java.util.Arrays; import java.util.List; /** * 通用请求处理 * * @author admin */ @Controller public class CommonController { public static final String[] EXCEL_EXTENSION = {"xls", "xlsx", "XLS", "XLSX"}; public static final String DAT_EXTENSION = "DAT"; @Resource private HelmetService helmetService; @GetMapping(value = {"/", "/index"}) public String index(Model model) { return "index"; } /** * 通用下载请求 */ @GetMapping("/download") public void fileDownload(HttpServletResponse response) { List<HelmetConfig> list = helmetService.queryAll(); ExcelUtil<HelmetConfig> util = new ExcelUtil<>(HelmetConfig.class); util.exportExcel(response, list, "Sheet1"); } /** * 通用上传请求（单个） */ @PostMapping("/upload") @ResponseBody public Result uploadFile(MultipartFile file) { if (file == null || file.isEmpty()) { return Result.error("文件不能为空"); } String extension = FilenameUtils.getExtension(file.getOriginalFilename()); List<CellItem> list; if (Arrays.asList(EXCEL_EXTENSION).contains(extension)) { list = ExcelUtil.getData(file); } else if (DAT_EXTENSION.equalsIgnoreCase(extension)) { list = DatUtil.readDat(file); } else { return Result.error("文件格式不正确"); } if (list.isEmpty()) { return Result.error("操作失败，请重试"); } helmetService.batchAdd(list); return Result.success("操作成功，请点击【下载文件】"); } }
数据库根据最后的查询sql创建一个视图（View），通过mybatis对这个试图进行查询，然后把结构进行导出即可。
ExcelUtil.getData()内容
public static List<CellItem> getData(MultipartFile file) { InputStream inputStream = null; List<CellItem> rowList = new ArrayList<>(); try { inputStream = file.getInputStream(); XSSFWorkbook wb = new XSSFWorkbook(inputStream); int ignoreRows = 0; int sheetNum = wb.getNumberOfSheets(); //for循环：取前N个表,下标从0开始 for (int i = 0; i < sheetNum; i++) { XSSFSheet sheetI = wb.getSheetAt(i); //列数 int cellSize = sheetI.getRow(0).getLastCellNum(); //第N+1行开始，可以通过传参，从第N+1行开始取 for (int rowIndex = ignoreRows; rowIndex <= sheetI.getLastRowNum(); rowIndex++) { XSSFRow row = sheetI.getRow(rowIndex); if (row == null) { continue; } if (cellSize == 1) { XSSFCell cell = row.getCell(0); String cellValue = cell.getStringCellValue(); if (cellValue.contains(",")) { CellItem item = new CellItem(); String[] cells = cellValue.split(","); String deviceId = cells[1]; Boolean exists = checkExists(rowList, deviceId); if (exists) { continue; } item.setDeviceId(deviceId.trim()); item.setProductId(cells[2]); item.setMac(cells[3]); rowList.add(item); } } else if (cellSize == 4){ //在每行中的每一列，从下标1开始，忽略第一列，一直取到所有 CellItem item = new CellItem(); String deviceId = row.getCell(1).getStringCellValue(); Boolean exists = checkExists(rowList, deviceId); if (exists) { continue; } item.setDeviceId(deviceId.trim()); item.setProductId(row.getCell(2).getStringCellValue()); item.setMac(row.getCell(3).getStringCellValue()); rowList.add(item); } } } } catch (IOException e) { e.printStackTrace(); } finally { if (inputStream != null) { try { inputStream.close(); } catch (Exception e) { log.error("文件流关闭失败:{}", e.getMessage()); } } } return rowList; } private static Boolean checkExists(List<CellItem> rowList, String key) { for (int i = 0; i < rowList.size(); i++) { CellItem item = rowList.get(i); if (item.getDeviceId().equals(key.trim())) { return Boolean.TRUE; } } return Boolean.FALSE; }
DatUtil.readDat()
public static List<CellItem> readDat(MultipartFile file) { List<CellItem> list = new ArrayList<>(); try (BufferedReader reader = new BufferedReader(new InputStreamReader(file.getInputStream()))) { String line; while ((line = reader.readLine()) != null) { String[] split = line.split(","); String deviceId = split[1]; Boolean exists = checkExists(list, deviceId); if (exists) { continue; } CellItem item = new CellItem(); item.setDeviceId(deviceId.trim()); item.setMac(split[2]); item.setProductId(split[3]); list.add(item); } } catch (IOException e) { e.printStackTrace(); } return list; } private static Boolean checkExists(List<CellItem> rowList, String key) { for (int i = 0; i < rowList.size(); i++) { CellItem item = rowList.get(i); if (item.getDeviceId().equals(key.trim())) { return Boolean.TRUE; } } return Boolean.FALSE; }
导出的代码这里省略了。
2.3 配置
application.yml
# 开发环境配置 server: # 服务器的HTTP端口 port: 8080 servlet: # 应用的访问路径 context-path: / # Spring配置 spring: profiles: active: druid #thymeleaf 页面的缓存开关 thymeleaf: enabled: true cache: true mode: HTML5 encoding: utf-8 suffix: .html # 文件上传 servlet: multipart: # 单个文件大小 max-file-size: 10MB # 设置总上传的文件大小 max-request-size: 50MB # MyBatis配置 mybatis: # 搜索指定包别名 typeAliasesPackage: com.xxx.domain # 配置mapper的扫描，找到所有的mapper.xml映射文件 mapperLocations: classpath:mapper/*.xml # 加载全局的配置文件 configLocation: classpath:mybatis/mybatis-config.xml # 日志配置 logging: level: com.xxx: info org.springframework: warn
数据库配置application-druid.yml
# 数据源配置 spring: datasource: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://172.16.1.2:3306/test?useUnicode=true&useSSL=false&allowLoadLocalInfile=false&autoReconnect=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8 username: root password: root #Spring Boot 默认是不注入这些属性值的，需要自己绑定 #druid 数据源专有配置 initialSize: 5 minIdle: 5 maxActive: 20 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 FROM DUAL testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true
2.3 前端处理逻辑
把layui的相关文件放到resources/static目录，再新建一个index.html文件放入resources/templates目录，这两个目录是thymeleaf默认的，如果要修改可以在application.yml进行配置。静态文件如下：
为了压缩jar包的体积，把所有不必要的文件都精简掉了。
以下是index.html内容
<!DOCTYPE html> <html lang="en" xmlns:th="http://www.thymeleaf.org"> <head> <meta charset="UTF-8"> <title>测试</title> <script th:src="@{/layui.js}"></script> <link rel="stylesheet" th:href="@{/css/layui.css}" media="all"> </head> <body> <div class="layui-card"> <div class="layui-card-header">操作面板</div> <div class="layui-card-body"> <div class="layui-tab" lay-filter="window"> <ul class="layui-tab-title"> <li class="layui-this" lay-id="uploadTab">文件上传</li> <li lay-id="downloadTab">文件下載</li> </ul> <div class="layui-tab-content"> <div class="layui-tab-item layui-show"> <form id="upload_form" class="layui-form" enctype="multipart/form-data"> <div class="layui-form-item"> <label class="layui-form-label">文件</label> <div class="layui-input-block"> <button type="button" class="layui-btn" id="upload"> <i class="layui-icon"></i>选择文件 </button> </div> </div> <div class="layui-form-item"> <div class="layui-input-block"> <button id="btnSubmit" class="layui-btn" onclick="return false;">立即提交</button> </div> </div> </form> </div> <div class="layui-tab-item"> <div class="layui-form-item"> <label class="layui-form-label">文件</label> <div class="layui-input-block"> <button type="button" class="layui-btn" id="downloadBtn"> <i class="layui-icon"></i>下载文件 </button> </div> </div> </div> </div> </div> </div> </div> </body> </html> <script> layui.use(['upload', 'layer', 'element'], function () { let $ = layui.jquery , layer = layui.layer , element = layui.element , upload = layui.upload; //执行实例 upload.render({ elem: '#upload' //绑定元素 , url: '/upload' //上传接口 , accept: 'file' //允许上传的文件类型，不写默认是图片 , acceptMime: ".xlsx,.xls,.DAT,.dat" //不写默认验证图片格式，一定要省略【exts】参数 , auto: false //选择文件后不自动上传 , bindAction: '#btnSubmit' //指向一个按钮触发上传 , before: function (obj) { layer.load(); //上传loading } ,done: function (res) { console.log(res) layer.closeAll('loading'); //关闭loading layer.alert(res.msg); if (res.code === 200) { element.tabChange('window', 'downloadTab'); } } , error: function (res) { console.error(res) layer.msg(res.msg); layer.closeAll('loading'); //关闭loading } }); $("#downloadBtn").on('click', function () { location.href = "/download"; }) }); </script>
编辑好测试没问题直接打包放到服务器上执行就可以啦。
三、实现效果
3.1 文件导入
导入成功后会自动切换到【文件下载】的tab页
3.2 文件导出
