Last column not recognized from Excel with even no of columns

[Kurada, Srinivas]

Hi All,

I have a simple java program to generate an Excel using POI 3.5 ,and iam loading that Excel data to MS-Access database table.Excel file is generating fine with data and I can see all the columns if I open it, but when iam loading that file to the DB table,last column is missing. strange part is that this issue occuring when I generate the Excel sheet with Even number of columns ( for example 50 colums).

But when iam generating Excel with Odd number of columns ( for example 51 colums), its working fine and all the columns are loading into the table.

Can anybody faced this problem? Any idea what is the issue?please suggest.

Thanks&Regards
Srini


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Josh Micich]

Hello Srini,

POI writes Excel files which are clearly different (at a binary level)
 from those written by Excel and sometimes these differences cause
bugs (applications failing to read POI generated files properly).  If
I have understood correctly, you have said that Excel reads the file
OK but MS-Access does not.  Priority is given to fixing bugs which
cause files to read incorrectly in Excel.  It also sounds like you
have a work-around (just add an extra column and ignore it later).
Having said that, if you're able to help with some more investigation,
we might be able to find a fix.

You don't seem to have stated explicitly whether creating the file
manually/directly with Excel causes the same problem in MS-Access.
While this seems very unlikely, if true it would imply that the
problem is entirely within MS-Access.  On a similar note - what
happens when you re-save the POI generated file with Excel?  Can
MS-Access read it properly after that?

There are a few more details that might be important: Are you using
HSSF or XSSF?  In MS-Access, are you using the workbook as an ODBC
Datasource, or are  you just doing an import (converting Excel sheets
to MS-Access tables)?  What about boundary cases - how does MS-Access
behave when POI writes a file with 3,2,1 or even zero columns?

regards,
Josh

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Kurada, Srinivas]

Hello Josh,

Thanks for your response!!!

The problem is not with MS-Acess,since other applications also unable to load it(using SQL server).
My answers below regarding your questions.

1.manually created files on XLS directly loads into MS-Access properly but not the one created with POI
2.On resaving the XLS created by POI is loading fine in MS-Access
3.We are using HSSF. ( we didn't change it to XSSF since end users are using still XLS 2003)
4.We are directly importing the XLS into MS-Access table.
5.if the columns are in Odd numbers generated by POI are able to load the file into Access fine.Problem is with even number of columsns.
For example: no of columsn with 1,3 loads fine.
No of columns with 2,4 doesn't load properly, instead it skips the last column ( i.e if there are 50 columsn it loads only 49 columns)

Regards
Srini

-----Original Message-----
From: Josh Micich [mailto:josh.micich@...]
Sent: Friday, October 30, 2009 1:14 AM
To: POI Users List
Subject: Re: Last column not recognized from Excel with even no of columns

Hello Srini,

POI writes Excel files which are clearly different (at a binary level)  from those written by Excel and sometimes these differences cause bugs (applications failing to read POI generated files properly).  If I have understood correctly, you have said that Excel reads the file OK but MS-Access does not.  Priority is given to fixing bugs which cause files to read incorrectly in Excel.  It also sounds like you have a work-around (just add an extra column and ignore it later).
Having said that, if you're able to help with some more investigation, we might be able to find a fix.

You don't seem to have stated explicitly whether creating the file manually/directly with Excel causes the same problem in MS-Access.
While this seems very unlikely, if true it would imply that the problem is entirely within MS-Access.  On a similar note - what happens when you re-save the POI generated file with Excel?  Can MS-Access read it properly after that?

There are a few more details that might be important: Are you using HSSF or XSSF?  In MS-Access, are you using the workbook as an ODBC Datasource, or are  you just doing an import (converting Excel sheets to MS-Access tables)?  What about boundary cases - how does MS-Access behave when POI writes a file with 3,2,1 or even zero columns?

regards,
Josh

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@... For additional commands, e-mail: user-help@...


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...

[Kurada, Srinivas]

Hello Josh,

Thanks for your response!!!

The problem is not with MS-Acess,since other applications also unable to load it(using SQL server).
My answers below regarding your questions.

1.manually created files on XLS directly loads into MS-Access properly but not the one created with POI 2.On resaving the XLS created by POI is loading fine in MS-Access 3.We are using HSSF. ( we didn't change it to XSSF since end users are using still XLS 2003) 4.We are directly importing the XLS into MS-Access table.
5.if the columns are in Odd numbers generated by POI are able to load the file into Access fine.Problem is with even number of columsns.
For example: no of columsn with 1,3 loads fine.
No of columns with 2,4 doesn't load properly, instead it skips the last column ( i.e if there are 50 columsn it loads only 49 columns)

Regards
Srini

-----Original Message-----
From: Josh Micich [mailto:josh.micich@...]
Sent: Friday, October 30, 2009 1:14 AM
To: POI Users List
Subject: Re: Last column not recognized from Excel with even no of columns

Hello Srini,

POI writes Excel files which are clearly different (at a binary level)  from those written by Excel and sometimes these differences cause bugs (applications failing to read POI generated files properly).  If I have understood correctly, you have said that Excel reads the file OK but MS-Access does not.  Priority is given to fixing bugs which cause files to read incorrectly in Excel.  It also sounds like you have a work-around (just add an extra column and ignore it later).
Having said that, if you're able to help with some more investigation, we might be able to find a fix.

You don't seem to have stated explicitly whether creating the file manually/directly with Excel causes the same problem in MS-Access.
While this seems very unlikely, if true it would imply that the problem is entirely within MS-Access.  On a similar note - what happens when you re-save the POI generated file with Excel?  Can MS-Access read it properly after that?

There are a few more details that might be important: Are you using HSSF or XSSF?  In MS-Access, are you using the workbook as an ODBC Datasource, or are  you just doing an import (converting Excel sheets to MS-Access tables)?  What about boundary cases - how does MS-Access behave when POI writes a file with 3,2,1 or even zero columns?

regards,
Josh

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@... For additional commands, e-mail: user-help@...


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@...
For additional commands, e-mail: user-help@...