Apache POI导出Excel经典代码

引入pom依赖

1
2
3
4
5
6
7
8
9
10
11
12
<!--导出Excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
<!--导出Excel end-->

代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.util.CollectionUtils;
import org.springframework.web.bind.annotation.*;
import sun.tools.jar.resources.jar;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Controller
@RequestMapping("/export")
public class ExportToExcelController {
@Autowired
private ReportFormImpl reportFormImpl;

/**
* 批量导出 白名单信息 到Excel
*
* @param excelName
* @param response
* @return
*/
@ResponseBody
@RequestMapping(value = "/exportStatisticsNum/{excelName}")
public jar exportWhiteList(@PathVariable("excelName") String excelName,
HttpServletResponse response) {
HSSFWorkbook wb = new HSSFWorkbook();
//创建工作表
HSSFSheet sheet = wb.createSheet();
//创建行列
HSSFRow nRow = sheet.createRow(0);
HSSFCell nCell = nRow.createCell(0);
//控制行号列号
int rowNo = 0;
int colNo = 0;
//列标题
String[] title;
title = new String[]{"业务名称", "项目版本", "重要程度", "风险需求数", "风险需求修复数", "安全漏洞数", "安全漏洞修复数", "修复率"};
//设置标题到第一行的列中
nRow = sheet.createRow(rowNo++);
for (int i = 0; i < title.length; i++) {
nCell = nRow.createCell(i);
nCell.setCellValue(title[i]);
}
try {
/*
* 调用逻辑层函数查询
*/

List<ReportFormQuerySecurityProject> statisticsNumList = reportFormImpl.getStatisticsNum(new QuerySecurityProject());
//遍历并且创建行列
for (ReportFormQuerySecurityProject dto : statisticsNumList) {
//控制列号
colNo = 0;
//每遍历一次创建一行
nRow = sheet.createRow(rowNo++);
//业务名称
nCell = nRow.createCell(colNo++);
nCell.setCellValue(dto.getJobName());
//项目版本
nCell = nRow.createCell(colNo++);
nCell.setCellValue(dto.getProjectVersion());
//重要程度
nCell = nRow.createCell(colNo++);
Byte degree = dto.getImportanceDegree();
String importanceDegree = "";
if (degree == 1) {
importanceDegree = "严重";
} else if (degree == 2) {
importanceDegree = "高危";
} else if (degree == 3) {
importanceDegree = "中危";
} else if (degree == 4) {
importanceDegree = "低危";
}
nCell.setCellValue(importanceDegree);
//风险需求数
nCell = nRow.createCell(colNo++);
nCell.setCellValue(dto.getRiskDemandNum());
//风险需求修复数
nCell = nRow.createCell(colNo++);
nCell.setCellValue(dto.getRiskDemandRepairNum());
//安全漏洞数
nCell = nRow.createCell(colNo++);
nCell.setCellValue(dto.getBugNum());
//安全漏洞修复数
nCell = nRow.createCell(colNo++);
nCell.setCellValue(dto.getBugRepairNum());
//修复率
nCell = nRow.createCell(colNo++);
nCell.setCellValue(String.format("%.2f", dto.getRepairRate()) + "%");
}
loadResponse(excelName, response, wb);
} catch (Exception e) {
}
return null;
}

/**
* 设置Excel相关参数
*
* @param excelName
* @param response
* @param wb
* @throws IOException
*/
private void loadResponse(String excelName, HttpServletResponse response, HSSFWorkbook wb) throws IOException {
//到这里,excel就已经生成了,然后就需要通过流来写出去
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
//将excel写入流
wb.write(byteArrayOutputStream);
//设置文件标题
String dateTime = DateFormatUtils.format(new Date(), "yyyyMMddHHmm");
String outFile = excelName + dateTime + ".xls";
//设置返回的文件类型
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//对文件编码
outFile = response.encodeURL(new String(outFile.getBytes("gb2312"), "iso8859-1"));
//使用Servlet实现文件下载的时候,避免浏览器自动打开文件
response.addHeader("Content-Disposition", "attachment;filename=" + outFile);
//设置文件大小
response.setContentLength(byteArrayOutputStream.size());
//创建Cookie并添加到response中
Cookie cookie = new Cookie("fileDownload", "true");
cookie.setPath("/");
response.addCookie(cookie);
//将流写进response输出流中
ServletOutputStream outputstream = response.getOutputStream();
byteArrayOutputStream.writeTo(outputstream);

byteArrayOutputStream.close();
outputstream.flush();
}
}