ASP.NET MVC - 从 DataSet 生成 Excel 表格，具有自动调整列宽、自动筛选和交替背景颜色

• 下载源代码 - 1.2 MB

引言

我需要从 DataSet 内容生成一个格式良好的表格，并从多个来源收集代码来构建这个项目。

这是一个生成的示例表格

在该项目中，我包含了一个 MVC 页面，该页面在单击按钮时使用简单的 Ajax post 自动下载生成的文件。

下载项目后，请确保右键单击解决方案并还原 NuGet 包。

背景

我合并了大量的代码。以下是所有链接供参考：

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

单元格着色
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

日期格式
https://stackoverflow.com/questions/2792304/how-to-insert-a-date-to-an-open-xml-worksheet

自动筛选
https://community.dynamics.com/crm/b/crmmitchmilam/archive/2010/11/04/openxml-worksheet-adding-autofilter

字体加粗
https://stackoverflow.com/questions/29913094/how-to-make-excel-work-sheet-header-row-bold-using-openxml

从 DataSet 生成电子表格
http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

通过 AJAX MVC 下载 Excel 文件
https://stackoverflow.com/questions/16670209/download-excel-file-via-ajax-mvc

Using the Code

使用一行代码生成电子表格非常简单，提供 DataSet、电子表格文件的完整路径以及是否应启用自动筛选。

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

关注点

我优化了原始的电子表格生成代码，以避免在电子表格中查找行并缓存现有行，这大大提高了生成具有更多行和列的电子表格的时间。

这段代码查找每一列中所有行的最大文本，然后计算列的正确大小。

// 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;
}

这是在调用时下载电子表格的 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);
            }
        }
    });
}

它将调用两个 MVC 控制器，一个用于生成电子表格，另一个用于下载它。

[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);
}

历史

• 版本 1.0