Excel Interop & C#

If you've ever developed even a small line-of-business application, sooner or later, you know your users are going to ask:

"Erm... This is great and everything but can we get this data exported to excel?"

Now, of course, exporting data to Excel has always been possible and back in the day, you would use COM Interop to create an Excel file using C# but... how do I put this? - Let's just say it wasn't pretty!

Take this small snip of code for example:

private void ExcelTest()
{
     object mMissingValue = System.Reflection.Missing.Value;

     Application excelApp = new Application();

     // Open the file
     Workbook workBook = excelApp.Workbooks.Open(templatePath,
              mMissingValue, mMissingValue, mMissingValue, mMissingValue, mMissingValue,
              mMissingValue, mMissingValue, mMissingValue, mMissingValue, mMissingValue,
              mMissingValue, mMissingValue, mMissingValue, mMissingValue);

     Worksheet sheet = (Worksheet)workBook .Worksheets[1];
}

Notice all those missing values? Your code would be littered with them. Yuk!. - Those missingValue definitions represented "optional parameters" that can be passed through to the API, but even though we wasn't using any of them, we still had to specify all of them.

Of course, that was before the dynamic keyword was introduced with .NET 4.0. Then everything changed.... The above code could be written like this:

private void ExcelTest()
{
   var excelApp = new Application();

   var workBook = excelApp .Workbooks.Open(templatePath);

   var sheet = (Worksheet)workBook.Worksheets[1];
}

That's much better and easier on the eye. The dynamic keyword made interop with COM libraries so much simpler. For all places where an API had took an object the new interop library itself could replace that object with a dynamic.

...Of course that was then. This is now and things are always changing. - Today, if someone asks me to create an Excel file, I head off to NuGet and reach for "EPPlus".

EPPlus

EPPlus allows you to create advanced Excel spreadsheets using .NET, without the need of interop and its very feature rich. What I particularly like about the library is the documentation and samples. It's very well documented and is chock full of example code.

To install it via Nuget, type: the command: Install-Package EPPlus

As a quick example, if your working in C# there is a good chance you already have the app data held in a DataTable. In that scenario, here is a full example of outputting a DataTable to an excel file using EPPlus:

public static void WriteToExcel(DataTable tbl)
{
    var saveFileDialog = new SaveFileDialog();
    saveFileDialog.Filter = "Excel Workbook (*.xlsx)|*.xlsx|All files (*.*)|*.*";
    saveFileDialog.DefaultExt = "xlsx";
    saveFileDialog.Title = "Save As Excel Report";
    saveFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Personal);

    if (saveFileDialog.ShowDialog() == DialogResult.OK)
    {
        FileInfo outputFile = new FileInfo(saveFileDialog.FileName);
                       
        using (ExcelPackage package = new ExcelPackage())
        {
            //Create the worksheet
            ExcelWorksheet ws = package.Workbook.Worksheets.Add("AppTab");

            // Write the datatable into the sheet, starting from cell A1. 
            ws.Cells["A1"].LoadFromDataTable(tbl, true);

            // save our new workbook and we are done!
            package.Save();

            // open the excel file
            System.Diagnostics.Process.Start(outputFile.FullName);
        }
    }
}

Just, so we are on the same page this one line is the main work-horse of this function:

// Write the datatable into the sheet, starting from cell A1. 
ws.Cells["A1"].LoadFromDataTable(tbl, true);

How easy is that? Very nice! There is so much that the EPPlus library can do, and I have only scratched the surface with this teeny-tiny example.

ExcelDNA

Okay, that's all cool but what if you want to go deeper? What if you need to create a bespoke excel function that your users can use to run a business specific formula?

Thats where ExcelDna comes in. I have only found out about this recently but it is a fast and easy way to create Excel AddIns in C# (again, something that used to be a pain in the what-now!)

Again, the library is available via Nuget via the command: : Install-Package ExcelDna.AddIn

Using this library, creating Excel functions is as easy as this:

public static class MyFunctions
{
    [ExcelFunction(Description = "Say Hello to Ocean Airdop")]
    public static string SayHello(string name)
    {
        return "Hello " + name;
    }

    [ExcelFunction(Description = "Lookup From Database")]
    public static int OceanAirdrop(string nameToFind)
    {
        var dbConn = new DBConnectionSqlSvr(@"127.0.0.1", "sa", "password", "database");

        var sql = "select count(*) as [count] from [some_table] where name = " + nameToFind;

        var result = dbConn.ExecSqlCommandScalar(sql);

        return result;
    }      
}

As you can see above, you have the full power of C# and the .NET framework using this library and it is easy-peasy!

I have not really explored ExcelDNA but the documenation for the project can be found here and the github page for the project can be found here

Wrapping Up

As EPPlus has saved my bacon on a number of occasions, I have added to my programmers toolkit!. If you're in a pinch, and need to export some business data into excel I would reach for EPPlus. It's a nice and simple library!

If you have other needs that extend beyond simply creating excel documents from C# perhaps ExcelDna could be the ticket for you.