开发过程我们经常需要生成excel 文档报表等,今天我们实战详细分析一下生成并下载的流程:
1、依赖 jar 导入pom文件:
<!-- excel文件生成需要,必须导入,后面两个jar非必须 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${version.poi}</version>
</dependency>
<!-- excel,word,ppt均需要 -->
<!-- <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${version.poi}</version>
</dependency>-->
<!-- excel需要 -->
<!-- <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${version.poi}</version>
</dependency>-->
2、controller 层只负责传参数 省略,,,,
3、service 业务层 :
@Override
public void exportHighExcel(HttpServletRequest request , HttpServletResponse response) {
String fileName = courtName+regionName+companyName+"人员数据统计表";
try {
ByteArrayOutputStream os = new ByteArrayOutputStream();
downloadExcelFile( os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
String agent = request.getHeader("User-Agent").toLowerCase();
logger.debug("User-Agent ---> " +agent);
boolean isMSIE = (agent != null && (agent.indexOf("msie") != -1 || (agent.indexOf("rv") != -1 && agent.indexOf("firefox") == -1)));
if (isMSIE) {
fileName = URLEncoder.encode(fileName, "UTF-8");
} /*else {
// fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
fileName = new String(fileName.getBytes("UTF-8"), "UTF-8");
}*/
// 设置response参数,可以打开下载页面
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ new String((fileName+".xls").getBytes("UTF-8"), "ISO-8859-1"));
response.setContentLength(content.length);
//response.setCharacterEncoding("utf-8");
ServletOutputStream outputStream = response.getOutputStream();
BufferedInputStream bis = new BufferedInputStream(is);
BufferedOutputStream bos = new BufferedOutputStream(outputStream);
byte[] buff = new byte[8192];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
bis.close();
bos.close();
outputStream.flush();
outputStream.close();
}catch (Exception e) {
e.printStackTrace();
}
}
4、单独抽出来生成excel文档的方法 downloadExcelFile(so)
/**
*此方法主要把数据合到excel表格里,并准备下载到页面
/
public void downloadExcelFile( OutputStream out) throws Exception{
String sheetName = "数据统计";
List<Map<String, Object>> statisticsList = new ArrayList<>();
Map<String, Object> staticMap = new HashMap<>();
staticMap.put("orgName","南道公司");
staticMap.put("name","nandao");
staticMap.put("age","18");
Map<String, Object> staticMap1 = new HashMap<>();
staticMap1.put("orgName","四方");
staticMap1.put("name","dao");
staticMap1.put("age","28");
statisticsList.add(staticMap);
statisticsList.add(staticMap1);
String[][] dataList = null;//采用二维数组封装数据
String[] columnName = new String[3];
columnName = new String[]{ "区域名称", "姓名", "年龄" };
dataList = new String[statisticsList.size()][columnName.length];
for (int i = 0; i < statisticsList.size(); i++) {
int s = 0;
dataList[i][s] = statisticsList.get(i).get("orgName").toString() == null ? "" : statisticsList.get(i).get("orgName").toString();
dataList[i][++s] = statisticsList.get(i).get("name").toString() == null ? "" : statisticsList.get(i).get("name").toString();
dataList[i][++s] = statisticsList.get(i).get("age").toString() == null ? "0" : statisticsList.get(i).get("age").toString();
}
// 第一步,创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 创建第1行 也就是表头
HSSFRow row = sheet.createRow((int) 0);
row.setHeightInPoints(20);// 设置表头高度
// 第四步,创建表头单元格样式 以及表头的字体样式
HSSFCellStyle style = wb.createCellStyle();
// style.setWrapText(true);// 设置自动换行
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式
// style.setFillForegroundColor(HSSFColor.YELLOW.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
// headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
headerFont.setFontName("黑体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
style.setFont(headerFont); // 为标题样式设置字体样式
// 第四.一步,创建表头的列
for (int i = 0; i < columnName.length; i++) {
//====== 这里是重点,马上要自定义单元格的样式了 =============
//拿到palette颜色板
HSSFPalette palette = wb.getCustomPalette();
//这个是重点,具体的就是把之前的颜色 HSSFColor.LIME.index
//替换为 RGB(51,204,204) 宝石蓝这种颜色
//你可以改为 RGB(0,255,127)
palette.setColorAtIndex((short)i, (byte) 221, (byte) 235, (byte) 247);
style.setFillForegroundColor((short)i);
HSSFCell cell = row.createCell(i);
cell.setCellValue(columnName[i]);
cell.setCellStyle(style);
}
if(statisticsList != null){
// 第五步,创建单元格,并设置值
for (int i = 0; i < statisticsList.size(); i++) {
row = sheet.createRow((int) i + 1);
// 为数据内容设置特点新单元格样式1 自动换行 上下居中
HSSFCellStyle zidonghuanhang = wb.createCellStyle();
// zidonghuanhang.setWrapText(true);// 设置自动换行
zidonghuanhang.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个居中格式
// 设置边框
zidonghuanhang.setBottomBorderColor(HSSFColor.BLACK.index);
zidonghuanhang.setBorderBottom(HSSFCellStyle.BORDER_THIN);
zidonghuanhang.setBorderLeft(HSSFCellStyle.BORDER_THIN);
zidonghuanhang.setBorderRight(HSSFCellStyle.BORDER_THIN);
zidonghuanhang.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 为数据内容设置特点新单元格样式2 自动换行 上下居中左右也居中
HSSFCellStyle zidonghuanhang2 = wb.createCellStyle();
// zidonghuanhang2.setWrapText(true);// 设置自动换行
zidonghuanhang2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 创建一个上下居中格式
zidonghuanhang2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
// 设置边框
zidonghuanhang2.setBottomBorderColor(HSSFColor.BLACK.index);
zidonghuanhang2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
zidonghuanhang2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
zidonghuanhang2.setBorderRight(HSSFCellStyle.BORDER_THIN);
zidonghuanhang2.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFCell datacell = null;
for (int j = 0; j < columnName.length; j++) {
datacell = row.createCell(j);
try {
datacell.setCellValue(Double.parseDouble(dataList[i][j].replace(",","")));
} catch (Exception e) {
datacell.setCellValue(dataList[i][j]);
}
datacell.setCellStyle(zidonghuanhang2);
}
}
}
// 第六步,将文件存到浏览器设置的下载位置
try {
wb.write(out);
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
5、启动后访问就可以生成并下载了:
分析到此结束!关于 jar 相关功能可以参考这篇文章:https://www.cnblogs.com/xinaixia/p/6707868.html