vb.net 如何仅使用标准 .Net 而不是 3rd 方 dll 仅将数据表导出到 Excel？

How do I export only a datatable to Excel using only standard .Net and not a 3rd party dll?

提问by huffmaster

I know this has been answered on here and a thousand other sites on the web, but every single one I've tried does not work as I want it to. I want to export ONLY a datatable, not the page I'm running the code from, and I want to do this without having to download a 3rd party dll. Every piece of code I've tried ends up exporting the page I'm running it from. Here's the current iteration I'm using...

我知道这已经在这里和网络上的一千个其他网站上得到了回答，但是我尝试过的每一个都没有按我想要的那样工作。我只想导出一个数据表，而不是我运行代码的页面，我想这样做而不必下载第 3 方 dll。我尝试过的每一段代码最终都会导出我运行它的页面。这是我正在使用的当前迭代...

Private Sub ExporttoExcel(table As DataTable)
  Dim attachment As String = "attachment; filename=file.xls"
  Response.ClearContent()
  Response.AddHeader("content-disposition", attachment)
  Response.ContentType = "application/vnd.ms-excel"
  Dim tab As String = ""

  For Each dc As DataColumn In table.Columns
    Response.Write(tab + dc.ColumnName)
    tab = vbTab
  Next

  Response.Write(vbLf)

  Dim i As Integer
  For Each dr As DataRow In table.Rows
    tab = ""
    For i = 0 To table.Columns.Count - 1
      Response.Write(tab & dr(i).ToString())
      tab = vbTab
    Next
    Response.Write(vbLf)
  Next
  Response.End()
End Sub

Can anyone explain why this doesn't work or how I can export ONLY the datatable into Excel?

谁能解释为什么这不起作用或我如何仅将数据表导出到 Excel 中？

采纳答案by Stefan Steiger

The only way to do this without some kind of 3rd party dll is to write a csv file.

在没有某种 3rd 方 dll 的情况下执行此操作的唯一方法是编写一个 csv 文件。

Columns separated by semicolon, rows separated by /n

列以分号分隔，行以 /n 分隔

回答by Paritosh

I usually do this and it has worked for me (ds is a DataSet which has the data I want to push to excel):

我通常这样做并且它对我有用（ds 是一个 DataSet，其中包含我想要推送到 excel 的数据）：

            HttpResponse response = HttpContext.Current.Response;

        // first let's clean up the response.object
        response.Clear();
        response.Charset = "";

        string filename = "TEMP/ex1.xls";

        // set the response mime type for excel
        response.ContentType = "application/vnd.ms-excel";
        response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");

        // create a string writer
        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                // instantiate a datagrid
                DataGrid dg = new DataGrid();
                dg.DataSource = ds.Tables[0];
                dg.DataBind();

                dg.RenderControl(htw);
                response.Write(sw.ToString());
                response.End();
            }
        }

回答by Fredou

this should work with EPPlus

这应该适用于 EPPlus

Public Shared Function ExportToExcel(FileName As String, SheetName As String, data As DataTable) As Boolean
    Dim pck As ExcelPackage
    pck = New ExcelPackage()

    Try

        Dim ws = pck.Workbook.Worksheets.Add(SheetName)
        ws.Cells("A1").LoadFromDataTable(data, True)
        Dim excel = pck.GetAsByteArray()

        HttpContext.Current.Response.ClearHeaders()
        HttpContext.Current.Response.ClearContent()
        HttpContext.Current.Response.Clear() 'really clear it :-p
        HttpContext.Current.Response.BufferOutput = False
        HttpContext.Current.Response.ContentType = "application/octet-stream"
        HttpContext.Current.Response.AddHeader("cache-control", "max-age=0")
        HttpContext.Current.Response.AddHeader("Pragma", "public")
        HttpContext.Current.Response.AddHeader("Content-Length", excel.Length.ToString())
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;  filename=""" & FileName & ".xlsx""")

        HttpContext.Current.Response.BinaryWrite(excel)

        HttpContext.Current.Response.[End]()

        Return True
    Catch
        Return False
    Finally
        pck.Dispose()
    End Try
End Function