Read / write from an Excel spreadsheet using C #

Question

I need to create a program that writes some data to an Excel spreadsheet. Something basic about the lines First name, last name, phone number, email address in a row with each category in its column.

I don’t even know where to start. If someone can tell me which assemblies to reference and maybe point me to a website or book that covers writing / reading data from an Excel spreadsheet using a C # program that will be great.

Thank you very much.

Answer 1

Add a link to Microsoft.Office.Interop.Excel.

Assuming you have a data repository, and your model looks something like this:

class Contact { public string Firstname { get; set; } public string Lastname { get; set; } public string Email { get; set; } public string PhoneNumber { get; set; } } 

you can import it into excel like this

 Application excelapp = new Application(); excelapp.Visible = true; _Workbook workbook = (_Workbook)(excelapp.Workbooks.Add(Type.Missing)); _Worksheet worksheet = (_Worksheet)workbook.ActiveSheet; worksheet.Cells[1, 1] = "First Name"; worksheet.Cells[1, 2] = "Last Name"; worksheet.Cells[1, 3] = "Email"; worksheet.Cells[1, 4] = "Phone Number"; int row = 1; foreach (var contact in contacts) { row++; worksheet.Cells[row, 1] = contact.Firstname; worksheet.Cells[row, 2] = contact.Lastname; worksheet.Cells[row, 3] = contact.Email; worksheet.Cells[row, 4] = contact.PhoneNumber; } excelapp.UserControl = true; 

You can read more about the Excel interaction library here: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel%28v=office.11%29.aspx

Answer 2

This feature is called "Excel Automation" in .NET, where you can use C # to manage your spreadsheet.

A good starting point would be http://support.microsoft.com/kb/302084#top

Regards, Andy.

Answer 3

Depending on the difficulty level:

• Enter a value delimited (CSV) text file. Excel will open it, however you will not get any formatting.
• Write the HTML table to the file and name the file as filename.xls.
• Write the XML file in a format that Excel can open .
• Call Excel directly and get it to create a spreadsheet. (See cherhan answer).

Answer 4

Why don't you just create a csv file by saving it as xls. Is native excel mandatory?

Answer 5

As mentioned in Cherhan, Excel Automation is Microsoft's standard approach to programmatically creating spreadsheets. The disadvantages of this approach are that it uses the COM interaction layer and therefore Excel (and a license) is required for each server that runs your code.

I was not able to find any large open source projects to do it better, however, I can recommend the GemBox API for tables as a well - a commercial option (plus they have a basic free version , good for testing it). One of the main advantages of this approach is that it is 100% managed code making deployment a bit neat.

The choice probably depends on whether there is a budget that you are willing to spend on the library!