Limitations of OLEDB Connection when reading excel file

[jbutardo]

Hi,

I have this program that uses OLEDB connection to import excel file to data table then use it in other functions.

Now I have this question: What are the possible limitations of OLEDB when it reads the Excel file. The File is on xls format, and I want to know if there is such limitations
(for example: It has limitation in reading value on a cell).

Thanks

[Reverend Jim]

Not sure how to answer that other than to ask what specific things you expect to be able to do, then address those things one by one. If the data is in a regular format (like a table where all rows are the same) then there should be no problems using OLEDB or ADO. Of course, that's assuming that we are talking about actual data rather than something that makes sense only when in an Excel spreadsheet. If the data is not in a table format and you need to get at specific cells then there are other ways to open the spreadsheet.

[jbutardo]

Not sure how to answer that other than to ask what specific things you expect to be able to do, then address those things one by one. If the data is in a regular format (like a table where all rows are the same) then there should be no problems using OLEDB or ADO. Of course, that's assuming that we are talking about actual data rather than something that makes sense only when in an Excel spreadsheet. If the data is not in a table format and you need to get at specific cells then there are other ways to open the spreadsheet.

I do mean that is there such errors that can occur when importing excel into vb.net? I also have this kind of problem that even if I have deleted the values on the sheet, the OLEDB still reads the cell while telling it has a null value.is it part of this limitations?

[Reverend Jim]

I tried this on a test sheet and found that when I deleted a cell the field was imported as a null string. You could always scan the data after importing and do an appropriate substitution. Unfortunately the COALESCE function is not available when reading the Excel spreadsheet. If you are not familiar with this, it is a useful feature when qoing a query on SQL databases. COALESCE takes a variable number of parameters and returns the first non-NULL value. For example

SELECT fname,lname,age,COALESCE(salary,0) from mytable

would substitute 0 for the salary in any record where salary was NULL.

If youfind that you are having a problem with NULL values on reading the spreadsheet then I suggest you process the data cell by cell and do your own validation. Do you need some sample code for this?

[jbutardo]

I tried this on a test sheet and found that when I deleted a cell the field was imported as a null string. You could always scan the data after importing and do an appropriate substitution. Unfortunately the COALESCE function is not available when reading the Excel spreadsheet. If you are not familiar with this, it is a useful feature when qoing a query on SQL databases. COALESCE takes a variable number of parameters and returns the first non-NULL value. For example

SELECT fname,lname,age,COALESCE(salary,0) from mytable

would substitute 0 for the salary in any record where salary was NULL.

If youfind that you are having a problem with NULL values on reading the spreadsheet then I suggest you process the data cell by cell and do your own validation. Do you need some sample code for this?

yes I think i need a sample code for this, because I don't really know how why did the OLEDB still reads the deleted cell.. thanks

[Reverend Jim]

The following code creates an Excel object to access an Excel spreadsheet. It is up to you to determine whether appropriate values are entered in the cells. ListView1 is in Details view and has four columns.

Imports Microsoft.Office.Interop ' requires "Microsoft Excel ## Object Library" resource (COM)
Imports System.Windows.Forms

Public Class Form1

    Private Sub btnImport_Click(sender As System.Object, e As System.EventArgs) Handles btnImport.Click

        Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet

        xls.Workbooks.Open("d:\temp\test.xls")
        sheet = xls.Workbooks(1).Sheets(1)

        Dim row As Integer = 2
        Dim col As Integer = 1

        Do Until Len(sheet.Cells(row, 1).Value) = 0
            Dim item As New ListViewItem
            item.Text = sheet.Cells(row, 1).Value
            item.SubItems.Add(sheet.Cells(row, 2).Value)
            item.SubItems.Add(sheet.Cells(row, 3).Value)
            item.SubItems.Add(sheet.Cells(row, 4).Value)
            ListView1.Items.Add(item)
            row += 1
        Loop
 
        xls.Workbooks.Close()
        xls.Quit()

        releaseObject(sheet)
        releaseObject(xls)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)

        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

    End Sub

End Class

[jbutardo]

The following code creates an Excel object to access an Excel spreadsheet. It is up to you to determine whether appropriate values are entered in the cells. ListView1 is in Details view and has four columns.

Imports Microsoft.Office.Interop ' requires "Microsoft Excel ## Object Library" resource (COM)
Imports System.Windows.Forms

Public Class Form1

    Private Sub btnImport_Click(sender As System.Object, e As System.EventArgs) Handles btnImport.Click

        Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet

        xls.Workbooks.Open("d:\temp\test.xls")
        sheet = xls.Workbooks(1).Sheets(1)

        Dim row As Integer = 2
        Dim col As Integer = 1

        Do Until Len(sheet.Cells(row, 1).Value) = 0
            Dim item As New ListViewItem
            item.Text = sheet.Cells(row, 1).Value
            item.SubItems.Add(sheet.Cells(row, 2).Value)
            item.SubItems.Add(sheet.Cells(row, 3).Value)
            item.SubItems.Add(sheet.Cells(row, 4).Value)
            ListView1.Items.Add(item)
            row += 1
        Loop
 
        xls.Workbooks.Close()
        xls.Quit()

        releaseObject(sheet)
        releaseObject(xls)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)

        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

    End Sub

End Class

I'll study this code, thanks by the way.