asked on

Empty cell in Excel not recognized by java

Hi experts,

I have (tried) write a java program to convert an excel file .xlsx to CSV, this is working fine, with only one issue; blank cells are not recognized.

If cell a2=empty; I expect:
A1;;A3, but get A1;A3, so for some lines the result is shifting to the left :-(
apparently, the function "cell = cells.next();"is not working as expexted;

while (rows.hasNext()) {
row = (XSSFRow) rows.next();

// Iterating all the cells of the current row
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
cell = cells.next();
....... some programming
csvData += CVS_SEPERATOR_CHAR;
}
csvData += NEW_LINE_CHARACTER;
}

Please can I get some help ?

THANKS

Ryan Chong

Are you using Apache POI for this?

you could read my article regarding how to read Excel in Java.

How to load Excel's data into SQL Server in different ways

https://www.experts-exchange.com/articles/34011/How-to-load-Excel's-data-into-SQL-Server-in-different-ways.html

Hope this give you some idea how to handle empty cells.

Theo Kouwenhoven

ASKER

Hello Ryan Chong,

I don't use Apache Poi nor SQL server. I think I have to explain the whole flow.

a user puts an Excel file on a map on a server (like a kind of dropbox)
On our AS/400 (IBM-i) there is a mount to this server/path.
a local AS/400 process is inspecting the mounted path for new .xls or .xlsx files
when a file is found it has to be translated into a CSV (tab-separated)
this CSV is transferred (with some internal logic) in one or more tables.

This is a batch process.

It running fine if all cells are filled, but empty cells are skipped and the separation character also
The result is that fields shift to the left so that the content in some of the fields are NOT aligned to the header.

Regards

CEHJ

soffice  --headless --convert-to csv inputfile.xlsx

Open in new window

might be a way

Might be an idea to attach an input file that reproduces the problem

Ryan Chong

It running fine if all cells are filled, but empty cells are skipped

I guess because row.cellIterator() will only read the non-empty cells, which causing the issue. So, this method is not suitable in this case.

CEHJ

I guess because row.cellIterator() will only read the non-empty cells, which causing the issue.

It seems so. SO you need to access them by index.

Also, your code could easily turn valid xls/xlsx into invalid csv, which will break on opening or parsing. You might have got away with it so far, but if you want to do it in Java so it's robust, you should use a proper csv api which will handle quoting and escapement properly for you.

Theo Kouwenhoven

ASKER

Hi CEHJ,
"It seems so. SO you need to access them by index."

That is what I expected, but I'm not a java developer, so I really don't know how to do that.
I expect that i have to replace the part :
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
cell = cells.next();

But withwhat?

CEHJ

Maybe something like:

Sheet s = wb.getSheetAt(0);
Row r = s.getRow(0);

int patchColumn = -1;
for (int cn=0; cn<r.getLastCellNum(); cn++) {
   Cell c = r.getCell(cn);
   if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) {
       // Can't be this cell - it's empty
       continue;
   }
   if (c.getCellType() == Cell.CELL_TYPE_STRING) {
      String text = c.getStringCellValue();
      if ("Patch".equals(text)) {
         patchColumn = cn;
         break;
      }
   }
}

Open in new window

Theo Kouwenhoven

ASKER

Hi CEHJ,

I changed it and created the class object, but I don't get a result file :-(

Maybe the option "soffice"is the way to go:
Can I install that on an AS/400?

CEHJ

I changed it and created the class object, but I don't get a result file :-(

Strange - please post code

Maybe the option "soffice"is the way to go:
Can I install that on an AS/400?

Of that i'm not sure, but i don't see why not, if only via compiling its source. Avoid that if poss by getting a version for AS/400

Theo Kouwenhoven

ASKER

Hi CEHJ,

Don't blame me if this source is garbage, I only did some copy and pasting.
My java knowledge ends with the last "a" of Java :-(

Source.rar

ASKER CERTIFIED SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CEHJ