下载带有ClosedXML的文件

本文关键字：文件 ClosedXML 下载 | 更新日期: 2023-09-27 17:49:18

所有

我如何下载一个文件，让用户看到它正在下载(就像流一样？(

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

下面的方法效果很好，但我必须创建自己的excel文件，它是基于HTML的，而且文件变得太大了，当我使用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();

感谢

下载带有ClosedXML的文件

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();

旧线程，但我无法让公认的解决方案正常工作。经过更多的搜索，我发现了这个，它对我来说非常有效：

        // 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();

下载可以做得更简单、更短，所以控制器中的完整操作可能是这样的——下载部分只有一行，而不是七到十行

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

如果使用Asp。NetMVC，基本上是一样的，但稍微整洁一点(好吧，我是这么认为的：((。

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

如果您使用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");
   }
 }

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

//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();
}
相关文章：