How to read Excel cell from VB.Net

Question

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

Can you show me sample code? Thanks in advance!

Best reagrds, thlaing

Answer 1

Try 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 particular cell try this (it will read cell D6). The point to note is that it is not using OLEDB connection rather it is directly accessing.

Namespace required using Microsoft.Office.Core;

Add it by adding reference from COM to 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 an Excel compatible spreadsheet component for .NET which you can use to get the formula, value, formatted text, etc... of 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 samples here or download the free trial here if you want to try it yourself.

Disclaimer: I own SpreadsheetGear LLC