第一部分是excel下载的代码，本身实现的前提是重写公司excel代码，参数上存在迭代的粘性，所以参数大家可以自行设计。
public String exportExcelKqResultWithMerge(Collection<ValidateResult> resultData, String typeCode) {
// 获取所有异常项内容
Map<String, String> exceptionContent = getExceptionContent();
// 获取类型
Map<String, String> resultStatu = getResultStatu();
FileInputStream inStream = null;
FileOutputStream fout = null;
File file = null;
//获取property文件中下载文件的配置路径
String path = CommonConst.getProperties("template_path");
try {
//创建文件夹
ValidateResultExport.createDir(path);
//加载文件所有路径
path += "kqValidate.xls";
// 1.创建工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 标题合并单元格设计
CellRangeAddress callRangeAddress31 = new CellRangeAddress(0, 1, 0, 0);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress32 = new CellRangeAddress(0, 1, 1, 1);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress33 = new CellRangeAddress(0, 1, 2, 2);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress34 = new CellRangeAddress(0, 1, 3, 3);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress35 = new CellRangeAddress(0, 1, 4, 4);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress36 = new CellRangeAddress(0, 1, 5, 5);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress37 = new CellRangeAddress(0, 1, 6, 6);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress38 = new CellRangeAddress(0, 1, 7, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress39 = new CellRangeAddress(0, 1, 8, 8);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress40 = new CellRangeAddress(0, 1, 9, 9);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress41 = new CellRangeAddress(0, 1, 10, 10);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress42 = new CellRangeAddress(0, 1, 11, 11);// 起始行,结束行,起始列,结束列
// 上期/去年同期值
CellRangeAddress callRangeAddress43 = new CellRangeAddress(0, 0, 12, 13);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress44 = new CellRangeAddress(0, 1, 14, 14);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress45 = new CellRangeAddress(0, 1, 15, 15);// 起始行,结束行,起始列,结束列
// 标题样式
HSSFCellStyle colStyle = createCellStyle(workbook, (short) 10, true, true);
// 内容样式
HSSFCellStyle cellStyle = createCellStyle(workbook, (short) 10, false, true);
// 2.创建工作表
HSSFSheet sheet = workbook.createSheet("跨期校验");
// 2.1加载合并单元格对象
sheet.addMergedRegion(callRangeAddress31);
sheet.addMergedRegion(callRangeAddress32);
sheet.addMergedRegion(callRangeAddress33);
sheet.addMergedRegion(callRangeAddress34);
sheet.addMergedRegion(callRangeAddress35);
sheet.addMergedRegion(callRangeAddress36);
sheet.addMergedRegion(callRangeAddress37);
sheet.addMergedRegion(callRangeAddress38);
sheet.addMergedRegion(callRangeAddress39);
sheet.addMergedRegion(callRangeAddress40);
sheet.addMergedRegion(callRangeAddress41);
sheet.addMergedRegion(callRangeAddress42);
sheet.addMergedRegion(callRangeAddress43);
sheet.addMergedRegion(callRangeAddress44);
sheet.addMergedRegion(callRangeAddress45);
// 设置默认列宽
sheet.setDefaultColumnWidth(15);
// 3.创建行
// 3.1创建头标题行;并且设置头标题
// 3.2创建列标题;并且设置列标题
HSSFRow row2 = sheet.createRow(0);
String[] titles = { "序号", "报表名称", "口径", "期数", "异常类型", "单元格坐标","报表项目", "异常项", "公式", "本期值", "上期值", "增减值",
"上期/去年同期值", "", "类型" ,"说明"};// ""为占位字符串
for (int i = 0; i < titles.length; i++) {
HSSFCell cell2 = row2.createCell(i);
// 加载单元格样式
cell2.setCellStyle(colStyle);
cell2.setCellValue(titles[i]);
}
HSSFRow rowFour = sheet.createRow(1);
String[] titleFour = { "范围", "预警/异常" };
for (int i = 0; i < titleFour.length; i++) {
System.out.println(i);
HSSFCell cell2 = rowFour.createCell(i + 12);
// 加载单元格样式
cell2.setCellStyle(colStyle);
cell2.setCellValue(titleFour[i]);
}
// 4.操作单元格;将用户列表写入excel
if (resultData != null) {
int i = 1;
int count = 0;
for (ValidateResult validateResult : resultData) {
// 筛选校验结果的类型
if (!typeCode.equals(validateResult.getObjectValidateRule().getValidateExpressionTypeCode())) {
continue;
}
// 创建数据行,前面有两行,头标题行和列标题行
HSSFRow row3 = sheet.createRow(count + 2);
// 创建列
HSSFCell cell0 = row3.createCell(0);
cell0.setCellStyle(cellStyle);
cell0.setCellValue(i++);
HSSFCell cell1 = row3.createCell(1);
cell1.setCellStyle(cellStyle);
cell1.setCellValue(validateResult.getValidateTable());
HSSFCell cell2 = row3.createCell(2);
cell2.setCellStyle(cellStyle);
cell2.setCellValue("境内汇总数据");
HSSFCell cell3 = row3.createCell(3);
cell3.setCellStyle(cellStyle);
cell3.setCellValue(
validateResult.getTerm().substring(0, 4) + "-" + validateResult.getTerm().substring(4, 6));
HSSFCell cell4 = row3.createCell(4);
cell4.setCellStyle(cellStyle);
cell4.setCellValue("预设");
HSSFCell cell5 = row3.createCell(5);
cell5.setCellStyle(cellStyle);
cell5.setCellValue(validateResult.getValidateColumn());
HSSFCell cell6 = row3.createCell(6);
cell6.setCellStyle(cellStyle);
cell6.setCellValue("");
HSSFCell cell7 = row3.createCell(7);
cell7.setCellStyle(cellStyle);
cell7.setCellValue(exceptionContent.get(validateResult.getValidateColumn()));
HSSFCell cell8 = row3.createCell(8);
cell8.setCellStyle(cellStyle);
cell8.setCellValue("比上期比例（绝对值）");
String valueFormu = validateResult.getValueFormu();
String row9 = valueFormu.substring(valueFormu.indexOf("[", 1) + 1, valueFormu.indexOf("]", 1));
String row10 = valueFormu.substring(valueFormu.indexOf("[", valueFormu.indexOf("[", 1) + 1) + 1,
valueFormu.indexOf("]", valueFormu.indexOf("]", 1) + 1));
HSSFCell cell9 = row3.createCell(9);
cell9.setCellStyle(cellStyle);
cell9.setCellValue(row9);
HSSFCell cell10 = row3.createCell(10);
cell10.setCellStyle(cellStyle);
cell10.setCellValue(row10);
HSSFCell cell11 = row3.createCell(11);
cell11.setCellStyle(cellStyle);
cell11.setCellValue(Integer.parseInt(row9) - Integer.parseInt(row10));
HSSFCell cell12 = row3.createCell(12);
cell12.setCellStyle(cellStyle);
cell12.setCellValue(">" + validateResult.getTolerance() + " or <-" + validateResult.getTolerance());
HSSFCell cell13 = row3.createCell(13);
cell13.setCellStyle(cellStyle);
cell13.setCellValue(validateResult.getLeftValue() + "%");
String result = resultStatu.get(validateResult.getResultStatu());
if (row9.equals("0") && row10.equals("0")) {
result = "同为0";
}
HSSFCell cell14 = row3.createCell(14);
cell14.setCellStyle(cellStyle);
cell14.setCellValue(result);
HSSFCell cell15 = row3.createCell(15);
cell15.setCellStyle(cellStyle);
cell15.setCellValue("");
count++;
}
}
// 5.输出
file = new File(path);
if (!file.exists()) {
file.createNewFile();
}
fout = new FileOutputStream(path);
workbook.write(fout);
fout.close();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
} finally
{
try {
if (inStream != null) {
inStream.close();
}
if (null != fout) {
fout.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
//返回的是文件的所有路径，这个时候代码只是下载下来，但是并没用下载响应，也就是说并没有浏览器下载图形化界面。
return path;
}第二部分
将返回的path文件进行File.separator进行解析，这是防止系统的路径问题，如果感兴趣可以了解一下。
ValidateResultExport.downloadFile
(fileName.substring(0, fileName.lastIndexOf(File.separator)),
fileName.substring(fileName.lastIndexOf(File.separator) + 1), response);第三部分
进行文件下载操作
public static void downloadFile(String filePath, String fileName, HttpServletResponse response) {
try {
File file = new File(filePath, fileName);
// 以流的形式下载文件。
BufferedInputStream fis = new BufferedInputStream(new FileInputStream(file.getPath()));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
// response.setCharacterEncoding("utf-8");
response.setCharacterEncoding("GBK");
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
// toClient.write(new byte[] { (byte) 0xEF, (byte) 0xBB,(byte) 0xBF
// }); //UTF-8编码,注释掉此行,文件编码是默认ANSI格式
toClient.write(buffer);
toClient.flush();
toClient.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
