一、需求目标
- 通过poi生成多个Excel文件
- 需要把已生成的Excel文件压缩成ZIP文件
- 支持客户客户端下载
二、解决方案
- 单线程生成Excel,单线程压缩文件
- 多线程生成Excel,单线程压缩文件
三、代码实例
1、pom.xml配置
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
2、客户端代码
$(function () {
$("#exportButon").on("click", function () {
if(verifyDate() && verifyNull()){
window.location.href = "${base}/pool/zip";
}
icont++;
})
});
3、Excel工具类
package com.tangsm.demo.util;
import java.awt.Color;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtils {
public static XSSFCellStyle createHeadStyle(XSSFWorkbook workbook) {
XSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(new XSSFColor(new Color(87, 163, 250)));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
XSSFFont font = workbook.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
return style;
}
public static XSSFCellStyle createBodyStyle(XSSFWorkbook workbook) {
XSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
XSSFFont font = workbook.createFont();
font.setFontName("微软雅黑");
font.setFontHeightInPoints((short) 9);
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
style.setFont(font);
return style;
}
public static XSSFWorkbook createExcel(String sheetName) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultColumnWidth(15);
XSSFCellStyle headerStyle = createHeadStyle(workbook);
XSSFCellStyle bodyStyle = createBodyStyle(workbook);
Row row = sheet.createRow((short) 0);
String[] excelHeader = { "标题1", "标题2", "标题3", "标题4" };
for (int i = 0; i < excelHeader.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(excelHeader[i]);
cell.setCellStyle(headerStyle);
}
for (int j = 1; j < 10; j++) {
row = sheet.createRow((short) j);
Cell cell;
for (int k = 0; k < 4; k++) {
cell = row.createCell(k);
cell.setCellValue("行" + j + "-数据" + (k + 1));
cell.setCellStyle(bodyStyle);
}
}
return workbook;
}
}
4、单线程生成Excel,单线程压缩文件
package com.tangsm.demo.controller;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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.ResponseBody;
import com.tangsm.demo.util.ExcelUtils;
@Controller
public class ExportZipController {
private Logger logger = LoggerFactory.getLogger(getClass());
@ResponseBody
@RequestMapping(value = "/zip", produces = "text/html;charset=UTF-8")
public void zip(HttpServletResponse response) throws IOException {
ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
ZipOutputStream zip = new ZipOutputStream(byteOut);
OutputStream servletOut = response.getOutputStream();
try {
for (int i = 1; i < 11; i++) {
XSSFWorkbook workbook = ExcelUtils.createExcel("Sheet1");
zip.putNextEntry(new ZipEntry("test/订单" + i + ".xlsx"));
ByteArrayOutputStream bos = new ByteArrayOutputStream();
workbook.write(bos);
bos.writeTo(zip);
zip.flush();
zip.closeEntry();
IOUtils.closeQuietly(bos);
IOUtils.closeQuietly(workbook);
}
IOUtils.closeQuietly(zip);
byte[] data = byteOut.toByteArray();
String currentDate = nowDate();
String fileName = String.format("test-%s", currentDate);
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
response.reset();
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".zip");
response.addHeader("Content-Length", "" + data.length);
response.setContentType("application/octet-stream; charset=UTF-8");
IOUtils.write(data, servletOut);
} catch (Exception e) {
logger.error("系统异常", e);
} finally {
IOUtils.closeQuietly(byteOut);
IOUtils.closeQuietly(servletOut);
}
}
private String nowDate() {
Date now = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
return dateFormat.format(now);
}
}
5、多线程生成Excel,单线程压缩文件
(1)Excel处理任务-ExcelTask.java
package com.tangsm.demo.util;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.RecursiveTask;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ExcelTask extends RecursiveTask<List<XSSFWorkbook>> {
private static final long serialVersionUID = 4388520935732444679L;
private Logger logger = LoggerFactory.getLogger(getClass());
private static final int THRESHOLD = 50;
private final List<String> dataList;
public ExcelTask(List<String> dataList) {
this.dataList = dataList;
}
@Override
protected List<XSSFWorkbook> compute() {
int size = dataList.size();
logger.info("子线程处理数据大小:{}", size);
if (size <= THRESHOLD) {
return process();
}
ExcelTask ltTask = new ExcelTask(dataList.subList(0, size / 2));
ExcelTask rtTask = new ExcelTask(dataList.subList(size / 2, size));
invokeAll(ltTask, rtTask);
List<XSSFWorkbook> allList = new ArrayList<XSSFWorkbook>();
List<XSSFWorkbook> ltResult = ltTask.join();
List<XSSFWorkbook> rtResult = rtTask.join();
allList.addAll(ltResult);
allList.addAll(rtResult);
return allList;
}
private List<XSSFWorkbook> process() {
List<XSSFWorkbook> wbList = new ArrayList<XSSFWorkbook>();
for (String sheetName : dataList) {
XSSFWorkbook workbook = ExcelUtils.createExcel(sheetName);
wbList.add(workbook);
}
return wbList;
}
}
(2)Controller导出实现-ExportPoolZipController.java
package com.tangsm.demo.controller;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ForkJoinPool;
import java.util.concurrent.ForkJoinTask;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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.ResponseBody;
import com.tangsm.demo.util.ExcelTask;
@Controller
public class ExportPoolZipController {
private Logger logger = LoggerFactory.getLogger(getClass());
@ResponseBody
@RequestMapping(value = "/pool/zip", produces = "text/html;charset=UTF-8")
public void poolZip(HttpServletResponse response) throws IOException {
logger.info("多线程处理ZIP开始...");
String currentDate = nowDate();
ForkJoinPool pool = new ForkJoinPool(4);
ByteArrayOutputStream byteOut = new ByteArrayOutputStream();
ZipOutputStream zip = new ZipOutputStream(byteOut);
OutputStream servletOut = response.getOutputStream();
try {
List<String> list = Arrays.asList("test1", "test2", "test3", "test4", "test6", "test6",
"test7", "test8", "test9", "test10");
ForkJoinTask<List<XSSFWorkbook>> mainTask = new ExcelTask(list);
List<XSSFWorkbook> wbList = pool.invoke(mainTask);
int i = 1;
for (XSSFWorkbook wb : wbList) {
String sheetName = wb.getSheetName(0);
String entryName = String.format("%s/%d-%s.xlsx", currentDate, i, sheetName);
putEntry(entryName, zip, wb);
i++;
}
IOUtils.closeQuietly(zip);
byte[] data = byteOut.toByteArray();
String fileName = String.format("test-%s", currentDate);
fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
response.reset();
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".zip");
response.addHeader("Content-Length", "" + data.length);
response.setContentType("application/octet-stream; charset=UTF-8");
IOUtils.write(data, servletOut);
} catch (Exception e) {
logger.error("系统异常...", e);
} finally {
pool.shutdown();
IOUtils.closeQuietly(byteOut);
IOUtils.closeQuietly(servletOut);
}
}
private void putEntry(String entryName, ZipOutputStream zip,
XSSFWorkbook workbook) throws IOException {
zip.putNextEntry(new ZipEntry(entryName));
ByteArrayOutputStream bos = new ByteArrayOutputStream();
workbook.write(bos);
bos.writeTo(zip);
zip.flush();
zip.closeEntry();
IOUtils.closeQuietly(bos);
}
private String nowDate() {
Date now = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
return dateFormat.format(now);
}
}
四、运行效果