我做過的一個項目，需要實現(xiàn)在線實時生成 Excel文件供客戶端下載的需求,最初考慮的是先在服務(wù)器端生成真實的文件，然后在客戶端下載該文件。后來發(fā)現(xiàn)這樣做不但性能不夠好、速度較慢，而且還要占用服務(wù)器空間。所以采取了在服務(wù)器端生成文件輸出流（ServletOutputStream），通過HttpServletResponse對象設(shè)置相應的響應頭，然后將此輸出流傳往客戶端的方法實現(xiàn)。在實現(xiàn)過程中，用到了Apache組織的Jakarta開源組件POI，讀者朋友可到其官方網(wǎng)站查閱相關(guān)資料及下載。現(xiàn)將整個實現(xiàn)過程介紹如下:
?????????一、首先，根據(jù)Excel表的特點，我編寫了一個Excel表模型類ExcelModel，代碼如下：
package?com.qnjian.myprj.excel;
import?java.util.ArrayList;
/**?*//**
?*
?*?<p>Title:?ExcelModel</p>
?*
?*?<p>Description:?Excel表的操作模型</p>
?*
?*?<p>Copyright:?Copyright?(c)?2005-10-20</p>
?*
?*?<p>Company:?***?</p>
?*
?*?@author?zzj
?*?@version?1.0
?*/
public?class?ExcelModel?
{
??????/**?*//**
???????*?文件路徑，這里是包含文件名的路徑
???????*/
??????protected?String?path;
??????/**?*//**
???????*?工作表名
???????*/
??????protected?String?sheetName;
??????/**?*//**
???????*?表內(nèi)數(shù)據(jù),保存在二維的ArrayList對象中
???????*/
??????protected?ArrayList?data;
??????/**?*//**
???????*?數(shù)據(jù)表的標題內(nèi)容
???????*/
??????protected?ArrayList?header;
??????/**?*//**
???????*?用于設(shè)置列寬的整型數(shù)組
???????*?這個方法在程序中暫未用到
???????*?適用于固定列數(shù)的表格
???????*/
??????protected?int[]?width;
??????public?ExcelModel()?{
????????????path="report.xls";
??????}
??????public?ExcelModel(String?path)?{
???????????this.path=path;
?????}
??????public?void?setPath(String?path){
???????????this.path=path;
??????}
??????public?String?getPath(){
????????????return?this.path;
??????}
??????
??????public?void?setSheetName(String?sheetName){
??????????this.sheetName=sheetName;
??????}
??????
??????public?String?getSheetName(){
??????????return?this.sheetName;
??????}
??????public?void?setData(ArrayList?data){
????????????this.data=data;
??????}
??????public?ArrayList?getData(){
????????????return?this.data;
??????}
??????public?void?setHeader(ArrayList?header){
????????????this.header=header;
??????}
??????public?ArrayList?getHeader(){
????????????return?this.header;
??????}
??????public?void?setWidth(int[]?width){
????????????this.width=width;
??????}
??????public?int[]?getWidth(){
????????????return?this.width;
??????}
}
??????
二、編寫一個下載接口ExcelDownLoad，定義基本的方法：
package?com.qnjian.myprj.excel;
import?java.io.IOException;
import?java.util.List;
import?javax.servlet.http.HttpServletResponse;
import?org.apache.struts.action.ActionForm;
public?interface?ExcelDownLoad?{
????
????/**?*//**
?????*?初始化要生成的Excel的表模型
?????*?@param?list?List 填充了?Excel表格數(shù)據(jù)的集合
?????*?@param?form?ActionForm及其子類
?????*?@param?excel?ExcelModel?Excel表的對象模型
?????*?@see?ExcelModel
?????*?@throws?Exception
?????*/
????public?ExcelModel?createDownLoadExcel?(List?list,?ActionForm?form,?
????????????ExcelModel?excel)throws?Exception;
????
????/**?*//**
?????*?在已文件已存在的情況下，采用讀取文件流的方式實現(xiàn)左鍵點擊下載功能，
?????*?本系統(tǒng)沒有采取這個方法,而是直接將數(shù)據(jù)傳往輸出流,效率更高。
?????*?@param?inPutFileName?讀出的文件名
?????*?@param?outPutFileName 保存的文件名
?????*?@param?HttpServletResponse ????
?????*?@see?HttpServletResponse
?????*?@throws?IOException
?????*/
????public?void?downLoad(String?inPutFileName,?String?outPutFileName,
????????????HttpServletResponse?response)?throws?IOException?;
????
????/**?*//**
?????*?在已文件不存在的情況下，采用生成輸出流的方式實現(xiàn)左鍵點擊下載功能。
?????*?@param?outPutFileName 保存的文件名
?????*?@param?out?ServletOutputStream對象????
?????*?@param?downExcel?填充了數(shù)據(jù)的ExcelModel
?????*?@param?HttpServletResponse ????
?????*?@see?HttpServletResponse
?????*?@throws?Exception
?????*/
????public?void?downLoad(String?outPutFileName,?ExcelModel?downExcel,
????????????HttpServletResponse?response)?throws?Exception?;
}
?????????三、編寫一個實現(xiàn)了以上接口的公共基類BaseExcelDownLoad，并提供downLoad()方法的公共實現(xiàn)，代碼如下：
/**?*//**
?*
?*?<p>Title:?BaseExcelDownLoad</p>
?*
?*?<p>Description:Excel表下載操作基類，生成excel格式的文件流供下載?</p>
?*
?*?<p>Copyright:?Copyright?(c)?2005-10-27</p>
?*
?*?<p>Company:?***?</p>
?*
?*?@author?zzj
?*?@version?1.0
?*/
package?com.qnjian.myprj.excel;
import?java.io.FileInputStream;
import?java.io.IOException;
import?java.io.InputStream;
import?java.io.OutputStream;
import?java.util.List;
import?javax.servlet.http.HttpServletResponse;
import?org.apache.struts.action.ActionForm;
public?abstract?class?BaseExcelDownLoad?implements?ExcelDownLoad{
????
????
?????/**?*//**
?????*?初始化要生成的Excel的表模型
?????*?@param?list?List 填充了?Excel表格數(shù)據(jù)的集合
?????*?@param?form?ActionForm及其子類
?????*?@param?excel?ExcelModel?Excel表的對象模型
?????*?@see?ExcelModel
?????*?@throws?Exception
?????*/
????public?abstract?ExcelModel?createDownLoadExcel?(List?list,?ActionForm?form,?
????????????ExcelModel?excel)throws?Exception;
????
????/**?*//**
?????*?在已文件已存在的情況下，采用讀取文件流的方式實現(xiàn)左鍵點擊下載功能，
?????*?本系統(tǒng)沒有采取這個方法,而是直接將數(shù)據(jù)傳往輸出流,效率更高。
?????*?@param?inPutFileName?讀出的文件名
?????*?@param?outPutFileName 保存的文件名
?????*?@param?HttpServletResponse ????
?????*?@see?HttpServletResponse
?????*?@throws?IOException
?????*/
????public?void?downLoad(String?inPutFileName,?String?outPutFileName,
????????????HttpServletResponse?response)?throws?IOException?{????????
????????
???????//打開指定文件的流信息
????????InputStream?is?=?new?FileInputStream(inPutFileName);
????????//寫出流信息
?????????int?data?=?-1;
?????????OutputStream?outputstream?=?response.getOutputStream();
?????????
????????//清空輸出流
???????????response.reset();?????????????
?????????//設(shè)置響應頭和下載保存的文件名??????????????
?????????response.setHeader("content-disposition","attachment;filename="+outPutFileName);
?????????//定義輸出類型
?????????response.setContentType("APPLICATION/msexcel");
?????????
????????while?(?(data?=?is.read())?!=?-1)outputstream.write(data);
????????is.close();
????????outputstream.close();
????????response.flushBuffer();????
????}
????
????/**?*//**
?????*?在文件不存在的情況下，采用生成輸出流的方式實現(xiàn)左鍵點擊下載功能。
?????*?@param?outPutFileName 保存的文件名
?????*?@param?out?ServletOutputStream對象????
?????*?@param?downExcel?填充了數(shù)據(jù)的ExcelModel
?????*?@param?HttpServletResponse ????
?????*?@see?HttpServletResponse
?????*?@throws?Exception
?????*/
????public?void?downLoad(String?outPutFileName,?ExcelModel?downExcel,
????????????HttpServletResponse?response)?throws?Exception?{
???????
????????//取得輸出流
????????OutputStream?out?=?response.getOutputStream();
????????//清空輸出流
????????response.reset();
????????
????????//設(shè)置響應頭和下載保存的文件名??????????????
????????response.setHeader("content-disposition","attachment;filename="+outPutFileName);
????????//定義輸出類型
????????response.setContentType("APPLICATION/msexcel");???????
??
????????
????????ExcelOperator?op?=?new?ExcelOperator();
????????//out:傳入的輸出流
????????op.WriteExcel(?downExcel,out);
????????out.close();
????????
????????//這一行非常關(guān)鍵，否則在實際中有可能出現(xiàn)莫名其妙的問題！??！
???????response.flushBuffer();//強行將響應緩存中的內(nèi)容發(fā)送到目的地??????????????????????????????
????
???
?????}
}
請注意，BaseExcelDownLoad只是提供了一個生成下載文件流的公共方法，而Excel表格需要顯示的數(shù)據(jù)內(nèi)容則因具體業(yè)務(wù)與需求的不同而不同，因此，必須根據(jù)具體情況提供一個該類的繼承類，實現(xiàn)createDownLoadExcel()方法，以生成所需要輸出內(nèi)容的Excel表格文件流。要說明的是，該方法的參數(shù)list ,實際上是一個ArrayList集合，我們將從數(shù)據(jù)庫查詢出來的記錄集保存在其中，我想這是很容易做到的，實現(xiàn)的方式也可以各種各樣。我項目中是通過Hibernate的Query對象取得查詢結(jié)果集，它正好也是一個ArrayList。不同的客戶，甚至不同功能模塊內(nèi)需要生成的Excel報表的內(nèi)容都是不一樣的。下面還是給出我的一個實現(xiàn)類作為例子吧。
四、實現(xiàn)按照具體的需求生成Excel表格文件流的類舉例：繼承自BaseExcelDownLoad類的AgentInfoExcelDownLoad：
package?com.qnjian.myprj.excel;
import?java.math.BigDecimal;
import?java.util.ArrayList;
import?java.util.Iterator;
import?java.util.List;
import?org.apache.struts.action.ActionForm;
import?com.netease.bid.cs.model.BidAgent;
import?com.netease.bid.cs.model.BidUser;
import?com.netease.bid.cs.util.DateUtil;
import?com.netease.bidding.cs.servlet.InitCsServlet;
import?com.netease.bidding.cs.vo.InstructUser;
public?class?AgentInfoExcelDownLoad?extends?BaseExcelDownLoad?{
????public?ExcelModel?createDownLoadExcel(List?list,?ActionForm?form,
????????????ExcelModel?excel)?throws?Exception?{
????????
????????String?titleStr?=?"客戶帳號;公司名稱;所屬地區(qū);帳戶余額;注冊日期;聯(lián)系方式;聯(lián)系人;";
????
????????ArrayList?data?=?new?ArrayList();????????
????????
????????Iterator?ir?=?list.iterator();
????????while(ir.hasNext()){
????????????
????????????ArrayList?rowData?=?new?ArrayList();
????????????
????????????BidAgent?user?=?(BidAgent)ir.next();
????????
????????????rowData.add(user.getName());
????????????rowData.add(user.getCorpName());????????????
????????
????????????//取得所在省名稱
????????????String?provStr?=?(user.getProvince()==null?)???
????????????????????""?:?InitCsServlet.getProvinceStr(
????????????????????user.getProvince());
????????????
????????????//取得所在地區(qū)名稱
?????????????String?cityStr?=?(user.getCity()==null)??
?????????????????????""?:?InitCsServlet.getCityStr(
???????????????????user.getCity());
????????????if(provStr.equals(cityStr)){
????????????????cityStr?=?"";
????????????}
????????????rowData.add(provStr+"?"+cityStr);
????????????
????????????????????????BigDecimal?balance?=user.getReturnBalance().add(user.getBalance());
????????????rowData.add(balance);????????????
????????????
????????????String?date?=?DateUtil.getFormatDate(user.getCreateTime(),"yyyy-MM-dd");
????????????rowData.add(date);????????
????
????????????rowData.add(user.getPhone());
????????????rowData.add(user.getLinkMan());
????????????data.add(rowData);????????
????????????
????????}
????????
????
????????String[]?titles?=?titleStr.split(";");
????????
????????/**//*for(int?i=0;i<titles.length;i++){
????????????System.out.print(titles[i]+"?");
????????}*/
????????
????????ArrayList?header?=?new?ArrayList();
????????for?(int?i=0;i<titles.length;i++){
????????????header.add(titles[i]);
????????}
????????
????????//設(shè)置報表標題
????????excel.setHeader(header);
????????//設(shè)置報表內(nèi)容
????????excel.setData(data);
????????return?excel;
????}
}
????????五、編寫一個操作類，進行生成下載文件流的操作：
/**?*//**
?*
?*?<p>Title:?Excel表操作</p>
?*
?*?<p>Description:用于生成Excel格式文件?</p>
?*
?*?<p>Copyright:?Copyright?(c)?2005-10-20</p>
?*
?*?<p>Company:?***?</p>
?*
?*?@author?zzj
?*?@version?1.0
?*/
package?com.qnjian.myprj.excel;
import?org.apache.poi.hssf.usermodel.*;
import?java.io.FileOutputStream;
import?java.io.BufferedOutputStream;
import?java.util.ArrayList;
import?java.math.BigDecimal;
import?java.io.OutputStream;
/**?*//**
?*實現(xiàn)生成Excel文件的操作
?*/
public?class?ExcelOperator{
????
??????/**?*//**
???????*?將數(shù)據(jù)信息寫入到Excel表文件，采取自建輸出流的方式。
???????*?@param?excel?ExcelModel?Excel表的模型對象????
???????*?@throws?Exception
???????*/
?????public??void?WriteExcel(ExcelModel?excel)throws?Exception{?????????
??????try{??
????????
???????String?file?=?excel.getPath();
???????
???????//新建一輸出文件流
???????FileOutputStream?fOut?=?new?FileOutputStream(file);
???????BufferedOutputStream?bf?=new?BufferedOutputStream(fOut);
????????
????????HSSFWorkbook?workbook?=this.getInitWorkbook(excel);
????????
????????//?把相應的Excel?工作簿存盤
????????workbook.write(fOut);
????????fOut.flush();
????????bf.flush();
????????//?操作結(jié)束，關(guān)閉文件?
????????bf.close();
????????fOut.close();
???????//?System.out.println("Done!");
??????}catch(Exception?e){
????????//System.out.print("Failed!");
??????????throw?new?Exception(e.getMessage());
?????????}
??????
?????}
?????
?????
?????/**?*//**
??????*?將數(shù)據(jù)信息寫入到Excel表文件?，采取傳入輸出流的方式。
??????*?@param?excel?Excel表的模型對象?
??????*?@param?out??OutputStream?輸出流
??????*?@throws?Exception
??????*/
?????public??void?WriteExcel(ExcelModel?excel,OutputStream?out)throws?Exception{
?????????try{
?????????????HSSFWorkbook?workbook?=this.getInitWorkbook(excel);
?????????????workbook.write(out);??????????
????????????????out.close();
????????????//?System.out.println("Done!");
?????????}catch(Exception?e){
?????????????//System.out.println("Failed!");
?????????????throw?new?Exception(e.getMessage());
?????????}
?????????
?????????
?????}
????
????/**?*//**
?????*?取得填充了數(shù)據(jù)的工作簿
?????*?@param?excel?ExcelModel?Excel表的模型對象
?????*?@return?HSSFWorkbook?工作簿對象
?????*/
????private??HSSFWorkbook?getInitWorkbook(ExcelModel?excel){
????????????
????????????//?創(chuàng)建新的Excel?工作簿
????????????HSSFWorkbook?workbook?=?new?HSSFWorkbook();
?????????????//在Excel工作簿中建一工作表
?????????????HSSFSheet?sheet?=?null;
?????????????String?sheetName?=?excel.getSheetName();
?????????????
?????????????if(sheetName!=null)sheet=workbook.createSheet(sheetName);
?????????????else?sheet=workbook.createSheet();
?????????????//設(shè)置表頭字體
?????????????HSSFFont?font_h?=?workbook.createFont();
?????????????font_h.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
?????????????//設(shè)置格式
?????????????HSSFCellStyle?cellStyle=?workbook.createCellStyle();
?????????????cellStyle.setFont(font_h);????????????
??????????????//在索引0的位置創(chuàng)建行（最頂端的行）
???????????????HSSFRow?row?=?sheet.createRow((short)0);
???????????????ArrayList?header?=?excel.getHeader();
???????????????if(header!=null){
???????????????????for(int?i=0;i<header.size();i++){
??????????????????//在索引0的位置創(chuàng)建單元格（左上端）
??????????????????HSSFCell?cell?=?row.createCell((short)i);
??????????????????//?定義單元格為字符串類型
?????????????????cell.setCellType(HSSFCell.CELL_TYPE_STRING);
?????????????????//設(shè)置解碼方式
?????????????????cell.setEncoding((short)1);
?????????????????//設(shè)置單元格的格式
?????????????????cell.setCellStyle(cellStyle);
?????????????????//?在單元格中寫入表頭信息
?????????????????cell.setCellValue((String)header.get(i));
???????????????????}
???????????????}
???????????????ArrayList?cdata?=?excel.getData();
???????????????for?(int?i=0;i<cdata.size();i++){
????????????????//從第二行開始
????????????????HSSFRow?row1?=?sheet.createRow(i+1);
????????????????ArrayList?rdata?=(ArrayList)cdata.get(i);
???????????????//打印一行數(shù)據(jù)
????????????????for?(int?j=0;j<rdata.size();j++){
????????????????????HSSFCell?cell?=?row1.createCell(?(short)j);
????????????????????cell.setCellType(HSSFCell.CELL_TYPE_STRING);
????????????????????//設(shè)置字符編碼方式
????????????????????cell.setEncoding((short)1);
????????????????????
???????????????????Object?o?=?rdata.get(j);
????????????????????
????????????????????//造型,使寫入到表中的數(shù)值型對象恢復為數(shù)值型，
???????????????????//這樣就可以進行運算了
????????????????????if(o?instanceof?BigDecimal){
????????????????????????BigDecimal?b=(BigDecimal)o;
????????????????????????cell.setCellValue(b.doubleValue());
????????????????????}
????????????????????else?if(o?instanceof?Integer){
???????????????????????????Integer?it?=(Integer)o;
???????????????????????????cell.setCellValue(it.intValue());
???????????????????????????
????????????????????}
????????????????????else?if(o?instanceof?Long){
????????????????????????Long?l?=(Long)o;
????????????????????????cell.setCellValue(l.intValue());
????????????????????????
????????????????????}
????????????????????else?if(o?instanceof?Double){
????????????????????????Double?d?=(Double)o;
????????????????????????cell.setCellValue(d.doubleValue());
????????????????????}
????????????????????else?if(o?instanceof?Float){
????????????????????????Float?f?=?(Float)o;
????????????????????????cell.setCellValue(f.floatValue());
????????????????????}
????????????????????else{
????????????????????????cell.setCellValue(o+"");
????????????????????}?????????????
????????????
????????????????????
????????????????}
???????????????}
???????????return?workbook;
????????????
????????}
??/**?*//**
???*?Just?to?test
???*?@param?args?String[]
???*/
??public?static?void?main(String[]?args){
????????????????ArrayList?data=new?ArrayList();
????????????????ArrayList?header?=?new?ArrayList();
????????????????header.add("學號");
????????????????header.add("姓名");
????????????????header.add("成績");
????????????????for?(int?i=0;i<3;i++){
?????????????????????ArrayList?data1=new?ArrayList();
????????????????????????????data1.add((i+1)+"");
????????????????????????????data1.add("Name"+(i+1));
????????????????????????????data1.add(""+(80+i));
????????????????????????????data.add(data1);
????????????????}
????????????????ExcelModel?model?=?new?ExcelModel();
????????????????model.setPath("E:/test.xls");
????????????????model.setHeader(header);
????????????????model.setData(data);
????????????????ExcelOperator?eo=?new?ExcelOperator();
???????????????try{
??????????????????eo.WriteExcel(model);
???????????????}catch(Exception?e){
?????????????????????System.out.println(e.getMessage());
???????????????}
????????}
}
?????????六、功能流程小結(jié)：
?????????涉及到不同的項目，采取的框架與結(jié)構(gòu)是可能不同的。我的實現(xiàn)方法可以應用到不同的項目中去，只是作為一個借鑒，它可能需要針對不同情況做相應調(diào)整與修改。
?????????我的項目是采取了Spring+Struts＋Hibernate的框架實現(xiàn)的，顯示層仍然使用HTML、JSP文件，通過它傳遞客戶端的請求，轉(zhuǎn)到Action類調(diào)用業(yè)務(wù)邏輯對象實現(xiàn)相應功能。持久層使用了Hibernate，使用Hibernate作為數(shù)據(jù)持久層，在開發(fā)與維護方面都帶來了較大的便利。至于相關(guān)框架的配置與實現(xiàn)，則不在本文論述的范圍，請讀者朋友參閱相關(guān)資料。我的項目中，使用了一個Service類（類似Manager的功能，編寫接口與實現(xiàn)類，在Spring配置文件中加入，利用Spring的依賴注入技術(shù)在運行中取得對應的Bean實例......）來集成業(yè)務(wù)邏輯功能，通過它調(diào)用涉及到的數(shù)據(jù)訪問類（DAO類），具體Dao類又利用Hibernate提供的對象進行數(shù)據(jù)庫的查詢或其他操作。這些東西，我就不再詳述了，相信對這些技術(shù)使用得比我嫻熟者大有人在。功能的最終實現(xiàn)，請看我在某個Action中的幾行代碼：
?ExcelModel?excel?=?new?ExcelModel();
?????????????????????????excel.setSheetName("BidCost");???????????????????????????
?????????????????????????????????
????????????????????????????//寫入到Excel格式輸出流供下載
???????????????????????????try{???????????????????????????
?????????????????????????
????????????????????????????????//調(diào)用自編的下載類，實現(xiàn)Excel文件的下載
???????????????????????????????ExcelDownLoad?downLoad?=?new?BidCostExcelDownLoad();
???????????????????????????????ExcelModel?downExcel=?downLoad.createDownLoadExcel(bidReportList,bcf,excel);
???????????????????????????????//不生成文件，直接生成文件輸出流供下載
???????????????????????????????downLoad.downLoad("BidCost.xls",downExcel,response);
???????????????????????????????
???????????????????????????????log.info("create?Excel?outputStream?successful!");???????????????????????????????
???????????????????????????
???????????????????????????}catch(Exception?e){
???????????????????????????????msg.add(ActionMessages.GLOBAL_MESSAGE,?new?ActionMessage(
???????????????????????????????????????"bidding.cs.fileUpDown.fileDownError"));//文件下載失敗!
??????????????????????????????????saveErrors(request,?msg);????????????????????????????????
??????????????????????????????
?????????????????????????????????log.info("create?Excel?outputStream??failed!");
??????????????????????????????log.info(e.getMessage());
??????????????????????????????//e.printStackTrace();
??????????????????????????????
??????????????????????????????return?mapping.getInputForward();?????????????????????????????
???????????????????????????}???????????????????????
?????????請看客戶端的顯示頁面：
?。暶鳎捍宋臑樵瓌?chuàng)，轉(zhuǎn)載請注明出處及鏈接網(wǎng)址!）Last modified on 2006年4月15日 13:37:17
?
posted on 2005-12-30 10:08
南一郎 閱讀(12285)
評論(16) 編輯 收藏