给自己做个备忘录
我这个下载的是一个zip的压缩包,压缩包中有,一个固定的Excel 表格,还有更加数据导出N个的Excel(模板是一样的,数据不一样),下载内容如图:
HttpServletResponse 实现文件下载 zip
ZipOutputStream 实现压缩zip
WritableSheet ws = workbook.createSheet("Sheet1", 1);创建Excel文件
下载demo
注:为了脱敏中间很多字段名称对不上
下载接口入口
@RequestMapping(value = "/yzjhExportZip", method = RequestMethod.POST, produces = "application/json; charset=utf-8")
public void YzjhExportZip() throws Exception {
LOG.debug("开始导出");
String lrrqq = new String(request.getParameter("lrrqq").getBytes("iso-8859-1"), "UTF-8");
String lrrqz = new String(request.getParameter("lrrqz").getBytes("iso-8859-1"), "UTF-8");
Map<String, Object> paraMap = new HashMap<>();
paraMap.put("lrrqq", lrrqq);
paraMap.put("lrrqz", lrrqz);
// 1.先查xxxxx
……//省略业务上的代码
ArrayList<Object> arrayList = new ArrayList<Object>();
List<Map<String, String>> maps2 = (List<Map<String, String>>)resultMap2.get("data");
arrayList.add(maps2);//这个arrayList就是一个需要导出的Excel数据
// 先查询动态表头,这个导出的excel表头是动态的。
resultVo = xxxx.queryXxxx();
List<Map<String, Object>> fpzlList = (List<Map<String, Object>>) resultVo.getValue();
……//省略业务上的代码
// 查询另一个需要导出的Excel
……//省略业务上的代码
List<Map<String, String>> mapZyzjh = (List<Map<String, String>>) resultMapZyzjh.get("data");
for (int i = 0; i < mapZyzjh.size(); i++) {
……//省略业务上的代码
// 求每行合计
int num = 0;
for (int y = 0; y < fpzlList.size(); y++) {
String key = fpzlList.get(y).get("field").toString();
if (!StringUtil.isNullString(fs_)) {
num = num + Integer.parseInt(fs_);
}
}
mapZyzjh.get(i).put("rowSum", num + "");
}
Map<String, Object> mapdata = new HashMap<>();
mapdata.put("zyzjhHeaderData", fpzlList);//xxxx的动态表头
mapdata.put("zyzjhData", mapZyzjh);//xxx需要导出的Excel
mapdata.put("yztzsData", arrayList);//xxx需要导出的Excel
ExportYzfpUtil.downloadYzfp(response, mapdata);
}
ExportYzfpUtil.java
package xxxxxxxxx.common.util;
import xxxxx.xxxx.util.StringUtil;
import com.alibaba.fastjson.JSON;
import jxl.JXLException;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.format.*;
import jxl.write.*;
import org.apache.log4j.Logger;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
public class ExportYzfpUtil {
private static Logger log = Logger.getLogger(ExportYzfpUtil.class);
/**
* 获取日期字符串
*
* @param formatType 日期格式类型,例如:"yyyy-MM-dd" "yyyy-MM-dd HH:mm"
* @return String 格式转换成功后的字符串
*/
public static String getDateStr(String formatType) {
if (StringUtil.isNullString(formatType)) {
formatType = "yyyy-MM-dd";
}
Calendar cal = Calendar.getInstance();
SimpleDateFormat myFmt = new SimpleDateFormat(formatType);
String timeStr = myFmt.format(cal.getTime()).toString();
return timeStr;
}
/**
* 表头名称列表
*
* @param ws
* @return
* @throws WriteException
* @throws JXLException
*/
private static WritableSheet addSheetHead(WritableSheet ws, List<String> columNames) throws WriteException,
JXLException {
WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setAlignment(Alignment.CENTRE);
int index = 0;
for (String name : columNames) {
ws.addCell(new Label(index, 5, name, wcf));
index++;
}
return ws;
}
public static void downloadYzfp(HttpServletResponse response, Map<String, Object> mapdata) {
String time = getDateStr("yyyyMMddHHmmss");
WritableWorkbook workbook = null;
ZipOutputStream zos = null;
response.reset();
response.setContentType("application/zip");
try {
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("xxx通知书" + time + ".zip", "UTF-8"));
zos = new ZipOutputStream(response.getOutputStream());
// 循环生成xxx通知书
ArrayList dataArray = (ArrayList) mapdata.get("yztzsData");
log.error("需要导出的Excel数量:" + dataArray.size());
for (int i = 0; i < dataArray.size(); i++) {
List<Map<String, String>> array = (List<Map<String, String>>) dataArray.get(i);
workbook = Workbook.createWorkbook(zos);
Map<String, Object> map = createExcel(workbook, array);
WritableWorkbook workbook_ = (WritableWorkbook) map.get("workbook");
String name = map.get("name").toString();
time = getDateStr("yyyyMMddHHmmss");
ZipEntry entry1 = new ZipEntry(name + time + ".xls");
zos.putNextEntry(entry1);
workbook_.write();
workbook_.close();
zos.closeEntry();
}
// xxxxx表
workbook = Workbook.createWorkbook(zos);
List<Map<String, Object>> headerData = (List<Map<String, Object>>) mapdata.get("zyzjhHeaderData");
List<Map<String, Object>> zyzjhData = (List<Map<String, Object>>) mapdata.get("zyzjhData");
Map<String, Object> map = createExcelYzjhZb(workbook, headerData, zyzjhData);
WritableWorkbook workbook_ = (WritableWorkbook) map.get("workbookYzjhZb");
time = getDateStr("yyyyMMddHHmmss");
ZipEntry entry1 = new ZipEntry("xx总表" + time + ".xls");
zos.putNextEntry(entry1);
workbook_.write();
workbook_.close();
zos.closeEntry();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != zos)
zos.close();
response.flushBuffer();
} catch (Exception e) {
log.error("文件字节输出流关闭异常", e);
}
}
}
public static Map<String, Object> createExcel(WritableWorkbook workbook, List<Map<String, String>> gridDataArray) throws Exception {
Map<String, String> object = gridDataArray.get(0);
List<String> headNames = Arrays.asList("代码", "名称", "xx规格", "xx次", "xx金额", "数量", "xx号码起", "xx号码止");
List<String> fieldNames = Arrays.asList("xxDm", "xxJc", "ggMc", "lcMc", "je", "zfs", "qshm", "zzhm");
// /创建工作薄
WritableSheet ws = workbook.createSheet("Sheet1", 1); // 创建一个工作表
// ws.setColumnView(0, 20); // 设置列的宽度
// 设置字体
WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableFont bodyFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
// 增加第1行-标题
ws.mergeCells(0, 0, 7, 0); // 合并单元格
WritableCellFormat wcf_title = new WritableCellFormat(titleFont);
wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf_title.setAlignment(Alignment.CENTRE);
ws.addCell(new Label(0, 0, "xxxxxxxxxx通知书", wcf_title));
// 增加第2行-xxx编号
ws.mergeCells(0, 1, 7, 1); // 合并单元格
WritableCellFormat wcf_jhbh = new WritableCellFormat(bodyFont);
wcf_jhbh.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf_jhbh.setAlignment(Alignment.CENTRE);
ws.addCell(new Label(0, 1, object.get("jhbhMc"), wcf_jhbh));
// 增加第3行-xxx
ws.mergeCells(0, 2, 7, 2); // 合并单元格
WritableCellFormat wcf_date = new WritableCellFormat(bodyFont);
wcf_date.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf_date.setAlignment(Alignment.LEFT);
ws.addCell(new Label(0, 2, object.get("ysc") + ":请贵单位按如下要求xxx样张印制。", wcf_date));
// 增加第4行-
ws.mergeCells(0, 3, 7, 3); // 合并单元格
WritableCellFormat wcf_ysc = new WritableCellFormat(bodyFont);
wcf_ysc.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf_ysc.setAlignment(Alignment.LEFT);
ws.addCell(new Label(0, 3, "单位名称:各市xxxx", wcf_ysc));
// 增加第5行-交货时间、交货地点
ws.mergeCells(0, 4, 2, 4); // 合并单元格
ws.mergeCells(3, 4, 7, 4); // 合并单元格
WritableCellFormat wcf_jh = new WritableCellFormat(bodyFont);
wcf_jh.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf_jh.setAlignment(Alignment.LEFT);
ws.addCell(new Label(0, 4, "交货时间:依使用单位需求", wcf_jh));
ws.addCell(new Label(3, 4, "交货地点:各市xxxx", wcf_jh));
// 增加第6行-列名
addSheetHead(ws, headNames);
log.debug("下载中,开始组装数据行内容");
// 在第7行开始正文表格内容
int row = 6;
// 用于正文的表格样式
WritableCellFormat wcf_body = new WritableCellFormat(bodyFont);
wcf_body.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_body.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_body.setAlignment(Alignment.LEFT);
wcf_body.setWrap(true); // 是否换行
for (int i = 0; i < gridDataArray.size(); i++) {
Map<String, String> dataObject = gridDataArray.get(i);
for (int j = 0; j < fieldNames.size(); j++) {
String data = dataObject.get(fieldNames.get(j));
ws.addCell(new Label(j, row, StringUtil.trim(data), wcf_body));
}
row++;
}
// 填表人
// 设置最后两行末尾备注
ws.mergeCells(0, row, 4, 0); // 合并单元格
ws.mergeCells(5, row, 7, 0); // 合并单元格
WritableCellFormat wcf_tbr = new WritableCellFormat(bodyFont);
wcf_tbr.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf_tbr.setAlignment(Alignment.LEFT);
ws.addCell(new Label(0, row, "填表人:", wcf_tbr));
// 批注人
ws.mergeCells(0, row + 1, 4, 0); // 合并单元格
WritableCellFormat wcf_pzr = new WritableCellFormat(bodyFont);
wcf_pzr.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf_pzr.setAlignment(Alignment.LEFT);
ws.addCell(new Label(0, row + 1, "批注人:", wcf_pzr));
// 把创建的内容写入到输出流中,并关闭输出流
// workbook.write();
// workbook.close();
// bytes.close();
Map<String, Object> map = new HashMap<>();
map.put("workbook", workbook);
map.put("name", object.get("ysc"));
return map;
}
public static Map<String, Object> createExcelYzjhZb(WritableWorkbook workbook, List<Map<String, Object>> listHeader, List<Map<String, Object>> Listdata) throws Exception {
// 初始化表头
List<String> headNames = new ArrayList<>();
headNames.add("序号");
headNames.add("单位");
List<String> fieldNames = new ArrayList<>();
fieldNames.add("autorowno");
fieldNames.add("mc");
for (int l = 0; l < listHeader.size(); l++) {
headNames.add(listHeader.get(l).get("fpzlJc").toString());
fieldNames.add(listHeader.get(l).get("field").toString().toLowerCase());
}
headNames.add("合计");
fieldNames.add("rowSum");
// 表头初始化结束
WritableSheet ws = workbook.createSheet("Sheet1", 1); // 创建一个工作表
// 设置字体
WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableFont bodyFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
int colNumber = headNames.size();//列数
// 增加第1行-标题
ws.mergeCells(0, 0, colNumber-1, 0); // 合并单元格
WritableCellFormat wcf_title = new WritableCellFormat(titleFont);
wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf_title.setAlignment(Alignment.CENTRE);
ws.addCell(new Label(0, 0, " 年 季度xxxxx总表", wcf_title));
// 第2行表头
WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setAlignment(Alignment.CENTRE);
int index = 0;
for (String name : headNames) {
if (name.equals("序号")) {
ws.mergeCells(0, 1, 0, 3); // 合并单元格-序号
ws.addCell(new Label(index, 1, name, wcf));
} else if (name.equals("单位")) {
ws.mergeCells(1, 1, 1, 3); // 合并单元格-单位
ws.addCell(new Label(index, 1, name, wcf));
} else if (name.equals("合计")) {
ws.mergeCells(colNumber - 1, 1, colNumber - 1, 3); // 合并单元格-合计
ws.addCell(new Label(index, 1, name, wcf));
} else {
ws.addCell(new Label(index, 1, name, wcf));
}
index++;
}
// 第3、4行 MXFS
index = 2;
for (int j = 0; j < listHeader.size(); j++) {
ws.addCell(new Label(index, 2, listHeader.get(j).get("mxfs").toString() + "份/箱", wcf));
ws.addCell(new Label(index, 3, "份数", wcf));
index++;
}
// 第5行 正文数据行
index = 4;
// 用于正文的表格样式
WritableCellFormat wcf_body = new WritableCellFormat(bodyFont);
wcf_body.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_body.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcf_body.setAlignment(Alignment.LEFT);
wcf_body.setWrap(true); // 是否换行
for (int i = 0; i < Listdata.size(); i++) {
Map<String, Object> dataObject = Listdata.get(i);
for (int j = 0; j < fieldNames.size(); j++) {
Object data = dataObject.get(fieldNames.get(j));
if (data == null) {
data = "";
}
ws.addCell(new Label(j, index, StringUtil.trim(data.toString()), wcf_body));
}
index++;
}
Map<String, Object> map = new HashMap<>();
map.put("workbookYzjhZb", workbook);
return map;
}
}