How to read an Excel cell from VB.Net

Question

How can I read a specific cell from an Excel file using OLEDB connection with VB.NET?

Can you show me some sample code?

Answer 1

Try using the following C # code:

Dim MyCommand As System.Data.OleDb.OleDbDataAdapter Dim MyConnection As System.Data.OleDb.OleDbConnection MyConnection = New System.Data.OleDb.OleDbConnection( _ "provider=Microsoft.Jet.OLEDB.4.0; " & _ "data source=" & ExcelFilePath & "; " & _ "Extended Properties=Excel 8.0") ' Select the data from Sheet1 ([in-house$]) of the workbook. MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [in-house$]", MyConnection) DS = New System.Data.DataSet MyCommand.Fill(DS) Dt = DS.Tables(0) DataGrid1.DataSource = Dt 

For a specific cell, try this (it will read cell D6). It should be noted that it does not use the OLEDB connection, but has direct access.

The namespace is required using Microsoft.Office.Core;

Add it by adding a link from COM to the Microsoft Office 12.0 Object Library

 Dim oApp As New Excel.Application Dim oWBa As Excel.Workbook = oApp.Workbooks.Open("c:\Test.XLS") Dim oWS As Excel.Worksheet = DirectCast(oWBa.Worksheets(1), Excel.Worksheet) oApp.Visible = False Dim oRng As Excel.Range oRng = oWS.Range("D6") MsgBox(oRng.Value) 

Answer 2

SpreadsheetGear for .NET is a .NET compatible Excel spreadsheet component that you can use to get formulas, values, rich text, etc. any cell. Here is a simple example:

 using System; using SpreadsheetGear; namespace Program { class Program { static void Main(string[] args) { // Load a workbook from disk and get the first worksheet. IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(@"C:\tmp\HelloWorld.xlsx"); IWorksheet worksheet = workbook.Worksheets[0]; // Get a reference to cell A1 and write the formatted value to the console. IRange a1 = worksheet.Cells["A1"]; Console.WriteLine("A1={0}", a1.Text); // Get a reference to B2 and write the formula / value / text to the console. IRange b2 = worksheet.Cells[1, 1]; Console.WriteLine("B2 Formula={0}, Value={1}, Text={2}", b2.Formula, b2.Value, b2.Text); } } } 

You can see live examples here or download a free trial here if you want to try it yourself.

Disclaimer: I have SpreadsheetGear LLC

Answer 3

try this c # code,

 DimobjEXCELCon As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=EXCLE_FILE_PATH;Extended Properties=""Excel 12.0 Xml;HDR=Yes""") ExcelConnection.Open() Dim objQuery As String = "SELECT * FROM [Sheet1$]" 'get values from sheet1, here you can change your sheet name Dim objCMD As OleDbCommand = New OleDbCommand(objQuery,objEXCELCon) Dim objDR As OleDbDataReader Dim SQLconn As New SqlConnection() Dim szCON As String = "Connection string for database" SQLconn.ConnectionString = szCON SQLconn.Open() Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLConn) bulkCopy.DestinationTableName = "TableToWriteToInSQLSERVER" Try objDR = objCMD.ExecuteReader bulCopy.WriteToServer(objDR) objDR.Close() SQLConn.Close() Catch ex As Exception MsgBox(ex.ToString) End Try