如何把GridView模板中的内容导入到Excel

linjiangxian11 2012-08-09 08:27:33

以下是把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

...全文

149 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

net5354 2012-12-13

打赏
举报

引用 2 楼 linjiangxian11 的回复:

谢谢您的提示，这个办法更好！能否给个具体的例子呢？我还不会把Gridview的数据源Sqldatasource直接导入到Excel!

敬请见谅，很久没有上论坛就是循环数据集，导出（或称为插入）excel

linjiangxian11 2012-08-10

打赏
举报

谢谢您的回复！但是我已经用了SqlDatasource了，并通过各种控件来实现Gridview的显示、更新、删除等功能，再转换成datatable实在是有点从头再来的感觉！
我还是希望用我1楼的方法，那个错误应该不难解决，但我就是不知道该怎么改动。。。

hzsasheng 2012-08-10

打赏
举报



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

        }

    }