springboot -- 整合 poi 解析Excel 更新数据库数据

最新推荐文章于 2024-08-19 16:30:20 发布

兮家小二

最新推荐文章于 2024-08-19 16:30:20 发布

阅读量1.9k

点赞数 1

分类专栏： # springboot 文章标签：解析Excel 更新数据库数据

本文链接：https://blog.csdn.net/qq_41463655/article/details/92008001

版权

springboot 专栏收录该内容

45 篇文章 4 订阅

订阅专栏

目录：
1、建表，实体类，dao层，service层，自行操作
2、Excel–>Poi --> Maven依赖
3、解析Excel --> Poi 工具类 --> ConfigExcelUtil.java
4、JDBC 配置，dbcp.properties
5、JDBC 工具类，JDBCPool.java
6、读取配置文件 .properties 工具类 PropUtli.java
7、定义mysql 与excel 对应关系配置文件 --> SqlConfig.properties
8、文件上传解析Excel 并添加到数据到数据库工具类（核心类）
9、文件上传表单预览

效果预览

Excel：
在这里插入图片描述

后台操作：
在这里插入图片描述
数据库：上传Excel 后数据

格式规则说明：

springboot – 通过Excel 直接更新数据库数据
excel 第二行为字段列，第三行后为数据列，excel 数据必须是第一个页签

1、先建表

使用Navicat 导入 Excel 文件同步至mysql数据库
https://mp.csdn.net/mdeditor/92003625#

自行建表及实体类，dao层，service层，确定表和excel有一定的对应关系，保证能添加数据的情况下看下方内容

2、Excel–>Poi --> Maven依赖

 <!-- excel2003使用的包 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>
    <!-- excel2007+使用的包 -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.17</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.17</version>
    </dependency>

3、解析Excel --> Poi 工具类 --> ConfigExcelUtil.java

传入文件路径–> 解析成 Map 集合，空数据默认设置为0
Map 集合数据：
get(0) == 字段行
get(1…) == ecxel中每一行的数据

package com.hy.wargame.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ConfigExcelUtil {
	/**
	 * 读取excle 表数据
	 * 
	 * @param path
	 */
	public static List<Map<String, String>> readExcle(File file) {
		List<Map<String, String>> rows = new ArrayList<>();
		boolean isExcel2003 = true;
		Workbook wb = null;
		if (file.getAbsolutePath().matches("^.+\\.(?i)(xlsx)$")) {
			isExcel2003 = false;
		}
		try {
			FileInputStream inputStream = new FileInputStream(file);
			wb = isExcel2003 ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
			FormulaEvaluator evaluator = isExcel2003 ? new HSSFFormulaEvaluator((HSSFWorkbook) wb)
					: new XSSFFormulaEvaluator((XSSFWorkbook) wb);
			// 获取表
			Sheet sheet = wb.getSheetAt(0);
			int rownum = sheet.getLastRowNum();
			int cellNum = sheet.getRow(1).getLastCellNum();// 列数

			Map<String, String> title = new LinkedHashMap<>();
			for (int rowIndex = 1; rowIndex <= rownum; rowIndex++) {
//				System.out.println(rowIndex);
				Row row = sheet.getRow(rowIndex);
				if (row == null || row.getCell(0) == null) {
					break;
				}
				Cell cell = row.getCell(0);

				String value = "0";
				if (cell != null) {
					if (cell.getCellTypeEnum() == CellType.NUMERIC) {
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							SimpleDateFormat sdf = null;
							if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
								sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
							} else {// 日期
								sdf = new SimpleDateFormat("yyyy-MM-dd");
							}
							value = sdf.format(cell.getDateCellValue());
						} else {
							DecimalFormat df = new DecimalFormat("#.##");
							value = df.format(cell.getNumericCellValue());
						}
					} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
						value = cell.getBooleanCellValue() + "";
					} else if (cell.getCellTypeEnum() == CellType.FORMULA) {
						CellValue cellValue = evaluator.evaluate(cell);
						if (cellValue.getCellTypeEnum() == CellType.NUMERIC) {
							DecimalFormat df = new DecimalFormat("#.##");
							value = df.format(cellValue.getNumberValue());
						} else {
							value = cellValue.getStringValue();
						}
					} else if (cell.getCellTypeEnum() == CellType.BLANK) {
						value = "0";
					} else if (cell.getCellTypeEnum() == CellType.STRING) {
						value = cell.getStringCellValue();
					} else if (cell.getCellTypeEnum() == CellType._NONE) {
						value = "0";
					}
				}
				if (value.equals("") || value.equals("0")) {
					break;
				}
				Map<String, String> rowValue = new LinkedHashMap<>();
				// 读取数据
				for (int cellIndex = 0; cellIndex < cellNum; cellIndex++) {
					cell = row.getCell(cellIndex);

					value = "0";
					if (cell != null) {
						if (cell.getCellTypeEnum() == CellType.NUMERIC) {
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								SimpleDateFormat sdf = null;
								if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
									sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
								} else {// 日期
									sdf = new SimpleDateFormat("yyyy-MM-dd");
								}
								value = sdf.format(cell.getDateCellValue());
							} else {
								DecimalFormat df = new DecimalFormat("#.##");
								value = df.format(cell.getNumericCellValue());
							}
						} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
							value = cell.getBooleanCellValue() + "";
						} else if (cell.getCellTypeEnum() == CellType.FORMULA) {
							CellValue cellValue = evaluator.evaluate(cell);
							if (cellValue.getCellTypeEnum() == CellType.NUMERIC) {
								DecimalFormat df = new DecimalFormat("#.##");
								value = df.format(cellValue.getNumberValue());
							} else {
								value = cellValue.getStringValue();
							}
						} else if (cell.getCellTypeEnum() == CellType.BLANK) {
							value = "0";
						} else if (cell.getCellTypeEnum() == CellType.STRING) {
							value = cell.getStringCellValue();
						} else if (cell.getCellTypeEnum() == CellType._NONE) {
							value = "0";
						}
					}
					if (rowIndex == 1) {
						// 所有字段名保存为小写 value.toLowerCase()
						rowValue.put(cellIndex + "", value.toLowerCase());
					} else {
						if (title.containsKey(cellIndex + "")) {
							rowValue.put(title.get(cellIndex + ""), value.replaceAll(",", "&prime;"));
						}
					}
				}
				if (rowIndex == 1) {
					title = rowValue;
				}
				rows.add(rowValue);
			}
		} catch (IOException e) {`在这里插入代码片`
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rows;
	}
}

前题添加
前台上传文件，要先有表，并测试添加数据到数据库没有问题

4、JDBC 配置，dbcp.properties

driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://192.168.103.8:3306/WarGameConfig?useUnicode=true&characterEncoding=utf-8&useTimezone=true&serverTimezone=GMT%2B8
username=root
password=hykj2015
initialSize=10
maxActive=5
maxIdle=5
minIdle=3
maxWait=-1

5、JDBC 工具类，JDBCPool.java

package com.hy.wargame.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/* *
 * @Description //TODO JDBC 数据源
 * @Date  2018/9/16/016/
 * @Param
 * @return
 **/
public class JDBCPool {

	// 数据源
	public static Connection getConn() {
		InputStream in = JDBCPool.class.getClassLoader().getResourceAsStream("dbcp.properties");
		Properties prop = new Properties();
		Connection conn = null;
		try {
			prop.load(in);
			String driverClass = prop.getProperty("driverClass");
			String url = prop.getProperty("url");
			String username = prop.getProperty("username");
			String password = prop.getProperty("password");
			// 注册驱动
			Class.forName(driverClass);
			conn = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	// 连接数据源 - 写入sql
	public static PreparedStatement getPstmt(String sql) {
		Connection conn = getConn();
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}

	// 添加 修改关闭
	public static void closeUpdateRes(PreparedStatement ps) {
		if (ps != null) {
			try {
				Connection conn = ps.getConnection();
				ps.close();
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	// 查询关闭
	public static void closeQueryRes(ResultSet rs) {
		if (rs != null) {
			Statement pstmt;
			try {
				pstmt = rs.getStatement();
				if (pstmt != null) {
					Connection conn = pstmt.getConnection();
					rs.close();
					pstmt.close();
					if (conn != null) {
						conn.close();
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

6、读取配置文件 .properties 工具类 PropUtli.java

package com.hy.wargame.util;

import java.io.InputStream;
import java.util.Properties;

/**
 * 读取配置文件
 * 
 * @author wangsong
 * @date 2019年5月31日 下午7:03:14
 */
public class PropUtli {
	/**
	 * 读取配置文件
	 */
	public static String findprop(String key) {
		InputStream in = PropUtli.class.getClassLoader().getResourceAsStream("SqlConfig.properties");
		Properties prop = new Properties();
		String rw = null;
		try {
			prop.load(in);
			rw = prop.getProperty(key);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rw;
	}
}

7、定义mysql 与excel 对应关系配置文件 --> SqlConfig.properties

Activity=ActivityConfig Activity为文件名，ActivityConfig为数据库名
Activity_type 为实体类的字段数据类型，判断生成sql 是否加y引号 “”
Activity_mysql 为数据库的字段
Activity_excel 为Excel的字段

数据类型，数据库字段,excel字段，必须对应（程序通过逗号分隔然后通过索引取值）


### _type  定义，对应实体类字段类型(可无视大小写，程序自动解析为小写，int,int,Short,String,Double,Float,Data)
### _mysql 定义，对应实体类或数据库字段 (大小写必须对应)
### _excel 定义，对应excel类第二行字段 (无视大小写，程序自动解析为小写)


### 51-- 精彩活动主(页签)
Activity=ActivityConfig
Activity_type=int,String,Date,Date,Date,Date,String,int,int,String,String,int,int,String
Activity_mysql=id,activename,showtime,starttime,endtime,hidetime,bgpic,activemark,activetype,rewardpreview,tips,type,`sort`,path
Activity_excel=id,activename,showtime,starttime,endtime,hidetime,bgpic,activemark,activetype,rewardpreview,tips,type,sort,path

8、文件上传解析Excel 并添加到数据到数据库工具类（核心类）

版本号可以先无视
流程 --> 如遇错误继续执行–> 添加错误信息至map 集合
1、上传文件
2、删除原文件(上次上传)
3、所有文件上传成功–>返回文件路径
循环开始----
4、解析Excel 为Map数据
5、通过文件名获取到配置文件映射关系
6、根据映射拼接添加sql
7、sql特殊字符转译
8、根据映射关系获取清空原表sql
9、通过JDBC执行清空表数据 sql
10、通过JDBC执行添加数据sql
循环结束----
11、更新文件版本号

package com.hy.wargame.controller;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import com.hy.wargame.domain.User;
import com.hy.wargame.entity.VersionConfig;
import com.hy.wargame.util.ConfigExcelUtil;
import com.hy.wargame.util.JDBCPool;
import com.hy.wargame.util.PropUtli;

/**
 * 文件上传，更新配置表
 * 
 * @author wangsong
 * @date 2019年5月22日 上午11:50:55
 */
@Controller
public class ExcelController extends BaseController {

	/**
	 * 跳转到配置页面
	 */
	@RequestMapping("/findFile")
	public String findFile(HttpServletRequest request) throws IOException {
		return "config/file";
	}

	/**
	 * 更新配置,上传文件即可，不限制上传文件数量
	 * 
	 * @throws InterruptedException
	 */
	@RequestMapping(value = "/updateFile", method = RequestMethod.POST)
	public String updateFile(HttpServletRequest request, Model model) throws IOException, InterruptedException {
		// 接收到的文件
		List<MultipartFile> files = ((MultipartHttpServletRequest) request).getFiles("file");
		// 文件临时保存路径
		String filePath = "xlsx/";
		// 上传文件--> 删除原文件 --> 返回文件保存路径
		List<String> filePaths = handleFileUpload(files, filePath);
		List<Map<String, String>> readExcle = null;
		List<Map<String, String>> retList = new ArrayList<Map<String, String>>();
		for (String path : filePaths) {
			Map<String, String> map = new HashMap<String, String>();
			int fileXs = path.lastIndexOf("\\");
			String fileName = null;
			String suffixName = path.substring(path.indexOf('.') + 1, path.length());
			if (suffixName.equals("xlsx") || suffixName.equals("xls")) {
				if (suffixName.equals("xlsx")) {
					fileName = path.substring(fileXs + 1, path.length() - 5);
				} else {
					fileName = path.substring(fileXs + 1, path.length() - 4);
				}
				// 获取文件名判断是否存在Prop配置信息
				if (PropUtli.findprop(fileName) != null) {
					// 获取解析后的上传数据
					readExcle = ConfigExcelUtil.readExcle(new File(path));
					// 获取清空表sql
					String deleteSql = deleteSql(fileName);
					// 获取添加sql
					String insertSql = insertSql(readExcle, fileName);
					// jdbc执行sql,先清空，在添加
					System.out.println(insertSql);
					executeSql(deleteSql);
					String resutl = executeSql(insertSql);
					if ("success".equals(resutl)) {
						map.put("desc", "数据更新成功");
					} else {
						map.put("desc", "错误：" + resutl);
					}
				} else {
					map.put("desc", "不存在后台配置信息，请确定上传的文件是否正确");
				}
			} else {
				map.put("desc", "非excel文件，请确定上传的文件是否正确");
			}
			map.put("fileName", fileName);
			retList.add(map);
		}
		// 更新版本号
		addVersion(retList);
		model.addAttribute("retList", retList);
		return "config/file";
	}

	/**
	 * 获取添加sql
	 * 
	 */
	public String insertSql(List<Map<String, String>> readExcles, String fileName) {
		StringBuffer insertSql = new StringBuffer();
		insertSql.append("insert into ");
		// 获取表信息
		String tabieName = PropUtli.findprop(fileName);
		String typeProp = PropUtli.findprop(fileName + "_type");
		String sqlProp = PropUtli.findprop(fileName + "_mysql");
		String excelProp = PropUtli.findprop(fileName + "_excel");
		String[] types = typeProp.split(",");
		String[] sqls = sqlProp.split(",");
		String[] excels = excelProp.split(",");
		// 拼接字段名
		insertSql.append(tabieName + "(");
		StringBuffer fields = new StringBuffer();
		for (int i = 0; i < sqls.length; i++) {
			fields.append(sqls[i] + ",");
		}
		insertSql.append(fields.substring(0, fields.length() - 1) + ") values");
		// 拼接属性
		boolean judge = true;
		for (Map<String, String> queryMap : readExcles) {
			if (judge) {
				judge = false;
				continue;
			}
			StringBuffer sb = new StringBuffer();
			sb.append("(");
			for (int i = 0; i < excels.length; i++) {
				// 获得每个字段对应的属性
				String key = excels[i].toLowerCase();
				String value = queryMap.get(key);
				String type = types[i].toLowerCase();
				if (value == null) {
					value = "0";
				}
				if (type.equals("int") || type.equals("integer") || type.equals("short") || type.equals("double")
						|| type.equals("float")) {

					sb.append(value + ",");
				} else if (type.equals("string") || type.equals("date")) {
					// 特殊符号处理
					value = useList(value);
					sb.append("'" + value + "'" + ",");
				}
			}
			insertSql.append(sb.subSequence(0, sb.length() - 1) + "),");
		}
		String insertSqlVal = insertSql.substring(0, insertSql.length() - 1) + ";";
		return insertSqlVal;
	}

	/**
	 * 特殊符号转义
	 */
	public static String useList(String targetValue) {
		String[] arr = { "≈", "≡", "≠", "=", "≤", "≥", "≮", "≯", "∷", "±", "+", "－", "×", "÷", "/", "∫", "∮", "∝", "∞",
				"∧", "∨", "∑", "∏", "∪", "∩", "∈", "∵", "∴", "⊥", "∥", "∠", "⌒", "⊙", "≌", "∽", "√", "∟", "∣", "∶", "≒",
				"≦", "≧", "⊕", "⊿", "°", "′", "″", "$", "￡", "￥", "‰", "%", "℃", "¤", "￠", "`", "[", "]", "、", "；", "‘",
				"，", "。", "！", "·", "…", "—", "*", "（", "）", "～", "{", "}", "|", "：", "“", "《", "？", "〈", "〉", "@", "&",
				"\\", "ˉ", "ˇ", "¨", "々", "‖", "\"", "\'", "〃", "〔", "〕", "「", "」", "『", "』", "．", "〖", "〗", "【", "】",
				"^", "~" };
		boolean resutl = Arrays.asList(arr).contains(targetValue);
		if (resutl) {
			targetValue = "\\" + targetValue;
		}
		return targetValue;
	}

	/**
	 * 获取清空表sql
	 */
	public String deleteSql(String fileName) {
		// 获取表名
		String tabieName = PropUtli.findprop(fileName);
		String delSql = "TRUNCATE TABLE " + tabieName + ";";
		return delSql;
	}

	/**
	 * 文件上传
	 */
	public static List<String> handleFileUpload(List<MultipartFile> files, String filepath) throws IOException {
		List<String> filePaths = new ArrayList<String>();
		MultipartFile file = null;
		BufferedOutputStream stream = null;
		for (int i = 0; i < files.size(); ++i) {
			file = files.get(i);
			if (!file.isEmpty()) {
				try {
					// 上传路径
					String uploadFilePath = file.getOriginalFilename();
					String uploadFileName = uploadFilePath.substring(uploadFilePath.lastIndexOf('\\') + 1,
							uploadFilePath.indexOf('.'));
					String uploadFileSuffix = uploadFilePath.substring(uploadFilePath.indexOf('.') + 1,
							uploadFilePath.length());
					// 获取跟目录
					File path = new File(ResourceUtils.getURL("classpath:").getPath());
					if (!path.exists())
						path = new File("");
					File upload = new File(path.getAbsolutePath(), filepath);
					if (!upload.exists())
						upload.mkdirs();
					String pathfile = upload + "\\" + uploadFileName + "." + uploadFileSuffix;
					System.out.println(pathfile);
					// 删除原文件
					deleteFile(pathfile);
					stream = new BufferedOutputStream(new FileOutputStream(new File(pathfile)));
					byte[] bytes = file.getBytes();
					stream.write(bytes, 0, bytes.length);
					filePaths.add(pathfile);
				} catch (IOException e) {
					e.printStackTrace();
				} finally {
					if (stream != null) {
						stream.close();
					}
				}
			}
		}
		return filePaths;
	}

	/**
	 * 文件删除
	 */
	@ResponseBody
	public static void deleteFile(String filePath) {
		java.io.File file = new java.io.File(filePath);
		// 判断目录或文件是否存在
		boolean exis = file.exists();
		if (exis == true) {
			// 判断是否为文件
			boolean isex = file.isFile();
			if (isex == true) {
				file.delete();
			}
		}
	}

	/**
	 * 执行sql 方法
	 */
	@SuppressWarnings("finally")
	public String executeSql(String sql) {
		PreparedStatement pstmt = JDBCPool.getPstmt(sql);
		String resutl = "success";
		try {
			pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			resutl = e.getMessage();
		} finally {
			JDBCPool.closeUpdateRes(pstmt);
			return resutl;
		}
	}

	/**
	 * 更新版本号
	 * 
	 * @param id=库名
	 * @throws InterruptedException
	 */
	public void addVersion(List<Map<String, String>> retList) throws InterruptedException {
		for (Map<String, String> rets : retList) {
			Map<String, String> retMap = rets;
			if (retMap.get("desc") == "数据更新成功") {
				try {
					String fileName = retMap.get("fileName");
					// 获取数据库表名
					String tabieName = PropUtli.findprop(fileName);
					User user = (User) session.getAttribute("user");
					VersionConfig versionConfig = versionConfigService.findName(fileName);
					if (versionConfig == null) {
						// 添加
						Random ran = new Random();
						versionConfig = new VersionConfig();
						versionConfig.setVersion(1);
						versionConfig
								.setId(ran.nextInt(100) + ran.nextInt(1000) + ran.nextInt(10000) + ran.nextInt(100000));
					} else {
						// 修改
						versionConfig.setVersion(versionConfig.getVersion() + 1);
					}
					// 共同属性
					versionConfig.setName(tabieName);
					versionConfig.setFileName(fileName);
					versionConfig.setTime(new Date());
					versionConfig.setUid(user.getId());
					versionConfigService.addAndUpd(versionConfig);
					logsService.addAndUpd(8, 0, session, "更新了配置表[ " + fileName + " ]的数据");
				} catch (Exception e) {
					retMap.put("desc", retMap.get("desc") + ",---版本号更新错误：" + e.getMessage());
				}
			}
		}
	}
}

9、文件上传表单

   <form id="formDemo" class="layui-form" action="/updateFile" method="post" enctype="multipart/form-data" >
         <div id="divs" class="layui-form-item" style="margin-top: 2%;" >
                   <div class="layui-inline"  style="margin-left: 2%;" >
                                                               文件：<input type="file"  name="file"   multiple=true />
                      
	                    <button id="laySubmit" class="layui-btn layui-btn-sm" lay-submit="" lay-filter="component-form-demo1">确认更新</button>
                                                             可随意选择文件数量,---1、上传的数据必须页签必须方在第一个,---2、第二行为字段行,---3、添加字段或修改了字段请及时沟通,否则将导致数据为0
                  </div>
              </div>
        </form>

全文–> 这里采用的Layui



<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8">
<title>权限管理 - 数据表格</title>
<meta name="renderer" content="webkit">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport" content="width=device-width, initial-scale=1.0, minimum-scale=1.0, maximum-scale=1.0, user-scalable=0">
<link rel="stylesheet" href="../../../layuiadmin/layui/css/layui.css" media="all">
<link rel="stylesheet" href="../../../layuiadmin/style/admin.css" media="all">
 <link rel="stylesheet" href="../../../css/table.css" media="all">
</head>
<body>
<script src="../../../layuiadmin/layui/layui.js"></script>                          
<script src="http://libs.baidu.com/jquery/2.1.4/jquery.min.js"></script>             

<div class="layui-form-item">
	        <div class="layui-form-item">
        <form id="formDemo" class="layui-form" action="/updateFile" method="post" enctype="multipart/form-data" >
         <div id="divs" class="layui-form-item" style="margin-top: 2%;" >
                   <div class="layui-inline"  style="margin-left: 2%;" >
                                                               文件：<input type="file"  name="file"   multiple=true />
                      
	                    <button id="laySubmit" class="layui-btn layui-btn-sm" lay-submit="" lay-filter="component-form-demo1">确认更新</button>
                                                             可随意选择文件数量,---1、上传的数据必须页签必须方在第一个,---2、第二行为字段行,---3、添加字段或修改了字段请及时沟通,否则将导致数据为0
                  </div>
              </div>
        </form>
    </div>
  </div> 
  
<!--    <form action="" method="POST" enctype="multipart/form-data" >
    <p></p>
    <p><input type="submit" value="上传" /></p>
</form>  -->
    
	<fieldset class="layui-elem-field layui-field-title"
		style="margin-top: 20px;">
		<legend>数据更新状态</legend>
	</fieldset>

	<div class="layui-form">
	    <table class="layui-table" >
        <tbody>
			<tr th:each="ret:${retList}" >
	         	<td width="15%"  th:text="${ret.fileName}"></td>
	         	<td width="90%" th:text="${ret.desc}"></td>
			</tr>
		</tbody>
		</table>
	</div>
</body>
<script src="../../../layuiadmin/layui/layui.js"></script>  
<script src="http://libs.baidu.com/jquery/2.1.4/jquery.min.js"></script>

<!-- 注意：如果你直接复制所有代码到本地，上述js路径需要改成你本地的 -->
<script>
layui.use(['form', 'layedit', 'laydate'], function(){
  var form = layui.form
  ,layer = layui.layer
  ,layedit = layui.layedit
  ,laydate = layui.laydate;
 
  //监听submit提交按钮 button ，lay-filter 为 userAdd 的
  form.on('submit(component-form-demo1)', function(data){   
    

	  layer.load();
	  //此处演示关闭
/* 	  setTimeout(function(){
	    layer.closeAll('loading');
	  }, 2000); */
	  $("#formDemo").submit;
       //阻止页面跳转
       // return false;
  });
});
</script>

</html>

版本号有需要的也可以使用，建表sql


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for VersionConfig
-- ----------------------------
DROP TABLE IF EXISTS `VersionConfig`;
CREATE TABLE `VersionConfig`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '配置表名',
  `fileName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'excel文件名',
  `uid` int(11) DEFAULT NULL COMMENT '最后更新用户',
  `version` int(11) DEFAULT NULL COMMENT '版本号',
  `time` datetime(0) DEFAULT NULL COMMENT '最后更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 109145 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;