將GridView內的資料匯出成Excel檔

JDP · 發表於 2011-1-7 14:30:50

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class _Default : System.Web.UI.Page
{
protected void Button2_Click(object sender, EventArgs e)
{
Response.ClearContent();
Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
string excelFileName = "測試Excel檔案.xls";
Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode(excelFileName));
Response.ContentType = "application/excel";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
// '處理'GridView' 的控制項 'GridView' 必須置於有 runat=server 的表單標記之中
}
}

複製代碼

當用GridView導出Execl的時候，會發生只能在執行 Render() 的過程中調用 RegisterForEventValidation的錯誤提示。
有兩種方法可以解決以上問題：
1.修改web.config(不推薦)<pages enableEventValidation ="false" ></pages>
2.直接在導出Execl的頁面修改
<%@ Page Language="C#" EnableEventValidation = "false" AutoEventWireup="true"
CodeFile="ExportGridView.aspx.cs" Inherits="ExportGridView" %>

Reference:
http://www.dotblogs.com.tw/chou/archive/2009/04/03/7831.aspx
http://www.cnblogs.com/xucanzhao/archive/2006/04/29/388576.html
http://blog.xuite.net/tolarku/blog/28080185
http://www.codedigest.com/Articl ... Table_to_Excel.aspx

JDP · 發表於 2011-1-10 08:31:52

Sub npoi_excel(ByVal excel_array As Array, ByVal filename As String, ByVal size As Integer) 'NPOI_Excel 多工作表匯出
'----載入函示庫
'Imports NPOI.HSSF.UserModel
'Imports System.IO
'----叫用方式----
'Dim excel_array(,) As String = {{"測試囉", "gridview1", "工作表的標題欄", "備註1"}, {"測試囉222", "gridview2", "工作表的標題欄222", "備註2"}, {"測試囉33333", "gridview1", "工作表的標題欄33333", "備註3"}}
'npoi_excel(excel_array, "file_name.xls", 3) '資料陣列、檔名、Gridview 數量
'----------------------
Dim i, j, m As Integer '資料陣列、檔名、Gridview 數量
Dim gv(size) As GridView
Dim workbook As New HSSFWorkbook '使用 NPOI 函示庫要載入 Imports NPOI.HSSF.UserModel
Dim ms As New MemoryStream
Dim mysheet(size + 1) As HSSFSheet '定義工作頁
Dim up_row As Integer
Dim up_column As Integer '記錄 Gridview 的 Row 與 Column 上限
Dim region As NPOI.HSSF.Util.Region
For i = 0 To size - 1
mysheet(i) = workbook.CreateSheet(excel_array(i, 0))
mysheet(i).DisplayGridlines = True '宣告要畫出 Excel 格線，預設值就是 True
mysheet(i).IsPrintGridlines = True '定義 Excel 印出時要印出框線
gv(i) = New GridView
gv(i) = CType(FindControl(excel_array(i, 1)), GridView) '取得對應的 Gridview 物件
up_row = gv(i).Rows.Count - 1 '抓取Gridview 的欄位數 column、筆數 row
up_column = gv(i).Columns.Count - 1 '從0開始，所以要減1
region = New NPOI.HSSF.Util.Region(0, 0, 0, up_column) '合併儲存格，依欄位數
mysheet(i).AddMergedRegion(region)
mysheet(i).CreateRow(0).CreateCell(0).SetCellValue(excel_array(i, 2)) '印 Caption
'gv(i).Caption 其實也可以直接抓取 Gridview 中所定義的 Caption
For j = 0 To up_column '印出對應的 Gridview 欄位名稱
mysheet(i).CreateRow(1).CreateCell(j).SetCellValue(gv(i).HeaderRow.Cells(j).Text)
Next
For m = 0 To up_row '---印出資料
For j = 0 To up_column
mysheet(i).CreateRow(m + 2).CreateCell(j).SetCellValue(gv(i).Rows(m).Cells(j).Text)
Next
Next
'結尾提示文字，可以用以表現資料產出時間
mysheet(i).CreateRow(up_row + 4).CreateCell(0).SetCellValue("資料產出時間：" & Now.ToString) '時間記錄
mysheet(i).CreateRow(up_row + 5).CreateCell(0).SetCellValue(excel_array(i, 3)) '備註
Next 'For i = 0 To k
'---- 匯入 asp TABEL 的方式 ---
'mysheet(size) = workbook.CreateSheet("La La test")
'Dim tb1x As Table = CType(FindControl(Table1.ID), Table) '用 findcontrol 抓取 asp table 的元件內容
'For m = 0 To tb1x.Rows.Count - 1
' For j = 0 To tb1x.Rows(m).Cells.Count - 1
' mysheet(size).CreateRow(m).CreateCell(j).SetCellValue(tb1x.Rows(m).Cells(j).Text)
' Next
'Next
'====匯入ASP Table ====
'-----手編 Table ---
'Dim pp As Integer = size
'Dim rsp As Integer =0 '用來做上下資料列印位置移動
'Dim csp As Integer =0 '用來做左右資料列印位置移動
'mysheet(pp) = workbook.CreateSheet("統計分析")
'mysheet(pp).DisplayGridlines = True
'mysheet(pp).IsPrintGridlines = True
'region = New NPOI.HSSF.Util.Region(0, 0, 1, 1) '合併儲存格
'mysheet(pp).AddMergedRegion(region)
'mysheet(pp).CreateRow(rsp+0).CreateCell(csp + 0).SetCellValue("成員歸類")
'region = New NPOI.HSSF.Util.Region(0, 2, 0, 3) '合併儲存格
'mysheet(pp).AddMergedRegion(region)
'mysheet(pp).CreateRow(rsp+0).CreateCell(csp + 2).SetCellValue("人數")
'mysheet(pp).CreateRow(rsp+1).CreateCell(csp + 2).SetCellValue("主管")
'mysheet(pp).CreateRow(rsp+1).CreateCell(csp + 3).SetCellValue("工程師")
'region = New NPOI.HSSF.Util.Region(2, 0, 7, 0) '合併儲存格
'mysheet(pp).AddMergedRegion(region)
'mysheet(pp).CreateRow(rsp+2).CreateCell(csp + 0).SetCellValue("系統管理者")
'mysheet(pp).CreateRow(rsp+2).CreateCell(csp + 1).SetCellValue("程式設計師")
'mysheet(i).CreateRow(rsp + up_row + 4).CreateCell(csp + 0).SetCellValue("資料產出時間：" & Now.ToString) '時間記錄
'mysheet(i).CreateRow(rsp + up_row + 5).CreateCell(csp + 0).SetCellValue(excel_array(i, 3)) '備註
'=====手編====
workbook.Write(ms)
Response.AddHeader("Content-Disposition", String.Format("attachment; filename=" & filename))
Response.BinaryWrite(ms.ToArray)
ms.Close()
ms.Dispose()
End Sub

複製代碼

Reference: http://blog.xuite.net/tolarku/blog/30630114?ref=rel

		自動登錄	找回密碼
密碼			註冊

將GridView內的資料匯出成Excel檔

ASP.NET - NPOI Excel 匯出 - 多工作表 Sheets