macOS Excel, Java, and no JDBC

[SilentPanda]

So I'm trying to access Excel files without JDBC as I hate having to set up the connections. I tried POI which worked very well except for one thing. If a user had put the following values in different cells they all returned 9.0.

9
9.0
9.00
9.000

There was really no good way to tell what was what. I could discern between 9 and 9.00 due to various other information I could gather but couldn't tell anything for 9.000 or 9.0.... POI always returns the numeric value if it can be a number. The workaround I read of was to have the users put a ' in front of all the values to make them strings which would be fine except my users can't/won't.

The main reason I don't want to use JDBC is because from everything I've read you have to set up a connection through Windows ODBC Managers. It'd be fine if it was one connection but in the course of one year I would have to set up probably a new connection every day easily for a new workbook. Granted I'm not well versed in this area at all so I could be doing more work than is needed.

Anybody have familiarity in this area and could point towards a package that might work? I only need to read Excel files, not write them, and everything (so far) is Excel 2003 or earlier, but mostly 2003/2000.

 

[lazydog]

How about exporting the Excel files as csv? Each field would then be a string.

b e n

 

[jeremy.king]

So whats the cell type in your excel workbook? Are you using a custom number format?

I ask because by default you can't enter in those values, they will all truncate the decimal unless the cell is using a custom number format or is defined as text - and if its text - POI should have no problems.

PS. Have you tried forcing the string value using POI? I think this only works if the type is HSSFCell.CELL_TYPE_STRING

cell.getStringCellValue();

PPS - if you don't mind spending the money, you can use e.Spreadsheet from Actuate.

 

[SilentPanda]

How about exporting the Excel files as csv? Each field would then be a string.

b e n

That's what I currently do (actually tab delimited but whatever). I'm trying to move away from that.

So whats the cell type in your excel workbook? Are you using a custom number format?

I ask because by default you can't enter in those values, they will all truncate the decimal unless the cell is using a custom number format or is defined as text - and if its text - POI should have no problems.

PS. Have you tried forcing the string value using POI? I think this only works if the type is HSSFCell.CELL_TYPE_STRING

cell.getStringCellValue();

PPS - if you don't mind spending the money, you can use e.Spreadsheet from Actuate.

The cell type is frankly whatever the user sets it as. I have a fairly large user base and getting them to fill the spreadsheets out correctly is hard enough. Getting them to set the cell types too will be near impossible.

I've tried the things you're suggesting in POI. From what I understand, Excel stores every single number as a number despite any formatting you put on the cell. The only way to have it store as a string is to put the ' in front of it. Even a cell formatted as text is still a number in the Excel file. POI can only retrieve the number. cell.getStringCellValue() throws an error if the cell is a number (doesn't have the ' in front of it). The custom formats don't seem to work as when I asked POI what the cell format was it returned 170. The cell format listing only had 50 entries so I have no idea what it was telling me.

Purchasing a reader is an option. It's not my money.

 

[lazydog]

Perhaps if you look at the Excel exported file in a text editor you might be able to find something useful. Perhaps, if a cell has a custom format for a number it is preserved in the output file. I'm just guessing here though.

good luck

b e n

 

[jeremy.king]

cell.getStringCellValue() throws an error if the cell is a number (doesn't have the ' in front of it).

Have you tried setting the type?

cell.setCellType(CELL_TYPE_STRING);
cell.getStringCellValue();

Purchasing a reader is an option. It's not my money.

Let me know if you would consider e.SpreadSheet (eSS) - I work for an Actuate business partner, so we could probably save you a few bucks

PS. Depending on your use of these spreadsheets, you could web enable them with eSS and allow the users to enter data via a web browser (and applet).

 

[toddburch]

I have no answer, but I would like to ask a question so I can understand this.

My confusion lies with with your comment about having to setup and use JDBC to access Excel data (of which, I have never personally used - so please bear with my understandings if they are not correct).

At my level of understanding of the whole big picture (Microsoft - Excel - and the DOM of Excel) is that Excel provides access to it's object model that can be tapped (and traversed and modified) via OLE (or ActiveX).

So, why would you even want to set up JDBC when you can (I think) use OLE to access the data? (Side stepping again - I'm assuming you can get OLE access from Java - perhaps JDBC is the mechanism for that?) And if you CAN use OLE to get the data, then you could also use OLE to get the field type to determine if should be CHAR or NUMERIC.

Am I on my own planet?

Todd

 

[SilentPanda]

Perhaps if you look at the Excel exported file in a text editor you might be able to find something useful. Perhaps, if a cell has a custom format for a number it is preserved in the output file. I'm just guessing here though.

Exporting to tab delimited works fine. I'm merely trying to remove a possibly unneeded step.

Have you tried setting the type?

cell.setCellType(CELL_TYPE_STRING);
cell.getStringCellValue();

I have tried that too.

And if you CAN use OLE to get the data, then you could also use OLE to get the field type to determine if should be CHAR or NUMERIC.

POI I believe uses OLE2. Unfortunately the field type is indeed numeric for all numbers period with the exception of fields that start with a single quote ' .

 

[toddburch]

I'm starting to think that POI is the mechanism to use OLE. Is that correct?

 

[toddburch]

OK, I get it now. I found this link that explains a lot of the options. Thanks for letting me interrupt.

http://www.rgagnon.com/javadetails/java-0516.html

 

[SilentPanda]

OK, I get it now. I found this link that explains a lot of the options. Thanks for letting me interrupt.

http://www.rgagnon.com/javadetails/java-0516.html

Nice! I'll look into OpenXLS and JExcel as those look to suit my needs best.