如何把GridView模板中的内容导入到Excel
本帖最后由 linjiangxian11 于 2012-08-09 20:33:47 编辑 以下是把GridView的数据导入到Excel表的代码，有点特别的是，这个GridView中大量使用了模板。因此，必须先要判断模板的类型，再把模板的内容写到str字段里，最后写到Excel对应的Cell中。为了方便测试，我把模板都做成TextBox，可是每次运行到 if (GridView_Search.Controls[j].GetType() == typeof(TextBox)) 这一句时，就会出现异常。
我尝试把GridView_Search.Controls[j].GetType()改成GridView_Search.Columns[j].GetType(),但还是读取不到数据，我到底错在哪里了？请各位帮忙看看，谢谢！
-----------------------前台----------------------
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
width: 100%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellpadding="0" cellspacing="0" class="style1">
<tr>
<td>
<asp:Button ID="Button_SQLToExcel" runat="server"
onclick="Button_SQLToExcel_Click" Text="导入到Excel表" /></td>
</tr>
<tr>
<td>
<asp:GridView ID="GridView_Search" runat="server" AutoGenerateColumns="False"
CellPadding="0" DataSourceID="SqlDataSource1">
<Columns>
<asp:TemplateField HeaderText="SupplierID" SortExpression="SupplierID">
<ItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("SupplierID") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="CategoryID" SortExpression="CategoryID">
<ItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("CategoryID") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="QuantityPerUnit"
SortExpression="QuantityPerUnit">
<ItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("QuantityPerUnit") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT [SupplierID], [CategoryID], [QuantityPerUnit] FROM [Alphabetical list of products]">
</asp:SqlDataSource>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
-------------------------后台-------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web.Security;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using Word = Microsoft.Office.Interop.Word;
using System.Threading;
using office = Microsoft.Office.Core;
using System.Reflection;
using System.IO;
using System.Text.RegularExpressions;
using System.Text;
public partial class Default3 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button_SQLToExcel_Click(object sender, EventArgs e)
{
if (GridView_Search.Rows.Count == 0)
return;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = true;
for (int i = 0; i < GridView_Search.Columns.Count; i++)
{
excel.Cells[1, i + 1] = GridView_Search.Columns[i].HeaderText;
}
for (int i = 0; i < GridView_Search.Rows.Count; i++)
{
for (int j = 0; j < GridView_Search.Columns.Count; j++)
{
//if (GridView_Search.Columns[j].GetType() == typeof(TextBox))//这样写不会出现异常，但也是只有Excel的标题行，内容都没有
//Response.Write(GridView_Search.Columns[j].GetType());//得到的是System.Web.UI.WebControls.TemplateField
if (GridView_Search.Controls[j].GetType() == typeof(TextBox))
{
string str = (GridView_Search.Controls[j] as TextBox).Text;
excel.Cells[i + 2, j + 1] = str;
}
else
{
excel.Cells[i + 2, j + 1] = GridView_Search.Rows[i].Cells[j].Text;
}
//if (GridView_Search.Controls[j].GetType() == typeof(TextBox))
//{
// string str = (GridView_Search.Controls[j] as TextBox).Text;
// excel.Cells[i + 2, j + 1] = str;
//}
//else
//{
// excel.Cells[i + 2, j + 1] = GridView_Search.Rows[i].Cells[j].Text;
//}
}
}
}
}
-----------------------图1---------------------
如果看不到图片，错误提示为“指定的参数已超出有效值的范围。参数名：index”
图片的地址 http://my.csdn.net/my/album/detail/1246413#1246413
[img=http://my.csdn.net/my/album/detail/1246413#1246413][/img]
[解决办法]
把GridView的数据绑定的数据集的内容导入EXCEL就可以了
[解决办法]
public static void DataTable2Excel(System.Data.DataTable dtData)
{
System.Web.UI.WebControls.DataGrid dgExport = null;
// 当前对话
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// IO用于导出并返回excel文件
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
if (dtData != null)
{
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
curContext.Response.Charset = "";
// 导出excel文件
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
// 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dtData.DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
// 返回客户端
dgExport.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
curContext.Response.End();
}
}
直接用DataTable来绑定会比较方便