Click here to Skip to main content
15,879,490 members
Articles / Web Development / HTML

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (8 votes)
25 Nov 2017CPOL1 min read 22.3K   658   21   2
Project to generate Excel Spreadsheet from DataSet with auto size (AutoFit) columns, auto-filter, alternating light blue background color with Open XML using ASP.NET MVC

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:

Image 1

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:

C#
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:

C#
// 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:

JavaScript
 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:

C#
[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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Brazil Brazil
I started development 37 years from now, since MSX basic. Started Windows programming with VB 2.0 and Web programming with ASP 3.0. Then I built Windows Forms, Web Applications, NT services and WPF applications using Microsoft.NET. I am MCP in Visual Basic 6.0, MCAD and MCSD.NET in Framework 1.1, MCPD Web in Framework 2.0, MCTS in .NET 3.5 workflow, MCTS in .NET 3.5 communication foundation, windows presentation foundation and MVC applications. Built MVC Web Application and WCF services using Micro Services architecture proposed by me. Working with AI projects to improve the business performance and customer experience. Besides programming I love running, swimming, reading and movies.

Comments and Discussions

 
QuestionParabéns! Pin
Rodrigo Freitas26-Nov-17 1:20
Rodrigo Freitas26-Nov-17 1:20 
AnswerRe: Parabéns! Pin
Daniel Liedke26-Nov-17 6:26
professionalDaniel Liedke26-Nov-17 6:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.