對於開發人員來說，經常會遇到數據導出到Excel文件需求，在到處數據較大的時常會遇到各類問題，以下整理了些這類問題的常用解決方法。
Excel導出常用方式：
Java當中常用的Excel導出主要有POI、JXL和「直接IO流」三種方式，他們各有優缺點！
1、POI介紹
ApachePOI是用Java編寫的免費開源的跨平臺的Java API，提供了一套對Microsoft Office格式文檔讀和寫的強大功能，由於Office 2007的文件結構完全不同於2003，POI有不同的處理API，當導出格式為2003時，POI調用的是HSSF包，當導出格式為2007時，則調用XSSF包，而SXSSF包是POI3.8版本智商對XSSF的一個擴展，用於大數據量的到處，實際應用中可有效避免內存溢出的問題。
此方式不僅對Excel操作支持，同時也對Word、PowerPoint等支持，並且該方式的讀取效率上遠遠優於JXL方式，在數據處理量級上也遠非JXL方式可比。
推薦使用情況：
2、JXL介紹
JXL是一個開源的JavaExcel API項目。他能作為Java Excel API的一個共同的支持庫，是因為它的基本功能是可創建，讀取和寫入電子表格。
主要特徵：1、生成Excel文件 2、從工作簿和電子表格導入數據 3、獲得行和列的總數。
此方式對中文支持很好，不會出現亂碼情況，支持單元格的常用操作，滿足一般需求，該方式在寫入效率上優於POI方式。但是需要注意：JXL只支持xls檔案格式，且處理的數據量非常有限。
推薦使用情況：
3、IO流介紹
IO流是一組有順序的，有起點和終點的字節集合，是對數據傳輸的總稱和抽象。即數據在兩設備間的傳輸稱為流，流的本質是數據傳輸，根據數據傳輸特性將流抽象為各種類，方便更直觀的進行數據操作，而上面提到的POI和JXL實際都是基於IO流封裝的工具。
該方式採用最原始的形式進行導出工作，選擇合適的流工作效率會非常出色。但是僅支持對文本文件的操作，如：CSV、TXT等，且導出的文件會相對較大。
推薦使用情況：
一、Excel2003格式
Excel2003導出時需要注意行數和列數的限制，每個工作表中最多有65,536行和256列。對於工作表能夠支持的最大個數，受CPU反應速度和內存大小影響。
常用導出方式中的POI支持該格式的只有HSSF包，當導出數據量大於一頁的最大行數（65536）時，可採用分頁的形式進行存儲。
推薦導出方式：POI和JXL
二、Excel2007格式
Excel2007導出時也需要注意行數和列的限制，每個工作表最多支持有1,048,576行和16,384列。
常用導方式支持該格式的只有XSSF包，保護SXSSF擴展包，並且僅有SXSSF支持大數據。（推薦POI3.8版本以上）
三、CSV格式
CSV是一種通用的、相對簡單的文件格式，廣泛的應用在程序之間轉移表格數據，具有以下特徵：
常用導出方式推薦:直接IO流導出
四、典型問題
1、傳統POI方式下利用HSSFWorkbook到處的情況下，當數據量達到一定程度，因為查詢出的數據集會直接釋放到Java內存中，導致出現內存溢出。
示例一、利用HSSFWorkBook方式一次性查詢出一百萬數據導出
//傳統POI使用模式
Workbook wb = new HSSFWorkBook();
//創建Excel的Sheet
Sheet sh = wb.createSheet();
//查詢數據
List<ExcelTemplateVo> queryList = newLinkedList<ExcelTemplateVo>();
queryList =iExcelTemplateService.getExcelTemplateVoByItems(0,1000000);
for(int rownum=0;rownum <queryList.size();rownum ++){
//創建行
Rowrow = sh.createRow(rownum);
ExcelTemplateVovo = queryList.get(rownum);
Cell cell = row.createCell(0);
cell.setCellValue(vo.getc1());
...
cell= row.createCell(19);
cell.setCellValue(vo.getc20());
}
FileOutputStream out = newFileOutputStream("/excelbigdata.xmlsx");
wb.write(out);
out.close;
如上代碼會拋出異常：
Packet for query is too large(3750197 >1048576).you can change this value on the server by setting themax_allowed_packet… …
分析：
對於示例代碼，可能是由於一次性查詢的條數過大，也可能是過多用戶同時使用內存造成，所以設置一次性查詢條數參數時，請綜合考慮多方面因素，給出最合理的值。
示例二、傳統POI方式下採用HSSFWorkbook導出，採用傳統JDBC解決查詢數據集一次性全部放到內存中導致內存溢出的問題，但由於POI方式HSSFWorkbook的限制，仍出現內存溢出問題。
示例代碼：採用HSSFWorkbook方式，使用傳統JDBC查詢導出100萬數據
//傳統POI使用模式
Workbook wb = new HSSFWorkBook();
//創建Excel的Sheet
Sheet sh = wb.createSheet();
//使用JDBC返回結果集
ResultSet rs =iExcelTemplateJdbcDao.getExcelTemplateVoByItemsForjJdbc(0,items);
Int rownum = 0;
While(rs.next()){
Row row = sh.createRow(rownum);
Cellcell = row.createCell(0);
cell.setCellValue(rs.getString(「c1」));
...
cell= row.createCell(19);
cell.setCellValue(rs.getString(「c20」));
rownum++;
}
拋出異常：
Java.lang.outofMemoryError:Javaheap space
分析：
為了避免示例一中問題出現，此處採用傳統JDBC方式獲取大數據的方法，當對於大數據導出，仍然報內存溢出問題，主要是由於該方式需要把整個sheet數據全部生成到內存後再同意寫到文件，這樣子會超過了虛擬機的最大可用內存，所以報錯。
小結：
大數據導出Excel時發生內存溢出問題主要原因有：
1、從資料庫獲取到的數據放到List時，因數據量過大，佔用內存過多發生內存溢出。
解決方法:應採用從resultSet中讀一條數據寫一條到Excel的方式，不要把查詢出來的數據集先放到List，再逐條寫到Excel中。
2、將獲取到的數據轉換成Excel時，因轉換工具需要把Excel全部轉換好後再一次性寫文件，佔用內存過大發生內存溢出。
解決方法：使用POI3.8以上版本的SXSSFWorkbook方式進行到處。
3、每個導出線程佔用內存較大，因並行導數據的線程較多而發生內存溢出。
解決方法:加大伺服器JVM內存、使用伺服器集群或控制導出線程數分擔壓力。
解決方案：
方案一
使用POI3.8以上版本，使用SXSSFWorkbook方式導出Excel2007.導出文件單頁限制為1,048,576行和16,384列，默認可創建255個工作表，實際創建數受內存大小影響，可解決百萬級以上數據量的導出。
POI3.8及以上版本的SXSSF包是XSSF的一個擴展本，支持流處理，在生成大數據量的電子表格且堆空間有限時使用。SXSSF通過限制內存中可訪問的記錄行數的方法來實現其低內存的利用，當記錄行數達到限定值時，新一行數據的加入會引起當前最老一行的數據被刷新到硬碟。比如內存中限制行數為100，當行號到達101行時，行號為0的記錄刷新到硬碟並從內存中刪除，以此類推。因此生成Excel時不會出現內存溢出問題。
該方案執行流程大致如下：
示例代碼：
IntcacheItems = 100;
Workbookwb = new SXSSFWorkbook(100);
Sheetsh = sb.createSheet();
//使用JDBC返回結果集
ResultSet rs =iExcelTemplateJdbcDao.getExcelTemplateVoByItemsForjJdbc(0,items);
Int rownum=0;
While(rs.next()){
Row row = sh.createRow(rownum);
Cell cell = row.createCell(0);
cell.setCellValue(rs.getString(「c1」));
...
cell = row.createCell(19);
cell.setCellValue(rs.getString (「c20」));
rownum++;
//當行數達到設置的值就刷新數據到硬碟，以清理內存
If(rownum%cacheItems==0){
((SXSSFSheet)sh).flushRows();
}
}
JdbcUtil.closeJdbc(rs);
FileOutputStreamout = new FileOutputStream(「/excelbigdata.xmlsx」);
wb.write(out);
out.close();
方案二
使用緩衝流寫入數據，到處csv文件。此方式對於POI和JXL來說，可以採用分割數據包，每個數據包單獨使用一個線程分別寫入數據的方法來提升導出效率，在這僅提議，不做具體實現。
該方案使用緩衝流的方式來寫入文件，生成csv格式的文本文件，便於開發人員對數據進行二次解析，以減少不必要的開發時間。
該方案大致流程如下：
創建目標文件xxx.csv
打開流
寫入表頭信息
使用JDBC得到結果集
循環數據，寫入文件
關閉JDBC連接
關閉流
示例代碼
FilecsvFile = null;
BufferedWritercsvWriter = null;
csvFile= new File(「/excelbigdata.csv」);
Fileparent = csvFile.getParentFile();
if(parent!= null && !parent.exists()){
Parent.mkdir();
}
cvsFile.createNewFile();
//UTF-8使用正確讀取分隔符」,」
csvWriter= new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile),」UTF-8」),1024);
//寫入文件頭部(省略部分)
Object[]headArr={「C1」,」C2」,」C3」……」C20」};
List<Object>head = Arrays.asList(headArr);
StringBuffersb = new StringBuffer();
for(inti=0;i<head.size();i++){
Objectdata = head.get(i);
if(I== head.size()-1){
sb.append(「\」」).append(data).append(「\」」).toString();
}else{
sb.append(「\」」).append(data).append(「\」;」).toString();
}
csvWriter.write(sb.toString());
}
csvWriter.newLine();
ResultSet rs =iExcelTemplateJdbcDao.getExcelTemplateVoByItemsForjJdbc(0,items);
while(rs.next()){
ExcelTemplateVovo = new ExcelTemplateVo();
vo.setc1(rs.getString(「c1」));
vo.setc2(rs.getString(「c2」));
。。。。。。
vo.setc20(rs.getString(「20」));
writeRowData(vo,csvWriter);
}
//寫一行數據方法
Privatestatic void writeRowData(ExcelTemplateVo vo,BufferedWriter csvWriter) throwsException{
if(vo!=null){
stringBuffersb = new StringBuffer();
sb.append(「\」」).append(vo.getc1()).append(「\」;」);
。。。。。
sb.append(「\」」).append(vo.getc20()).append(「\」」);
csvWriter.write(sb.toString());
}
}
方案三（不推薦）
使用JXL方式導出Excel，單頁受行數和列數的限制，默認可創建255個工作表，實際創建受內存大小影響，由於使用JXL方式使用內存開銷過大，實際可到處數據量遠遠小於Excel2003可容納量。
該方案採用JXL方式到處EXCEL，生成文件後綴為xls，該方式對於中文支持非常好，操作簡單。
大致流程如下：
示例代碼：
WorkbookSettingswbSetting = new WorkbookSettings();
wbSetting.setUserTemporaryFileDuringWrite(true);
wbSetting.setTemporaryFileDuringWriteDirectory(newFile(「/」));//臨時文件夾的位置
WritableWorkbookbook = jxl.Workbook.createWorkbook(new File(「/excelbigdata.xls」),wbSetting);
//設置樣式、字體
WritableFontfont1 = new WritableFont(WritableFont.createFont(「微軟雅黑」),10,WritableFont.BOLD);
WritableFontfont2 = new WritableFont(WritableFont.createFont(「微軟雅黑」),9,WritableFont.NO_BOLD);
WritableCellFormatwcf1 = new WritableCellFormat(font1);
WritableCellFormatwcf2 = new WritableCellFormat(font2);
//平行居中
wcf1.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf2.setAlignment(Alignment.CENTRE);
wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);
ResultSet rs =iExcelTemplateJdbcDao.getExcelTemplateVoByItemsForjJdbc(0,items);
int rownum = 0;
int sheetCount =50000;
int sheetNum =0;
WritableSheetsheet = null;
while(rs.next()){
if(rownum % sheetCount == 0){
sheetNum ++;
rownum = 0 ;
wheet = book.createSheet(「第」+sheetNum+」頁」,sheetNum-1);
}
//頁設置對象
sheet.addCell(newLabel(0,rownum,rs.getString(「c1」),wcf2));
sheet.addCell(newLabel(1,rownum,rs.getString(「c2」),wcf2));
。。。。。。
sheet.addCell(newLabel(19,rownum,rs.getString(「c20」),wcf2));
//設置行高
sheet.setRowView(rownum,370);
Rownum ++;
}
jdbcUtil.closeJdbc(rs);
//關閉數據並關閉文件
book.write();
book.close();
註：因為代碼為在Word文檔中純手打，所以會存在部分代碼首字母自動變大些等情況，還有些部分無關緊要的代碼被我省略