Winforms DataTable Convert to Excel

Question

In asp.net, it is quite easy to convert a datatable to an excel file. How do I do the same for datatables in winforms?

For Example: in my asp.net code, here is my function to convert the datatable to excel:

Public Shared Sub DataTableToExcel(ByVal dt As DataTable, ByVal FileName As String
  HttpContext.Current.Response.Clear()
  HttpContext.Current.Response.Write(Environment.NewLine)
  For Each row As DataRow In dt.Rows
    For i As Integer = 0 To dt.Columns.Count - 1
      HttpContext.Current.Response.Write(row(i).ToString().Replace(";", String.Empty) + ";")
    Next

    HttpContext.Current.Response.Write(Environment.NewLine)
  Next

  HttpContext.Current.Response.ContentType = "application/ms-excel"
  HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName + ".xls")
  HttpContext.Current.Response.[End]()
End Sub

But in winforms, you cannot use the same as it is. You need to read out the datatable and create/open the excel workbook.

I wish there is a way to directly convert datatable used in winforms to excel fast.

Thanks.

Answer 1

All this code is really doing is creating a CSV file (with semicolons instead of commas) and telling Excel to open it. You could do something similar in C# with the following code. Note that 'FileName' must include an extension that Excel is associated with.

Public Shared Sub DataTableToExcel(ByVal dt As DataTable, ByVal FileName As String)
     
    StreamWriter writer = new StreamWriter(FileName)
    
    For Each row As DataRow In dt.Rows
        For i As Integer = 0 To dt.Columns.Count - 1
          writer.Write(row(i).ToString().Replace(";", String.Empty) + ";")
        Next
    
        writer.WriteLine()
    Next
    
    writer.Close()
    
    System.Diagnostics.Process.Start(FileName)

End Sub