Open And Read Excel Spreadsheet In VB.Net
Here's an example showing how to open and read in an Excel spreadsheet in the VB.Net language. I'm sure you can manage to easily convert this code to other Visual Studio languages if you're using C#, for example.
Reading an Excel spreadsheet isn't hard to do but it's important to get the code right and clean up after yourself, otherwise you'll find loads of excel.exe processes laying around in memory after your program finishes. Use the following example as a basis for your code and you shouldn't get any memory leaks.
First, add a reference to your project:
I use three subroutines to read open the spreadsheet, read a worksheet range, and clean up the objects from memory, as follows:
Private Sub importExcel(ByVal sFile As String) Dim xl As Excel.Application Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Try xl = New Excel.Application Try wb = xl.Workbooks.Open(sFile) Try ws = CType(wb.Worksheets(1), Excel.Worksheet) Call importSheet(ws.UsedRange) Catch ex As Exception Throw Finally clearUp(ws) End Try Catch ex As Exception Throw Finally wb.Close() clearUp(wb) xl.Workbooks.Close() clearUp(xl.Workbooks) End Try Catch ex As Exception Throw Finally xl.Quit() xl = Nothing clearUp(xl) GC.Collect() End Try End Sub Private Sub importSheet(ByVal range As Excel.Range) Dim sString1 As String Dim sString2 As String Dim sString3 As String Try For iRow As Integer = 1 To range.Rows.Count sString1 = CStr(CType(range.Cells(iRow, 1), Excel.Range).Value) sString2 = CStr(CType(range.Cells(iRow, 2), Excel.Range).Value) sString3 = CStr(CType(range.Cells(iRow, 3), Excel.Range).Value) ' Add code to read required columns from the spreadsheet here Next Catch ex As Exception Throw ex End Try End Sub Private Sub clearUp(ByVal o As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(o) Catch ex As Exception Finally o = Nothing End Try End Sub