Export DataTable in Excel

Un'altra funzione che spesso ci viene chiesta da un cliente è quella di esportare dati da un'applicazione web in formato excel.
Supponendo di avere questi dati in una DataTable:

Dim dt As DataTable = New DataTable
    dt.Columns.Add("Id", GetType(Integer))
    dt.Columns.Add("Descrizione", GetType(String))
    dt.Columns.Add("Data", GetType(DateTime))
    Dim row As DataRow

    Dim i As Integer = 0
    For i = 0 To 10
      row = dt.NewRow()
      row("Id") = i
      row("Descrizione") = "Descrizione: " + i.ToString()
      row("Data") = DateTime.Now.AddDays(i)
      dt.Rows.Add(row)
    Next

l'esportazione in excel, generando un html text:

Response.AddHeader("content-disposition", "attachment;filename=TestExportInHtml.xls")
    Response.Charset = String.Empty
    Response.ContentType = "application/vnd.xls"
    Dim stringWrite As System.IO.StringWriter = New System.IO.StringWriter
    Dim htmlWrite As System.Web.UI.Html32TextWriter = New System.Web.UI.Html32TextWriter(stringWrite)
    Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder

    sb.Append("<table border=""1"">")
    For Each rowInsert As DataRow In dt.Rows
      sb.Append("<tr>")
      For Each colInsert As DataColumn In dt.Columns
        sb.Append("<td>" + rowInsert(colInsert).ToString() + "</td>")
      Next
      sb.Append("</tr>")
    Next
    sb.Append("</table")
    htmlWrite.Write(sb)
    Response.Write(stringWrite.ToString())
    Response.End()

oppure si può generare un file .csv:

Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder

    For Each rowInsert As DataRow In dt.Rows
      For Each col As DataColumn In dt.Columns
        sb.Append(rowInsert(col).ToString() + ";")
      Next
      sb.Append(vbCrLf)
    Next

    Response.ContentType = "text/csv"
    Response.AppendHeader("content-disposition", "attachment; filename=TestExportInCsv.csv")
    Response.Write(sb.ToString())
    Response.End()