How can i write to an excel using Microsoft.Office.Interop.Excel.Application in c#

Question

I would like to have a simple example to write to an Excel spreadsheet using the Microsoft.Office.Interop.Excel.Application objects in c#

Any one please help Thanks

Solution 1

Interop is not supported in server scenarios (like ASP.NET) according to MS.

There are many options to read/edit/create Excel files without Interop/installing Excel on the server:

MS provides the free OpenXML SDK V 2.0 - see http://msdn.microsoft.com/en-us/library/bb448854%28office.14%29.aspx (XLSX only)

This can read+write MS Office files (including Excel).

Another free option see http://www.codeproject.com/KB/office/OpenXML.aspx (XLSX only)

If you need more like handling older Excel versions (like XLS, not only XLSX), rendering, creating PDFs, formulas etc. then there are different free and commercial libraries like ClosedXML (free, XLSX only), EPPlus (free, XLSX only), Aspose.Cells, SpreadsheetGear, LibXL and Flexcel etc.

Solution 2

Here is a simple example of creating a new workbook and writing a value to a cell.

using Excel = Microsoft.Office.Interop.Excel;
var xlApplication = new Excel.Application();
var workbooks = xlApplication.Workbooks;
Excel.Workbook sampleWorkbook = workbooks.Add();
Excel.Worksheet sampleWorksheet = sampleWorkbook[1];
Excel.Range sampleCell = sampleWorksheet.get_Range("A1");
sampleCell.Value = "New Value";
sampleWorkbook.SaveAs("Output.xlsx");
sampleWorkbook.Close();
xlApplication.Quit();

Solution 3

From my previous experience working with Microsoft.Office.Interop.Excel, I can tell you it will become hell down the line. Instead I suggest to use managed Excel engine like EPPlus

http://epplus.codeplex.com/

I implemented couple of Excel export functionality using EPPlus and it's working great.

Comments

[tdbeckett]

This won't build. That is all.

[Sahil Bhatia]

This works with an error. In order to fix it, replace the line -> Excel.Worksheet sampleWorksheet = sampleWorkbook[1]; with this line -> Excel.Worksheet sampleWorksheet = sampleWorkbook.Worksheets[1];