Bug 51693 - Missing column
Summary: Missing column
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.7-FINAL
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-08-19 20:32 UTC by myonlyanon
Modified: 2012-03-14 14:06 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description myonlyanon 2011-08-19 20:32:15 UTC
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) + "'");
			}

.....
Comment 1 perceval.barbu 2012-03-14 14:06:14 UTC
Hi,

The issue concerns only files with an even number of column.

If you create a small file (2 or 4 cells in a row), import data doesn't find last column.

I simulate ODBC connection via excel 2003
    Menu Data > External data > Import data
    
import_2_col_expected.xls => KO (1 cell) cell_1
import_3_col_expected.xls => OK (3 cell) cell_1,cell_2,cell_3
import_4_col_expected.xls => KO (3 cell) cell_1,cell_2,cell_3
import_5_col_expected.xls => OK (5 cell) cell_1,cell_2,cell_3,cell_4,cell_5

Here is the code to create small test import file.

// Create test files
  public static void main(String[] args) {
    try {
      for (int nbCol = 2; nbCol < 6; nbCol++) {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFRow row = wb.createSheet("test").createRow(0);
        for (int i = 0; i < nbCol; i++)
          row.createCell(i).setCellValue("cell_" + (i + 1));
        String filename = "/tmp/import_" + nbCol + "_col_expected.xls";
        System.out.println("writing " + filename);
        FileOutputStream out = new FileOutputStream(filename);
        wb.write(out);
        out.close();
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }