CODEWITHSUNDEEP
javafile-ioxls
Cindrella
Cindrella

Reputation: 1721

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

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.

Upvotes: 30

Views: 64851

Answers (6)

Tomasz
Tomasz

Reputation: 1396

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/

Upvotes: 0

ravi kumar p
ravi kumar p

Reputation: 11

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

Upvotes: 0

Zechreich
Zechreich

Reputation: 1

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

Upvotes: 0

Simon Nickerson
Simon Nickerson

Reputation: 43159

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.

Upvotes: 46

Ewald
Ewald

Reputation: 5751

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?

Upvotes: 2

brain
brain

Reputation: 5546

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.

Upvotes: 1

Related Questions