Invalid row number (65536) outside allowable range (0..65535)

Question

I am reading integers from a text file, giving them as input to a query and getting the query output and writing to an xls file.

ResultSet rs;
Connection con = null;
PreparedStatement ps = null;
int person_org_id, external_person_org_id;
File f = null;
Scanner scan = null;

try {
    System.out.println("----------checkpoint-----------");
    Class.forName("oracle.jdbc.driver.OracleDriver");
    System.out.println("----------checkpoint 1-----------");
    con = DriverManager.getConnection("jdbc:oracle:thin:@ksdjf.kjdlk.jkd.com:2222:edb", "aaaaa", "aaaa");
    System.out.println("----------checkpoint 2 ----------");
    if (con == null) {
        System.out.println("unable to connect to database");
    }
    System.out.println("----------checkpoint 3::connected to database---------");
    StringBuffer sql = new StringBuffer();
    sql.append("select abd from edb.abd where customer_id=510 and person_org_id =? ");
    ps = con.prepareStatement(sql.toString());

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Excel Sheet");
    HSSFRow rowhead = sheet.createRow(0);
    rowhead.createCell(0).setCellValue("ABC");
    rowhead.createCell(1).setCellValue("DEF");

    f = new File("/tmp/contacts.txt");
    scan = new Scanner(f);
    int index=1;

    while (scan.hasNextInt()) {

        person_org_id = scan.nextInt();

        ps.setInt(1,person_org_id);
        rs= ps.executeQuery();

        while (rs.next()) {                 

            external_person_org_id = rs.getInt(1);

            HSSFRow row = sheet.createRow(index);
            row.createCell(0).setCellValue(person_org_id);
            row.createCell(1).setCellValue(external_person_org_id);
            index++;
        }           

    }   
    FileOutputStream fileOut = new FileOutputStream(new File("/tmp/External_contact_id.xls"));
    wb.write(fileOut);
    fileOut.close();
    System.out.println("--------checkpoint 4:: writing data to xls completed------------");
}
catch (Exception e) {
    System.out.println(e.getMessage());
}

I am getting error Invalid row number (65536) outside allowable range (0..65535)

My contacts.txt file has around 36000 numbers.

Toowoomba

You've given us a lot of code - please show which exact line throws the exception. (Print the exception stack trace to find it out - not just the message...) – Propitiate

i didnt debug the code.problem is when i am giving less numbers in contacts.txt file i am not getting this error. when i am running with 36000 numbers in contacts.txt file its giving the invalid row number error – Toowoomba

I would try running with a subset of your file and examining the output. It's possible that you're creating multiple rows per contact. – Millais

yes i am creating multiple rows per contact .. i want the output in that way only – Toowoomba

Answer 1

HSSF targets a version of Excel (Excel 2003) which only supports a maximum of 65536 rows.

You could try using the newer XSSF API instead, which supports later versions of Excel which have a more generous row limit.

There is a conversion guide which will help you convert between the two APIs.

Millais

It may be as simple as replacing new HSSFWorkbook() with new XSSFWorkbook(), and removing references to HSSF from method calls. The APIs are supposed to be pretty similar, according to poi.apache.org/spreadsheet/converting.html, which you should read through. Make sure you are using a recent version of the poi JAR. – Millais

finally used 8 jars and executed successfully – Toowoomba

Answer 2

If you only have 36000 items in the text file, something else must be wrong.

Create a small sample, of let's say, 100 entries, and test with that.

Take a careful look at the resulting Excel file, if you can. It looks as if the following piece of code is your problem:

while(rs.next()){                   

        external_person_org_id = rs.getInt(1);

        HSSFRow row = sheet.createRow(index);
            row.createCell(0).setCellValue(person_org_id);
            row.createCell(1).setCellValue(external_person_org_id);
            index++;
        }       

I'm just guessing, but won't the fact that the index++ is in the WHILE cause it to create a new row each time for EVERY entry in a record set?

Krona

Answer 3

Excel (perhaps only older versions) only allow 65535 rows.

Here's a linkto the Excel 2003 limits, which is indeed 65535 rows. It got increased for 2010 to 1,048,576.

Moonfish

Answer 4

int person_org_id, external_person_org_id;

you need to change int variable become Integer, there are limit from primitive and cannot more than -32767 to 32767

Unlawful

Data type ´short´ has that range, not int: By default, the int data type is a 32-bit signed two's complement integer, which has a minimum value of -2^31 and a maximum value of 2^31-1. In Java SE 8 and later, you can use the int data type to represent an unsigned 32-bit integer, which has a minimum value of 0 and a maximum value of 2^32-1. Use the Integer class to use int data type as an unsigned integer – Burlesque

Answer 5

<h:commandButton styleClass="dataExporter"
  value="#{o:translate('Export As Excel')}" style="margin-left:5px">
    <p:dataExporter type="**xls**" target="deviceAuditTable" fileName="DeviceAudit"/>
</h:commandButton>

When you new HSSFWorkbook() with new XSSFWorkbook(), and removing references to HSSF from method calls and also make sure file extension also, for new XSSFWorkBook() here the file type xlsx

<h:commandButton styleClass="dataExporter"
                            value="#{o:translate('Export As Excel')}" style="margin-left:5px">
    <p:dataExporter type="xlsx" target="deviceAuditTable"
                                fileName="DeviceAudit" />
</h:commandButton>

Afroasian

Answer 6

The reason is XLS limit, you need to change to XLSX it gives you not 65 535 rows but 1 048 576.

Required steps

1. You need to change HSSFWorkbook() to XSSFWorkbook(). H -> X

2. Change file extension from xls to xlsx

3. Upgrade library version to newest one https://mvnrepository.com/artifact/org.apache.poi/poi

    <!-- xlsx lib -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.5</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.5</version>
    </dependency>
   

Code example :

    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("Report");
    Row header = sheet.createRow(0);
    int cellNum = 0;

    List<String> headers = Arrays.asList("Lp.", "System", "Col2");
    for (String headerText : headers) {
        header.createCell(cellNum++).setCellValue(headerText);
    }

Full examples : https://howtodoinjava.com/java/library/readingwriting-excel-files-in-java-poi-tutorial/

Ruinous