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
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.
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?
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?
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
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 ClassThe 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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.