Writing basic information to Excel files from a .NET application

There are cases when an application needs to write data to an external workbook. There are a couple of ways to do so. The first (and more complicated) is to learn the native Excel file format by understanding the internal formatting. This can be quite time-consuming. The quicker way (although with some limitations I will mention later in this article) is to use the direct Excel Interop Objects.

To start, create a C# application (either WPF or WinForms) and open the Add Reference dialog. Excel Interop Objects are not a part of the .NET Framework Class Libraries, therefore you will need to look in the COM section. Note, that you must have Microsoft Excel installed on the development, as well as on the client machine for this method to work. This is the first limitation and it can be critical in applications that run a on a multitude of systems, where it is impossible to make Excel run on every single machine. However, for a very strict application distribution ecosystem, this should work just fine.

Once you add a reference to the Microsoft Excel x.0 Interop Objects (where x is the version of Excel installed on the local machine) you can proceed to the code. First of all, the namespace declaration should be added:

using Microsoft.Office.Interop.Excel;

Now, the code I will be explaining above should be placed in an event handler that somehow will be triggered by the user. I will start simple – by creating a workbook, with a single worksheet.

Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application();

if (application == null)
{
    throw new DllNotFoundException();
}
else
{
    Workbook book = application.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
    book.SaveAs("D:\\Temporary\\book.xlsx");
    application.Quit();
}

Note that I am verifying whether the application is null or not. If it is null, then Excel is not installed on the machine, where the application is used. I am throwing a DllNotFoundException for this, but you can show a message box or call a custom function. Note, that the dependency DLL will not be copied locally and won’t be distributed with the application. Excel is a prerequisite. Also, version compatibility is the key.

Also, notice the fact that I am saving in XLSX file format (native for Office 2007/2010). At this moment, I cannot save to XLS (which is a legacy format). This can also be considered a drawback in a production environment where not all machines are using the newest version of Office/Excel.

Now, to some interesting parts. For example, I want to write some data to my workbook, as well as create an additional worksheet. To do this, I am using the following code:

Microsoft.Office.Interop.Excel.Application application = new 
Microsoft.Office.Interop.Excel.Application();

if (application == null)
{
    throw new DllNotFoundException();
}
else
{
    Workbook book = application.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
    Worksheet sheet = (Worksheet)book.Worksheets.Add();
    sheet.Name = "Sample1";
    sheet.Cells[1, 1].Value = "MyValue";

    book.SaveAs("D:\\Temporary\\book.xlsx");
    application.Quit();
}

Here, as you see, I am adding an additional sheet names Sample1 and writing a value to a cell (first row in the first column). It is important to remember that in Excel, the numbering starts from 1, not from 0 as some developers would expect. Using 0 as the index for a column and/or cell row will result in an exception.

Also, the developer doesn’t have to worry about the file processing in this case. In case the file already exists, the Excel Interop layer will handle that and a message will appear, asking whether to overwrite the existing file.