excel的导入导出和异常非空 总计等处理；导出多个excel合并导出zip（hutool导出）（详细讲解包括分析等等）
excel的导入导出和异常非空 总计等处理；导出多个excel合并导出zip（hutool导出）（详细讲解包括分析等等）
默 语
发布于 2024-11-20 08:24:57
发布于 2024-11-20 08:24:57
代码可运行
文章被收录于专栏：JAVA
运行总次数：0
代码可运行
配置文件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();
}
}
}导出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
在这里插入图片描述
如有疑问也可以提出；有需要优化的地方也请多多指教； 希望可以对大家有所帮助；
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2022-08-30，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
