springboot + thymeleaf + layui 初嘗試

一、背景

公司運營的同事有個任務，提供一個數據文件給我，然后從數據庫中找出對應的加密串再導出來給他。這個活不算是很難，但時不時就會有需求。

同事給我的文件有時是給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";@Resourceprivate 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")@ResponseBodypublic 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: 8080servlet:# 應用的訪問路徑context-path: /# Spring配置
spring:profiles:active: druid#thymeleaf 頁面的緩存開關thymeleaf:enabled: truecache: truemode: HTML5encoding: utf-8suffix: .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: infoorg.springframework: warn

數據庫配置application-druid.yml

# 數據源配置
spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://172.16.1.2:3306/test?useUnicode=true&useSSL=false&allowLoadLocalInfile=false&autoReconnect=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8username: rootpassword: root#Spring Boot 默認是不注入這些屬性值的，需要自己綁定#druid 數據源專有配置initialSize: 5minIdle: 5maxActive: 20maxWait: 60000timeBetweenEvictionRunsMillis: 60000minEvictableIdleTimeMillis: 300000validationQuery: SELECT 1 FROM DUALtestWhileIdle: truetestOnBorrow: falsetestOnReturn: falsepoolPreparedStatements: 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">&#xe61f;</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">&#xe601;</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'); //關閉loadinglayer.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>