易云自助建站,南京户外广告公司排行榜,搜索引擎推广与优化,企业网店推广运营策略一、背景
公司运营的同事有个任务#xff0c;提供一个数据文件给我#xff0c;然后从数据库中找出对应的加密串再导出来给他。这个活不算是很难#xff0c;但时不时就会有需求。
同事给我的文件有时是给excel表格#xff0c;每一行有4列#xff0c;逗号隔开#xff0c;…一、背景
公司运营的同事有个任务提供一个数据文件给我然后从数据库中找出对应的加密串再导出来给他。这个活不算是很难但时不时就会有需求。
同事给我的文件有时是给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 version1.0 encodingUTF-8?
project xmlnshttp://maven.apache.org/POM/4.0.0xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsdparentgroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-parent/artifactIdversion2.5.14/version/parentmodelVersion4.0.0/modelVersiongroupIdcom.xxx/groupIdartifactIdtest/artifactIdversion1.0/versionpropertiesmaven.compiler.source8/maven.compiler.sourcemaven.compiler.target8/maven.compiler.target/propertiesdependencies!-- Spring框架基本的核心工具 --dependencygroupIdorg.springframework/groupIdartifactIdspring-context-support/artifactId/dependency!-- SpringBoot Web容器 --dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactIdexclusionsexclusiongroupIdorg.apache.tomcat.embed/groupIdartifactIdtomcat-embed-el/artifactId/exclusionexclusiongroupIdorg.apache.tomcat.embed/groupIdartifactIdtomcat-embed-websocket/artifactId/exclusion/exclusions/dependency!-- thymeleaf --dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-thymeleaf/artifactId/dependencydependencygroupIdorg.mybatis.spring.boot/groupIdartifactIdmybatis-spring-boot-starter/artifactIdversion2.1.4/versionexclusionsexclusiongroupIdcom.zaxxer/groupIdartifactIdHikariCP/artifactId/exclusion/exclusions/dependency!-- 阿里数据库连接池 --dependencygroupIdcom.alibaba/groupIdartifactIddruid-spring-boot-starter/artifactIdversion1.2.16/version/dependency!-- Mysql驱动包 --dependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactId/dependency!--常用工具类 --dependencygroupIdorg.apache.commons/groupIdartifactIdcommons-lang3/artifactId/dependency!-- io常用工具类 --dependencygroupIdcommons-io/groupIdartifactIdcommons-io/artifactIdversion2.11.0/version/dependency!-- excel工具 --dependencygroupIdorg.apache.poi/groupIdartifactIdpoi-ooxml/artifactIdversion4.1.2/versionexclusionsexclusiongroupIdorg.apache.commons/groupIdartifactIdcommons-math3/artifactId/exclusionexclusiongroupIdorg.zaxxer/groupIdartifactIdSparseBitSet/artifactId/exclusion/exclusions/dependency!-- servlet包 --dependencygroupIdjavax.servlet/groupIdartifactIdjavax.servlet-api/artifactId/dependency!-- lombok --dependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdscopeprovided/scope/dependency/dependenciesbuildpluginsplugingroupIdorg.apache.maven.plugins/groupIdartifactIdmaven-compiler-plugin/artifactIdversion3.1/versionconfigurationsource${java.version}/sourcetarget${java.version}/targetencoding${project.build.sourceEncoding}/encoding/configuration/pluginplugingroupIdorg.springframework.boot/groupIdartifactIdspring-boot-maven-plugin/artifactIdversion2.7.3/versionexecutionsexecutiongoalsgoalrepackage/goal/goals/execution/executionsconfigurationmainClasscom.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) {ListHelmetConfig list helmetService.queryAll();ExcelUtilHelmetConfig 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());ListCellItem 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 ListCellItem getData(MultipartFile file) {InputStream inputStream null;ListCellItem 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();//第N1行开始可以通过传参从第N1行开始取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(ListCellItem 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 ListCellItem readDat(MultipartFile file) {ListCellItem 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(ListCellItem 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?useUnicodetrueuseSSLfalseallowLoadLocalInfilefalseautoReconnecttruecharacterEncodingutf8zeroDateTimeBehaviorconvertToNullserverTimezoneGMT%2B8username: rootpassword: root#Spring Boot 默认是不注入这些属性值的需要自己绑定#druid 数据源专有配置initialSize: 5minIdle: 5maxActive: 20maxWait: 60000timeBetweenEvictionRunsMillis: 60000minEvictableIdleTimeMillis: 300000validationQuery: SELECT 1 FROM DUALtestWhileIdle: truetestOnBorrow: falsetestOnReturn: falsepoolPreparedStatements: true2.3 前端处理逻辑
把layui的相关文件放到resources/static目录再新建一个index.html文件放入resources/templates目录这两个目录是thymeleaf默认的如果要修改可以在application.yml进行配置。静态文件如下 为了压缩jar包的体积把所有不必要的文件都精简掉了。
以下是index.html内容
!DOCTYPE html
html langen xmlns:thhttp://www.thymeleaf.org
headmeta charsetUTF-8title测试/titlescript th:src{/layui.js}/scriptlink relstylesheet th:href{/css/layui.css} mediaall
/head
bodydiv classlayui-carddiv classlayui-card-header操作面板/divdiv classlayui-card-bodydiv classlayui-tab lay-filterwindowul classlayui-tab-titleli classlayui-this lay-iduploadTab文件上传/lili lay-iddownloadTab文件下載/li/uldiv classlayui-tab-contentdiv classlayui-tab-item layui-showform idupload_form classlayui-form enctypemultipart/form-datadiv classlayui-form-itemlabel classlayui-form-label文件/labeldiv classlayui-input-blockbutton typebutton classlayui-btn iduploadi classlayui-icon#xe61f;/i选择文件/button/div/divdiv classlayui-form-itemdiv classlayui-input-blockbutton idbtnSubmit classlayui-btn onclickreturn false;立即提交/button/div/div/form/divdiv classlayui-tab-itemdiv classlayui-form-itemlabel classlayui-form-label文件/labeldiv classlayui-input-blockbutton typebutton classlayui-btn iddownloadBtni classlayui-icon#xe601;/i下载文件/button/div/div/div/div/div/div/div
/body
/html
scriptlayui.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编辑好测试没问题直接打包放到服务器上执行就可以啦。
三、实现效果
3.1 文件导入 导入成功后会自动切换到【文件下载】的tab页
3.2 文件导出
