POI導出Excel，每個Excel中有多個sheet
通過Zip導出多個Excel的壓縮檔案。
假如項目部署到伺服器上，就無法直接下載下傳到本地桌面，需要先下載下傳到伺服器的某個檔案夾，再使用response通過浏覽器下載下傳到用戶端的桌面。
假如需要同時下載下傳多個Excel，response請求隻滿足一次下載下傳一個檔案，這是需要先将多個Excel檔案下載下傳到同一個檔案夾下，再将此檔案夾壓縮，之後下載下傳壓縮檔案。
根據别人方法總結的，在此記錄一下。
參考别人的：java将不同子產品資料導出到多個excel并壓縮下載下傳
private ExportToExcelByPOI exportToExcelByPOI{
private final static Logger logger = LoggerFactory.getLogger(BasyybqkckController.class);
/**
* 工具類
*
* @param workbook
* @param sheetNum 第幾個sheet
* @param sheetTitle sheet的名稱
* @param headers 表頭，可生成複雜的表頭
* @param fieldName 表頭對飲的字段名
* @param list 資料
* @param type 用來分辨不同頁面的導出
* @throws Exception
*/
public void exportExcelPoiTool(SXSSFWorkbook workbook, int sheetNum, String sheetTitle, String[][] headers, String[] fieldName,
List<HashMap<String, Object>> list, String type) throws Exception {
//設計表頭
int mergerNum = 0; //合并數
//表頭占幾行，正文内容從表頭之下開始
int headersLen = headers.length;
// 第一步，建立一個webbook，對應一個Excel以xsl為擴充名檔案
//SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();
Sheet sheet = workbook.createSheet(sheetTitle);
workbook.setSheetName(sheetNum, sheetTitle);
// 當機最左邊的兩列、當機最上面的一行
// 即：滾動橫向滾動條時，左邊的第一、二列固定不動;滾動縱向滾動條時，上面的第一，二，三行固定不動。
//sheet.createFreezePane(2, 3);
//設定列寬度大小
// sheet.setDefaultColumnWidth((short) 30);
//第二步， 生成表格第一行的樣式和字型
// 設定并擷取到需要的樣式
XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(workbook);
XSSFCellStyle xssfCellStyleOne = getAndSetXSSFCellStyleOne(workbook);
XSSFCellStyle xssfCellStyleTwo = getAndSetXSSFCellStyleTwo(workbook);
// 産生表格标題行
// 循環建立header單元格(根據實際情況靈活建立即可)
//給單元格設定值
for (int i = 0; i < headers.length; i++) {
//XSSFRow row = sheet.createRow(i);
Row row = sheet.createRow(i);
row.setHeight((short) 700);
for (int j = 0; j < headers[i].length; j++) {
//XSSFCell cell = row.createCell(j);
Cell cell = row.createCell(j);
cell.setCellStyle(xssfCellStyleHeader);
cell.setCellValue(headers[i][j]);
}
}
Map<Integer, List<Integer>> jumpMap = new HashMap<Integer, List<Integer>>(); // 合并行時要跳過的行列
//合并列
for (int i = 0; i < headers[headers.length - 1].length; i++) {
if ("".equals(headers[headers.length - 1][i])) {
for (int j = headers.length - 2; j >= 0; j--) {
if (!"".equals(headers[j][i])) {
sheet.addMergedRegion(new CellRangeAddress(j, headers.length - 1, i, i)); // 合并單元格
break;
} else {
if (jumpMap.containsKey(j)) {
List<Integer> jumpList = jumpMap.get(j);
jumpList.add(i);
jumpMap.put(j, jumpList);
} else {
List<Integer> jumpList = new ArrayList<Integer>();
jumpList.add(i);
jumpMap.put(j, jumpList);
}
}
}
}
}
//合并行
for (int i = 0; i < headers.length - 1; i++) {
for (int j = 0; j < headers[i].length; j++) {
List<Integer> jumpList = jumpMap.get(i);
if (jumpList == null || (jumpList != null && !jumpList.contains(j))) {
if ("".equals(headers[i][j])) {
mergerNum++;
if (mergerNum != 0 && j == (headers[i].length - 1)) {
sheet.addMergedRegion(new CellRangeAddress(i, i, j - mergerNum, j)); // 合并單元格
mergerNum = 0;
}
} else {
if (mergerNum != 0) {
sheet.addMergedRegion(new CellRangeAddress(i, i, j - mergerNum - 1, j - 1)); // 合并單元格
mergerNum = 0;
}
}
}
}
}
// 第三步：周遊集合資料，産生資料行，開始插入資料
// 周遊建立行,導出資料
if (list.size() > 0) {
for (int rownum = 0; rownum < list.size(); rownum++) {
// 從第四行開始有資料，前三行是表頭
Row row = sheet.createRow(rownum + headersLen);
// 循環建立單元格
for (int cellnum = 0; cellnum < fieldName.length; cellnum++) {
Cell cell = row.createCell(cellnum);
// 根據行數,設定該行内的單元格樣式
cell.setCellStyle(xssfCellStyleOne);
if (fieldName[cellnum].equals("xh")) {
cell.setCellValue(rownum + 1);//序号
} else {
cell.setCellValue(list.get(rownum).get(fieldName[cellnum]) + "");
}
}
}
}
//設定内容的sheet
setSheet(sheet);
// 即：滾動橫向滾動條時，左邊的第一、二列固定不動;滾動縱向滾動條時，上面的第一，二，三行固定不動。
sheet.createFreezePane(2, 3);
//sheet.createFreezePane(2, headersLen);
/**
* 導出到本機桌面
*
* @param list，要導出的資料，一條資料對應一個sheet。 一條資料中包含多條要導出的資料，格式如下：
* List<List<Map<String,Object>>> list
* @param headers 表頭，表頭有一行的，兩行的，三行的
* @param fieldName 表頭對應的字段名稱
* @param fileName 檔案名
* @param sheetTile sheet名稱，一個sheetTitle對應list中的一條資料
* @param type 用來分辨不同的導出：
* @return
*/
public int exportToExcelByPoi_pc(List list, String[][] headers, String[] fieldName,
String fileName, String[] sheetTile, String type) {
//擷取桌面路徑,用于下載下傳檔案
FileSystemView fsv = FileSystemView.getFileSystemView();
File com = fsv.getHomeDirectory();
// 導出的excel,全檔案名
//final String excelExportDestfilepath = com + "/" + map.get("fileName");
final String excelExportDestfilepath = com + "/" + "/" + fileName + ".xlsx";
ExportToExcelByPOI excelExport = null;
// 用于導出多個sheet
excelExport = new ExportToExcelByPOI();
FileOutputStream fos = null;
SXSSFWorkbook sxssfWorkbook = null;
try {
/// -> 從資料庫中查詢出要進行excel導出的資料
long startTime0 = System.currentTimeMillis();
long endTime0 = System.currentTimeMillis();
logger.info("查詢資料總耗時:{} 毫秒; list數量為 {}", endTime0 - startTime0, list.size());
/// -> excel到處邏輯
long startTime = System.currentTimeMillis();
// 擷取SXSSFWorkbook執行個體
sxssfWorkbook = new SXSSFWorkbook();
// sheet名稱
//Sheet sheet = sxssfWorkbook.createSheet(fileName);
// 建立第一行,作為header表頭;從第二行開始
int ts = sheetTile.length;
for (int i = 0; i < sheetTile.length; i++) {
excelExport.exportExcelPoiTool(sxssfWorkbook, i, sheetTile[i], headers, fieldName, (List<HashMap<String, Object>>) list.get(i), type);
}
//在後面設定sheet
//setSheet(sheet);
fos = new FileOutputStream(excelExportDestfilepath);
sxssfWorkbook.write(fos);
long endTime = System.currentTimeMillis();
logger.info("資料全部導出至excel總耗時:{} 毫秒!", endTime - startTime, list.size());
} catch (Exception e) {
logger.error("發生異常咯！", e);
} finally {
try {
if (sxssfWorkbook != null)
// dispose of temporary files backing this workbook on disk -> 處
// 理SXSSFWorkbook導出excel時，産生的臨時檔案
sxssfWorkbook.dispose();
if (fos != null)
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return 1;
}
與導出到本機桌面不同的是的地方就是多了一個檔案下載下傳，
1、先将檔案下載下傳到伺服器的桌面，
2、然後再将伺服器上的檔案通過浏覽器下載下傳到用戶端，
3、最後删除伺服器上的檔案
/**
* 伺服器導出到本機
*
* @param list，要導出的資料，一條資料對應一個sheet。 一條資料中包含多條要導出的資料，格式如下：
* List<List<Map<String,Object>>> list
* @param headers 表頭，表頭有一行的，兩行的，三行的
* @param fieldName 表頭對應的字段名稱
* @param fileName 檔案名
* @param sheetTile sheet名稱，一個sheetTitle對應list中的一條資料
* @param type 用來分辨不同的導出：
* @param response
* @return
*/
public void exportToExcelByPoi(List list, String[][] headers, String[] fieldName,
String fileName, String[] sheetTile, String type, HttpServletResponse response) {
//擷取桌面路徑,用于下載下傳檔案
FileSystemView fsv = FileSystemView.getFileSystemView();
File com = fsv.getHomeDirectory();
// 導出的excel,全檔案名
//final String excelExportDestfilepath = filePath + fileName + ".xlsx";
final String excelExportDestfilepath = com + "/" + "/" + fileName + ".xlsx";
ExportToExcelByPOI excelExport = null;
// 用于導出多個sheet
excelExport = new ExportToExcelByPOI();
FileOutputStream fos = null;
SXSSFWorkbook sxssfWorkbook = null;
try {
/// -> 從資料庫中查詢出要進行excel導出的資料
long startTime0 = System.currentTimeMillis();
long endTime0 = System.currentTimeMillis();
logger.info("查詢資料總耗時:{} 毫秒; list數量為 {}", endTime0 - startTime0, list.size());
/// -> excel到處邏輯
long startTime = System.currentTimeMillis();
// 擷取SXSSFWorkbook執行個體
sxssfWorkbook = new SXSSFWorkbook();
// sheet名稱
//Sheet sheet = sxssfWorkbook.createSheet(fileName);
//導出
for (int i = 0; i < sheetTile.length; i++) {
excelExport.exportExcelPoiTool(sxssfWorkbook, i, sheetTile[i], headers, fieldName, (List<HashMap<String, Object>>) list.get(i), type);
}
//在後面設定sheet
//setSheet(sheet);
fos = new FileOutputStream(excelExportDestfilepath);
sxssfWorkbook.write(fos);
// 以下是與導出到本機桌面不同的地方
// 檔案下載下傳
String excelName = fileName + ".xlsx";
InputStream inputStream = null;
ServletOutputStream outServletOutputStream = null;
// 輸出文檔路徑及名稱
File outFile = new File(excelExportDestfilepath);
inputStream = new FileInputStream(excelExportDestfilepath);
response.setCharacterEncoding("utf-8");
response.setContentType("application/msexcel");
//response.addHeader("Content-Disposition", "attachment;filename=我" + "你" + ".xlsx");
//response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
//防止檔案名中文亂碼
response.setHeader("Content-Disposition", "attachment;filename=" +
new String(excelName.getBytes("GBK"), "ISO8859-1"));
outServletOutputStream = response.getOutputStream();
byte[] buffer = new byte[512];
int bytesToRead = -1;
while ((bytesToRead = inputStream.read(buffer)) != -1) {
outServletOutputStream.write(buffer, 0, bytesToRead);
}
// 流關閉後
inputStream.close();
//删除伺服器上下載下傳的檔案
outFile.delete();
long endTime = System.currentTimeMillis();
logger.info("資料全部導出至excel總耗時:{} 毫秒!", endTime - startTime, list.size());
} catch (Exception e) {
logger.error("發生異常咯！", e);
} finally {
try {
if (sxssfWorkbook != null)
// dispose of temporary files backing this workbook on disk -> 處
// 理SXSSFWorkbook導出excel時，産生的臨時檔案
sxssfWorkbook.dispose();
if (fos != null)
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
調用exportToExcelByPoi方法，進行導出
@Autowired
private ExportToExcelByPOI exportToExcelByPOI;
@RequestMapping(value = "/exportToExcelByPoi", method = RequestMethod.GET)
@ResponseBody
public void exportPoi(@RequestParam Map<String, Object> map, HttpServletRequest request, HttpServletResponse response) {
//生成複雜的表頭
String[][] propertyDes = {{"序号","疾病", "例數", "", "", "死亡率", "", "", "藥占比", "", "", "材料占比",
"", "", "藥品和材料占比", "", "", "平均住院日", "", "", "平均住院費用", "", ""},
{"","", "本期值", "上期值", "", "本期值", "上期值", "", "本期值", "上期值", "", "本期值", "上期值",
"", "本期值", "上期值", "", "本期值", "上期值", "", "本期值", "上期值", ""},
{"","", "", "值", "±%", "", "值", "±%", "", "值", "±%", "", "值", "±%", "", "值", "±%",
"", "值", "±%", "", "值", "±%"}};
// 表頭對應的字段名稱
String[] fieldName = {"xh","name", "ls", "lsPrev", "lsRadio",
"swl", "swlPrev", "swlRadio", "yzb", "yzbPrev", "yzbRadio",
"clzb", "clzbPrev", "clzbRadio", "ypyclzb", "ypyclzbPrev", "ypyclzbRadio",
"pjzyr", "pjzyrPrev", "pjzyrRadio", "pjzyfy", "pjzyfyPrev","pjzyfyRadio"};
// 查詢要導出的資料
List<HashMap<String, Object>> list = service.getData(map);
//将資料轉換成要導出的格式，這裡根據情況而定
List<HashMap<String, Object>> listData = conversionListData(list);
// sheet的名稱，要導出多個sheet的話，就會對應多個
String[] sheetTile={fileName};
List listExcel=new ArrayList();
// 一個sheetTitle，對應一個listData，多個SheetTitle，對應多個ListData。這裡是一個sheetTitle對應一個ListData。
listExcel.add(listData);
//多個的話如下：
String[] sheetTile = {"濟南市", "青島市", "淄博市", "棗莊市", "東營市", "煙台市", "濰坊市", "濟甯市", "泰安市",
"威海市", "日照市", "臨沂市", "德州市", "聊城市", "濱州市", "菏澤市"};
for (int i = 0; i < sheetTile.length; i++) {
List param = new ArrayList();
// 分别查詢16個地級市的資料
List<HashMap<String, Object>> list = service.getData(map);
//将資料轉換為導出的格式
List<HashMap<String, Object>> listData = conversionListData(list);
// 把資料放到數組中，數組中放的是16個地級市的資料
listExcel.add(listData );
}
exportToExcelByPOI.exportToExcelByPoi(listExcel,propertyDes,fieldName,fileName,sheetTile,response);
}
以下代碼：将多個Excel壓縮成一個檔案進行導出
/**
* 伺服器導出多個Excel的Zip檔案到用戶端
*
*
* @param list，要導出的資料，一條資料對應一個sheet。 一條資料中包含多條要導出的資料，格式如下：
* List<List<Map<String,Object>>> list
* @param headers 表頭，表頭有一行的，兩行的，三行的
* @param fieldName 表頭對應的字段名稱
* @param fileName 檔案名
* @param sheetTile sheet名稱，一個sheetTitle對應list中的一條資料
* @param type 用來分辨不同的導出：
* @param response
* @return
*/
public void exportToExcelByPoi_zip(List list, String[][] headers, String[] fieldName,
String[] fileName, String[] sheetTile, String type,
HttpServletResponse response, HttpServletRequest request) {
// 生成的excel表格路徑
final List<String> fileNameList = new ArrayList<String>();
// list中包括多個excel的資訊
for (int i = 0; i < list.size(); i++) {
List ListQuarter = (List) list.get(i);
// 将資料導入到excel表格中，并将生成的表格路徑添加到fileNameList中
ExportExcelZipUtils.toExcelForZip(ListQuarter,headers,fieldName,fileName[i],sheetTile,type,response,request,fileNameList);
}
// 将生成的多個excel表格路徑壓縮成zip格式檔案，并傳回生成的臨時zip檔案路徑
final String zipFilePath = ExportExcelZipUtils.toZipFiles(request, fileNameList, "批量導出excel檔案.zip");
// zip檔案建立成功
if (!StringUtils.isEmpty(zipFilePath)) {
try {
// 設定response頭
ExportExcelZipUtils.setResponseHeader(response, "批量導出excel檔案.zip");
// 下載下傳
ExportExcelZipUtils.downloadZip(response.getOutputStream(), zipFilePath);
} catch (final IOException e) {
logger.info("get outputStream from response failed");
}
}
}
public class ExportExcelZipUtils {
private final static org.slf4j.Logger logger = LoggerFactory.getLogger(ExportToExcelByPOI.class);
/**
* 設定excel壓縮檔案導出時的請求頭
* @param response
* @param fileName:多個excel的壓縮檔案名
*/
public static void setResponseHeader(final HttpServletResponse response, final String fileName) {
logger.info("begin to set response header");
try {
response.reset();
response.setContentType("application/octet-stream");
response.setCharacterEncoding("UTF-8");
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
logger.info("set response header successfully");
} catch (final Exception e) {
logger.error("set response header failed", e);
}
}
/**
* 生成excel檔案
* @param list
* @param headers
* @param fieldName
* @param fileName
* @param sheetTile
* @param type
* @param response
* @param request
* @param fileNameList
*/
public static void toExcelForZip(List list, String[][] headers, String[] fieldName,
String fileName, String[] sheetTile, String type,
HttpServletResponse response, HttpServletRequest request,
List<String> fileNameList) {
final File dirFile = new File(request.getRealPath(File.separator + "excel") + File.separator);
if (!dirFile.exists()) {
dirFile.mkdirs();
}
final String file = request.getRealPath(File.separator + "excel") + File.separator + fileName + ".xlsx";
//擷取桌面路徑,用于下載下傳檔案
FileSystemView fsv = FileSystemView.getFileSystemView();
File com = fsv.getHomeDirectory();
// 導出的excel,全檔案名
final String excelExportDestfilepath = com + "/" + "/" + fileName + ".xlsx";
ExportToExcelByPOI excelExport = null;
// 用于導出多個sheet
excelExport = new ExportToExcelByPOI();
FileOutputStream fos = null;
SXSSFWorkbook sxssfWorkbook = null;
try {
fos = new FileOutputStream(file);//為壓縮檔案做準備
/// -> 從資料庫中查詢出要進行excel導出的資料
long startTime0 = System.currentTimeMillis();
long endTime0 = System.currentTimeMillis();
logger.info("查詢資料總耗時:{} 毫秒; list數量為 {}", endTime0 - startTime0, list.size());
/// -> excel到處邏輯
long startTime = System.currentTimeMillis();
// 擷取SXSSFWorkbook執行個體
sxssfWorkbook = new SXSSFWorkbook();
// sheet名稱
//Sheet sheet = sxssfWorkbook.createSheet(fileName);
//導出
for (int i = 0; i < sheetTile.length; i++) {
excelExport.exportExcelPoiTool(sxssfWorkbook, i, sheetTile[i], headers, fieldName, (List<HashMap<String, Object>>) list.get(i), type);
}
//在後面設定sheet
//setSheet(sheet);
//在這裡不能寫這一行，寫上這一行後，導出的檔案會有問題。
//fos = new FileOutputStream(excelExportDestfilepath);
sxssfWorkbook.write(fos);
fos.flush();//為壓縮檔案做準備
fileNameList.add(file);//為壓縮檔案做準備
long endTime = System.currentTimeMillis();
logger.info("資料全部導出至excel總耗時:{} 毫秒!", endTime - startTime, list.size());
} catch (Exception e) {
logger.error("發生異常咯！", e);
} finally {
try {
if (sxssfWorkbook != null)
// dispose of temporary files backing this workbook on disk -> 處
// 理SXSSFWorkbook導出excel時，産生的臨時檔案
sxssfWorkbook.dispose();
if (fos != null)
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public static String toZipFiles(final HttpServletRequest request, final List<String> fileNameList,
final String zipFileName) {
String zipFilePath = request.getRealPath(File.separator + "excel") + File.separator + zipFileName;
logger.info("begin to create zip file");
final File[] files = new File[fileNameList.size()];
for (int i = 0; i < fileNameList.size(); i++) {
files[i] = new File(fileNameList.get(i));
}
// 壓縮檔案
final File zipFile = new File(zipFilePath);
// 将excel檔案壓縮成zip檔案
final byte[] buf = new byte[1024];
ZipOutputStream zipOut = null;
FileInputStream fis = null;
try {
zipOut = new ZipOutputStream(new FileOutputStream(zipFile));
for (int i = 0; i < files.length; i++) {
fis = new FileInputStream(files[i]);
zipOut.putNextEntry(new ZipEntry(files[i].getName()));
int len = 0;
while ((len = fis.read(buf)) > 0) {
zipOut.write(buf, 0, len);
}
zipOut.closeEntry();
fis.close();
}
} catch (final Exception e) {
zipFilePath = null;
logger.error("failed to create zip file");
} finally {
if (zipOut != null) {
try {
zipOut.close();
} catch (final IOException e) {
logger.error("failed to close ZipOutputStream");
}
}
if (fis != null) {
try {
fis.close();
} catch (final IOException e) {
logger.error("failed to close FileInputStream");
}
}
}
return zipFilePath;
}
public static void downloadZip(final OutputStream out, final String zipFilePath) {
logger.info("begin to download zip file from " + zipFilePath);
FileInputStream inStream = null;
try {
inStream = new FileInputStream(zipFilePath);
final byte[] buf = new byte[4096];
int readLength;
while (((readLength = inStream.read(buf)) != -1)) {
out.write(buf, 0, readLength);
}
out.flush();
} catch (final Exception e) {
logger.error("download zip excel failed");
} finally {
try {
inStream.close();
} catch (final IOException e) {
logger.error("failed to close FileInputStream");
}
try {
out.close();
} catch (final IOException e) {
logger.error("failed to close OutputStream");
}
}
}
}
調用exportToExcelByPoi_zip方法
1、listQuarterExcel的格式：List<List<HashMap<String, Object>>
2、headers：表頭
3、field：表頭對應的字段名
4、fileName：檔案名
5、sheetTitle：sheet名稱
6、type：用來區分不同的頁面的導出
![poi導出excel[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)