Click here to Skip to main content
13,738,406 members
Click here to Skip to main content
Add your own
alternative version

Stats

8K views
314 downloads
20 bookmarked
Posted 25 Nov 2017
Licenced CPOL

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

, 25 Nov 2017
Rate this:
Please Sign up or sign in to vote.
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:

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

License

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

Share

About the Author

Daniel Carvalho Liedke
Architect
Brazil Brazil
I started development 31 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. Currently working with MVC Web Application and WCF services using Micro Services architecture proposed by me. Besides programming I love running, swimming, cats and movies.

You may also be interested in...

Comments and Discussions

 
QuestionParabéns! Pin
Rodrigo Freitas26-Nov-17 1:20
memberRodrigo Freitas26-Nov-17 1:20 
AnswerRe: Parabéns! Pin
Daniel Carvalho Liedke26-Nov-17 6:26
memberDaniel Carvalho 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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180920.1 | Last Updated 26 Nov 2017
Article Copyright 2017 by Daniel Carvalho Liedke
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid