如何导出到 Excel? [英] How do I export to Excel?

查看:26
本文介绍了如何导出到 Excel?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我遵循了

LogModelsController.cs

public ActionResult ExportData(){GridView gv = 新的 GridView();gv.DataSource = db.Log.ToList();gv.DataBind();Response.ClearContent();Response.Buffer = true;Response.AddHeader("content-disposition", "attachment; filename=Loglist.xls");Response.ContentType = "application/ms-excel";Response.Charset = "";StringWriter sw = new StringWriter();HtmlTextWriter htw = new HtmlTextWriter(sw);gv.RenderControl(htw);Response.Output.Write(sw.ToString());Response.Flush();Response.End();return RedirectToAction("~/LogModels/Index");}

LogModel.cs

命名空间 ASDMVC.Models{[表(日志")]公共类 LogModel{[钥匙]公共长 ID { 获取;放;}公共字符串消息 { 获取;放;}公共日期时间时间戳 { 获取;放;}公共字符串级别 { 获取;放;}公共 int customerId { 获取;放;}}公共类 LogDBContext:DbContext{公共 LogDBContext() : base("MySqlConnection"){}公共 DbSet 日志{得到;放;}}}

TD;DR:如何将此表正确导出到 Excel?

奖励:它也适用于 PagedList 吗?

<小时>

编辑 3 - 已解决

Index.cshtml:

@using (Html.BeginForm("ExportData", "LogModels")){<input type="submit" name="Export" id="Export" value="Export"/>}

LogModelsController.cs

public ActionResult ExportData(){数据表数据表 = GetData();使用 (ExcelPackage package = new ExcelPackage()){var ws = package.Workbook.Worksheets.Add("My Sheet");//true 生成标题ws.Cells["A1"].LoadFromDataTable(dataTable, true);var stream = new MemoryStream();package.SaveAs(stream);string fileName = "myfilename.xlsx";string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";流.位置= 0;返回文件(流,内容类型,文件名);}}公共数据表 GetData(){数据表 dt = 新数据表();如果(模型状态.IsValid){使用 (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString)){使用 (SqlCommand comm = conn.CreateCommand()){comm.Parameters.AddWithValue("@val1", Session["myID"]);comm.CommandText = "SELECT * FROM dbo.Log WHERE CustomerId = @val1";尝试{conn.Open();dt.Load(comm.ExecuteReader());}捕获(SqlException e){抛出新的异常(e.ToString());}}}}返回 dt;}

我在 CommandText 中犯了一个错误,我用Id"代替了CustomerId"——它现在返回 Excel 文件中的整个表格.

解决方案

该指南没有展示如何创建 Excel 文件,它展示了一个使用表格创建 HTML 文件的技巧.Excel 将尝试使用通常会失败的默认设置导入带有表格的 csv 或 HTML 文件.

使用之类的库创建正确 Excel 文件要容易得多EPPlus.我确定之前已经回答过这个问题,但我找不到一个答案来显示如何 从数据创建一个 Excel 文件 并设置正确xlsx 文件的内容类型.

EPPlus 提供了 LoadFromDataTable 和 LoadFromCollection 方法来使用从 DataTable 或通用集合中提取的数据填充 Excel 范围.列名或属性名分别用作标题.

public ActionResult ExportData(){//不知何故,将数据加载到DataTable使用 (ExcelPackage package = new ExcelPackage()){var ws = package.Workbook.Worksheets.Add("My Sheet");//true 生成标题ws.Cells["A1"].LoadFromDataTable(dataTable, true);var stream = new MemoryStream();package.SaveAs(stream);string fileName = "myfilename.xlsx";string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";流.位置= 0;返回文件(流,内容类型,文件名);}}

LoadFromDataTable 或 LoadFromCollection 返回可用于设置表格格式的 Excel 单元格范围:

var range=ws.Cells["A1"].LoadFromDataTable(table);range.Style.Numberformat.Format = "#,##0.00";range.Style.Horizo​​ntalAlignment = ExcelHorizo​​ntalAlignment.Right;

您还可以将表格样式应用于范围,或将所需样式传递给 LoadFromDataTableLoadFromCollection,例如:

ws.Cells[1,1].LoadFromDataTable(table, true, TableStyles.Light1);

图书馆的Githup wiki 展示了如何格式化结果,生成公式、数据透视表、处理表格等.

如果您想创建一个相对较大的工作表,保存到 MemoryStream 可能是一个问题.两次写入相同的数据,一次写入 MemoryStream,然后将其复制到输出,浪费周期.但是,从 MVC 操作直接写入输出流是一种不好的做法.诀窍是创建一个自定义 FileResult 来处理 EPPlus 包并返回它而不是 File 返回的 FileResult,例如:

公共类EpplusResult:FileResult{公共 EpplusResult(ExcelPackage 包): base("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"){如果(包 == 空){throw new ArgumentNullException("package");}包 = 包;}公共 ExcelPackage Package { get;私人订制;}protected override void WriteFile(HttpResponseBase response){//抓取数据块并写入输出流流 outputStream = response.OutputStream;使用(包){Package.SaveAs(outputStream);}}}

允许您编写以下操作:

public FileResult ExportData(){ExcelPackage package = new ExcelPackage();var ws = package.Workbook.Worksheets.Add("My Sheet");...ws.Cells[1,1].LoadFromDataTable(table, true, TableStyles.Light1);return new EpplusResult(package){FileDownloadName = "SomeFile.xlsx"};}

I followed this guide which was recommended in another post about this but I can't quite get it to work. I get a 404 error when trying to redirect to /LogModelsController/ExportData which - as I understood it - was what I was supposed to do.

_LogPartialLayout.cshtml

@using (Html.BeginForm("ExportData", "LogModelsController", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    <table class="table">
        <tr>
            <th>
                @Html.ActionLink("message", "Index", new { sortOrder = ViewBag.NameSortParm, currentFilter = ViewBag.CurrentFilter })

            </th>
            <th>
                @Html.ActionLink("timestamp", "Index", new { sortOrder = ViewBag.NameSortParm, currentFilter = ViewBag.CurrentFilter })
            </th>
            <th>
                @Html.ActionLink("level", "Index", new { sortOrder = ViewBag.NameSortParm, currentFilter = ViewBag.CurrentFilter })
            </th>

        </tr>

        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.message)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.timeStamp)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.level)
                </td>
            </tr>
        }
    </table>
    <input type="submit" name="Export" id="Export" value="Export" />
}

LogModelsController.cs

public ActionResult ExportData()
        {
            GridView gv = new GridView();
            gv.DataSource = db.Log.ToList();
            gv.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=Loglist.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();

            return RedirectToAction("~/LogModels/Index");
        }

LogModel.cs

namespace ASDMVC.Models
{
    [Table("Log")]
    public class LogModel
    {
        [Key]
        public long id { get; set; }
        public string message { get; set; }
        public DateTime timeStamp { get; set; }
        public string level { get; set; }
        public int customerId { get; set; }
    }

    public class LogDBContext:DbContext
    {
        public LogDBContext() : base("MySqlConnection")
        {

        }

        public DbSet <LogModel> Log { get; set; }

    }
}

TD;DR: How do I properly export this table to Excel?

Bonus: Does it work with PagedList too?


Edit 3 - SOLVED

Index.cshtml:

@using (Html.BeginForm("ExportData", "LogModels"))
{
    <input type="submit" name="Export" id="Export" value="Export" />
}

LogModelsController.cs

public ActionResult ExportData()
{
    DataTable dataTable = GetData();

    using (ExcelPackage package = new ExcelPackage())
    {
        var ws = package.Workbook.Worksheets.Add("My Sheet");
        //true generates headers
        ws.Cells["A1"].LoadFromDataTable(dataTable, true);

        var stream = new MemoryStream();
        package.SaveAs(stream);

        string fileName = "myfilename.xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        stream.Position = 0;
        return File(stream, contentType, fileName);
    }
}

public DataTable GetData()
{
    DataTable dt = new DataTable();
    if (ModelState.IsValid)
    {
        using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnection"].ConnectionString))
        {
            using (SqlCommand comm = conn.CreateCommand())
            {
                comm.Parameters.AddWithValue("@val1", Session["myID"]);
                comm.CommandText = "SELECT * FROM dbo.Log WHERE CustomerId = @val1";
                try
                {
                    conn.Open();
                    dt.Load(comm.ExecuteReader());
                }
                catch (SqlException e)
                {
                    throw new Exception(e.ToString());
                }
            }
        }
    }
    return dt;
}

I made a mistake in the CommandText and put "Id" instead of "CustomerId" - it now returns the entire table in the Excel file.

解决方案

The guide doesn't show how to create an Excel file, it shows a hack that creates an HTML file with a Table. Excel will try to import csv or HTML files with tables using default settings which can often fail.

It's far easier to create a proper Excel file using a library like EPPlus. I'm sure this has been answered before but I can't find an answer that shows both how to create an Excel file from data and set the proper content type for an xlsx file.

EPPlus provides the LoadFromDataTable and LoadFromCollection methods to fill an Excel range with data pulled from a DataTable or generic collection. The column or property names are used as headers respectively.

public ActionResult ExportData()
{

    //Somehow, load data to a DataTable

    using (ExcelPackage package = new ExcelPackage())
    {
        var ws = package.Workbook.Worksheets.Add("My Sheet");
        //true generates headers
        ws.Cells["A1"].LoadFromDataTable(dataTable, true);

        var stream = new MemoryStream();
        package.SaveAs(stream);

        string fileName = "myfilename.xlsx";
        string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        stream.Position = 0;
        return File(stream, contentType, fileName);
    }
}

LoadFromDataTable or LoadFromCollection return an Excel cell range which can be used to format the table:

var range=ws.Cells["A1"].LoadFromDataTable(table);
range.Style.Numberformat.Format = "#,##0.00";
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;

You can also apply a table style to the range, or pass the desired style to LoadFromDataTable or LoadFromCollection, eg:

ws.Cells[1,1].LoadFromDataTable(table, true, TableStyles.Light1);

The library's Githup wiki shows how to format the result, generate formulas, PivotTables, handle tables etc.

Saving to a MemoryStream may be an issue if you want to create a relatively large sheet. Writing the same data twice, once to the MemoryStream and then copying it to output, wastes cycles. Writing directly to the output stream from an MVC action is a bad practice though. The trick is to create a custom FileResult that handles EPPlus packages and return that instead of the FileResult returned by File, eg:

public class EpplusResult:FileResult
{
    public EpplusResult(ExcelPackage package)
        : base("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    {
        if (package == null)
        {
            throw new ArgumentNullException("package");
        }

        Package = package;
    }

    public ExcelPackage Package { get; private set; }

    protected override void WriteFile(HttpResponseBase response)
    {
        // grab chunks of data and write to the output stream
        Stream outputStream = response.OutputStream;
        using (Package)
        {
            Package.SaveAs(outputStream);
        }
    }
}

Which allows you to write the following action:

public FileResult ExportData()
{

    ExcelPackage package = new ExcelPackage();
    var ws = package.Workbook.Worksheets.Add("My Sheet");       
    ...
    ws.Cells[1,1].LoadFromDataTable(table, true, TableStyles.Light1);

    return new EpplusResult(package){FileDownloadName = "SomeFile.xlsx"};
}

这篇关于如何导出到 Excel?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆