使用大量行时RowSexceededException

Question

我尝试使用Java JXL（超过20 GB的文件）创建一个非常大的Excel文件，问题是，当我尝试使循环更大一点以生成更多行时，我有一个错误消息

"jxl.write.biff.RowsExceededException: The maximum number of rows permitted on a worksheet been exceeded
  at jxl.write.biff.WritableSheetImpl.getRowRecord(WritableSheetImpl.java:975)
  at jxl.write.biff.WritableSheetImpl.addCell(WritableSheetImpl.java:951)
  at test2.Test2.main(Test2.java:66)

这是我的代码

try { 
        WritableWorkbook workbook = Workbook.createWorkbook(new File("sortie.xls")); 
        WritableSheet sheet = workbook.createSheet("Premier classeur", 0); 
        //Crée le format d’une cellule 
        WritableFont arial10font = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, true, UnderlineStyle.NO_UNDERLINE,Colour.BLACK, ScriptStyle.NORMAL_SCRIPT); 
        WritableCellFormat arial10format = new WritableCellFormat(arial10font); 
            //Crée un label à la ligne 0, colonne 0 avec le format spécifique 
            for (int i = 0; i < 100000; i++) {

                    String chars = "abcdefghijklmnopqrstuvwxyz" ;
                    String chars2 ="azertyuiopqsdfghjklmwxcvbn" ;
                    String chars3 ="aqwzsxedcrfvtgbyhnujikolpm" ;
                    String pass = "";
                    String pass2 = "";
                    String pass3 = "";
                    int alava = 6 ;
                    for (int x = 0; x < alava; x++) {
                        int p = (int)Math.floor(Math.random()*26);
                        pass +=chars.charAt(p);
                        pass2 +=chars2.charAt(p);
                        pass3 +=chars3.charAt(p);
                    }
                    Label label = new Label(0, i, pass); 
                    Label label2 = new Label(1, i, pass2); 
                    Label label3 = new Label(2, i, pass3); 
                    //Crée un label à la ligne 2, colonne 0 sans style prédéfini 
                    //Label label2 = new Label(0, 2, "Résultat"); 
                    //Ajout des cellules 
                    sheet.addCell(label); 
                    sheet.addCell(label2);
                    sheet.addCell(label3);

        }
            //Ajout d’une cellule ligne 2, colonne 1 
            //Number number = new Number(1, 2, 3.1459); 
            //sheet.addCell(number); 
            //Ajout d’une image ligne 4, colonne 0 
            //Taille de l’image : 6 lignes et 2 colonnes 
            //WritableImage image = new WritableImage(0, 4, 2, 6,new File("Logo-Labo-Sun.png")); 
            //sheet.addImage(image); 
            //Ecriture et fermeture du classeur 
            workbook.write(); 
            workbook.close(); 
            } catch (RowsExceededException e1) { 
            e1.printStackTrace(); 
            } catch (WriteException e1) { 
            e1.printStackTrace(); 
            } catch (IOException e) { 
            e.printStackTrace(); 
        }finally{ 
        System.out.println("Le fichier "sortie.xls" à été généré correctement."); 
    } 
    // TODO code application logic here
}

Answer 1

鉴于您似乎对任何高级Excel功能都没有用，并且只想生成可以在选择的Spresheet编辑器中打开的表格数据，我认为您应该考虑生成CSV文件。

以下代码在您自己的情况下生成相似的数据，而无需任何其他库，我就可以通过双击Window的文件Explorer双击它在Excel中打开结果文件。

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
public class TestCSV {
    private static final String chars = "abcdefghijklmnopqrstuvwxyz";
    private static final String chars2 ="azertyuiopqsdfghjklmwxcvbn";
    private static final String chars3 ="aqwzsxedcrfvtgbyhnujikolpm";
    private static final int alava = 6;
    public static void main(String[] args) throws IOException {
        try (FileWriter out = new FileWriter(new File("sortie.csv"))) {
            for (int i = 0; i < 100000; i++) {
                String pass="", pass2="", pass3="";
                for (int x=0; x < alava; x++) {
                    int p = (int)Math.floor(Math.random()*26);
                    pass +=chars.charAt(p);
                    pass2 +=chars2.charAt(p);
                    pass3 +=chars3.charAt(p);
                }
                out.write(String.format(""%s";"%s";"%s"%n", pass, pass2, pass3));
            }
        }
    }
}

Answer 2

代码的问题超过XLS文件上的写入限制。限制为65536行。以下代码片段说明了如何修复

WritableWorkbook workbook = Workbook.createWorkbook(new File("file_test.xls")); 
WritableSheet sheet = workbook.createSheet("Test sheet", 0); 
for (int i = 0; i < 65536; i++) {
    ...
}

ps：要生成一个20 GB文件，当达到65536行的限制时，有必要创建多个XLS文件。