poi报表导出 复杂导出 指定合并列和对比重复列合并行动态导出
导出代码：
@Override public void statCheckAndCaptureOutPutExcel(Query params, HttpServletRequest req, HttpServletResponse resp) { // 创建表格时间 // 2.定义变量值 创建Excel文件 String headString = "学上考试成绩统计"; // 定义表格标题 String fileName = DateUtil.format(new Date(), DateUtil.DATE_TIME_PATTERN_14) + headString; // 定义文件名 String sheetName = DateUtil.format(new Date(), DateUtil.DATE_TIME_PATTERN_14) + headString; // 定义工作表表名 String[] columnNames = { "姓名", "考试时间", "科目", "及格次数", "及格率","班级" }; int[] sheetWidth = { 4500, 5500, 4500, 4500, 4500 }; // 定义每一列宽度 XSSFWorkbook wb = new XSSFWorkbook(); // 创建Excel文档对象 XSSFSheet sheet = wb.createSheet(sheetName); // 创建工作表 // 3.生成表格 // ①创建表格标题 ExportExcelXssfUtil.createHeadTittle(wb, sheet, headString, columnNames.length - 1); // ②创建表头 ExportExcelXssfUtil.createThead(wb, sheet, columnNames, sheetWidth, 1); // 实体类转换为map List<LinkedHashMap<String, String>> result = setExcelStatCheckAndCaptureMap(params); // ③填入数据 List<Integer> colIdxs = new LinkedList<Integer>(); colIdxs.add(0); colIdxs.add(1); colIdxs.add(1); List<Integer> startCells = new LinkedList<Integer>(); startCells.add(0); startCells.add(1); startCells.add(4); List<Integer> endCells = new LinkedList<Integer>(); endCells.add(0); endCells.add(1); endCells.add(5); List<String> flgKays = new LinkedList<String>(); flgKays.add("name"); flgKays.add("examtime"); flgKays.add("examtime"); // ③填入数据 ExportExcelXssfUtil.createTable(wb, sheet, result, 2, colIdxs, startCells, endCells, false, flgKays); // ④输出流网页下载 ExportExcelXssfUtil.respOutPutExcel(fileName, wb, req, resp); }
工具类：
package com.ywtg.common.util;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Pattern;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import lombok.extern.slf4j.Slf4j;
/**
* @Package com.slife.pdfg.util
* @ClassName: ExportExcelXssfUtil
* @Description: 报表导出工具类
* @Author youli
* @date 2020年7月30日
* @CopyRight：上海成生科技有限公司
*/
@Slf4j
public class ExportExcelXssfUtil {
/**
* 创建表格标题
*
* @param wb Excel文档对象
* @param sheet 工作表对象
* @param headString 标题名称
* @param col 标题占用列数
*/
public static void createHeadTittle(XSSFWorkbook wb, XSSFSheet sheet, String headString, int col) {
XSSFRow row = sheet.createRow(0); // 创建Excel工作表的行
XSSFCell cell = row.createCell(0); // 创建Excel工作表指定行的单元格
row.setHeight((short) 1000); // 设置高度
cell.setCellType(CellType.STRING); // 定义单元格为字符串类型
cell.setCellValue(new XSSFRichTextString(headString));
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col)); // 指定标题合并区域
// 定义单元格格式，添加单元格表样式，并添加到工作簿
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 指定单元格水平居中对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 指定单元格垂直居中个对齐
cellStyle.setWrapText(true); // 指定单元格自动换行
// 设置单元格字体
Font font = wb.createFont();
font.setBold(true);// 设置字体为粗体
font.setFontName("微软雅黑");
font.setColor(HSSFColorPredefined.BLACK.getIndex());
font.setFontHeightInPoints((short) 16); // 字体大小
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
/**
* 创建表头
*
* @param wb Excel文档对象
* @param sheet 工作表对象
* @param thead 表头内容
* @param sheetWidth 每一列宽度
*/
public static void createThead(XSSFWorkbook wb, XSSFSheet sheet, String[] thead, int[] sheetWidth) {
XSSFRow row1 = sheet.createRow(1);
row1.setHeight((short) 600);
// 定义单元格格式，添加单元格表样式，并添加到工作簿
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中对齐
cellStyle.setWrapText(true);
// 设置背景色灰色25%
cellStyle.setFillForegroundColor(HSSFColorPredefined.GREY_25_PERCENT.getIndex()); // 设置背景色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBorderRight(BorderStyle.THIN); // 设置右边框类型
cellStyle.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex()); // 设置右边框颜色
// 设置单元格字体
Font font = wb.createFont();
font.setBold(true);// 设置字体为粗体
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
// 设置表头内容
for (int i = 0; i < thead.length; i++) {
XSSFCell cell1 = row1.createCell(i);
cell1.setCellType(CellType.STRING);// 定义单元格为字符串类型
cell1.setCellValue(new XSSFRichTextString(thead[i]));
cell1.setCellStyle(cellStyle);
}
// 设置每一列宽度
for (int i = 0; i < sheetWidth.length; i++) {
sheet.setColumnWidth(i, sheetWidth[i]);
}
}
/**
* 创建表头
*
* @param wb Excel文档对象
* @param sheet 工作表对象
* @param thead 表头内容
* @param sheetWidth 每一列宽度
*/
public static void createThead(XSSFWorkbook wb, XSSFSheet sheet, String[] thead, int[] sheetWidth, int indexRow) {
XSSFRow row1 = sheet.createRow(indexRow);
row1.setHeight((short) 600);
// 定义单元格格式，添加单元格表样式，并添加到工作簿
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中对齐
cellStyle.setWrapText(true);
// 设置背景色灰色25%
cellStyle.setFillForegroundColor(HSSFColorPredefined.GREY_25_PERCENT.getIndex()); // 设置背景色
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN); // 设置右边框类型
cellStyle.setTopBorderColor(HSSFColorPredefined.BLACK.getIndex()); // 设置上边框颜色
cellStyle.setBottomBorderColor(HSSFColorPredefined.BLACK.getIndex()); // 设置下边框颜色
cellStyle.setLeftBorderColor(HSSFColorPredefined.BLACK.getIndex()); // 设置左边框颜色
cellStyle.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex()); // 设置右边框颜色
// 设置单元格字体
Font font = wb.createFont();
font.setBold(true);// 设置字体为粗体
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
// 设置表头内容
for (int i = 0; i < thead.length; i++) {
XSSFCell cell1 = row1.createCell(i);
cell1.setCellType(CellType.STRING);// 定义单元格为字符串类型
cell1.setCellValue(new XSSFRichTextString(thead[i]));
cell1.setCellStyle(cellStyle);
}
// 设置每一列宽度
for (int i = 0; i < sheetWidth.length; i++) {
sheet.setColumnWidth(i, sheetWidth[i]);
}
}
/*
* @param sheet
*
* @param colIdx 合并的列
*
* @param startRow 起始行
*
* @param stopRow 结束行
*
* @param isForward 是否递进合并其它列
*
* @param forwardToColIdx 递进到的列
*/
public static void mergeRowCell(XSSFSheet sheet, int colIdx, int startRow, int stopRow, boolean isForward,
int forwardToColIdx) {
String compareValue = null;
int beginRow = startRow;
int endRow = startRow;
for (int i = startRow; i <= stopRow; ++i) {
String value = sheet.getRow(i).getCell(colIdx).getRichStringCellValue() == null ? ""
: sheet.getRow(i).getCell(colIdx).getRichStringCellValue().toString();
// 定义单元格格式，添加单元格表样式，并添加到工作薄
XSSFCellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setWrapText(true);
// 单元格字体
Font font = sheet.getWorkbook().createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
if (i == startRow) {
compareValue = value;
} else {
if (compareValue.equals(value)) {// 相同，则设置重复的值为空
sheet.getRow(i).getCell(colIdx).setCellValue("");
endRow = i;
} else {// 不同，则合并之前相同的单元格
if (beginRow < endRow) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(beginRow, endRow, colIdx, colIdx);
sheet.addMergedRegion(cellRangeAddress);
XSSFCellStyle jz = sheet.getWorkbook().createCellStyle();// 新建单元格样式
jz.setAlignment(HorizontalAlignment.CENTER); // 指定单元格水平居中对齐
jz.setVerticalAlignment(VerticalAlignment.CENTER); // 指定单元格垂直居中个对齐
if (isForward) {// 递进合并下一列
int nextColIndex = colIdx;
if (colIdx < forwardToColIdx) {
nextColIndex++;
} else if (colIdx > forwardToColIdx) {
nextColIndex--;
} else {
return;
}
mergeRowCell(sheet, nextColIndex, beginRow, endRow, isForward, forwardToColIdx);
}
}
compareValue = value;
beginRow = i;
endRow = i;
}
}
}
if (beginRow < endRow) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(beginRow, endRow, colIdx, colIdx);
sheet.addMergedRegion(cellRangeAddress);
XSSFCellStyle jz = sheet.getWorkbook().createCellStyle();// 新建单元格样式
jz.setAlignment(HorizontalAlignment.CENTER); // 指定单元格水平居中对齐
jz.setVerticalAlignment(VerticalAlignment.CENTER); // 指定单元格垂直居中个对齐
if (isForward) {// 递进合并下一列
int nextColIndex = colIdx;
if (colIdx < forwardToColIdx) {
nextColIndex++;
} else if (colIdx > forwardToColIdx) {
nextColIndex--;
} else {
return;
}
mergeRowCell(sheet, nextColIndex, beginRow, endRow, isForward, forwardToColIdx);
}
}
}
/**
* 填入数据
*
* @param wb // Excel文档对象
* @param sheet // 工作表对象
* @param result // 表数据
*/
public static void createTable(XSSFWorkbook wb, XSSFSheet sheet, List<LinkedHashMap<String, String>> result) {
// 定义单元格格式，添加单元格表样式，并添加到工作薄
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);
// 单元格字体
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
// 循环插入数据
for (int i = 0; i < result.size(); i++) {
XSSFRow row = sheet.createRow(i + 2);
row.setHeight((short) 400); // 设置高度
XSSFCell cell = null;
int j = 0;
for (String key : (result.get(i).keySet())) {
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
cell.setCellValue(new XSSFRichTextString(result.get(i).get(key)));
j++;
}
}
}
/**
* @Title: createTable
* @Description: 填入数据
* @Author youli
* @date 2023年1月31日
* @param wb Excel文档对象
* @param sheet 工作表对象
* @param result 表数据
* @param indexRow 开始行
* @param colIdx 指定对比列
* @param startCell 开始合并列
* @param endCell 结束合并列
* @param flgColor 标记颜色
* @param flgBold 标记是否加租
* @param flgKay 标记列
* @param startColorCell 标记列颜色
* @param endColorCell 标记列颜色
*/
/**
* @Title: createTable
* @Description: 创建复杂表格 标记列和需要对比列长度需和开始和并列及结束合并列数组长度相等
* @Author youli
* @date 2024年5月10日
* @param wb Excel文档对象
* @param sheet 工作表对象
* @param result 表数据
* @param indexRow 开始行
* @param colIdx 指定对比列
* @param startCells 指定开始合并列
* @param endCells 指定结束合并列
* @param flgBold 标记是否加租
* @param flgKays 标记列
*/
public static void createTable(XSSFWorkbook wb, XSSFSheet sheet, List<LinkedHashMap<String, String>> result,
Integer indexRow, List<Integer> colIdxs, List<Integer> startCells, List<Integer> endCells, Boolean flgBold,
List<String> flgKays) {
// 定义单元格格式，添加单元格表样式，并添加到工作薄
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);
// 单元格字体
Font font = wb.createFont();
font.setFontName("宋体");
font.setColor((short) 0);// //设置字体颜色黑色
font.setBold(flgBold); // 设置字体加粗
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中对齐
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中对齐
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN); // 设置右边框类型
cellStyle.setTopBorderColor(HSSFColorPredefined.BLACK.getIndex()); // 设置上边框颜色
cellStyle.setBottomBorderColor(HSSFColorPredefined.BLACK.getIndex()); // 设置下边框颜色
cellStyle.setLeftBorderColor(HSSFColorPredefined.BLACK.getIndex()); // 设置左边框颜色
cellStyle.setRightBorderColor(HSSFColorPredefined.BLACK.getIndex()); // 设置右边框颜色
List<String> compareValues = new LinkedList<String>();
List<Integer> beginRows = new LinkedList<Integer>();
List<Integer> endRows = new LinkedList<Integer>();
for (int e = 0; e < flgKays.size(); e++) {// 初始化可能合并行
compareValues.add("");
beginRows.add(indexRow);
endRows.add(indexRow);
}
int size = result.size();
// 循环插入数据
for (int i = 0; i < size; i++) {
XSSFRow row = sheet.createRow(i + indexRow);
row.setHeight((short) 400); // 设置高度
XSSFCell cell = null;
int j = 0;
LinkedHashMap<String, String> resultMap = result.get(i);
for (String key : (resultMap.keySet())) {
String value = resultMap.get(key);
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
XSSFRichTextString text = new XSSFRichTextString(value);
for (int e = 0; e < flgKays.size(); e++) {
String compareValue = compareValues.get(e);
int beginRow = beginRows.get(e);
int endRow = endRows.get(e);
int colIdx = colIdxs.get(e);
String flgKay = flgKays.get(e);
if (key.equals(flgKay)) {
if (j == colIdx) {
if (StringUtils.isNotBlank(value) && StringUtils.isNotBlank(compareValue)
&& compareValue.equals(value)) {// 相同，则设置重复的值为空
endRow = i + indexRow;
} else {// 不同，则合并之前相同的单元格
compareValue = value;
if (beginRow < endRow) {
Integer startCell = startCells.get(e);// 开始合并列
Integer endCell = endCells.get(e);// 结束合并列
for (int k = startCell; k <= endCell; k++) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(beginRow, endRow, k,
k);
sheet.addMergedRegion(cellRangeAddress);
}
}
beginRow = i + indexRow;
endRow = i + indexRow;
}
}
compareValues.set(e, compareValue);
beginRows.set(e, beginRow);
endRows.set(e, endRow);
}
}
cell.setCellValue(text);
j++;
}
if (size - 1 == i && CollectionUtils.isNotEmpty(beginRows) && CollectionUtils.isNotEmpty(endRows)) {
for (int e = 0; e < startCells.size(); e++) {
Integer startCell = startCells.get(e);// 开始合并列
Integer endCell = startCells.get(e);// 结束合并列
int beginRow = beginRows.get(e);
int endRow = endRows.get(e);
for (int k = startCell; k <= endCell; k++) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(beginRow, endRow, k, k);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
}
/**
* 判断是否为整数
*
* @param str 传入的字符串
* @return 是整数返回true,否则返回false
*/
public static boolean isInteger(String str) {
Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
return pattern.matcher(str).matches();
}
/**
* 填入数据
*
* @param wb // Excel文档对象
* @param sheet // 工作表对象
* @param result // 表数据
*/
public static void createTable(XSSFWorkbook wb, XSSFSheet sheet, List<LinkedHashMap<String, String>> result,
int indexRow) {
// 定义单元格格式，添加单元格表样式，并添加到工作薄
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);
// 单元格字体
Font font = wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 10);
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
// 循环插入数据
for (int i = 0; i < result.size(); i++) {
XSSFRow row = sheet.createRow(i + indexRow);
row.setHeight((short) 400); // 设置高度
XSSFCell cell = null;
int j = 0;
for (String key : (result.get(i).keySet())) {
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
cell.setCellValue(new XSSFRichTextString(result.get(i).get(key)));
j++;
}
}
}
/**
* 输出创建的Excel
*
* @param fileName
* @param wb
* @param resp
*/
public static String respOutPutExcel(String fileName, XSSFWorkbook wb) {
FileOutputStream fos;
try {
fos = new FileOutputStream(new File(fileName));
wb.write(fos);
fos.close();
System.out.println(fileName + "导出excel成功");
} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
}
return fileName;
}
/**
* @Title: respOutPutExcel
* @Description: 导出本地二进制流
* @Author youli
* @date 2024年1月4日
* @param fileFoodLitterContractinTotalListExcel
* @param req
* @param resp
*/
public static void respOutPutExcel(String path, HttpServletRequest req, HttpServletResponse resp) {
try {
log.info("下载路径：{}", path);
// path是指欲下载的文件的路径。
File file = new File(path);
// 取得文件名。
String fileName = file.getName();
// 以流的形式下载文件。
InputStream fis = new BufferedInputStream(new FileInputStream(path));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
resp.reset();
resp.setContentType("application/vnd.ms-excel;charset=utf-8");
String userAgent = req.getHeader("user-agent");
if (userAgent != null && userAgent.indexOf("Firefox") >= 0 || userAgent.indexOf("Chrome") >= 0
|| userAgent.indexOf("Safari") >= 0) {
fileName = new String((fileName + ".xls").getBytes(), "iso-8859-1");
} else {
fileName = URLEncoder.encode(fileName + ".xls", "UTF8"); // 其他浏览器
}
resp.setHeader("Content-Disposition", "attachment;filename=" + fileName);
resp.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
resp.addHeader("Content-Length", "" + file.length());
resp.setHeader("Access-Control-Allow-Origin", "*");
resp.setHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE, PUT, PATCH");
resp.setHeader("Access-Control-Allow-Headers",
"Origin, X-Requested-With, Content-Type, Accept, Authorization");
resp.setHeader("Access-Control-Allow-Credentials", "true");
OutputStream toClient = new BufferedOutputStream(resp.getOutputStream());
resp.setContentType("application/octet-stream");
toClient.write(buffer);
toClient.flush();
toClient.close();
log.info("下载成功：{}", path);
} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
} finally {
}
}
/**
* 输出创建的Excel
*
* @param fileName
* @param wb
* @param resp
*/
public static void respOutPutExcel(String fileName, XSSFWorkbook wb, HttpServletRequest req,
HttpServletResponse resp) {
ByteArrayOutputStream os = new ByteArrayOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
wb.write(os);
System.out.println("导出excel成功");
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面，配置跨域
resp.reset();
resp.setContentType("application/vnd.ms-excel;charset=utf-8");
String userAgent = req.getHeader("user-agent");
if (userAgent != null && userAgent.indexOf("Firefox") >= 0 || userAgent.indexOf("Chrome") >= 0
|| userAgent.indexOf("Safari") >= 0) {
fileName = new String((fileName + ".xls").getBytes(), "iso-8859-1");
} else {
fileName = URLEncoder.encode(fileName + ".xls", "UTF8"); // 其他浏览器
}
resp.setHeader("Content-Disposition", "attachment;filename=" + fileName);
ServletOutputStream out = resp.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
} finally {
try {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 输出创建的Excel转pdf
*
* @param fileName
* @param wb
* @param resp
*/
public static void respOutPutExcelToPdf(String fileName, XSSFWorkbook wb, HttpServletRequest req,
HttpServletResponse resp) {
ByteArrayOutputStream os = new ByteArrayOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
wb.write(os);
System.out.println("导出excel成功");
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数，可以打开下载页面，配置跨域
resp.reset();
// resp.addHeader("Access-Control-Allow-Origin", "*");
// resp.addHeader("Access-Control-Allow-Methods", "*");
// resp.addHeader("Access-Control-Allow-Headers", "*");
resp.setContentType("application/pdf;charset=utf-8");
String userAgent = req.getHeader("user-agent");
if (userAgent != null && userAgent.indexOf("Firefox") >= 0 || userAgent.indexOf("Chrome") >= 0
|| userAgent.indexOf("Safari") >= 0) {
fileName = new String((fileName + ".pdf").getBytes(), "iso-8859-1");
} else {
fileName = URLEncoder.encode(fileName + ".pdf", "UTF8"); // 其他浏览器
}
resp.setHeader("Content-Disposition", "attachment;filename=" + fileName);
ServletOutputStream out = resp.getOutputStream();
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
} finally {
try {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
导出效果图:
