ASP.NET MVC - Generate Excel Spreadsheet from DataSet with Auto Size Columns, Auto-filter and Alternating Background Color

• Download source code - 1.2 MB

Introduction

I needed to generate a nicely formatted spreadsheet from DataSet content and I gathered code from several sources to build this project.

This is a sample spreadsheet generated:

In the project, I included an MVC page that download the generated file automatically clicking on a button using simple Ajax post.

After downloading the project, make sure to right-click in the solution and restore nuget packages.

Background

I merged a lot of code. I am including all the links below for reference:

Autofit Content:
https://social.msdn.microsoft.com/Forums/office/en-US/28aae308-55cb-479f-9b58-d1797ed46a73/solution-how-to-autofit-excel-content?forum=oxmlsdk

Coloring Cells:
https://social.msdn.microsoft.com/Forums/office/en-US/a973335c-9f9b-4e70-883a-02a0bcff43d2/coloring-cells-in-excel-sheet-using-openxml-in-c?forum=oxmlsdk

Date Formats:
https://stackoverflow.com/questions/2792304/how-to-insert-a-date-to-an-open-xml-worksheet

Auto filter:
https://community.dynamics.com/crm/b/crmmitchmilam/archive/2010/11/04/openxml-worksheet-adding-autofilter

Font Bold:
https://stackoverflow.com/questions/29913094/how-to-make-excel-work-sheet-header-row-bold-using-openxml

Generating Spreadsheet from Dataset:
http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

Download Excel file via AJAX MVC:
https://stackoverflow.com/questions/16670209/download-excel-file-via-ajax-mvc

Using the Code

Generating the spreadsheet is very simple with one line of code, provide the DataSet, the full path of the spreadsheet file and if autofilters should be enabled or not:

CreateExcelFile.CreateExcelDocument(dataSet, fullPath, includeAutoFilter: true);

Points of Interest

I optimized the original spreadsheet generation code to avoid finding rows in the spreadsheet and cache the existing rows, this improved a lot the time to generate the spreadsheet with a bigger amount of rows and columns.

This is the code that finds the biggest text for all rows in each column, then calculate the correct size of the column:

// Create columns calculating size of biggest text for the database column
int numberOfColumns = dt.Columns.Count;
Columns columns = new Columns();
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
    DataColumn col = dt.Columns[colInx];

    string maxText = col.ColumnName;
    foreach (DataRow dr in dt.Rows)
    {
        string value = string.Empty;
        if (col.DataType.FullName == "System.DateTime")
        {
            DateTime dtValue;
            if (DateTime.TryParse(dr[col].ToString(), out dtValue))
                value = dtValue.ToShortDateString();
        }
        else
        {
            value = dr[col].ToString();
        }

        if (value.Length > maxText.Length)
        {
            maxText = value;
        }
    }
    double width = GetWidth("Calibri", 11, maxText);
    columns.Append(CreateColumnData((uint)colInx + 1, (uint)colInx + 1, width+2));
}
worksheetPart.Worksheet.Append(columns);

...

private static double GetWidth(string font, int fontSize, string text)
{
    System.Drawing.Font stringFont = new System.Drawing.Font(font, fontSize);
    return GetWidth(stringFont, text);
}

private static double GetWidth(System.Drawing.Font stringFont, string text)
{
    // This formula is based on this article plus a nudge ( + 0.2M )
    // http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column.width.aspx
    // Truncate(((256 * Solve_For_This + Truncate(128 / 7)) / 256) * 7) = DeterminePixelsOfString

    System.Drawing.Size textSize = System.Windows.Forms.TextRenderer.MeasureText(text, stringFont);
    double width = (double)(((textSize.Width / (double)7) * 256) - (128 / 7)) / 256;
    width = (double)decimal.Round((decimal)width + 0.2M, 2);

    return width;
}

This is the JavaScript that downloads the spreadsheet when called:

 function downloadSpreadsheet() {
    $.ajax({
        type: "POST",
        url: '/Home/GenerateSpreadsheet', //call your controller and action
        success: function (data) {
            if (data != null && (data.errorMessage == null || data.errorMessage === "")) {

                // Get the file name for download
                if (data.fileName != "") {
                    // use window.location.href for redirect to download action for download the file
                    window.location.href = "DownloadSpreadsheet/?file=" + data.fileName;
                }
            } else {
                alert("An error ocurred", data.errorMessage);
            }
        }
    });
}

It will call two MVC controllers, one to generate the spreadsheet and another to download it:

[HttpPost]
public JsonResult GenerateSpreadsheet()
{
    // Create temp path and file name
    var path = Server.MapPath("~/temp");
    var fileName = "Spreadsheet.xlsx";

    // Create temp path if not exits
    if (Directory.Exists(path) == false)
    {
        Directory.CreateDirectory(path);
    }

    // Create the sample DataSet
    DataSet dataSet = new DataSet("Hospital");
    dataSet.Tables.Add(Table());

    // Create the Excel file in temp path
    string fullPath = Path.Combine(path, fileName);
    CreateExcelFile.CreateExcelDocument(dataSet, fullPath, includeAutoFilter: true);

    // Return the Excel file name
    return Json(new { fileName = fileName, errorMessage = "" });
}

[HttpGet]
[NoCache]
public ActionResult DownloadSpreadsheet(string file)
{
    // Get the temp folder and file path in server
    string fullPath = Path.Combine(Server.MapPath("~/temp"), file);

    // Return the file for download, this is an Excel 
    // so I set the file content type to "application/vnd.ms-excel"
    return File(fullPath, "application/vnd.ms-excel", file);
}

History

• Version 1.0