4

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

Greg
  • 23,155
  • 11
  • 57
  • 79
shekar
  • 49
  • 1
  • 4

6 Answers6

4

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);
     }
}
Vinay B R
  • 8,089
  • 2
  • 30
  • 45
  • 1
    When using OleDb make sure you are aware of possible issues described here: http://blog.lab49.com/archives/196 Also that your application will have to target 32-bit only (there's no 64-bit version of Jet) http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/45aa44dd-0e6f-42e5-a9d6-9be5c5c8fcd1 http://www.telerik.com/support/kb/aspnet-ajax/general/error-on-64-bit-windows-machines-the-microsoft-jet-oledb-4-0-provider-is-not-registered-on-the-local-machine.aspx – Piotr Owsiak Oct 08 '10 at 08:30
  • huh? The last line is incorrect! Use Object[,] oArray = (Object[,])range.Value2 and get rid of the for loop altogether. There is no way in hell you want to iterate the .Cells with com interop. You should be iterating the object array and passing it back in a modified state if you want to write back to Excel. – Anonymous Type Jan 04 '11 at 21:04
0

Programming with the C API in Excel 2010

Svisstack
  • 16,203
  • 6
  • 66
  • 100
0

If the data is tabular, use OleDB, otherwise you can use Automation to automate Excel and copy the values over to your app.

Or if it's the new XML format excel sheets you might be able to do it via the framework classes.

Info about Excel Automation: How to: Use COM Interop to Create an Excel Spreadsheet

Info about Excel via OleDB: How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

Hans Olsson
  • 54,199
  • 15
  • 94
  • 116
0

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)
Kangkan
  • 15,267
  • 10
  • 70
  • 113
0

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.

Jamie Treworgy
  • 23,934
  • 8
  • 76
  • 119
  • Or you could just use an XML library with or without linq. NPOI is alright for simple jobs though. – Anonymous Type Jan 05 '11 at 05:13
  • I am not sure what you mean by "NPOI is alright for simple jobs." Other than Office.Interop it's the only way to have almost complete access to the features of a spreadsheet, and it's far more robust for production apps than office interop. You can't access native format XLS spreadsheets with XML, and you have only basic capabilities for creating new ones, it's not really a general solution to the problem at all. – Jamie Treworgy Jan 05 '11 at 12:35
  • .. or maybe you're talking about XLSX format. When I think of "reading Excel spreadsheets", I think of XLS which is still in very widespread use. It is completely different than the the XML-based XLSX format, and the two problems are very different since XLS is not a text/XML format. – Jamie Treworgy Jan 05 '11 at 12:42
0
  1. Excel COM Interop - via Microsoft.Office.Interop.Excel + Microsoft.Office.Interop.Excel.Extensions
  2. ADO.Net via OLEDB data provider for Excel.
  3. 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).
  4. 3rd party library such as NPOI.
  5. 3rd party vendor package such as spreadsheetgear
Anonymous Type
  • 3,051
  • 2
  • 27
  • 45