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

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

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

??????但這有個(gè)不好的就是它只能打開(kāi)一個(gè)EXCEL的一個(gè)工作簿，如果數(shù)據(jù)量非常大的話(huà)，后面的數(shù)據(jù)會(huì)丟失，根據(jù)這個(gè)BUG，我重新做了一個(gè)動(dòng)態(tài)生成EXCEL工作薄的例子，以方便后面對(duì)此應(yīng)用的改造。

?首先從數(shù)據(jù)庫(kù)里取數(shù)據(jù)的時(shí)候，不知道行，列，這樣就需要一個(gè)動(dòng)作，把RS轉(zhuǎn)換成一個(gè)VECTOR，此對(duì)象里每一行也是一個(gè)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) {
????? }
??? }

?再通過(guò)寫(xiě)一個(gè)方法，把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");//頁(yè)面?zhèn)鬟^(guò)來(lái)的每個(gè)SHEET可以存放的條數(shù)
???if (rows_temp == null){
????rows_temp = "20000";// 一個(gè)表單默認(rèn)20000行。
???}
???int count_rows = Integer.parseInt(rows_temp);
?? WritableWorkbook wb = Workbook.createWorkbook(file);
?????
????Vector v_Rs = RsToVector.ResultSetToVector(rs);// 把RS的值轉(zhuǎn)換成VECTOR
???boolean fg = true;????
??? if (v_Rs != null) {
????int a = v_Rs.size();????
????int sheet_count = a / count_rows;????
????if (sheet_count >0){//大于0，則需要多個(gè)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對(duì)象、單元薄名、數(shù)據(jù)、行數(shù)、第幾個(gè)單元薄??????
?????}????
?????
?????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對(duì)象、單元薄名、數(shù)據(jù)、行數(shù)、第幾個(gè)單元薄?
?????}
????}else{
?????writeExcel(wb,"sheet"+sheet_count,v_Rs,numColumns,0);//EXCEL對(duì)象、單元薄名、數(shù)據(jù)、行數(shù)、第幾個(gè)單元薄?
????}
????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("沒(méi)有數(shù)據(jù)導(dǎo)出！");????
???}
???int seg = msgs.indexOf(":");
???msgs = msgs.substring(0,seg)+":\\"+msgs.substring(seg+1,msgs.length());
???out.println("<script>alert('"+msgs+"');window.close();</script>");


???
寫(xiě)EXCLE的方法
???/**
???? * 寫(xiě)Excel文件
???? * @param filepath String
???? * @param sheetname String? 工作簿名稱(chēng)
???? * @param list Vector?? 內(nèi)容
???? * @param colum int???? 列數(shù)
???? * @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, "寫(xiě)excel文件錯(cuò)誤", "", "writeexcel.log");
???return false;
??}
??return true;
?}



以上方法也有一個(gè)問(wèn)題，就是當(dāng)程序?qū)?00000條數(shù)據(jù)以后，速度會(huì)慢下來(lái)，我看了WritableWorkbook 的構(gòu)造方法的時(shí)候，可以生成一個(gè)OUTPUTSTREAM對(duì)象的，我想可以用這個(gè)來(lái)做，速度可能會(huì)上去，但具體也沒(méi)有試，如何有哪位哥們?cè)囘^(guò)了，把性能跟兄弟分享一下！謝謝了！

轉(zhuǎn)自 : http://www.aygfsteel.com/wujiaqian/archive/2006/12/08/86269.html