Invalid row number (-32768) outside allowable range (0..1048575)

Question

I was trying to export a report in to xlsx format using Apache POI. Below is the code used for.

public static void main(String[]args){
        try{
            XSSFWorkbook wb=new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet("new sheet");

            XSSFRow rowhead= sheet.createRow((short)0);
            rowhead.createCell((short) 0).setCellValue("column1");
            rowhead.createCell((short) 1).setCellValue("column2");
            rowhead.createCell((short) 2).setCellValue("column3");
            rowhead.createCell((short) 3).setCellValue("column4");
            rowhead.createCell((short) 4).setCellValue("column5");
                 System.out.println("im here");
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:...,....);
            Statement st=con.createStatement();         
            System.out.println("im here1");
            ResultSet rs=st.executeQuery("SELECT * FROM table3 ");
            System.out.println("im here2");         
            int i=1;
            while(rs.next()){
                XSSFRow row= sheet.createRow((short)i);
                row.createCell((short) 0).setCellValue(rs.getString("column1"));
                row.createCell((short) 1).setCellValue(rs.getString("column2"));
                row.createCell((short) 2).setCellValue(rs.getString("column3"));
                row.createCell((short) 3).setCellValue(rs.getString("column4"));
                row.createCell((short) 4).setCellValue(rs.getString("column5"));
                i++;
            }

            FileOutputStream fileOut = new FileOutputStream(new File("E:/report.xlsx"));
            wb.write(fileOut);
            fileOut.close();
            System.out.println("Your excel file has been generated!");


        } catch ( Exception ex ) {
            System.out.println(ex);

        }

The total row count is only 200000. But while processing, I am getting the Invalid row number error.Here is the output.

im here
im here1
im here2
java.lang.IllegalArgumentException: Invalid row number (-32768) outside allowable range (0..1048575)

Solution 1

The highest value that a short (which you're casting to) can represent is 32,767, after which it wraps around to -32768.

XSSFSheet.createRow takes an int, so you don't need to cast your number.

Solution 2

The Integer type in VBA is only 16 bits long. (I kid you not).

As it's a signed type its range is -32768 to +32767. Incrementing 32767 by 1 causes wrap-around to the smallest number: this is what's happening to you.

Use a Long type instead: which is 32 bits long.

Comments

[Nidheesh]

The error has gone. Now Exception in thread "main" java.lang.OutOfMemoryError: Java heap space -- I have my JAVA_OPTS =-Xms64m -Xmx516m -XX:MaxPermSize=1024m

[awksp]

@tailorBird Increase the amount of memory available then

[Nidheesh]

Thanks all. We can use SXSSF (Streaming Usermodel API) instead.