Create Excel Dynamically in Code

Hey kids! So, let me guess ... the lady in Marketing wants you to give her some way to dynamically create a report based on some stuff in a database. And she needs it to be in Excel. And it needs to have a pivot table. And any missing names in column D needs to have a red background.

Well, shit.

For whatever reason, that has become a difficult task without using some crazy third party random weird GitHub created extension you have to do some massive coding to include... ugh.

Surprise!! There is a Nuget package available that will do this so easily. It is in GitHub, but it doesn't require crazy anything :) It is free if you are using it for personal use. If you are using it commercially (meaning you're making money off using it) there is a license to purchase. This awesome package is called EPPlus and can be found directly in Nuget.

Open your Nuget package manager and search for EPPlus. The first one with the green pic should be the one you need (shown below).

Troubleshooting Tip: I found in cases of updating an older app to include an Excel file, sometimes the EPPlus version 5 package will fail against an older version of Microsoft.Extensions.Configuration if the version is lower than 5.0.0. If you get an error based on compatibility, search for the package listed in the error and upgrade the Microsoft nuget package(s) to the current version and retry installing EPPlus.

Once installed, you should see this readme file added during the install. There are instructions on Commercial v Non Commercial usage. There are also instructions on how to add the license to your project. You'll add a value to the appsettings.json (.NET Core) or in the web.config (.NET framework). There are additional instructions if you're using a Mac.

Below is an example of how to add EPPlus to the appsettings file.

Copy the example from the readme file with the license and the using statement creating the ExcelPackage. Copy into your code where you want to create the Excel document. Use Intellisense and allow it to add the 'using OfficeOpenXml' for you. The remaining errors should render correctly.

Now that we have EPPlus installed and configured, we can begin to customize the sheet as we want. In the example below, I'm showing several things we're doing:

• adjusted the header in the file to be a blue background color with bolding

• autosizing for the width of the data

• fill the new Excel workbook with a datatable (created from the database values)

• created the folder if it doesn't exist

• created the new file, saving it to a specified location

using (ExcelPackage pck = new ExcelPackage())
{
    //Create the worksheet
    ExcelWorksheet reportSheet = pck.Workbook.Worksheets.Add("Sheet1");

    //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
    reportSheet.Cells["A1"].LoadFromDataTable(reporttable, true);

    const double minWidth = 0.00;
    const double maxWidth = 50.00;
    reportSheet.Cells.AutoFitColumns(minWidth, maxWidth);

    //Format the header 
    using (ExcelRange rng = reportSheet.Cells["A1:AJ1"])
    {
        rng.Style.Font.Bold = true;
        rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
        rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));
        rng.Style.Font.Color.SetColor(Color.White);
    }
 if (!Directory.Exists(foldername))
{
    Directory.CreateDirectory(foldername);
}
    //create file and close
    FileStream objFileStrm = File.Create(fullPath);
    objFileStrm.Close();

    //Write content to closed excel file    
    File.WriteAllBytes(fullPath, pck.GetAsByteArray());
}

It looks like this. The data is sensitive so this is all I can show you :)

If you have created your own object, you can directly insert it as well using:

 ws.Cells["A1"].LoadFromCollection<CustomClassObject>(worksheetdata, true);

If you need to add additional worksheets, this is the code:

  //Create the error worksheet
    ExcelWorksheet errorSheet = pck1.Workbook.Worksheets.Add("Errors");

There are TONS of ways to customize the workbook to hopefully be able to style things the way your user needs them. Find the wiki of 'things you can do with EPPlus' here: https://github.com/EPPlusSoftware/EPPlus/wiki

Tables, pivot tables, conditional formatting, oh my!!

Hopefully this tutorial helps you in your endeavor! Let me know in the comments if you need more direction or help, or if I missed something that is important to use EPPlus!!

Cheers!!