怎样根据asp。net的数据集导出excel表格？

Question

怎样根据asp。net的数据集导出excel表格？最好要一个能运行的例子

Answer 1

 /// <summary>
    /// DataTable导出Excel
    /// </summary>
    /// <param name="dtData"></param>
    public static void DataTable2Excel(System.Data.DataTable dtData)
    {
        System.Web.UI.WebControls.DataGrid dgExport = null;
        System.Web.HttpContext curContext = System.Web.HttpContext.Current;
        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 = "";            strWriter = new System.IO.StringWriter();
            htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);            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();
        }
    } 

Answer 2

private void ExportToExcel(System.Data.DataTable dt)     {         System.Text.StringBuilder sb = new System.Text.StringBuilder();         string strExlAddr = ConfigurationManager.AppSettings["ExlAddress"].Trim();                    try         {             sb.Append(" <Script Language=VBScript>");             sb.Append(" <!--\r\n");             sb.Append("dim xls\r\n");             sb.Append("Set xls=CreateObject(\"Excel.Application\")\r\n");             sb.Append("xls.WorkBooks.Open(\"" + strExlAddr + "FCT.xls\")\r\n");             sb.Append("xls.Sheets(1).Select\r\n");             sb.Append("xls.visible=true\r\n");             int r = dt.Rows.Count;             int c = dt.Columns.Count;             for (int i = 0; i < r; i++)             {                 for (int j = 0; j < c - 1; j++)                     sb.Append("xls.Sheets(1).Cells(" + (i + 4) + "," + (j + 1) + ")=\"" + dt.Rows[i][j].ToString().Trim() + "\"\r\n");             }             sb.Append("'xls.ActiveWorkbook.SaveAs(\"C:/Report.xls\")\r\n");             sb.Append("'xls.ActiveWorkbook.Saved=true\n");             sb.Append("'xls.Quit\r\n");             sb.Append("set xls=nothing\r\n");             sb.Append("-->");             sb.Append(" </script>");         }         catch         {             Response.Write(" <script>alert('Error!'); </script>");             return;         }         Page.RegisterClientScriptBlock("", sb.ToString());     }  

Answer 3

public void CreateExcel(DataSet ds, string FileName) 
    { 
        HttpResponse resp; 
        resp = Page.Response; 
        resp.ContentEncoding = System.Text.Encoding.GetEncoding("shift_jis"); 
        resp.ContentType = "application/excel"; 
        resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);         //System.IO.StringWriter stringWrite = new System.IO.StringWriter(); 
        //System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite); 
        //table1.RenderControl(htmlWrite); 
        //resp.Write("Excel出力調査\n"); 
        //resp.Write(stringWrite.ToString()); 
        string iii = " <h1>Excel出力調査 </h1>\n"; 
        resp.Write("\n"); 
        string colHeaders = "", ls_item = ""; 
        //定义表对象与行对象，同时用DataSet对其值进行初始化 
        DataTable dt = ds.Tables[0]; 
        DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的 
        int i = 0; 
        int cl = dt.Columns.Count; 
        //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符 
        for (i = 0; i < cl; i++) 
        { 
            if (i == (cl - 1))//最后一列，加n 
            { 
                colHeaders += dt.Columns[i].Caption.ToString() + "\n"; 
            } 
            else 
            { 
                colHeaders += dt.Columns[i].Caption.ToString() + "\t"; 
            } 
        } 
        resp.Write(colHeaders); 
        //向HTTP输出流中写入取得的数据信息 
        //逐行处理数据　 
        foreach (DataRow row in myRow) 
        { 
            //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据　　 
            for (i = 0; i < cl; i++) 
            { 
                if (i == (cl - 1))//最后一列，加n 
                { 
                    ls_item += row[i].ToString() + "\n"; 
                } 
                else 
                { 
                    ls_item += row[i].ToString() + "\t"; 
                } 
            } 
            resp.Write(ls_item); 
            ls_item = ""; 
        } 
        resp.End(); 
    }

Answer 4

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;
using System.Drawing;
using System.IO;
using System.Text;
/// <summary>
/// Author:匆匆  Blog:http://www.cnblogs.com/huangjianhuakarl/
/// 将Gridview中的数据导出Excel表格
/// </summary>
public partial class GridviewExcel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bind();
        }
    }
    /// <summary>
    /// 数据绑定
    /// </summary>
    public void bind()
    {
        string sqlStr = "select * from Employee";
        DataSet myds = Common.dataSet(sqlStr);
        GridView1.DataSource = myds;
        GridView1.DataKeyNames = new string[] { "ID" };
        GridView1.DataBind();
    }
    /// <summary>
    /// 在 GridView 控件中的某个行被绑定到一个数据记录时发生。此事件通常用于在某个行被绑定到数据时修改该行的内容。
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        foreach (TableCell tc in e.Row.Cells)
        {
            tc.Attributes["style"] = "border-color:Black";
        }
        if (e.Row.RowIndex != -1)
        {
            int id = GridView1.PageIndex * GridView1.PageSize + e.Row.RowIndex + 1;
            e.Row.Cells[0].Text = id.ToString();
        }
    }
    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        Export("application/ms-excel", "Employee information.xls");
    }
    /// <summary>
    /// 定义导出Excel的函数
    /// </summary>
    /// <param name="FileType"></param>
    /// <param name="FileName"></param>
    private void Export(string FileType, string FileName)
    {
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
        Response.ContentType = FileType;
        this.EnableViewState = false;
        StringWriter tw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(tw);
        GridView1.RenderControl(hw);
        Response.Write(tw.ToString());
        Response.End();
    }
    /// <summary>
    /// 此方法必重写，否则会出错
    /// </summary>
    /// <param name="control"></param>
    public override void VerifyRenderingInServerForm(Control control)
    {
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        //Export("application/ms-excel", "Employee.doc");
        Export("application/ms-word", "员工信息.doc");//都可以
    }
}<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridviewExportExcel.aspx.cs" Inherits="GridviewExcel" EnableEventValidation="false"  %><!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>Gridview Excel</title>
    <link href="~/CSS/Gridview.css" type="text/css" rel="Stylesheet" />
</head>
<body>
    <form id="form1" runat="server">
    <div id="container">
        <asp:GridView ID="GridView1" BorderColor="Black" OnRowDataBound="GridView1_RowDataBound"   runat="server" AutoGenerateColumns="False"  Font-Size="12px" Width="530px" AllowSorting="True">
          <Columns>
            <asp:BoundField DataField="EmpID" HeaderText="编号"  />
            <asp:BoundField DataField="EmpRealName" HeaderText="姓名"  />
            <asp:BoundField DataField="EmpSex" HeaderText="性别"  />
            <asp:BoundField DataField="EmpAddress" HeaderText="住址"  />
          </Columns>
          <HeaderStyle BackColor="Azure" Font-Size="12px" HorizontalAlign="Center" />
            <RowStyle HorizontalAlign="Center" />
            <PagerStyle HorizontalAlign="Center" />
        </asp:GridView>
        <asp:Button ID="Button1" runat="server" Text="导 出 EXCEL 表 格" Height="34px" OnClick="Button1_Click" Width="263px" />
        <asp:Button ID="Button2" runat="server" Text="导 出 Word 文 档" Height="34px" OnClick="Button2_Click" Width="263px" /></div>
    </form>
</body>
</html>

Answer 5

GridView导出数据到Excel
GridView导出数据到Excel时格式设置

Answer 6

导出楼上的几位都已经说好了，提示这个函数一定要  
  public override void VerifyRenderingInServerForm(Control control)
    {
        // .NET BUG ,支持导出excel,必须要
    }虽然看见函数体为空，但必须要，可能是.NET BUG

Answer 7

http://hi.baidu.com/dangzhang/blog/item/2e8ca86e6156badf81cb4a95.html

Answer 8

很感谢各位，不过好像不行啊，点了后是下载整个aspx页面的

Answer 9

使用开源控件myxls导出标准excel
你需要下载myxls控件
并引用using GBXls = org.in2bits.MyXls;/// <summary>
        /// 导出excel文件，采用开源组件org.in2bits.MyXls
        /// </summary>
        /// <param name="dt">要导出的数据</param>
        /// <param name="columnName">标题，多标题用半角“,”隔开，如 学号,班级,姓名，与数据表中的列要一一对应</param>
        /// <param name="fileName">文件路径，如：~/excel/school/myexcel.xls</param>
        /// <param name="sheetName">sheet页的名称，如：学生信息</param>
        public static void ExportExcelByMyXls(MyDataTable.DataTable dt, string columnName, string fileName, string sheetName)
        {
            GBXls.XlsDocument xlsdocument = new org.in2bits.MyXls.XlsDocument();
            GBXls.Workbook workbook = xlsdocument.Workbook; ;
            GBXls.Worksheets worksheets = workbook.Worksheets;
            string[] columnNameList = columnName.Split(',');
            int rowsCount = dt.Rows.Count;
            int columnCount = dt.Columns.Count;
            int sheetSize = 50000;//定义每个sheet页容量大小
            if (rowsCount > sheetSize)
            {
                int x = 1, y = 1, z = 0;
                for (int k = sheetSize; k < rowsCount; k = k + sheetSize)
                {
                    GBXls.Worksheet worksheet = worksheets.Add(sheetName + x.ToString());
                    GBXls.Cells cells = worksheet.Cells;
                    for (int i = 1; i <= columnNameList.Length; i++)
                    {
                        cells.Add(1, i, columnNameList[i - 1].ToString());
                    }                    for (int i = k - sheetSize + 2; i <= k + 1; i++)
                    {
                        y++;
                        for (int j = 1; j <= columnCount; j++)
                        {
                            cells.Add(y, j, dt.Rows[i - 2][j - 1].ToString());
                        }
                    }
                    x++;
                    y = 1;
                    z = k;//当k不再循环时的k值
                }
                if (rowsCount - z > 0)//如果还有剩余，则再添加一个sheet
                {
                    int w = rowsCount - z;
                    GBXls.Worksheet worksheet = worksheets.Add(sheetName + x.ToString());
                    GBXls.Cells cells = worksheet.Cells;
                    for (int i = 1; i <= columnNameList.Length; i++)
                    {
                        cells.Add(1, i, columnNameList[i - 1].ToString());
                    }
                    for (int i = z + 2; i <= rowsCount + 1; i++)
                    {
                        y++;
                        for (int j = 1; j <= columnCount; j++)
                        {
                            cells.Add(y, j, dt.Rows[i - 2][j - 1].ToString());
                        }
                    }
                }
            }
            else
            {
                GBXls.Worksheet worksheet = worksheets.Add(sheetName);
                GBXls.Cells cells = worksheet.Cells;
                for (int i = 1; i <= columnNameList.Length; i++)
                {
                    cells.Add(1, i, columnNameList[i - 1].ToString());
                }
                for (int i = 2; i <= rowsCount + 1; i++)
                {
                    for (int j = 1; j <= columnCount; j++)
                    {
                        cells.Add(i, j, dt.Rows[i - 2][j - 1].ToString());
                    }
                }
                //worksheet.Write(dt, 1, 1);
            }
            xlsdocument.FileName = Utility.ConvertToPhysicalPath(fileName);
            dt.Dispose();
            Utility.CreateDirectory(fileName);
            xlsdocument.Save(true);
            xlsdocument.Send(GBXls.XlsDocument.SendMethods.Attachment);
        }

Answer 10

    private void btnGetExcel_Click(object sender, EventArgs e) //导出Excel
    {
        DataSet ds = op.GetDataSet();
        DataTable dt = ds.Tables[0];
        DataTable2Excel(dt);
        Reponse.Write("<script>alert('导出成功！')</script>");
    }    /// <summary>
    /// DataTable导出Excel
    /// </summary>
    /// <param name="dtData"></param>
    public static void DataTable2Excel(System.Data.DataTable dtData)
    {
        System.Web.UI.WebControls.DataGrid dgExport = null;
        System.Web.HttpContext curContext = System.Web.HttpContext.Current;
        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 = "";            strWriter = new System.IO.StringWriter();
            htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);            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();
        }
    }