导出zip的前段 自定义的借口
<!DOCTYPE html> <!DOCTYPE html> <html> <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, maximum-scale=1"> <link rel="stylesheet" href="../layui/css/layui.css" media="all"> <script src="../layui/layui.js" charset="utf-8"></script> <script src="../layui/jquery-3.2.1.min.js" type="text/javascript" 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"> 提示：以下示例的是excel的文件的导入。 <!-- <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 layui-btn-fluid" id="test3"><i class="layui-icon"></i>上传文件</button>--> <!-- <!– <button class="layui-btn" οnclick="exportData();">导出</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>–>--> <!--原生的--> <form action="/excel/file" method="post" encType="multipart/form-data"> <input type="file" class="layui-btn layui-btn-normal" name="file"> <button type="submit" class="layui-btn layui-btn-fluid">上传</button> </form> </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: '/excel/file' //此处配置你自己的上传接口即可 , accept: 'file|xls' //普通文件 , exts: 'xls|xlsx|xlsm|xlt|xltx|xltm|txt' , done: function (res) { layer.msg('上传成功11'); console.log(res.data);// 先打印一下 if (res.code == 0) { layer.msg('上传成功'); // // 如果 导入成功再导出 // exportExcel(res.data); } } }); // // , error: function (res) { // // var dd = res.responseText.replace(/<\/?.+?>/g,""); // // var text = dd.replace(/ /g, "");//去掉 所有的空格 // // n++, o.msg("请求上传接口出现异常" + text), m(e), u() // // } // // // }); // // function file(list) { // // 用个ajax吧 // $.ajax({ // url: '/excel/file', // type: 'GET', // dataType: 'json', // data: { // list: list, // fileName: '10086' // }, // success: function (resultData) { // layer.msg('导出成功'); // } // }); // } // 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) // } // }); }); // function exportData() { // window.open("/user/exportExcel"); </script> </body> </html>
控制台的逻辑代码处理
package com.example.yan.excel_split.controller; 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 lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; 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.*; import java.math.BigDecimal; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.*; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream; @Controller @Slf4j @RequestMapping("/excel") public class UserController_zip { private final static Logger logger = LoggerFactory.getLogger(UserController_zip.class); @RequestMapping("/abc") public String abc() { return "file_zip"; } @RequestMapping(value = "/file", method = RequestMethod.POST) @ResponseBody public Map<String, Object> file(@RequestParam(value = "file") MultipartFile file, HttpServletResponse response) throws IOException { // 返回一个layui可识别的返回值 先默认成功吧 后面需要处理上传失败或者处理失败的情况 // retrue resultMap; Map map = new HashMap<String, Object>(); map.put("code", 0);// 0-表示成功 map.put("msg", "上传成功"); map.put("count", 1); Map<String, Object> resultMap = new HashMap<>(); resultMap.put("code", 0);// 0-表示成功 resultMap.put("msg", "上传成功"); resultMap.put("count", 1); log.info("开始上传附件"); //默认UTF-8编码，可以在构造中传入第二个参数做为编码 ExcelReader reader = null; try { //解析导入的文件内容 reader = ExcelUtil.getReader(file.getInputStream()); 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(); } } 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); // 终端编号的数组，最终得到一个去重的编号组 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); } } } 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; } } // 3、处理数据之后，生成转化后的字节流 List<ByteArrayOutputStream> bosList = new ArrayList<>();// 保存字节流数据 List<String> excelName = new ArrayList<>();// 保存单个excel的文件名 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; int num1 = 0; int num2 = 0; for (Object objects : dataList) { // Map jsonObject = JSON.parseObject(objects.toString()); Map jsonObject = JSON.parseObject(objects.toString()); Map row1 = new LinkedHashMap(); Map row2 = new LinkedHashMap(); Map row3 = 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 terNumber = jsonObject.get("终端号").toString(); String isBank = jsonObject.get("发卡行").toString(); String tranAmout = jsonObject.get("交易金额").toString(); String tradeType = jsonObject.get("交易类型").toString(); String carsds = jsonObject.get("卡种").toString(); String cardNumber = jsonObject.get("序列号").toString(); String tradeDate = jsonObject.get("交易日期").toString(); String tradingHour = jsonObject.get("交易时间").toString(); String authCode = jsonObject.get("授权号").toString(); String tip = jsonObject.get("小费").toString(); String inPeriods = jsonObject.get("分期期数").toString(); String bankCharges = jsonObject.get("银行手续费").toString(); String refundComm = jsonObject.get("DCC返还手续费").toString(); String arrivalAmout = jsonObject.get("划账金额").toString(); String voucherNumber = jsonObject.get("凭证号").toString(); String bathNumber = jsonObject.get("批次号").toString(); String tranNumber = jsonObject.get("POS交易序号").toString(); String settAccount = jsonObject.get("结算账号").toString(); String orderNumber = jsonObject.get("订单号").toString(); String counterNumber = jsonObject.get("柜台编号").toString(); String systemNumber = jsonObject.get("系统参考号").toString(); String cardName = jsonObject.get("持卡人姓名").toString(); String PayMent = jsonObject.get("付款凭证号").toString(); if (terNumber.equals(no)) { String amount = jsonObject.get("交易金额").toString(); switch (tradeType) { case "消费": case "消费撤销": case "联机退货": num++; if (!terNumber.contains("终端号")) { row1.put("序号", num); row1.put("终端号", terNumber); row1.put("发卡行", isBank); row1.put("交易金额", tranAmout); row1.put("交易类型", tradeType); row1.put("卡种", carsds); row1.put("卡号-序列号", cardNumber); row1.put("交易日期", tradeDate); row1.put("交易时间", tradingHour); row1.put("授权号", authCode); row1.put("小费", tip); row1.put("分期期数", inPeriods); row1.put("银行手续费", bankCharges); row1.put("DCC返还手续费", refundComm); row1.put("划账金额", arrivalAmout); row1.put("凭证号", voucherNumber); row1.put("批次号", bathNumber); row1.put("POS交易序号", tranNumber); row1.put("结算账号", settAccount); row1.put("订单号", orderNumber); row1.put("柜台编号", counterNumber); row1.put("系统参考号", systemNumber); row1.put("持卡人姓名", cardName); row1.put("付款凭证号", PayMent); } total = total.add(new BigDecimal(amount)); objList.add(row1); break; case "银联二维码消费(被扫)": case "银联二维码退货": //row2.put("序号", num); num2++; if (!terNumber.contains("终端号")) { row2.put("序号", num2); row2.put("终端号", terNumber); row2.put("发卡行", isBank); row2.put("交易金额", tranAmout); row2.put("交易类型", tradeType); row2.put("卡种", carsds); row2.put("卡号-序列号", cardNumber); row2.put("交易日期", tradeDate); row2.put("交易时间", tradingHour); row2.put("授权号", authCode); row2.put("小费", tip); row2.put("分期期数", inPeriods); row2.put("银行手续费", bankCharges); row2.put("DCC返还手续费", refundComm); row2.put("划账金额", arrivalAmout); row2.put("凭证号", voucherNumber); row2.put("批次号", bathNumber); row2.put("POS交易序号", tranNumber); row2.put("结算账号", settAccount); row2.put("订单号", orderNumber); row2.put("柜台编号", counterNumber); row2.put("系统参考号", systemNumber); row2.put("持卡人姓名", cardName); row2.put("付款凭证号", PayMent); } totalB = totalB.add(new BigDecimal(amount)); objListB.add(row2); break; case "外卡消费": num1++; // row3.put("序号", num); if (!terNumber.contains("终端号")) { row3.put("序号", num1); row3.put("终端号", terNumber); row3.put("发卡行", isBank); row3.put("交易金额", tranAmout); row3.put("交易类型", tradeType); row3.put("卡种", carsds); row3.put("卡号-序列号", cardNumber); row3.put("交易日期", tradeDate); row3.put("交易时间", tradingHour); row3.put("授权号", authCode); row3.put("小费", tip); row3.put("分期期数", inPeriods); row3.put("银行手续费", bankCharges); row3.put("DCC返还手续费", refundComm); row3.put("划账金额", arrivalAmout); row3.put("凭证号", voucherNumber); row3.put("批次号", bathNumber); row3.put("POS交易序号", tranNumber); row3.put("结算账号", settAccount); row3.put("订单号", orderNumber); row3.put("柜台编号", counterNumber); row3.put("系统参考号", systemNumber); row3.put("持卡人姓名", cardName); row3.put("付款凭证号", PayMent); } 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("发卡行", ""); // totalMap.put("交易金额", totalB); // objListB.add(totalMap); allListB.addAll(objListB); } if (objListC.size() > 0) { // Map totalMap = new HashMap<>(); // totalMap.put("序号", "合计"); // totalMap.put("终端号", ""); // totalMap.put("发卡行", ""); // totalMap.put("交易金额", totalC); // 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); // exportExcel1(allList, "建设银行国内卡", header); bosList.add(createExcel(allList, header)); excelName.add("建设银行国内卡" + ".xls"); } if (allListB.size() > 0) { // Map totalMap = new HashMap<>(); // totalMap.put("序号", "总计合约"); // totalMap.put("终端号", ""); // totalMap.put("发卡行", ""); // totalMap.put("交易金额", totaB); // allListB.add(totalMap); allListB.addAll(allListB); // System.out.println("根据编号分组之后的数据" + allList); // 导出 // exportExcel1(allListB, "建设银行银联二维码", header); bosList.add(createExcel(allListB, header)); excelName.add("建设银行银联二维码" + ".xls"); } if (allListC.size() > 0) { // Map totalMap = new HashMap<>(); // totalMap.put("序号", "总计合约"); // totalMap.put("终端号", ""); // totalMap.put("发卡行", ""); // totalMap.put("交易金额", totaC); //allListC.add(totalMap); allListC.addAll(allListC); // 导出 //exportExcel1(allListC, "建设银行国外卡", header); bosList.add(createExcel(allListB, header)); excelName.add("建设银行国外卡" + ".xls"); } } // 5、开始导出ZIP 创建HttpServerResponse的输出流 OutputStream out = response.getOutputStream(); // 创建要写入的文件 File outFile = new File("excel.zip"); // 通过ZipOutputStream定义要写入的对象 ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(outFile)); // 将处理好的excel数据流写入到zip流 writeZos(bosList, zos, excelName); zos.close(); // 设置请求头 定义返回类型 response.setContentType("text/html; charset=UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("excel.zip", "UTF-8")); // 创建写入流 BufferedInputStream bis = new BufferedInputStream(new FileInputStream("excel.zip")); // 定义byte，长度就是要转成zip文件的byte长度，避免浪费资源 byte[] buffer = new byte[bis.available()]; bis.read(buffer); out.flush(); out.write(buffer); return null; } } catch (IOException e) { e.printStackTrace(); logger.error("=======" + e); } return resultMap; } public ByteArrayOutputStream createExcel(List<Map> list, String header) { ExcelWriter writer = ExcelUtil.getWriter(); writer.merge(3, header); writer.write(list, true); // 写入流 ByteArrayOutputStream out = new ByteArrayOutputStream(); writer.flush(out); writer.close(); return out; } private static ByteArrayOutputStream exportExcel1(List<Map> list, String fileName, String header) { Date date = new Date(); String path = "home/bank/bankdata"; String path1 = " E:\\桌面\\新建文件夹(4)\\"; //ExcelWriter writer = ExcelUtil.getWriter(path1 + exportDate(date) + fileName + ".xlsx"); ExcelWriter writer = ExcelUtil.getWriter(); writer.setOnlyAlias(true); // 合并单元格后的标题行，使用默认标题样式 writer.merge(3, header); // 一次性写出内容，使用默认样式，强制输出标题 writer.write(list, true); // 写入流 ByteArrayOutputStream out = new ByteArrayOutputStream(); writer.flush(out); // 关闭writer，释放内存 writer.close(); System.out.println("执行完了导出在" + path1); return out; } //获取当下时间 private static String exportDate(Date date) { SimpleDateFormat sdf = new SimpleDateFormat();// 格式化时间 sdf.applyPattern("yyyyMMdd"); System.out.println("现在时间：" + sdf.format(date)); // return sdf.format(date); } /** * 把生成的excel的结果流，放到zip流中，以便导出 * * @param bosList 带数据的字节流合集 * @param zos zip流 * @param excelName 文件名合集 * @throws IOException */ public void writeZos(List<ByteArrayOutputStream> bosList, ZipOutputStream zos, List<String> excelName) throws IOException { for (int i = 0; i < bosList.size(); i++) { //将多个excel都转成字节流写入 zos.putNextEntry(new ZipEntry(excelName.get(i))); byte[] excelStream = bosList.get(i).toByteArray(); zos.write(excelStream); //记得关闭 zos.closeEntry(); } } }
整体DEMO
package com.xyz.layuiDemo.controller; 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.lang.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.math.BigDecimal; import java.net.URLEncoder; import java.util.*; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream; /** * Created by Administrator on 2017/9/8. */ @Controller @RequestMapping("/style/test") public class StyleTestController extends BaseController { private static final Logger LOGGER = LoggerFactory.getLogger(StyleTestController.class); @RequestMapping(value = "/toDown", method = RequestMethod.GET) public String toDown() { return "upload/demo"; } // 测试导出excel @RequestMapping(value = "/toDownload", method = RequestMethod.GET) public void toDownload(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletResponse response) { try { // 1、读取导入的excel ExcelReader fileReader = ExcelUtil.getReader(file.getInputStream()); List<Map<String, Object>> list = fileReader.readAll(); String fileName = "测试一下"; // 设置文件输出类型 response.setContentType("application/octet-stream"); // 设置文件名称 response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1") + ".xls"); OutputStream out = response.getOutputStream(); // 通过工具类创建writer，默认创建xls格式 ExcelWriter writer = ExcelUtil.getWriter(); writer.write(list); writer.flush(out); writer.close(); out.flush(); } catch (IOException e) { e.printStackTrace(); } } // 测试导出压缩包 原需求是导入excel解析后导出新的excel，暂时用本地excel文件代替 @RequestMapping(value = "/exportZip", method = RequestMethod.GET) public void exportZip(HttpServletResponse response) { try { // 1、解析excel，读取数据 ExcelReader fileReader = ExcelUtil.getReader("E:\\个人\\test\\excel.xls"); List<List<Object>> list = fileReader.read(); // 2、解析数据 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(); } } } // 我们想要解析出来的表头 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); } // 终端编号的数组，最终得到一个去重的编号组 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、处理数据之后，生成转化后的字节流 List<ByteArrayOutputStream> bosList = new ArrayList<>();// 保存字节流数据 List<String> excelName = new ArrayList<>();// 保存单个excel的文件名 // 编号去重 if (noList.size() > 0){ List<String> newNoList = new ArrayList<>(new TreeSet<>(noList)); if (newNoList.size() > 0){ // 要把原来的数据根据编号进行分组过滤 for (String no : newNoList){ List<Map<String, Object>> objList = new ArrayList<>(); // 合计值 BigDecimal total = new BigDecimal("0"); // 序号 Integer i = 1; for (Object objects : dataList){ Map map = JSON.parseObject(objects.toString()); String objNo = map.get("终端号").toString(); if (objNo.equals(no)){ map.put("序号", i); objList.add(map); // 计算合计 String amount = map.get("交易金额").toString(); total = total.add(new BigDecimal(amount)); // 序号 i++; } } if (objList.size() > 0){ Map<String, Object> totalMap = new HashMap<>(); totalMap.put("序号", i); totalMap.put("终端号", "合计"); totalMap.put("交易金额", total); objList.add(totalMap); bosList.add(createExcel(objList)); excelName.add(no + ".xls"); } } } } // 5、开始导出ZIP 创建HttpServerResponse的输出流 OutputStream out = response.getOutputStream(); // 创建要写入的文件 File outFile = new File("excel.zip"); // 通过ZipOutputStream定义要写入的对象 ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(outFile)); // 将处理好的excel数据流写入到zip流 writeZos(bosList, zos, excelName); zos.close(); // 设置请求头 定义返回类型 response.setContentType("text/html; charset=UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("excel.zip", "UTF-8")); // 创建写入流 BufferedInputStream bis = new BufferedInputStream(new FileInputStream("excel.zip")); // 定义byte，长度就是要转成zip文件的byte长度，避免浪费资源 byte[] buffer = new byte[bis.available()]; bis.read(buffer); out.flush(); out.write(buffer); } catch (Exception e){ LOGGER.error("出错了 ===", e); e.printStackTrace(); } } @RequestMapping(value = "/exportZip2", method = RequestMethod.GET) public void exportZip2(HttpServletResponse response) { try { // 1、解析excel，读取数据 ExcelReader fileReader = ExcelUtil.getReader("E:\\个人\\test\\excel.xls"); List<List<Object>> list = fileReader.read(); // 2、解析数据 去掉解析过程，假如拿到的是三个list List<Map<String, Object>> listA = new ArrayList<>(); List<Map<String, Object>> listB = new ArrayList<>(); List<Map<String, Object>> listC = new ArrayList<>(); // 3、处理数据之后，生成转化后的字节流 List<ByteArrayOutputStream> bosList = new ArrayList<>();// 保存字节流数据 List<String> excelName = new ArrayList<>();// 保存单个excel的文件名 // 分别处理三个数据 if (listA.size() > 0){ bosList.add(createExcel(listA));// 获取生成excel的字节流 excelName.add("A.xls");// excel的文件名 } if (listB.size() > 0){ bosList.add(createExcel(listB)); excelName.add("B.xls"); } if (listC.size() > 0){ bosList.add(createExcel(listC)); excelName.add("C.xls"); } // 5、开始导出ZIP 创建HttpServerResponse的输出流 OutputStream out = response.getOutputStream(); // 创建要写入的文件 File outFile = new File("excel.zip"); // 通过ZipOutputStream定义要写入的对象 ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(outFile)); // 将处理好的excel数据流写入到zip流 writeZos(bosList, zos, excelName); zos.close(); // 设置请求头 定义返回类型 response.setContentType("text/html; charset=UTF-8"); response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("excel.zip", "UTF-8")); // 创建写入流 BufferedInputStream bis = new BufferedInputStream(new FileInputStream("excel.zip")); // 定义byte，长度就是要转成zip文件的byte长度，避免浪费资源 byte[] buffer = new byte[bis.available()]; bis.read(buffer); out.flush(); out.write(buffer); } catch (Exception e){ LOGGER.error("出错了 ===", e); e.printStackTrace(); } } /** * 生成一个excel文件，并把【数据流】放到结果里 * @param list 要生成excel的数据 * @return 返回带流结果的集合 */ public ByteArrayOutputStream createExcel(List<Map<String, Object>> list) { ExcelWriter writer = ExcelUtil.getWriter(); writer.write(list, true); // 写入流 ByteArrayOutputStream out = new ByteArrayOutputStream(); writer.flush(out); writer.close(); return out; } /** * 把生成的excel的结果流，放到zip流中，以便导出 * @param bosList 带数据的字节流合集 * @param zos zip流 * @param excelName 文件名合集 * @throws IOException */ public void writeZos(List<ByteArrayOutputStream> bosList, ZipOutputStream zos, List<String> excelName) throws IOException { for (int i = 0; i < bosList.size(); i++) { //将多个excel都转成字节流写入 zos.putNextEntry(new ZipEntry(excelName.get(i))); byte[] excelStream = bosList.get(i).toByteArray(); zos.write(excelStream); //记得关闭 zos.closeEntry(); } } }
我的建议：
1、时时刻刻想着面向对象，写代码时多想一下，这个功能需要哪些对象来干这件事，怎么干在其次，想明白你需要的对象后就去想流程，最后在去组织代码，尽量从后往前看
2、搞清楚问题，遇到什么问题就去解决什么问题，问问题最怕不知道是啥问题，要不然百度都不知道怎么搜
源码放在 码云上面
有兴趣的可以了解下；https://gitee.com/yan_wen_chao/excel_split/tree/master
http://localhost:5601/user/abc 导出excel
http://localhost:5601/excel/abc 导出zip
如有疑问也可以提出；有需要优化的地方也请多多指教；
希望可以对大家有所帮助；
