package sh.pl;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class TestExcel {

    
    public static void main(String[] args) {
        String newFileName = "d:\\test_performance.xls";

        int rows = 60000;
        int cols = 12;
        
        Date time = new Date();
        WritableWorkbook book = null;
        try {
            book = Workbook.createWorkbook(new File(newFileName));
            WritableSheet sheet = book.createSheet("OQC抽检", 0);                        
            exportToExcel(book, sheet, rows, cols);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (book != null) {
                try {
                    book.write();
                    book.close();
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        System.out.println(new Date().getTime() - time.getTime());
    }
    
    public static void exportToExcel(WritableWorkbook book, WritableSheet sheet, int row, int col) throws RowsExceededException, WriteException {
        for (int i = 0; i < row; i++){
            for (int j = 0; j < col; j++) {
                String value = i + "_" + j;
                if (j == 2){
                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
                    value = sdf1.format(new Date());
                }
                sheet.addCell(new Label(j, i, value)); 
            }
        }
        
    }
}

------解决方案--------------------
OK啦。 试试看。
Java code

        WorkbookSettings wbs = new WorkbookSettings();
        wbs.setUseTemporaryFileDuringWrite(true);  //设置使用中间文件，而不是全内存保持输出内容。
        book = Workbook.createWorkbook(new File(newFileName), wbs); // 把WorkbookSettings对象设到Workbook里。

------解决方案--------------------
jxl输出的excel有行数限制的，最大65535。
你要改得话得改jxl的源代码了。
jxl.write.biff.WritableSheetImpl的，
Java code

  /**
   * The maximum number of rows excel allows in a worksheet
   */
  private final static int numRowsPerSheet = 65536;

------解决方案--------------------
探讨


引用:

引用:

引用:

你的表字段个数是？就是输出的excel有多少列？？
我写了个简单测试jxl性能的小程序。 你试试看？
虽然慢，但是没有很花内存吧？
谢谢，我的是70列，把列数改成70时，用你的程序在我电脑上 6000行就溢出了...

恩，改了，如果是 40000行，6列，加上你那句确实变好很多，能加……


------解决方案--------------------

POI代码，60000*70测试通过。 LZ参考下。  
不过输出是Excel2007格式。 没问题吧？

Java code

package sh.pl;

import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class TestExcelPOI {

    public static void main(String[] args) {
        String newFileName = "d:\\test_performance.xlsx";

        int rows = 60000;
        int cols = 70;

        Date time = new Date();         
        Workbook book = null;
        Sheet sheet = null;
        BufferedOutputStream out = null;
        try {
            book = new SXSSFWorkbook(128); //缓存128在内存。
            sheet = book.createSheet("OQC抽检");
                    
            exportToExcel(book, sheet, rows, cols);
            
            out = new BufferedOutputStream(new FileOutputStream(newFileName));
            book.write(out);  
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (out != null) {
                try {
                    out.close();
                } catch (Exception e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        System.out.println(new Date().getTime() - time.getTime());
    }
    
    public static void exportToExcel(Workbook book, Sheet sheet, int row, int col) throws RowsExceededException, WriteException, IOException {
        for (int i = 0; i < row; i++){
            Row sheetRow = sheet.createRow(i);
            for (int j = 0; j < col; j++) {
                Cell cell = sheetRow.createCell(j);
                String value = i + "_" + j;
                if (j == 2){
                    SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
                    value = sdf1.format(new Date());
                }
                cell.setCellValue(value); 
            }
        }
        
    }
}