C# and Excel files [closed]

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 3 months ago.

开发者_如何学编程 Improve this question

I need to create a program that can read from/write to an Excel file. Should I use OleDB or use an Excel Object? What are the advantages of one over another, and where can I read up to get started on implementing this?

---

I had to do this recently and found NPOI (http://npoi.codeplex.com/) the best bet, especially as I needed to generate binary .xls files that didn't throw up any confusing errors for a client about filetypes.

See this blog post for more info. You'll also find that as it's a port of an existing framework there's plenty of help available for the original framework that is applicable to the port.

---

Excel Automation can be messy, and isn't going to help if your program is intended to run on a web server, but it will give you full access to read, modify, and create Excel files.

Using the OLEDB driver only works with Excel 1998-2003 files, not Excel 2007, has only a limited subset of functionality (treats sheets and named regions as "tables" that you can read and write to like a database--no control over formatting, formulas, etc.), and has some significant limitations, but will work just fine in an ASP.NET application.

(There is a separate driver you can download to access Excel 2007's OOXML files (xlsx) via OLEDB. I don't know what its capabilities or limitations are compared to the older driver.)

Another option, if the application is running in ASP.NET, is to get a third-party library for reading and writing Excel files. Some are open-source (generally those that only read/write Excel XML files from Excel 2002-2007, NPOI being one notable exception), some are quite expensive.

About the only thing you don't want to do is try to implement your own Excel BIFF-format (standard 1997-2003 file format) reader/writer. The file format has evolved over time and is too complicated for mere mortals.

---

For writing, there is an easy workaround you might consider, makes your life easier.... Write everything into a comma separated text file and save it as csv. Then launch Excel through a shell command with your csv as the argument. Voilla, instant export to Excel.

Regards

---

SpreadsheetGear for .NET is an Excel compatible spreadsheet component which can read and write Excel workbooks (xls and xlsx) among other things and has no dependency on Excel, OleDb or anything other than the .NET libraries.

You can see some live samples here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC

---

If you want to read / write excel file in application at server you can use Excel Jetcell .NET component.

It has useful functions to export data from DataSet, DataTable to excel format. Also it can save the excel file (xls, xlsx) or represent the excel as a stream that open more opportunities for developers. See more C# excel files code samples.

---

you don't need a third party tool to read/write in excel files... all you need is already included in namspace Microsoft.Office.Interop.Excel;
first create an excel file and name it for example.. test1.xls ... or anything else here is the code you need:

using Excel = Microsoft.Office.Interop.Excel;
Excel.Application AppName = new Excel.Application();
Excel.Workbook bookName = AppName.Workbooks.Open(C:\....\test1);
Excel.Worksheet sheetName = AppName.Worksheets.get_Item(1);
sheetName.Rows[5].Cells[5].Value = "hello!";  //that writes hello in cell 5,5 in test1.xls
string box;
box = sheetName.Rows[10].Cells[10].Value;  //that reads the value of 10,10 cell in test1.xls and place it in box variable

---

Possibly the features of the free component "CarlosAg" is enough for you? Needs no installed Excel on the machine. I use it and am happy with it.