Cannot reference columns beyond the first column

Aspose.Cells Product Family
1

I’m having trouble with an Excel file that was created with
Aspose.Excel (v. 2.8.6.1). It is a very simple spreadsheet with 2
columns of data. Working in .NET, I use the DataSet.Fill method to
read the contents of the spreadsheet. But when I get a count of the
number of columns in the table, it only says there is 1 column in the
spreadsheet (and I can’t reference any columns beyond the first one).
If I then take the same spreadsheet, open it in Excel, save it, and
close Excel, the file size increases slightly (between 5K and 7K
depending on the file). With the newly saved file, I can execute the
DataSet.Fill code and it will then report the number of columns accurately
and I can also reference all of the data in the spreadsheet as I would
expect.


Is this a known issue with Aspose.Excel or am I doing something wrong? Let me know if you need me to provide a test file or sample code.

Thanks.



2

Please try this latest v3.5.2. If this problem still occurs, please post your file and sample code here. I will check them ASAP.

3

The same problem seems to happen with 3.5.2. The sample files are
attached. Test.xls was created directly from Aspose.Excel.
Test2.xls is a copy of that file that I opened in Excel and
saved. Notice the file size on Test2.xls is larger. Here is
the snippet of code that I am running to read the data.



// Code only finds the first column in Test.xls

string conn = “Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\Test.xls;Extended Properties=Excel 8.0;”;

OleDbDataAdapter dataAdapter = new OleDbDataAdapter(“SELECT * FROM
[Data$]”, conn);

DataSet dataSet = new DataSet();

dataAdapter.Fill(dataSet);

Console.WriteLine(dataSet.Tables[0].Columns.Count);


// Code finds both columns in Test2.xls

string conn2 = “Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\Test2.xls;Extended Properties=Excel 8.0;”;

OleDbDataAdapter dataAdapter2 = new OleDbDataAdapter(“SELECT * FROM
[Data$]”, conn2);

DataSet dataSet2 = new DataSet();

dataAdapter2.Fill(dataSet2);

Console.WriteLine(dataSet2.Tables[0].Columns.Count);

4

OLEDB data provider is much less robust in retrieving data in Excel files.

You can try to use Cells.ExportDataTable or Cells.ExportDataTableAsString method in Aspose.Excel to export data in files.

5

Thanks, but the machine that reads the file does not have Aspose.Excel
installed, so I cannot use Aspose methods to read. I’m more
concerned about what is different between the two files that is causing
the first one to appear to only have one column.

6

I will check this issue. Does the file created with v3.5.2 also have this problem?

7

Yes. The Test.xls file that I sent you was created with 3.5.2. Thanks.

8

Please try the attached fix with the following sample code:

Excel excel = new Excel();
excel.Worksheets[0].Name = "Data";
Cells cells = excel.Worksheets[0].Cells;
cells.SetColumnWidth(0, 21);
cells.SetColumnWidth(0, 14);
cells["A1"].PutValue("City");
cells["B1"].PutValue("State");

cells["A2"].PutValue("Chicago");
cells["B2"].PutValue("IL");

cells["A3"].PutValue("Chicago");
cells["B3"].PutValue("IL");

cells["A4"].PutValue("Report Total");

excel.Save("d:\\test\\abc.xls");


string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Test\\abc.xls;Extended Properties=Excel 8.0;";
OleDbDataAdapter dataAdapter = new OleDbDataAdapter("SELECT * FROM [Data$]", conn);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
Console.WriteLine(dataSet.Tables[0].Columns.Count);

9

This appears to be working now. Thanks!