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.

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...) — Jon Skeet, May 25 '12 at 11:27
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 — Cindrella, May 25 '12 at 11:32
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. — Simon Nickerson, May 25 '12 at 11:34
yes i am creating multiple rows per contact .. i want the output in that way only — Cindrella, May 25 '12 at 11:45

Simon Nickerson · Accepted Answer · 2012-05-25T11:57:17.083

44

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.

edited May 25 '12 at 11:57

answered May 25 '12 at 11:31

Simon Nickerson

42,159
20
102
127

2

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 http://poi.apache.org/spreadsheet/converting.html, which you should read through. Make sure you are using a recent version of the poi JAR. – Simon Nickerson May 25 '12 at 11:54
finally used 8 jars and executed successfully – Cindrella May 28 '12 at 08:13

score 2 · Answer 2 · answered May 25 '12 at 11:34

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?

score 0 · Answer 3 · answered May 25 '12 at 11:27

0

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.

answered May 25 '12 at 11:27

brain

5,496
1
26
29

score 0 · Answer 4 · edited Dec 08 '16 at 11:07

0

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

edited Dec 08 '16 at 11:07

General Failure

2,421
4
23
49

answered Dec 08 '16 at 09:41

Zechreich

1

1

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 – AtliB Jan 17 '18 at 15:27

score 0 · Answer 5 · edited Jun 13 '23 at 08:23

<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>

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

5 Answers5