https://issues.apache.org/bugzilla/show_bug.cgi?id=51693
Bug #: 51693
Summary: Missing column
Product: POI
Version: 3.7
Platform: PC
OS/Version: Windows XP
Status: NEW
Severity: normal
Priority: P2
Component: HSSF
AssignedTo: [email protected]
ReportedBy: [email protected]
Classification: Unclassified
Using POI I'm creating an *.xls file that contains 6 sheets of data. None of
the columns of data are formulas, all are either Strings or numbers (none are
blank/empty). If I open the file using Excel I see all of the columns and data.
If I try to read the file using Java and ODBC the first and last sheets have
all their columns but the sheets in between are missing the last column (If I
try to specifically select the column I get a failure - doing a Select * gets
all but the missing column on the failed sheets). The sheets have no more than
14 columns. Also if I run this validator tool against the file it fails
(http://blogs.msdn.com/b/officeinteroperability/archive/2011/07/12/microsoft-office-binary-file-format-validator-is-now-available.aspx).
If I simply have the file open in Excel and run my java reader it finds all the
columns (if I close Excel it fails to find all the columns as before). If I
open, save and exit the file using Excel the file then works correctly. My work
around is to add an extra "junk" column that contains and empty string to each
sheet. This will allow me to query the missing columns without the failure
("Select last_column from [SECOND_SHEET$]"). This problem surfaced at work. I'm
creating a file using POI and another person is trying to read it using
Microsoft.Jet.OLEDB.4.0 and the file is failing when being read. Opening and
saving via Excel allows the file to then be read.
Sample code for creating sheets (altered from actual work code):
private void writeSheetData()
{
int sheetNum = 0;
for (String name : Config.instance().getSheetNames())
{
myLogger.debug("Process sheet " + name);
HSSFSheet sheet = wb.getSheet(name);
HSSFRow row = sheet.createRow(0);
HSSFCell cell = null;
// Populate the sheet
int count = 0;
for (String columnname :
Config.instance().getSheetColumnNames(name))
{
cell = row.createCell(count++);
cell.setCellValue(columnname);
}
switch (sheetNum)
{
case FIRST_SHEET:
Vector<String> v = new Vector<String>(sHash.keySet());
Collections.sort(v);
for (Enumeration<String> idsenum = v.elements();
idsenum.hasMoreElements();)
{
String sss = idsenum.nextElement();
SSS sObj = sHash.get(sss);
row = sheet.createRow(sheet.getLastRowNum() + 1);
cell = row.createCell(0);
cell.setCellValue(sObj.getNumber());
cell = row.createCell(1);
cell.setCellValue(sObj.getID());
}
break;
case SECOND_SHEET:
Vector<String> vt = new Vector<String>(tHash.keySet());
Collections.sort(vt);
for (Enumeration<String> idsenum = vt.elements();
idsenum.hasMoreElements();)
{
THelper mObj = tHash.get(idsenum.nextElement());
row = sheet.createRow(sheet.getLastRowNum() + 1);
cell = row.createCell(0);
....
out = new FileOutputStream(filename);
wb.write(out);
Sample code for reading in data:
NOTE: mytest is being set using Administrative Tools -> Data Sources (ODBC) and
adding a User DSN for Excel Files (Microsoft Excel Driver *.xls) that is
pointing to the xls workbook file that was created.
public void mytest() {
Connection connection = null;
int numberOfColumns = 0;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con =
DriverManager.getConnection("jdbc:odbc:mytest","","");
Statement st = con.createStatement();
String query = "Select * from [SECOND_SHEET$]";
System.out.println("Query is: '" + query + "'");
ResultSet rs = st.executeQuery( query );
ResultSetMetaData rsmd = rs.getMetaData();
numberOfColumns = rsmd.getColumnCount();
System.out.println("Column count is: " + numberOfColumns);
for (int i = 1; i <= numberOfColumns; i++) {
System.out.println("Column is: '" + rsmd.getColumnLabel(i) +
"'");
}
.....
--
Configure bugmail: https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
