@RequestMapping(value = "/exportyc", method = RequestMethod.GET)
// @GetMapping("exportyc")
// @ResponseBody
// @ApiOperation("塔租稽核报表导出 异常 ")
public void exportyc(String province, String city, String county, String startTime, HttpServletResponse response) {
try {
//创建Excel工作薄
//如果是大批量导出数据超过一万条, 使用SXSSFWorkbook
//如果数量少的话可以使用XSSFWorkbook也可以使用SXSSFWorkbook
SXSSFWorkbook workbook = new SXSSFWorkbook();
//创建一个工作表shheet
SXSSFSheet sheet = workbook.createSheet("sheet");
CellRangeAddress rangeAddress = null;
//指定合并开始行、合并结束行 合并开始列、合并结束列
rangeAddress = new CellRangeAddress(0, 0, 7, 9);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 10, 12);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 13, 15);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 16, 18);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 19, 21);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 22, 24);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 25, 27);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 28, 30);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 31, 33);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 34, 36);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 37, 39);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 40, 42);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 43, 45);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 46, 48);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 49, 51);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 52, 54);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 55, 57);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 58, 60);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 61, 63);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 64, 66);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 67, 69);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 70, 72);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 73, 75);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 76, 78);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 79, 81);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 82, 84);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 85, 87);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 88, 90);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 91, 93);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 94, 96);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 97, 99);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 100, 102);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 103, 105);
sheet.addMergedRegion(rangeAddress);
rangeAddress = new CellRangeAddress(0, 0, 106, 108);
sheet.addMergedRegion(rangeAddress);
//创建行,指定起始行号,从0开始
SXSSFRow row = sheet.createRow(0);
SXSSFCell cell = null;
//创建单元格,指定起始列号,从7开始
cell = row.createCell(7);
//设置单元格内容
cell.setCellValue("产品服务费合计(不含税)");
cell = row.createCell(10);
//设置单元格内容
cell.setCellValue("服务起始日期");
cell = row.createCell(13);
//设置单元格内容
cell.setCellValue("铁塔共享信息");
cell = row.createCell(16);
//设置单元格内容
cell.setCellValue("铁塔产品");
cell = row.createCell(19);
//设置单元格内容
cell.setCellValue("机房共享信息");
cell = row.createCell(22);
//设置单元格内容
cell.setCellValue("机房产品");
cell = row.createCell(25);
//设置单元格内容
cell.setCellValue("配套共享信息");
cell = row.createCell(28);
//设置单元格内容
cell.setCellValue("配套产品");
cell = row.createCell(31);
//设置单元格内容
cell.setCellValue("产品单元数1");
cell = row.createCell(34);
//设置单元格内容
cell.setCellValue("对应实际最高天线挂高(米)1");
cell = row.createCell(37);
//设置单元格内容
cell.setCellValue("期末铁塔共享用户数");
cell = row.createCell(40);
//设置单元格内容
cell.setCellValue("铁塔共享折扣");
cell = row.createCell(43);
//设置单元格内容
cell.setCellValue("期末机房共享用户数");
cell = row.createCell(46);
//设置单元格内容
cell.setCellValue("机房共享折扣");
cell = row.createCell(49);
//设置单元格内容
cell.setCellValue("配套共享用户数");
cell = row.createCell(52);
//设置单元格内容
cell.setCellValue("配套共享折扣");
cell = row.createCell(55);
//设置单元格内容
cell.setCellValue("维护费共享用户数");
cell = row.createCell(58);
//设置单元格内容
cell.setCellValue("维护费共享折扣");
cell = row.createCell(61);
//设置单元格内容
cell.setCellValue("场地费共享用户数");
cell = row.createCell(64);
//设置单元格内容
cell.setCellValue("场地费共享折扣");
cell = row.createCell(67);
//设置单元格内容
cell.setCellValue("电力引入费共享用户数");
cell = row.createCell(70);
//设置单元格内容
cell.setCellValue("电力引入费共享折扣");
cell = row.createCell(73);
//设置单元格内容
cell.setCellValue("期末铁塔共享后基准价格1+2+3(出账费用)");
cell = row.createCell(76);
//设置单元格内容
cell.setCellValue("期末机房共享后基准价格1+2+3(出账费用)");
cell = row.createCell(79);
//设置单元格内容
cell.setCellValue("配套共享后基准价格1+2+3(出账费用)");
cell = row.createCell(82);
//设置单元格内容
cell.setCellValue("维护费折扣后金额1+2+3(出账费用)");
cell = row.createCell(85);
//设置单元格内容
cell.setCellValue("场地费折扣后金额(出账费用)");
cell = row.createCell(88);
//设置单元格内容
cell.setCellValue("BBU放在铁塔机房并占用空间费用(出账费用)");
cell = row.createCell(91);
//设置单元格内容
cell.setCellValue("电力引入费折扣后金额(出账费用)");
cell = row.createCell(94);
//设置单元格内容
cell.setCellValue("油机发电服务费(出账费用)");
cell = row.createCell(97);
//设置单元格内容
cell.setCellValue("蓄电池保障服务费(出账费用)");
cell = row.createCell(100);
//设置单元格内容
cell.setCellValue("WLAN费用(出账费用)");
cell = row.createCell(103);
//设置单元格内容
cell.setCellValue("微波费用(出账费用)");
cell = row.createCell(106);
//设置单元格内容
cell.setCellValue("其他费用1(出账费用)");
//指定合并开始行、合并结束行 合并开始列、合并结束列
rangeAddress = new CellRangeAddress(0, 1, 0, 0);
sheet.addMergedRegion(rangeAddress);
//创建单元格,指定起始列号,从7开始
cell = row.createCell(0);
//设置单元格内容
cell.setCellValue("地市");
//指定合并开始行、合并结束行 合并开始列、合并结束列
rangeAddress = new CellRangeAddress(0, 1, 1, 1);
sheet.addMergedRegion(rangeAddress);
//创建单元格,指定起始列号,从7开始
cell = row.createCell(1);
//设置单元格内容
cell.setCellValue("区县");
//指定合并开始行、合并结束行 合并开始列、合并结束列
rangeAddress = new CellRangeAddress(0, 1, 2, 2);
sheet.addMergedRegion(rangeAddress);
//创建单元格,指定起始列号,从7开始
cell = row.createCell(2);
//设置单元格内容
cell.setCellValue("账期月份");
//指定合并开始行、合并结束行 合并开始列、合并结束列
rangeAddress = new CellRangeAddress(0, 1, 3, 3);
sheet.addMergedRegion(rangeAddress);
//创建单元格,指定起始列号,从7开始
cell = row.createCell(3);
//设置单元格内容
cell.setCellValue("业务确认单号");
//指定合并开始行、合并结束行 合并开始列、合并结束列
rangeAddress = new CellRangeAddress(0, 1, 4, 4);
sheet.addMergedRegion(rangeAddress);
//创建单元格,指定起始列号,从7开始
cell = row.createCell(4);
//设置单元格内容
cell.setCellValue("需求单号");
//指定合并开始行、合并结束行 合并开始列、合并结束列
rangeAddress = new CellRangeAddress(0, 1, 5, 5);
sheet.addMergedRegion(rangeAddress);
//创建单元格,指定起始列号,从7开始
cell = row.createCell(5);
//设置单元格内容
cell.setCellValue("站址名称");
//指定合并开始行、合并结束行 合并开始列、合并结束列
rangeAddress = new CellRangeAddress(0, 1, 6, 6);
sheet.addMergedRegion(rangeAddress);
//创建单元格,指定起始列号,从7开始
cell = row.createCell(6);
//设置单元格内容
cell.setCellValue("站址编码");
row = sheet.createRow(1);
String[] header = {
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核",
"铁塔核算", "电信核算", "稽核"
};
for (int i = 0; i < 102; i++) {
cell = row.createCell(i+7);//创建第一行第i列单元格,索引从0开始
cell.setCellValue(header[i]);//给单元格填写数据
}
String[] keys = {"cityDx", "countyDx", "startTimeDx", "serviceNumberDx", "xqNumDx", "bbuNameDx", "bbuNumDx", "cpfwCostTotalTt",
"cpfwCostTotalDx", "cpfwCostTotalRecourse", "beginTimeTt", "beginTimeDx", "beginTimeRecourse", "ttInformationTt",
"ttInformationDx", "ttInformationRecourse", "ttProductTt", "ttProductDx", "ttProductRecourse", "jfInformationTt",
"jfInformationDx", "jfInformationRecourse", "jfProductTt", "jfProductDx", "jfProductRecourse", "ptInformationTt",
"ptInformationDx", "ptInformationRecourse", "ptProductTt", "ptProductDx", "ptProductRecourse", "unitNumOneTt",
"unitNumOneDx", "unitNumOneRecourse", "highOneTt", "highOneDx", "highOneRecourse", "ttuserNumEndTt", "ttuserNumEndDx",
"ttuserNumEndRecourse", "ttDiscountsTt", "ttDiscountsDx", "ttDiscountsRecourse", "jfuserNumEndTt", "jfuserNumEndDx",
"jfuserNumEndRecourse", "jfDiscountsTt", "jfDiscountsDx", "jfDiscountsRecourse", "ptuserNumTt", "ptuserNumDx", "ptuserNumRecourse",
"ptDiscountsTt", "ptDiscountsDx", "ptDiscountsRecourse", "whfuserNumTt", "whfuserNumDx", "whfuserNumRecourse",
"wffDiscountsTt", "wffDiscountsDx", "wffDiscountsRecourse", "cdfuserNumTt", "cdfuserNumDx", "cdfuserNumRecourse", "cdfDiscountsTt",
"cdfDiscountsDx", "cdfDiscountsRecourse", "dlyruserNumTt", "dlyruserNumDx", "dlyruserNumRecourse", "dlyrDiscountsTt",
"dlyrDiscountsDx", "dlyrDiscountsRecourse", "ttCostTt", "ttCostDx", "ttCostRecourse", "jfCostTt", "jfCostDx", "jfCostRecourse",
"ptCostTt", "ptCostDx", "ptCostRecourse", "wffDiscountsCostTt", "wffDiscountsCostDx", "wffDiscountsCostRecourse",
"discountsSiteUseFeeTt", "discountsSiteUseFeeDx", "discountsSiteUseFeeRecourse", "bbuSpaceUsageCostTt", "bbuSpaceUsageCostDx",
"bbuSpaceUsageCostRecourse", "dlyrDiscountsCostTt", "dlyrDiscountsCostDx", "dlyrDiscountsCostRecourse", "yjfdServiceChargeTt",
"yjfdServiceChargeDx", "yjfdServiceChargeRecourse", "dcbzServiceChargeTt", "dcbzServiceChargeDx", "dcbzServiceChargeRecourse",
"wlanCostTt", "wlanCostDx", "wlanCostRecourse", "wbCostTt", "wbCostDx", "wbCostRecourse", "qtCostTt", "qtCostDx", "qtCostRecourse"
};
//获取临时记录表中的所有表格数据
List<Map<String, String>> dataList = tzAllMapper.getAbnormal(province, city, county, startTime);
List<Map<String, String>> tempList = null;
Integer pageNum = 8000;
Integer pageSize = dataList.size() / pageNum + 1;
for (int i = 0; i < pageSize; i++) {
tempList = dataList.subList(pageNum * i,
i < dataList.size() / pageNum ? pageNum * (i + 1) : dataList.size());
if (!CollectionUtils.isEmpty(tempList) && tempList.size() > 0) {
//将查出来的4G数据转存到本地数据库
if (tempList.size() > 0) {
for (int h = 0; h < tempList.size(); h++) {
Map<String, String> map = tempList.get(h);
row = sheet.createRow(h + 2);//从第3行开始填写数据
for (int j = 0; j < keys.length; j++) {
//从0列开始填写
Cell cella = row.createCell(j);
cella.setCellValue(map.get(keys[j]) == null ? "" : map.get(keys[j]).toString());
}
}
}
}
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
try {
response.setHeader("content-disposition",
"attachment;filename=" + java.net.URLEncoder.encode("塔租稽核报表异常数据.xlsx", "UTF-8"));
} catch (UnsupportedEncodingException e) {
}
OutputStream out = response.getOutputStream();
// 将创建的Excel对象利用二进制流的形式强制输出到客户端去
workbook.write(out);
// 强制将数据从内存中保存
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
导出大批量excel数据
于 2023-04-20 21:25:49 首次发布
这段代码是用Java的ApachePOI库来创建一个Excel工作薄,用于导出塔租稽核报表。它涉及到创建工作表,设置单元格内容,以及大量单元格的合并操作,最后将内容响应给HTTP客户端。
摘要由CSDN通过智能技术生成