匯出程式碼：
@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();
}
}
}
}
匯出效果圖:
