??????以前公司在做下載數據的時候,主要是用以下這種方式：

??????response.setContentType("APPLICATION/OCTET-STREAM");

??????response.setHeader("Content-Disposition", "attachment; filename=\""+ Chinese.toPage(fileName) + "\"");

??????但這有個不好的就是它只能打開一個EXCEL的一個工作簿，如果數據量非常大的話，后面的數據會丟失，根據這個BUG，我重新做了一個動態生成EXCEL工作薄的例子，以方便后面對此應用的改造。

?首先從數據庫里取數據的時候，不知道行，列，這樣就需要一個動作，把RS轉換成一個VECTOR，此對象里每一行也是一個VECTOR
? public static Vector ResultSetToVector(ResultSet rs) {
??? try {
????? Vector rows = new Vector();
????? ResultSetMetaData rsmd = rs.getMetaData();
????? Vector columnHeads = new Vector();

????? for (int i = 1; i <= rsmd.getColumnCount(); i++) {
??????? columnHeads.addElement(rsmd.getColumnName(i));
????? }

????? Vector currentRow;
????? while(rs.next()){
??????? currentRow = new Vector();
??????? for (int i = 1; i <= rsmd.getColumnCount(); i++) {
????????? currentRow.addElement(Chinese.fromDatabase(rs.getString(i)));
??????? }
??????? rows.addElement(currentRow);
????? }
????? return rows;
??? }
??? catch (Exception err) {
????? Log.printError(err, "", "", log);
????? return null;
??? }
??? finally{
????? try {
??????? if(rs!=null){
????????? rs.close();
????????? rs = null;
??????? }
????? }
????? catch (Exception ex) {
????? }
??? }

?再通過寫一個方法，把VECTOR的值放到EXCEL里去。
?? ResultSetMetaData rmeta = rs.getMetaData();
???int numColumns = rmeta.getColumnCount();//取多少行
???String fileNametemp = "c:\\"+fileName;
???fileNametemp = fileNametemp.substring(0,fileNametemp.lastIndexOf("."))+CTime.getTime(12)+".xls";
???
???java.io.File file = new java.io.File(fileNametemp);
???if (file.exists()) {
????file.delete();
???}
???String rows_temp = request.getParameter("rows");//頁面傳過來的每個SHEET可以存放的條數
???if (rows_temp == null){
????rows_temp = "20000";// 一個表單默認20000行。
???}
???int count_rows = Integer.parseInt(rows_temp);
?? WritableWorkbook wb = Workbook.createWorkbook(file);
?????
????Vector v_Rs = RsToVector.ResultSetToVector(rs);// 把RS的值轉換成VECTOR
???boolean fg = true;????
??? if (v_Rs != null) {
????int a = v_Rs.size();????
????int sheet_count = a / count_rows;????
????if (sheet_count >0){//大于0，則需要多個SHEET
?????for (int i = 0;i<sheet_count;i++){
??????Vector temp_v = new Vector();
??????for (int b = i* count_rows ;b<(i+1) * count_rows ;b++){
???????
???????temp_v.add(v_Rs.get(b));
??????}??????
??????writeExcel(wb,"sheet"+i,temp_v,numColumns,sheet_count);//EXCEL對象、單元薄名、數據、行數、第幾個單元薄??????
?????}????
?????
?????if (sheet_count * count_rows < a){//不是正好，還有剩余
??????Vector temp_vv = new Vector();
??????for (int c = sheet_count* count_rows ;c<a ;c++){???????
???????temp_vv.add(v_Rs.get(c));
??????}
??????writeExcel(wb,"sheet"+(sheet_count+1),temp_vv,numColumns,sheet_count+1);//EXCEL對象、單元薄名、數據、行數、第幾個單元薄?
?????}
????}else{
?????writeExcel(wb,"sheet"+sheet_count,v_Rs,numColumns,0);//EXCEL對象、單元薄名、數據、行數、第幾個單元薄?
????}
????fg = true;
???}else{
????fg = false;
???}
???wb.write();
???wb.close();
???String msgs = "";
???PrintWriter out = response.getWriter();
???if (fg){
????msgs = "保存的文件名是"+fileNametemp;
????msgs = Chinese.toPage(msgs);????
???}else{
????msgs = Chinese.toPage("沒有數據導出！");????
???}
???int seg = msgs.indexOf(":");
???msgs = msgs.substring(0,seg)+":\\"+msgs.substring(seg+1,msgs.length());
???out.println("<script>alert('"+msgs+"');window.close();</script>");


???
寫EXCLE的方法
???/**
???? * 寫Excel文件
???? * @param filepath String
???? * @param sheetname String? 工作簿名稱
???? * @param list Vector?? 內容
???? * @param colum int???? 列數
???? * @return boolean
???? */
?private boolean writeExcel(WritableWorkbook wb ,String sheetname,
???Vector list, int colum,int count) {
??String temp = "";
??String[] s;
??int i = 0;
??try {
???if (list == null) {
????return false;
???}
???
???WritableSheet ws = wb.createSheet(sheetname, count);
???if (colum == 1) {
????while (i != list.size() && list.size() > 0) {
?????temp = (String) list.get(i);
?????Label labelC = new Label(i, 0, temp);
?????ws.addCell(labelC);
?????i++;
????}
???} else {
????while (i != list.size() && list.size() > 0) {
?????//s = (String[]) list.get(i);
?????Vector tm = (Vector) list.get(i);
?????if (tm == null) {
??????continue;
?????} else {
??????int kk = tm.size();
??????for (int j = 0; j < kk; j++) {
???????temp = (String)tm.get(j);
???????Label labelC = new Label(j, i, temp);
???????ws.addCell(labelC);
??????}
?????}
?????i++;
????}
???}
??} catch (Exception ex) {
???Log.printError(ex, "寫excel文件錯誤", "", "writeexcel.log");
???return false;
??}
??return true;
?}



以上方法也有一個問題，就是當程序寫100000條數據以后，速度會慢下來，我看了WritableWorkbook 的構造方法的時候，可以生成一個OUTPUTSTREAM對象的，我想可以用這個來做，速度可能會上去，但具體也沒有試，如何有哪位哥們試過了，把性能跟兄弟分享一下！謝謝了！

轉自 : http://www.tkk7.com/wujiaqian/archive/2006/12/08/86269.html