columns are not reading in sequential order while reading excel sheet using java

Question

I have a excel sheet with data in 200 columns, to import in to DB i'm using below code

private File upp;
InputStream input =  new FileInputStream(upp);
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);

Iterator rows = sheet.rowIterator();

while(rows.hasNext()) { 
    String data="";
    HSSFRow row = (HSSFRow) rows.next();
    if(row.getRowNum() == 0 || row.getRowNum() == 1) {
        Iterator rowCells = row.cellIterator();
        while(rowCells.hasNext()) {
            HSSFCell cell = (HSSFCell) rowCells.next();
            for(int i=0; i <= cell.getCellNum(); i++) {

            }             
            data = data + cell.getCellNum() + " ,";
        }
        System.out.println(data);
    }
    slrow++;
}

i'm excepting output to be

0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ,17 ,18 ,

but the output is

0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ,17 ,16 ,18 ,

its not reading in sequential order as expected I'm unable to find out where the mistake is in this code

Can you post the first 20 columns of the row that is giving you this issue? — Mad Physicist
Also, does the real version of the innermost loop do anything to missing cells, like fill them in? Because that might explain what is happening. — Mad Physicist
HSSFCell.getCellNum()is deprecated since a long time. Use getColumnIndex(). — Axel Richter
Your code looks a little odd, what happens when you simplify it and follow one of the Apache POI examples for iterating over rows and cells? — Gagravarr
@MadPhysicist no the innermost loop is doing nothing, i tried by removing that loop also — Sandeep

Shubham Yedage Shubham Yedage · Accepted Answer · 2016-08-11T14:03:01

Use JXL library

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6</version>
</dependency

Code example

FileInputStream inputStream = new FileInputStream(file);
            Workbook wb = Workbook.getWorkbook(inputStream);

            // TO get the access to the sheet. 
//For single sheet
            Sheet sheet = wb.getSheet(0);

            // To get the number of rows present in sheet
            int totalNoOfRows = sheet.getRows();

            // To get the number of columns present in sheet
            int totalNoOfCols = sheet.getColumns();

            Map<String, String> map = new HashMap<>();
            for (int row = 1; row < totalNoOfRows; row++) {

                for (int col = 0; col < totalNoOfCols; col++) {
                    map.put(sheet.getCell(col, 0).getContents(),
                            sheet.getCell(col, row).getContents());

                }
            }

I hope this will be helpfull.

columns are not reading in sequential order while reading excel sheet using java

1 Answers