how to read information inside an excel sheet using C# code......
-
this is an exact duplicate question. Somebody add the dupe links and close.Anonymous Type– Anonymous Type2011-01-04 21:09:16 +00:00Commented Jan 4, 2011 at 21:09
-
possible duplicate of Reading Excel files from C#Doc Brown– Doc Brown2011-01-04 21:34:33 +00:00Commented Jan 4, 2011 at 21:34
Add a comment
|
6 Answers
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);
}
}
2 Comments
Piotr Owsiak
When using OleDb make sure you are aware of possible issues described here: blog.lab49.com/archives/196 Also that your application will have to target 32-bit only (there's no 64-bit version of Jet) social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/… telerik.com/support/kb/aspnet-ajax/general/…
Anonymous Type
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.
Comments
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
1 Comment
Piotr Owsiak
For some important stuff on OleDb see my comment on Vinay B R's answer.
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)
Comments
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.
3 Comments
Anonymous Type
Or you could just use an XML library with or without linq. NPOI is alright for simple jobs though.
Jamie Treworgy
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
.. 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.
- 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
Comments
