ASP NET - Funções de Acesso ao Excel
Importante 1 : É necessário que o Excel já esteja instalado para esta função funcionar.
Importante 2 : Cuidado com o Idioma do Web Server(Server Side) e do Excel (Client Side).
Exportar para o Excel
Imports System
Imports System.Data
Imports System.Configuration
Imports System.IO
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Public Class Excel
Inherits System.Web.UI.Page
Public Sub ExporttoExcel(table As DataTable)
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.ClearContent()
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response.Buffer = True
HttpContext.Current.Response.ContentType = "application/ms-excel"
HttpContext.Current.Response.Write("<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">")
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachmentfilename=pminimo.xls")
HttpContext.Current.Response.Charset = "utf-8"
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250")
'sets font
HttpContext.Current.Response.Write("<font style='font-size:10.0pt font-family:Calibri'>")
HttpContext.Current.Response.Write("<BR><BR><BR>")
'sets the table border, cell spacing, border color, font of the text, background, foreground, font height
HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:10.0pt font-family:Calibri background:white'> <TR>")
'am getting my grid's column headers
Dim columnscount As Integer = table.Columns.Count ' GVlistaprecos_Prod_Cli.Columns.Count
Dim j As Integer
For j = 0 To j < columnscount
'write in New column
HttpContext.Current.Response.Write("<Td>")
'Get column headers And make it as bold in excel columns
HttpContext.Current.Response.Write("<B>")
HttpContext.Current.Response.Write(table.Columns(j).ColumnName.ToString()) ' HeaderText.ToString())
HttpContext.Current.Response.Write("</B>")
HttpContext.Current.Response.Write("</Td>")
Next
HttpContext.Current.Response.Write("</TR>")
For Each row As DataRow In table.Rows
'write in New row
HttpContext.Current.Response.Write("<TR>")
Dim i As Integer
For i = 0 To i < table.Columns.Count
HttpContext.Current.Response.Write("<Td>")
HttpContext.Current.Response.Write(row(i).ToString())
HttpContext.Current.Response.Write("</Td>")
Next
HttpContext.Current.Response.Write("</TR>")
Next
HttpContext.Current.Response.Write("</Table>")
HttpContext.Current.Response.Write("</font>")
HttpContext.Current.Response.Flush()
HttpContext.Current.Response.End()
End Sub
'Public Sub ExportarGV(fileName As String, gv As GridView)
' HttpContext.Current.Response.Clear()
' HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment filename={0}", fileName))
' HttpContext.Current.Response.ContentType = "application/ms-excel"
' 'Using (StringWriter sw = New StringWriter())
' 'Using (HtmlTextWriter htw = New HtmlTextWriter(sw))
' Dim sw = New StringWriter()
' Dim htw = New HtmlTextWriter(sw)
' ' Create a table to contain the grid
' Dim tbl As New Table()
' ' include the gridline settings
' tbl.GridLines = gv.GridLines
' ' add the header row to the table
' If Not IsDBNull(gv.HeaderRow) Then
' PrepararControleParaExportacao(gv.HeaderRow)
' tbl.Rows.Add(gv.HeaderRow)
' End If
' Dim row As GridViewRow
' ' add each of the data rows to the table
' For Each row In gv.Rows
' PrepararControleParaExportacao(row)
' tbl.Rows.Add(row)
' Next
' ' add the footer row to the table
' If Not IsDBNull(gv.FooterRow) Then
' PrepararControleParaExportacao(gv.FooterRow)
' tbl.Rows.Add(gv.FooterRow)
' End If
' ' render the table into the htmlwriter
' tbl.RenderControl(htw)
' ' render the htmlwriter into the response
' HttpContext.Current.Response.Write(sw.ToString())
' HttpContext.Current.Response.End()
'End Sub
' Replace any of the contained controls with literals
'Public Sub PrepararControleParaExportacao(Controle As Control)
' Dim i As Integer
' For i = 0 To i < Controle.Controls.Count
' Dim corrente As Control = Controle.Controls(i)
' If (corrente.GetType().ToString.Equals("System.Web.UI.WebControls.LinkButton") Then
' Controle.Controls.Remove(corrente)
' Controle.Controls.AddAt(i, New LiteralControl((corrente as LinkButton).Text))
' ElseIf corrente.GetType().ToString.Equals("System.Web.UI.WebControls.ImageButton") Then
' Controle.Controls.Remove(corrente)
' Controle.Controls.AddAt(i, New LiteralControl((corrente as ImageButton).AlternateText))
' ElseIf corrente.GetType().ToString.Equals("System.Web.UI.WebControls.HyperLink") Then
' Controle.Controls.Remove(corrente)
' Controle.Controls.AddAt(i, New LiteralControl((corrente as HyperLink).Text))
' ElseIf corrente.GetType().ToString.Equals("System.Web.UI.WebControls.DropDownList") Then
' Controle.Controls.Remove(corrente)
' Controle.Controls.AddAt(i, New LiteralControl((corrente as DropDownList).SelectedItem.Text))
' ElseIf corrente.GetType().ToString.Equals("System.Web.UI.WebControls.CheckBox") Then
' Controle.Controls.Remove(corrente)
' Controle.Controls.AddAt(i, New LiteralControl((corrente as CheckBox).Checked ? "True" : "False"))
' End If
' If (corrente.HasControls()) Then
' PrepararControleParaExportacao(corrente)
' End If
' Next
'End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
End Class