配置文件pom文件
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.2.4.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.example.yan</groupId> <artifactId>excel_split</artifactId> <version>0.0.1-SNAPSHOT</version> <name>excel_split</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <!-- <dependency>--> <!-- <groupId>org.springframework.boot</groupId>--> <!-- <artifactId>spring-boot-starter-security</artifactId>--> <!-- </dependency>--> <dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <!-- <version>5.7.14</version>--> <version>4.6.1</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <!-- Java运行环境的系统信息工具类 lang是java的核心类，这个commons-lang就是针对他的基础包--> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> <!-- <version>5.0.0</version>--> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- <dependency>--> <!-- <groupId>net.sf.json-lib</groupId>--> <!-- <artifactId>json-lib</artifactId>--> <!-- <version>2.4</version>--> <!-- <classifier>jdk15</classifier>--> <!-- </dependency>--> <!--alibaba Json--> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.47</version> </dependency> <!-- <dependency>--> <!-- <groupId>org.thymeleaf.extras</groupId>--> <!-- <artifactId>thymeleaf-extras-springsecurity5</artifactId>--> <!-- </dependency>--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- <dependency>--> <!-- <groupId>org.springframework.security</groupId>--> <!-- <artifactId>spring-security-test</artifactId>--> <!-- <scope>test</scope>--> <!-- </dependency>--> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> </project>
前段写了一个导入文件的按钮用的layui的
<!DOCTYPE html> <html> <head> <meta charset="utf-8"> <title>Layui</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, maximum-scale=1"> <link rel="stylesheet" href="../layui/css/layui.css" media="all"> <script src="../layui/layui.js" charset="utf-8"></script> <!-- <script src="../upload.js" charset="utf-8"></script>--> <!-- <link rel="stylesheet" href="../static/layui.css" media="all">--> <!-- <script src="../static/layui.js" charset="utf-8"></script>--> <!-- 注意：如果你直接复制所有代码到本地，上述 JS 路径需要改成你本地的 --> <!-- 注意：如果你直接复制所有代码到本地，上述css路径需要改成你本地的 --> </head> <blockquote class="layui-elem-quote layui-text"> 提示：以下示例的部分上传接口由第三方网站 <em>http://httpbin.org</em> 提供，它可以模拟各类 HTTP 请求。 <br>其他示例未配置上传接口，所以每次上传都会报「请求上传接口出现异常」的提示，这属于正常现象。 </blockquote> <body> <a name="list-progress"> </a> <div style="margin-top: 10px;"> </div> <fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;"> <legend>指定允许上传的文件类型</legend> </fieldset> <div class="layui-btn-container"> <button type="button" class="layui-btn" id="test3"><i class="layui-icon"></i>上传文件</button> <button type="button" class="layui-btn layui-btn-primary" id="test4"><i class="layui-icon"></i>只允许压缩文件</button> <button type="button" class="layui-btn" id="test5"><i class="layui-icon"></i>上传视频</button> <button type="button" class="layui-btn" id="test6"><i class="layui-icon"></i>上传音频</button> </div> <fieldset class="layui-elem-field layui-field-title" style="margin-top: 30px;"> <legend>拖拽上传</legend> </fieldset> <div class="layui-upload-drag" id="test10"> <i class="layui-icon"></i> <p>点击上传，或将文件拖拽到此处</p> <div class="layui-hide" id="uploadDemoView"> <hr> <img src="" alt="上传成功后渲染" style="max-width: 196px"> </div> </div> <script> layui.use(['upload', 'element', 'layer'], function(){ var $ = layui.jquery ,upload = layui.upload ,element = layui.element ,layer = layui.layer; //指定允许上传的文件类型 upload.render({ elem: '#test3' ,url: '/user/file' //此处配置你自己的上传接口即可 ,accept: 'file|xls' //普通文件 , exts: 'xls|xlsx|xlsm|xlt|xltx|xltm|txt' ,done: function(res){ layer.msg('上传成功'); console.log(res); } }); upload.render({ //允许上传的文件后缀 elem: '#test4' ,url: 'user/file/' //此处配置你自己的上传接口即可 ,accept: 'file' //普通文件 ,exts: 'zip|rar|7z' //只允许上传压缩文件 ,done: function(res){ layer.msg('上传成功'); console.log(res) } }); //拖拽上传 upload.render({ elem: '#test10' // ,url: 'https://httpbin.org/post' //此处用的是第三方的 http 请求演示，实际使用时改成您自己的上传接口即可。 ,url: 'user/file/' ,done: function(res){ layer.msg('上传成功'); layui.$('#uploadDemoView').removeClass('layui-hide').find('img').attr('src', res.files.file); console.log(res) } }); }); </script> </body> </html>
后端的demo接收文件
package com.example.yan.excel_split.controller; import cn.hutool.core.io.file.FileReader; import cn.hutool.poi.excel.ExcelReader; import cn.hutool.poi.excel.ExcelUtil; import com.alibaba.fastjson.JSON; import lombok.extern.slf4j.Slf4j; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.util.List; @Controller @Slf4j @RequestMapping("/user") public class UserController { //访问页面跳转 @RequestMapping("/abc") public String abc() { return "file"; } @RequestMapping("/file") @ResponseBody public String file(MultipartFile file){ log.info("开始上传附件"); //默认UTF-8编码，可以在构造中传入第二个参数做为编码 ExcelReader reader = null; try { //解析导入的文件内容 reader = ExcelUtil.getReader(file.getInputStream()); } catch (IOException e) { e.printStackTrace(); } file.getName();//文件名 List<List<Object>> readAll = reader.read(); for (List<Object> object : readAll) { String s = JSON.toJSONString(object); s.split(s); System.out.println(); } return "8888888"; } }
小知识 可以删除去掉JSON文件中空格
List<String> list = object.stream().map(o -> o.toString()).collect(Collectors.toList()); // 先转成 string List<String> filtered = list.stream().filter(string -> !string.isEmpty()).collect(Collectors.toList()); //isEmpty 去掉空的值
去重处理demo
public static List<String> zdh(List<String> list) { List<String> myList = list.stream().distinct().collect(Collectors.toList()); System.out.println("==>" + myList); return myList; }
我这边的需求是导入一个excel 根据 消费 二维码消费等等拆分为3个excel内容 故下面的操作实现这一步内容 核心处理分析页面
package com.example.yan.excel_split.controller; import cn.hutool.core.collection.CollUtil; import cn.hutool.json.JSONArray; import cn.hutool.poi.excel.ExcelReader; import cn.hutool.poi.excel.ExcelUtil; import cn.hutool.poi.excel.ExcelWriter; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONObject; import org.apache.commons.lang3.StringUtils; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.*; import static cn.hutool.core.util.NumberUtil.add; public class test6 { // 解析excel，按终端号对数据进行分组，然后对分组数据进行合计，最后将数据合并之后导出excel public static void main(String[] args) { // 把excel解析成key/value的形式 ExcelReader reader = ExcelUtil.getReader("E:\\\\桌面\\\\SHOP.105110000000005.20211013.02.success(1).xls"); List<List<Object>> list = reader.read(); String header = null; //删除空的单元格 for (List<Object> objects : list) { // 迭代删除空单元格 Iterator<Object> it = objects.iterator(); while (it.hasNext()) { Object cellValue = it.next(); if (cellValue == null || StringUtils.isBlank(cellValue.toString())) { it.remove(); } } // System.out.println("行数据" + JSON.toJSONString(objects)); String merchantCode = JSON.toJSONString(objects); if (merchantCode.contains("商户编号") && merchantCode.contains("商户地址")) { System.out.println("merchantCode==>" + merchantCode); //把所有的中括号取消 String str = merchantCode.substring(merchantCode.indexOf("[") + 1, merchantCode.lastIndexOf("]")); String st = "商户/部门编号:" + str.substring(12, 26); header = st; } } // 我们想要解析出来的表头 String[] keyArr = new String[]{"终端号", "发卡行", "卡种", "序列号", "交易日期", "交易时间", "交易类型", "授权号", "交易金额", "小费", "分期期数", "银行手续费", "DCC返还手续费", "划账金额", "凭证号", "批次号", "POS交易序号", "结算账号", "订单号", "柜台编号", "系统参考号", "持卡人姓名", "付款凭证号", "备注1", "备注2"}; JSONArray dataList = new JSONArray(); for (List<Object> aLineList : list) { if (aLineList.size() < keyArr.length)// 数据不满足表头长度的不要，说明该行数据不符合我们想要的格式 continue; Map<String, Object> hashMap = new HashMap<>(); for (int j = 0; j < keyArr.length; j++) { if (j < aLineList.size()) { Object property = aLineList.get(j); hashMap.put(keyArr[j], property); } } dataList.add(hashMap); } System.out.println("解析之后所有符合条件的数据列表===" + dataList); // 终端编号的数组，最终得到一个去重的编号组 //去重json 把相同的string删除 List<String> noList = new ArrayList<>(); for (Object objects : dataList) { JSONObject jsonObject = JSON.parseObject(objects.toString()); String no = jsonObject.getString("终端号"); if (StringUtils.isNotBlank(no)) { // 去除表头，只要终端编号 if (!no.equals("终端号")) { noList.add(no); } } } //下面获取的3个表格中每个金额的z double totalNew = 0; double totalNewB = 0; double totalNewC = 0; BigDecimal totaA = new BigDecimal("0"); BigDecimal totaB = new BigDecimal("0"); BigDecimal totaC = new BigDecimal("0"); BigDecimal total1 = new BigDecimal("0"); for (Object objects1 : dataList) { Map jsonObject = JSON.parseObject(objects1.toString()); String jylx = jsonObject.get("交易类型").toString(); switch (jylx) { case "消费": String amount = jsonObject.get("交易金额").toString(); totaA = total1.add(new BigDecimal(amount)); System.out.println("totaA==>" + totaA); totalNew += Double.parseDouble(amount); totaA = new BigDecimal(totalNew); break; case "银联二维码消费(被扫)": String amount1 = jsonObject.get("交易金额").toString(); totalNewB += Double.parseDouble(amount1); totaB = new BigDecimal(totalNewB); break; case "外卡消费": String amount2 = jsonObject.get("交易金额").toString(); totalNewC += Double.parseDouble(amount2); totaC = new BigDecimal(totalNewC); break; } } System.out.println("-----" + totalNew); Integer i = 1; // 编号去重 if (noList.size() > 0) { List<String> newNoList = new ArrayList<>(new TreeSet<>(noList)); if (newNoList.size() > 0) { System.out.println("去重后的编号列表===" + newNoList); List<Map> allList = new ArrayList<>(); List<Map> allListB = new ArrayList<>(); List<Map> allListC = new ArrayList<>(); // 要把原来的数据根据编号进行分组过滤 List<Map> objList1 = new ArrayList<>(); List<Map> objListA = new ArrayList<>(); for (String no : newNoList) { List<Map> objList = new ArrayList<>(); List<Map> objListB = new ArrayList<>(); List<Map> objListC = new ArrayList<>(); // 合计值 BigDecimal total = new BigDecimal("0"); BigDecimal totalB = new BigDecimal("0"); BigDecimal totalC = new BigDecimal("0"); // 序号 int num = 0; for (Object objects : dataList) { // Map jsonObject = JSON.parseObject(objects.toString()); Map jsonObject = JSON.parseObject(objects.toString()); Map row1 = new LinkedHashMap(); // Map<String, Object> row1 = new LinkedHashMap<>(); Map<String, Object> row11 = new LinkedHashMap<>(); Map<String, Object> row2 = new LinkedHashMap<>(); Map<String, Object> row3 = new LinkedHashMap<>(); String objNo = jsonObject.get("终端号").toString(); String fkh = jsonObject.get("发卡行").toString(); String kz = jsonObject.get("交易金额").toString(); String jylx = jsonObject.get("交易类型").toString(); if (objNo.equals(no)) { String amount = jsonObject.get("交易金额").toString(); switch (jylx) { case "消费": num++; row1.put("序号", num); if (!objNo.contains("终端号")) { row1.put("终端号", objNo); row1.put("发卡行", fkh); row1.put("交易金额", kz); row1.put("交易类型", jylx); } total = total.add(new BigDecimal(amount)); objList.add(row1); break; case "银联二维码消费(被扫)": row1.put("序号", num); if (!objNo.contains("终端号")) { row2.put("序号", num); row2.put("终端号", objNo); row2.put("发卡行", fkh); row2.put("交易金额", kz); row2.put("交易类型", jylx); } totalB = totalB.add(new BigDecimal(amount)); objListB.add(row2); break; case "外卡消费": row1.put("序号", num); if (!objNo.contains("终端号")) { row3.put("序号", num); row3.put("终端号", objNo); row3.put("发卡行", fkh); row3.put("交易金额", kz); row3.put("交易类型", jylx); } totalC = totalC.add(new BigDecimal(amount)); objListC.add(row3); break; } // objList.add(row1); // 计算合计 // 序号 i++; } } if (objList.size() > 0) { Map totalMap = new HashMap<>(); totalMap.put("序号", ""); totalMap.put("终端号", "合计"); totalMap.put("发卡行", ""); totalMap.put("交易金额", total); objList.add(totalMap); allList.addAll(objList); } if (objListB.size() > 0) { Map totalMap = new HashMap<>(); totalMap.put("序号", ""); totalMap.put("终端号", "合计"); totalMap.put("交易金额", total); objListB.add(totalMap); allListB.addAll(objListB); } if (objListC.size() > 0) { Map totalMap = new HashMap<>(); totalMap.put("序号", ""); totalMap.put("终端号", "合计"); totalMap.put("交易金额", total); objListC.add(totalMap); allListC.addAll(objListC); } } if (allList.size() > 0) { Map totalMap = new HashMap<>(); totalMap.put("序号", "总计合约"); totalMap.put("终端号", ""); totalMap.put("发卡行", ""); totalMap.put("交易金额", totaA); objListA.add(totalMap); allList.addAll(objListA); System.out.println("根据编号分组之后的数据" + allList); // 导出 exportExcel(allList, "A数据", header); } if (allListB.size() > 0) { Map totalMap = new HashMap<>(); totalMap.put("序号", "总计合约"); totalMap.put("终端号", ""); totalMap.put("发卡行", ""); totalMap.put("交易金额", totaB); allListB.add(totalMap); allList.addAll(allListB); System.out.println("根据编号分组之后的数据" + allList); // 导出 exportExcel(allListB, "B数据", header); } if (allListC.size() > 0) { Map totalMap = new HashMap<>(); totalMap.put("序号", "总计合约"); totalMap.put("终端号", ""); totalMap.put("发卡行", ""); totalMap.put("交易金额", totaC); allListC.add(totalMap); allList.addAll(allListC); // 导出 exportExcel(allListC, "C数据", header); } } } } //下面是利用 hutool的导出excel 的接口有兴趣的 可以去看看文档 private static void exportExcel(List<Map> list, String fileName, String header) { // 数据写入到excel中 /* ExcelWriter writer = ExcelUtil.getWriter("E:\\个人\\test\\" + fileName + ".xls"); writer.write(list); writer.close();*/ Date date = new Date(); ExcelWriter writer = ExcelUtil.getWriter("E:\\桌面\\新建文件夹(4)\\" + exportDate(date) + fileName + ".xlsx"); // ArrayList<Object> rows4 = CollUtil.newArrayList(list); // 默认的，未添加alias的属性也会写出，如果想只写出加了别名的字段，可以调用此方法排除之 writer.setOnlyAlias(true); // 合并单元格后的标题行，使用默认标题样式 writer.merge(3, header); // 一次性写出内容，使用默认样式，强制输出标题 writer.write(list, true); // 关闭writer，释放内存 writer.close(); System.out.println("执行完了"); } //获取当下时间 private static String exportDate(Date date) { SimpleDateFormat sdf = new SimpleDateFormat();// 格式化时间 // sdf.applyPattern("yyyy-MM-dd HH:mm:ss a");// a为am/pm的标记 sdf.applyPattern("yyyyMMdd"); // Date date = new Date();// 获取当前时间 System.out.println("现在时间：" + sdf.format(date)); // return sdf.format(date); } }
导出文件
下面我新增一个可以在浏览器直接导出的demo 是可以是用来在前段页面导出的
package com.example.yan.excel_split.controller; import cn.hutool.poi.excel.ExcelUtil; import cn.hutool.poi.excel.ExcelWriter; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.util.*; public class demo7 { @RequestMapping(value = "/q") public String aaa(){ return "788888"; } @RequestMapping(value = "/to",method = RequestMethod.GET) public void toDownload(HttpServletResponse response) { System.out.println("666"); String fileName = "测试"; try { List<Map<String, Object>> rows = new ArrayList<>(); Random r = new Random(); for (int i = 0; i < 10; i++) { Map<String, Object> map = new HashMap<>(); map.put("ID", i + 1); map.put("名字", "的去" + i); map.put("年龄", i + 18); rows.add(map); } response.setContentType("application/octet-stream"); //设置文件夹名称 response.setHeader("Content-disposition", "attachment;fileName=" + fileName + ".xlsx"); OutputStream out = response.getOutputStream(); //用工具类创建writer 默认xls格式 ExcelWriter writer = ExcelUtil.getWriter(); writer.write(rows); writer.flush(out); writer.close(); out.flush(); System.out.println("执行完了"); } catch (Exception e) { e.printStackTrace(); } } }
导出压缩包
@RequestMapping(value = "/po") public void poizip(HttpServletResponse response) throws IOException { //response 输出流 ServletOutputStream out = response.getOutputStream(); //压缩输出流 ZipOutputStream zipOutputStream = new ZipOutputStream(out); try { for (int i = 0; i < 6; i++) { //创建工作簿 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("sheet" + i); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); cell.setCellValue("内容" + i); response.setContentType("application/octet-stream; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + 88888 + "测试.zip"); //重点开始,创建压缩文件 ZipEntry z = new ZipEntry(i + ".xls"); zipOutputStream.putNextEntry(z); //写入一个压缩文件 wb.write(zipOutputStream); } zipOutputStream.flush(); } catch (IOException e) { e.printStackTrace(); } finally { //注意关闭顺序，否则可能文件错误 if (zipOutputStream != null) { zipOutputStream.close(); } if (out != null) { out.close(); } } }
