使用ClosedXML下载文件

Question

使用ClosedXML下载文件

c#asp.netclosedxml

30

所有

如何下载文件，以便用户看到正在下载的过程（像使用流一样）？

我目前正在使用ClosedXML，但如果我使用SaveAs方法，我必须提供一个硬编码的URL，如果我只给出文件名，它不会自动下载到下载文件夹。

下面的方法非常有效，但是我必须创建自己的基于HTML的Excel文件，当我使用ClosedXML时，文件大小仅为以下代码的50％左右，而且文件增长得比应该快得多：

但是，下载行为就是我想要的。

是否有一种方法可以将以下代码转换为我可以将“工作簿”作为对象提供，并且它只需下载此工作簿的方式呢？

HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls");
HttpContext.Current.Response.Charset ="UTF-8";    
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.Default;
HttpContext.Current.Response.ContentType = "application/ms-excel";
ctl.Page.EnableViewState =false;   
System.IO.StringWriter  tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();

谢谢

- Tjekkles

7个回答

17

虽然这是一个旧帖，但我无法完全理解已接受的解决方案。进一步搜索后发现了这个方法，对我来说非常有效：

        // Create the workbook
        XLWorkbook workbook = new XLWorkbook();
        workbook.Worksheets.Add("Sample").Cell(1, 1).SetValue("Hello World");

        // Prepare the response
        HttpResponse httpResponse = Response;
        httpResponse.Clear();
        httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        httpResponse.AddHeader("content-disposition", "attachment;filename=\"HelloWorld.xlsx\"");

        // Flush the workbook to the Response.OutputStream
        using (MemoryStream memoryStream = new MemoryStream())
        {
            workbook.SaveAs(memoryStream);
            memoryStream.WriteTo(httpResponse.OutputStream);
            memoryStream.Close();
        }

        httpResponse.End();

- Stanton

2

是的，这个帖子还是比较老的，但是我认为这个答案是最好的。仍然很有用。 - User970008

1

这看起来像是项目维基上的示例--https://github.com/closedxml/closedxml/wiki/Deliver-an-Excel-file-in-ASP.NET - jjk

10

下载可以更加简单和简洁，所以你的控制器中完整的操作可能看起来像这样-下载部分只需要一行代码，而不是七到十行。

public ActionResult XLSX()
{
    System.IO.Stream spreadsheetStream = new System.IO.MemoryStream();
    XLWorkbook workbook = new XLWorkbook();
    IXLWorksheet worksheet = workbook.Worksheets.Add("example");
    worksheet.Cell(1, 1).SetValue("example");
    workbook.SaveAs(spreadsheetStream);
    spreadsheetStream.Position = 0;

    return new FileStreamResult(spreadsheetStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { FileDownloadName = "example.xlsx" };
}

- outofmind

1

如果使用Asp.Net MVC，基本上是一样的，但稍微更整洁（至少我认为是这样的 :)）。

public ActionResult DownloadFile(XXXModel model)
{
    using (var workbook = new XLWorkbook(XLEventTracking.Disabled))
    {
        // create worksheets etc..

        // return 
        using (var stream = new MemoryStream())
        {
            workbook.SaveAs(stream);
            stream.Flush();

            return new FileContentResult(stream.ToArray(),
                   "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                   {
                       FileDownloadName = "XXXName.xlsx"
                   };
        }
    }

- Phil

嗨，我正在使用ASP.NET Core API和Angular。Angular在接收响应时出现错误，HTTP解析失败。 - MindRoasterMir

1

如果您正在使用MVC，只需按照以下方式使用File()方法：

using (XLWorkbook wb = new XLWorkbook())
 {
  //here you put your data in the WorkBook
  //then create a Memory Stream object
using (MemoryStream stream = new MemoryStream())
  {
   //save the workbook as a MemoryStream
     wb.SaveAs(stream);

   //use the File method to return a File
   return File(stream.ToArray(), "filetype","fileName.extension");
   }
 }

- antoprd

嗨，如何在前端Angular中处理请求？我在<api-url>处解析时遇到了HTTP失败。 - MindRoasterMir

-1

//Method for Export Excel using Closed Xml
public void ExportDataWithClosedXml_Method(DataTable table, string tabName, string fileType)
{
    var workbook = new XLWorkbook();
    var ws = workbook.Worksheets.Add(table, tabName);
    int row = 2 + table.Rows.Count;
    int col = table.Columns.Count;
    var redRow = ws.Row(1);
    //redRow.Style.Fill.BackgroundColor = XLColor.Red;
    redRow.InsertRowsAbove(1);
    ws.Cell(1, 1).Value = "Name of Report Type";
    ws.Cell(1, 1).Style.Font.Bold = true;
    ws.Table(0).ShowAutoFilter = false;
    //ws.Row(2).Style.Fill.BackgroundColor = XLColor.Red;
    ws.Range(2, 1, 2, col).Style.Fill.BackgroundColor = XLColor.Green;
    ws.Range(2, 1, 2, col).Style.Font.Bold = true;
    ws.Range(3, 1, row, col).Style.Font.Italic = true;

    HttpContext.Current.Response.Clear();
    using (MemoryStream memoryStream = new MemoryStream())
    {
        workbook.SaveAs(memoryStream);
        memoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
        memoryStream.Close();
    }
    if (fileType == "xlsx")
    {
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=\"Samplefile.xlsx\"");
    }
    else
    {
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=\"Samplefile.xls\"");
    }
    HttpContext.Current.Response.End();
}

- Shubhanshu

复制从开始到视图的所有代码 ----------------------------------@using (Html.BeginForm("ExportDataWithClosedXml", "Client", FormMethod.Post, new { enctype = "multipart/form-data" })) { <table> <tr> <td></td> <td><input type="submit" name="ExportDataWithClosedXml" id="ExportDataWithClosedXml" value="ExportDataWithClosedXml" /></td> <td><input type="hidden" id="myHiddenId" runat="server" /></td> </tr>

    
     }--------------- "ExportDataWithClosedXml" 是控制器名称。

- Shubhanshu

-1

public ActionResult SendFile()
{
    // Create the workbook
    XLWorkbook workbook = new XLWorkbook();
    workbook.Worksheets.Add("Sample").Cell(1, 1).SetValue("Hello World");

    // Send the file
    MemoryStream excelStream = new MemoryStream();
    workbook.SaveAs(excelStream);
    excelStream.Position = 0;
    return File(excelStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "MyFileName.xlsx");
}

- rmonfort

你需要解释为什么这个方法有效，这样才能算是一个好的答案。 - user5416120

使用这个解决方案时，我遇到了编码问题。 - Emanuele

antoprd有相同的答案。你的答案有什么帮助？ - MindRoasterMir

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Raidri · Accepted Answer

SaveAs()方法支持流，因此为了将ClosedXml工作簿作为流获取，我使用：

public Stream GetStream(XLWorkbook excelWorkbook)
{
    Stream fs = new MemoryStream();
    excelWorkbook.SaveAs(fs);
    fs.Position = 0;
    return fs;
}

然后进行文件下载：

string myName = Server.UrlEncode(ReportName + "_" + DateTime.Now.ToShortDateString() + ".xlsx");
MemoryStream stream = GetStream(ExcelWorkbook);

Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + myName);
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(stream.ToArray());
Response.End();