GridViewを使用してEXCEL出力を行う方法の開発備忘録です。

たまに、EXCEL出力をしたいと要望が出ます。
そんな時の便利なやり方です。

Dim dt As DataTable = New XXXXXDataTable
Dim da As New YYYYYTableAdapters.XXXXXTableAdapter

da.Fill(dt)
Me.GridView.DataSource = dt
Me.GridView.DataBind()

Dim i As Integer
‘フォーマットを指定する場合
For i = 0 To Me.GridView3.Rows.Count – 1
Me.GridView3.Rows(i).Cells(1).Style.Add(“mso-number-format”, “\@”)
Me.GridView3.Rows(i).Cells(2).Style.Add(“mso-number-format”, “\@”)
Me.GridView3.Rows(i).Cells(3).Style.Add(“mso-number-format”, “\@”)
Next

‘ Set the content type to Excel.
Response.AddHeader(“content-disposition”, “attachment; filename=XXXXX_” & Now.ToString(“yyyyMMddhh”) & “.xls”)
Response.ContentType = “application/vnd.ms-excel”
Response.Charset = “”
‘ Turn off the view state.
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
‘ Get the HTML for the control.
Me.GridView.RenderControl(hw)
‘ Write the HTML back to the browser.
Response.Write(tw.ToString())
‘ End the response.
Response.End()

と、まぁ、こんな感じでできまます。