springboot+thymeleaf+layui的實現(xiàn)示例

更新時間：2023年12月12日 09:16:04 作者：左邊的天堂

本文主要介紹了springboot+thymeleaf+layui的實現(xiàn)示例,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧

一、背景

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

同事給我的文件有時是給excel表格，每一行有4列，逗號隔開，合并成一列數(shù)據(jù)，這類文件需要把所有數(shù)據(jù)復制到文本編輯器進行處理，把逗號替換成空格，再使用列塊編輯模式復制2、3、4列替換原來的excel數(shù)據(jù)。有時是給.DAT的文件，這類文件需要手動修改后綴為csv，修改后就跟普通的excel表格一樣打開，去掉第一列。最后添加一行表頭，再對第一列進行篩選去重。

去重后準備導入到數(shù)據(jù)庫的臨時表，在此之前需要手動清空臨時表的歷史數(shù)據(jù)。導入后再執(zhí)行一段sql語句，然后把查詢結果導出為excel文件給到同事。

這樣的工作重復重復再重復，確實挺無趣的，何不鼓搗一個工具給同事自己去處理？

二、步驟

2.1 項目搭建

項目結構如下圖：

創(chuàng)建項目，使用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>

        <!-- 阿里數(shù)據(jù)庫連接池 -->
        <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>

為了節(jié)省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("操作成功，請點擊【下載文件】");
    }
}

數(shù)據(jù)庫根據(jù)最后的查詢sql創(chuàng)建一個視圖（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循環(huán)：取前N個表,下標從0開始
        for (int i = 0; i < sheetNum; i++) {
            XSSFSheet sheetI = wb.getSheetAt(i);
            //列數(shù)
            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

# 開發(fā)環(huán)境配置
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

數(shù)據(jù)庫配置application-druid.yml

# 數(shù)據(jù)源配置
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 數(shù)據(jù)源專有配置
    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進行配置。靜態(tài)文件如下：

在這里插入圖片描述

為了壓縮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}" rel="external nofollow"  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;

        //執(zhí)行實例
        upload.render({
            elem: '#upload' //綁定元素
            , url: '/upload' //上傳接口
            , accept: 'file' //允許上傳的文件類型，不寫默認是圖片
            , acceptMime: ".xlsx,.xls,.DAT,.dat" //不寫默認驗證圖片格式，一定要省略【exts】參數(shù)
            , auto: false //選擇文件后不自動上傳
            , bindAction: '#btnSubmit' //指向一個按鈕觸發(fā)上傳
            , 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>

編輯好測試沒問題直接打包放到服務器上執(zhí)行就可以啦。