Friday, September 14, 2007

Export DataTable in Excel

. Friday, September 14, 2007

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

0 commenti:

Post a Comment