Reputation: 49

reading data from excel sheet using C#

how to read information inside an excel sheet using C# code......

Upvotes: 4

Answers (6)

Anonymous Type

Reputation: 3061

Excel COM Interop - via Microsoft.Office.Interop.Excel + Microsoft.Office.Interop.Excel.Extensions
ADO.Net via OLEDB data provider for Excel.
Use of System.XML and/or Linq to XML and/or Open XML SDK (can also be used to create new books programmatically from scratch).
3rd party library such as NPOI.
3rd party vendor package such as spreadsheetgear

Upvotes: 0

Jamie Treworgy

Reputation: 24344

NPOI is the way to go.

Using office interop requires that Office (and the right version) be installed on the machine your app is running on. If a web abb, that probably means no, and it's not robust enough for a production environment anyway. OLEDB has serious limitations, unless it's a one-off and the data is really simple I wouldn't use it.

Upvotes: 0

Kangkan

Reputation: 15571

Excel has an API for programming. You can use it to get range of data using c#. You can use something like:

    Excel.Application oXL;
    Excel._Workbook oWB;
    Excel._Worksheet oSheet;
    oSheet.get_Range(RangeStart,RangeEnd)

Upvotes: 0

Vinay B R

Reputation: 8421

You can either use Oledb

    using System.Data;
    using System.Data.OleDb;

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0");
OleDbDataAdapter da = new OleDbDataAdapter("select * from MyObject", con);
DataTable dt = new DataTable();
da.Fill(dt);

or you use Office Interop

this.openFileDialog1.FileName = "*.xls";
  if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
   {
      Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(
         openFileDialog1.FileName, 0, true, 5,
          "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,
          0, true); 
     Excel.Sheets sheets = theWorkbook.Worksheets;
     Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
     for (int i = 1; i <= 10; i++)
     {
     Excel.Range range = worksheet.get_Range("A"+i.ToString(), "J" + i.ToString());
     System.Array myvalues = (System.Array)range.Cells.Value;
     string[] strArray = ConvertToStringArray(myvalues);
     }
}

Upvotes: 4