@SysLogger("导出表格")
public void export(HttpServletRequest request, Model model, HttpServletResponse response) throws Exception {
//要导出Export数据主体
List<Map<String, Object>> certnolist = new ArrayList<Object[]>();
//以下是生成Excel
List<Object[]> dataList = new ArrayList<Object[]>();
String[] title = null;
//合并的开始-结束行
Map<Integer, Integer> categoryRomMap = new HashMap<>();
Map<Integer, Integer> customerRomMap = new HashMap<>();
//合并行的合集
List<Map<Integer, Integer>> romListMap = new ArrayList<>();
if ("1".equals(paramap.get("flag").toString())) {
//区分导出Expor模板1
title = new String[]{"项目名", "营收金额（项目）", "设备号", "订单数（设备）", "营收金额（设备）"};
String[] data = null;
if (certnolist != null && certnolist.size() > 0) {
//合并开始行默认值
int state = 1;
Integer i = 0;
for (Map<String, Object> certnomap : certnolist) {
//编辑整理要导出数据
String categoryName = certnomap.get("categoryName") != null ? certnomap.get("categoryName").toString() : "";
String categoryPrice = certnomap.get("categoryPrice") != null ? certnomap.get("categoryPrice").toString() : "";
String account = certnomap.get("account") == null ? "" : certnomap.get("account").toString();
String count = certnomap.get("count") == null ? "" : certnomap.get("count").toString();
String price = certnomap.get("price") == null ? "" : certnomap.get("price").toString();
data = new String[]{categoryName, categoryPrice, account, count, price};
dataList.add(data);
//整理合并单元格的数据：统计开始行号与结束行号（数据不一致时为合并结束行号）
if (!categoryName.equals("") && i + 1 < certnolist.size() && !categoryName.equals(certnolist.get(i + 1).get("categoryName"))) {
categoryRomMap.put(state, i + 1);
state = i + 2;
}
//整理合并单元格的数据：统计开始行号与结束行号（数据结尾时为合并行结束行号）
if (!categoryName.equals("") && i + 1 == certnolist.size()) {
categoryRomMap.put(state, i + 1);
}
i = i + 1;
}
romListMap.add(categoryRomMap);
}
} else {
//区分导出模板2
title = new String[]{"景区名", "订单数（景区）", "营收金额（景区）", "项目名", "营收金额（项目）", "设备号", "订单数（设备）", "营收金额（设备）"};
String[] data = null;
if (certnolist != null && certnolist.size() > 0) {
//合并开始行默认值
int customerState = 1;
int categorystate = 1;
Integer i = 0;
for (Map<String, Object> certnomap : certnolist) {
//编辑整理要导出数据
String customerName = certnomap.get("customerName") != null ? certnomap.get("customerName").toString() : "";
String customerCount = certnomap.get("customerCount") != null ? certnomap.get("customerCount").toString() : "";
String customerPrice = certnomap.get("customerPrice") != null ? certnomap.get("customerPrice").toString() : "";
String categoryName = certnomap.get("categoryName") != null ? certnomap.get("categoryName").toString() : "";
String categoryPrice = certnomap.get("categoryPrice") != null ? certnomap.get("categoryPrice").toString() : "";
String account = certnomap.get("account") == null ? "" : certnomap.get("account").toString();
String count = certnomap.get("count") == null ? "" : certnomap.get("count").toString();
String price = certnomap.get("price") == null ? "" : certnomap.get("price").toString();
data = new String[]{customerName, customerCount, customerPrice, categoryName, categoryPrice, account, count, price};
dataList.add(data);
//多列统计合并行起始位置：（customerName）
//整理合并单元格的数据：统计开始行号与结束行号（数据不一致时为合并结束行号）
if (!customerName.equals("") && i + 1 < certnolist.size() && !customerName.equals(certnolist.get(i + 1).get("customerName"))) {
customerRomMap.put(customerState, i + 1);
customerState = i + 2;
}
//整理合并单元格的数据：统计开始行号与结束行号（数据结尾时为合并行结束行号）
if (!customerName.equals("") && i + 1 == certnolist.size()) {
customerRomMap.put(customerState, i + 1);
}
//多列统计合并行起始位置：（categoryName）
//整理合并单元格的数据：统计开始行号与结束行号（数据不一致时为合并结束行号）
if (!categoryName.equals("") && i + 1 < certnolist.size() && !categoryName.equals(certnolist.get(i + 1).get("categoryName"))) {
categoryRomMap.put(categorystate, i + 1);
categorystate = i + 2;
}
//整理合并单元格的数据：统计开始行号与结束行号（数据结尾时为合并行结束行号）
if (!categoryName.equals("") && i + 1 == certnolist.size()) {
categoryRomMap.put(categorystate, i + 1);
}
i = i + 1;
}
romListMap.add(categoryRomMap);
romListMap.add(customerRomMap);
}
}
String today = util.today();
ExportExcel exportexcel = new ExportExcel();
// 创建工作簿实例
HSSFWorkbook workbook = new HSSFWorkbook();
String fileName = "定时开关订单汇总报表" + today;
workbook = exportexcel.exportExcel(workbook, title, dataList, fileName);//设置列名，及数据LIST，SHEET名称
// 处理中文文件名
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
OutputStream outputStream = response.getOutputStream();
//合并单元格
if (romListMap.size() > 0) {
//标记区分共有列
int i = 0;
for (Map<Integer, Integer> maps : romListMap) {
if (i == 0) {
//合并（categoryName）共有的列的
for (Integer p : maps.keySet()) {
//区分共有列位子，并合并单元格
if (romListMap.size() == 2) {
this.addMergedRegions(workbook.getSheetAt(0), p, maps.get(p), 3, 3, true);
this.addMergedRegions(workbook.getSheetAt(0), p, maps.get(p), 4, 4, true);
} else {
this.addMergedRegions(workbook.getSheetAt(0), p, maps.get(p), 0, 0, true);
this.addMergedRegions(workbook.getSheetAt(0), p, maps.get(p), 1, 1, true);
}
}
} else {
//合并（customerName）私有的列的
for (Integer p : maps.keySet()) {
//合并单元格
this.addMergedRegions(workbook.getSheetAt(0), p, maps.get(p), 0, 0, true);
this.addMergedRegions(workbook.getSheetAt(0), p, maps.get(p), 1, 1, true);
this.addMergedRegions(workbook.getSheetAt(0), p, maps.get(p), 2, 2, true);
}
}
i++;
}
}
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
/**
* 合并单元格
*
* @param i_Sheet 工作表
* @param i_FirstRow 首行
* @param i_LastRow 尾行
* @param i_FirstColumn 首列
* @param i_LastColumn 尾列
* @param i_IsSafe 是要安全？还是要性能
* @author cyf(
* @createDate 2021-12-22
* @version v1.0
*/
public void addMergedRegions(HSSFSheet i_Sheet, int i_FirstRow, int i_LastRow, int i_FirstColumn, int i_LastColumn, boolean i_IsSafe) {
if (i_FirstRow == i_LastRow && i_FirstColumn == i_LastColumn) {
return;
}
CellRangeAddress v_CellRA = new CellRangeAddress(i_FirstRow
, i_LastRow
, i_FirstColumn
, i_LastColumn);
if (i_IsSafe) {
i_Sheet.addMergedRegion(v_CellRA);
} else {
i_Sheet.addMergedRegionUnsafe(v_CellRA);
}
}
浙公网安备 33010602011771号