今天主要记录一下excel文件的生成和下载
前端的话其实没有什么不同,依然是window.location.href =???
后端的话这里主要记录一下,生成excel文件的技术是HSSFWorkbook ,因为我做的是生成对比文件的差异报告,所以代码中的出入希望读者做好取舍。
@RequestMapping("/diffReport")
public void diffReport(String fileFormat, Long fileId1, Long fileId2, String system, String module, String deviceIpF, String deviceIpT, String fileName, HttpServletRequest request, HttpServletResponse response) {
String title = (system + module + "模块" + "fileName" + "差异报告").replaceAll(" ", "");
String[] titles = {"序号", deviceIpF, deviceIpT, "类型", "差异详情"};
List diff = systemModuleFileCollectNonCommonService.diffReport(fileFormat, contentF, fileIdF, contentT, fileIdT);
//生成报告
ExcelGenerator.generateExcel(request, response, title, titles, diff, fileName);
}
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
*
* @author
* @data 2020年5月13日
* excel文档生成
*/
public final class ExcelGenerator {
public static void generateExcel(HttpServletRequest request, HttpServletResponse response, String title, String[] titles, List dataList, String fileName) {
HSSFWorkbook wb = new HSSFWorkbook();
// 一级标题合并单元格
CellRangeAddress callRangeAddress01 = new CellRangeAddress(0, 0, 0, 4);// 起始行,结束行,起始列,结束列
// 标题样式
HSSFCellStyle tel1Style = createCellStyle(wb).get("title1Style");
HSSFCellStyle tel2Style = createCellStyle(wb).get("title2Style");
// 内容样式
HSSFCellStyle cellStyle = createCellStyle(wb).get("contentStyle");
HSSFCellStyle numStyle = createCellStyle(wb).get("numStyle");
// 2.创建工作表
HSSFSheet sheet = wb.createSheet("sheet1");
// 2.1加载合并单元格对象
sheet.addMergedRegion(callRangeAddress01);
// 设置列宽
sheet.setDefaultColumnWidth(25);
sheet.setColumnWidth(0, 256*8);
sheet.setColumnWidth(3, 256*12);
// 一级标题
HSSFRow row1 = sheet.createRow(0);
HSSFCell cell1 = row1.createCell(0);
// 加载单元格样式
cell1.setCellStyle(tel1Style);
cell1.setCellValue(title);
// 创建列标题
HSSFRow rowtel = sheet.createRow(1);
for (int i = 0; i < titles.length; i++) {
HSSFCell cell2 = rowtel.createCell(i);
cell2.setCellStyle(tel2Style);
cell2.setCellValue(titles[i]);
}
//创建内容
for (int i = 0; i < dataList.size(); i++) {
Map<String, String> data = (Map<String, String>) dataList.get(i);
HSSFRow rowContent1 = sheet.createRow(i + 2);
rowContent1.setHeight((short)1200);
HSSFCell cellContent1 = rowContent1.createCell(0);
cellContent1.setCellStyle(numStyle);
cellContent1.setCellValue(i+1);
HSSFCell cellContent2 = rowContent1.createCell(1);
cellContent2.setCellStyle(cellStyle);
cellContent2.setCellValue(data.get("fContent"));
HSSFCell cellContent3 = rowContent1.createCell(2);
cellContent3.setCellStyle(cellStyle);
cellContent3.setCellValue(data.get("tContent"));
HSSFCell cellContent4 = rowContent1.createCell(3);
cellContent4.setCellStyle(numStyle);
cellContent4.setCellValue(data.get("type"));
HSSFCell cellContent5 = rowContent1.createCell(4);
cellContent5.setCellStyle(cellStyle);
cellContent5.setCellValue(data.get("detail"));
}
OutputStream fos = null;
try {
response.reset();
fos = response.getOutputStream();
String userAgent = request.getHeader("USER-AGENT");
fileName = fileName.substring(0, fileName.lastIndexOf(".")) + ".xls";
if (StringUtils.contains(userAgent, "Mozilla")) {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} else {
fileName = URLEncoder.encode(fileName, "utf-8");
}
response.setCharacterEncoding("UTF-8");
// 设置contentType为excel格式
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(fileName, "UTF-8"));
wb.write(fos);
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
public static Map<String, HSSFCellStyle> createCellStyle(HSSFWorkbook workbook) {
Map<String, HSSFCellStyle> styleMap = new HashMap<String, HSSFCellStyle>();
//标题1格式
HSSFCellStyle titleStyle = workbook.createCellStyle();
//居中
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//自动换行
titleStyle.setWrapText(true);
//字体格式
setSimpleCellFontStyle(workbook, titleStyle, (short) 20);
styleMap.put("title1Style", titleStyle);
//标题2格式
HSSFCellStyle title2Style = workbook.createCellStyle();
title2Style.setAlignment(HorizontalAlignment.CENTER);
title2Style.setVerticalAlignment(VerticalAlignment.CENTER);
title2Style.setWrapText(true);
setSimpleCellFontStyle(workbook, title2Style, (short) 14);
styleMap.put("title2Style", title2Style);
HSSFCellStyle numStyle = workbook.createCellStyle();
numStyle.setAlignment(HorizontalAlignment.CENTER);
numStyle.setVerticalAlignment(VerticalAlignment.CENTER);
numStyle.setWrapText(true);
setSimpleCellFontStyle(workbook, numStyle, (short) 11);
styleMap.put("numStyle", numStyle);
//内容样式
HSSFCellStyle contentStyle = workbook.createCellStyle();
contentStyle.setAlignment(HorizontalAlignment.GENERAL);
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentStyle.setWrapText(true);
setSimpleCellFontStyle(workbook, contentStyle, (short) 11);
styleMap.put("contentStyle", contentStyle);
return styleMap;
}
private static HSSFCellStyle setSimpleCellFontStyle(HSSFWorkbook workbook, HSSFCellStyle cellStyle, short size) {
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints(size);
cellStyle.setFont(font);
return cellStyle;
}
}
各种不足,希望指正