Importing the saved .xls through oledb (select * from sheet) doesn't work unless you open the file again and save it

Aspose.Cells Product Family
1


We use Aspose to save excel spreadsheets. Our website does importing off of these files.
Something weird is going on. If I save from the site and directly try to import the file using:
------------------------------
Dim strConn As String
If ContainsHeaders Then
strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & SourceFile & “;Extended Properties=”“Excel 8.0;IMEX=1;HDR=YES”""
Else
strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & SourceFile & “;Extended Properties=”“Excel 8.0;IMEX=1;HDR=NO”""
End If

Dim myConnection As New OleDbConnection(strConn)
myConnection.Open()
Dim myCommand As OleDbDataAdapter
Dim ExcelSheets As DataTable = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

myCommand = New OleDbDataAdapter(“SELECT * FROM [” & ExcelSheets.Rows(0).Item(“table_name”) & “]”, strConn)
Dim myDataSet As DataSet = New DataSet
myCommand.Fill(myDataSet, “Test”)
myCommand.Dispose()
myCommand = Nothing
myConnection.Dispose()
myConnection = Nothing
---------------------------------------------
myDataSet.Tables(0).Columns.Count

My column count is 1 even though if you open the excel file it looks like everything is there.
If I then open and resave it, the column count is now correct.

Do you have an suggestions or knowledge of this type of problem???

Thanks…Karen


2

Hi Karen,
Have you tried the latest hotfix?

3

It did not help and it introduced several other problems that we did not have before. Do you have any other suggestions?

-Karen

4

Hi Karen,

Why don’t use Cells.ExportDataTable to export data in excel file?

5

Laurence,

Would I use the Cell.ExportDataTable instead of the Save?
What is the difference?

-Karen

6

Hi Karen,

Please refer to http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.Cells.ExportDataTable.html.
If you want to import data from an Excel file, you can try this:

Dim excel as Aspose.Excel = new Excel()

excel.Open(sourceFileName)

Dim ExcelSheets As DataTable

ExcelSheets = excel.Worksheets(0).Cells.ExportDataTable(1,1,5,5)

…

MS Excel allows the Excel file’s format to be more flexible than oledb driver.

7

Hi,

I am dealing with the same problem as Karen. I understand that using ExportDataTable() would solve the issue but it is not straight forward to use. I have to deal with the dimensions of the worksheets rather than just straight forward import the whole excel file.

I would rather prefer a method like:

Excel.ExportDataTable()

which exports the whole excel file into a DataSet with DataTables for each Worksheet in the file.

Regards,
Michael

8

Because there may be many null values in worksheets, we haven’t thought of providing such a method. We will check the feasibility of your suggestion. Thank you.