DataGrid导出Excel

一. 安装和引用OWC组件的步骤.

1.在服务器上安装MSOWC.MSI(就在office2000的安装盘上)。
2. 添加OWC引用:
在使用OWC之前,首先必须将OWC的引用加入到"解决方案资源管理器"中。具体步骤如下:打开"解决方案资源管理器"面板,鼠标右键单击"引用",选择 "添加引用"菜单,在弹出的"添加引用"对话框中单击"COM"卡片,找到"Microsoft Office Web Components 9.0",单击"选择"和"确定"按钮,OWC就被添加到了引用中。

二.实现代码部分.

WebForm1.aspx

<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="baolong.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
          <title>WebForm1</title>
          <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
          <meta content="C#" name="CODE_LANGUAGE">
          <meta content="JavaScript" name="vs_defaultClientScript">
          <meta content="
http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body style="FONT-SIZE: 9pt" MS_POSITIONING="GridLayout">
          <form id="Form1" method="post" runat="server">
           <asp:label id="Label1" runat="server"></asp:label><asp:textbox id="xlfile" Runat="server"></asp:textbox><br>
           <br>
           <asp:button id="ExportDataBase2Excel" Runat="server"></asp:button><asp:button id="ExportDataGrid2Excel" Runat="server"></asp:button><br>
           <asp:datagrid id="DataGrid1" runat="server" CellPadding="4" BackColor="White" BorderWidth="1px"
            BorderStyle="None" BorderColor="#CC9966" AutoGenerateColumns="False">
            <ItemStyle ForeColor="#330099" BackColor="White"></ItemStyle>
            <HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#990000"></HeaderStyle>
            <Columns>
             <asp:BoundColumn DataField="Title"></asp:BoundColumn>
             <asp:BoundColumn DataField="Author"></asp:BoundColumn>
            </Columns>
           </asp:datagrid></form>
</body>
</HTML>

WebForm1.aspx.cs

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using OWC;//Office Web Compent的缩写

using System.Data.SqlClient;

namespace baolong
{
/// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
          protected System.Web.UI.WebControls.Label Label1;
          protected System.Web.UI.WebControls.TextBox xlfile;
          protected System.Web.UI.WebControls.Button ExportDataBase2Excel;
          protected System.Web.UI.WebControls.Button ExportDataGrid2Excel;
     
  
                public static          SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["DBString"].ToString());
          protected System.Web.UI.WebControls.DataGrid DataGrid1;
          private SqlCommand comm=new SqlCommand("SELECT TOP 50 Title,Author FROM Document",conn);
          private void Page_Load(object sender, System.EventArgs e)
          {
           // 在此处放置用户代码以初始化页面
           if(!Page.IsPostBack)
           {
            BindDataGrid();
            Label1.Text = "请输入要保存得文件名字:";
            ExportDataGrid2Excel.Text = "由DataGrid生成Excel文件";
                        ExportDataBase2Excel.Text = "数据库直接生成Excel文件";
            DataGrid1.Columns[0].HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
                        DataGrid1.Columns[0].HeaderText = "文章名称";
                        DataGrid1.Columns[1].HeaderText = "作者";
                        DataGrid1.Columns[0].HeaderStyle.Font.Bold = true;
                        DataGrid1.Style.Add("font-size", "9pt");


           }
          }
          private void BindDataGrid()
          {
              conn.Open();
           SqlDataReader dr=comm.ExecuteReader();
           DataGrid1.DataSource = dr;
                     DataGrid1.DataBind();
           dr.Close();
           conn.Close();
           
          }
          private void WriteDataBase2Excel()
          {
       SpreadsheetClass xlsheet=new SpreadsheetClass();//电子表格类
           conn.Open();
           SqlDataReader dr=comm.ExecuteReader();
           int numbercols=dr.FieldCount;
           int row=2;
           int i;
           for(i=0;i<numbercols;i++)
           {
            xlsheet.ActiveSheet.Cells[1,i+1]=dr.GetName(i).ToString();
           }
           while(dr.Read())
           {
            for(i=0;i<numbercols;i++)
            {
            xlsheet.ActiveSheet.Cells[row,i+1]=dr.GetValue(i).ToString();
     
            }
            row=row+1;
           }
                  dr.Close();
                 conn.Close();
           try
           {
            xlsheet.ActiveSheet.Export(Server.MapPath(".")+"/Files/"+xlfile.Text.Trim()+".xls",OWC.SheetExportActionEnum.ssExportActionNone);

           }
           catch(System.Runtime.InteropServices.COMException ex)
           {
           Response.Write("错误:" + ex.Message);

           }
          }
          private void WriteDataGrid2Excel()
          {
          SpreadsheetClass xlsheet=new SpreadsheetClass();
           int i=0;
           int j=0;
           //Response.End();
                 //输出标题;
           foreach(DataGridColumn oItem in DataGrid1.Columns)
           {

        //将DataGrid的页眉值循环赋值给excel单元格
           xlsheet.ActiveSheet.Cells[1,i + 1] = oItem.HeaderText;
           //xlsheet.ActiveSheet.Range(xlsheet.ActiveSheet.Cells(1, 1),xlsheet.ActiveSheet.Cells(1, i + 1)).Font.Bold = True;
              //xlsheet.get_Range(xlsheet.Cells[1, i], xlsheet.Cells[1, i + 1]);
                    //xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, i + 1]);
           i=i+1;
           }
           int numbercols=DataGrid1.Items[0].Cells.Count;
           for(j=0;j<DataGrid1.Items.Count;j++)
           {
            for(i=0;i<numbercols;i++)
            {
               // xlsheet.get_Range(xlsheet.Cells[2, 2], xlsheet.Cells[j + 2, i + 1]).Font.Color = "blue";
                //xlsheet.Range("A2:B14").WrapText = True;
             xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[j + 2, i + 1]).AutoFitColumns();
             xlsheet.ActiveSheet.Cells[j + 2, i + 1] = DataGrid1.Items[j].Cells[i].Text.Replace("&nbsp;", " ");
            }
           }
           try
           {
            xlsheet.ActiveSheet.Export(Server.MapPath(".")+"/Files/"+xlfile.Text+".xls", OWC.SheetExportActionEnum.ssExportActionNone);------注意Files的权限为完全控制

           }
           catch(System.Runtime.InteropServices.COMException ex)
           {
            Response.Write("错误:" + ex.Message);

           }
          }
          #region Web 窗体设计器生成的代码
          override protected void OnInit(EventArgs e)
          {
           //
           // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
           //
           InitializeComponent();
           base.OnInit(e);
          }
  
          /// <summary>
          /// 设计器支持所需的方法 - 不要使用代码编辑器修改
          /// 此方法的内容。
          /// </summary>
          private void InitializeComponent()
          {    
           this.ExportDataBase2Excel.Click += new System.EventHandler(this.ExportDataBase2Excel_Click);
           this.ExportDataGrid2Excel.Click += new System.EventHandler(this.ExportDataGrid2Excel_Click);
           this.Load += new System.EventHandler(this.Page_Load);

          }
          #endregion

          private void ExportDataBase2Excel_Click(object sender, System.EventArgs e)
          {
           if(xlfile.Text.Trim()!="")
           {
           WriteDataBase2Excel();
           }         
          }

          private void ExportDataGrid2Excel_Click(object sender, System.EventArgs e)
          {
           if(xlfile.Text.Trim()!="")
           {
            WriteDataGrid2Excel();
           }
          }
}
}
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using OWC;
using System.Data.SqlClient;

namespace baolong
{
/// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
          protected System.Web.UI.WebControls.Label Label1;
          protected System.Web.UI.WebControls.TextBox xlfile;
          protected System.Web.UI.WebControls.Button ExportDataBase2Excel;
          protected System.Web.UI.WebControls.Button ExportDataGrid2Excel;
     
  
                public static          SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["DBString"].ToString());
          protected System.Web.UI.WebControls.DataGrid DataGrid1;
          private SqlCommand comm=new SqlCommand("SELECT TOP 50 Title,Author FROM Document",conn);
          private void Page_Load(object sender, System.EventArgs e)
          {
           // 在此处放置用户代码以初始化页面
           if(!Page.IsPostBack)
           {
            BindDataGrid();
            Label1.Text = "请输入要保存得文件名字:";
            ExportDataGrid2Excel.Text = "由DataGrid生成Excel文件";
                        ExportDataBase2Excel.Text = "数据库直接生成Excel文件";
            DataGrid1.Columns[0].HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
                        DataGrid1.Columns[0].HeaderText = "文章名称";
                        DataGrid1.Columns[1].HeaderText = "作者";
                        DataGrid1.Columns[0].HeaderStyle.Font.Bold = true;
                        DataGrid1.Style.Add("font-size", "9pt");


           }
          }
          private void BindDataGrid()
          {
              conn.Open();
           SqlDataReader dr=comm.ExecuteReader();
           DataGrid1.DataSource = dr;
                     DataGrid1.DataBind();
           dr.Close();
           conn.Close();
           
          }
          private void WriteDataBase2Excel()
          {
          SpreadsheetClass xlsheet=new SpreadsheetClass();
           conn.Open();
           SqlDataReader dr=comm.ExecuteReader();
           int numbercols=dr.FieldCount;
           int row=2;
           int i;
           for(i=0;i<numbercols;i++)
           {
            xlsheet.ActiveSheet.Cells[1,i+1]=dr.GetName(i).ToString();
           }
           while(dr.Read())
           {
            for(i=0;i<numbercols;i++)
            {
            xlsheet.ActiveSheet.Cells[row,i+1]=dr.GetValue(i).ToString();
     
            }
            row=row+1;
           }
                  dr.Close();
                 conn.Close();
           try
           {
            xlsheet.ActiveSheet.Export(Server.MapPath(".")+"/Files/"+xlfile.Text.Trim()+".xls",OWC.SheetExportActionEnum.ssExportActionNone);

           }
           catch(System.Runtime.InteropServices.COMException ex)
           {
           Response.Write("错误:" + ex.Message);

           }
          }
          private void WriteDataGrid2Excel()
          {
          SpreadsheetClass xlsheet=new SpreadsheetClass();
           int i=0;
           int j=0;
           //Response.End();
                    //输出标题;
           foreach(DataGridColumn oItem in DataGrid1.Columns)
           {
           xlsheet.ActiveSheet.Cells[1,i + 1] = oItem.HeaderText;
           //xlsheet.ActiveSheet.Range(xlsheet.ActiveSheet.Cells(1, 1),xlsheet.ActiveSheet.Cells(1, i + 1)).Font.Bold = True;
              //xlsheet.get_Range(xlsheet.Cells[1, i], xlsheet.Cells[1, i + 1]);
                    //xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, i + 1]);
           i=i+1;
           }
           int numbercols=DataGrid1.Items[0].Cells.Count;
           for(j=0;j<DataGrid1.Items.Count;j++)
           {
            for(i=0;i<numbercols;i++)
            {
               // xlsheet.get_Range(xlsheet.Cells[2, 2], xlsheet.Cells[j + 2, i + 1]).Font.Color = "blue";
                //xlsheet.Range("A2:B14").WrapText = True;
             xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[j + 2, i + 1]).AutoFitColumns();
             xlsheet.ActiveSheet.Cells[j + 2, i + 1] = DataGrid1.Items[j].Cells[i].Text.Replace("&nbsp;", " ");
            }
           }
           try
           {
            xlsheet.ActiveSheet.Export(Server.MapPath(".")+"/Files/"+xlfile.Text+".xls", OWC.SheetExportActionEnum.ssExportActionNone););------注意Files的权限为完全控制

           }
           catch(System.Runtime.InteropServices.COMException ex)
           {
            Response.Write("错误:" + ex.Message);

           }
          }
          #region Web 窗体设计器生成的代码
          override protected void OnInit(EventArgs e)
          {
           //
           // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
           //
           InitializeComponent();
           base.OnInit(e);
          }
  
          /// <summary>
          /// 设计器支持所需的方法 - 不要使用代码编辑器修改
          /// 此方法的内容。
          /// </summary>
          private void InitializeComponent()
          {    
           this.ExportDataBase2Excel.Click += new System.EventHandler(this.ExportDataBase2Excel_Click);
           this.ExportDataGrid2Excel.Click += new System.EventHandler(this.ExportDataGrid2Excel_Click);
           this.Load += new System.EventHandler(this.Page_Load);

          }
          #endregion

          private void ExportDataBase2Excel_Click(object sender, System.EventArgs e)
          {
           if(xlfile.Text.Trim()!="")
           {
           WriteDataBase2Excel();
           }         
          }

          private void ExportDataGrid2Excel_Click(object sender, System.EventArgs e)
          {
           if(xlfile.Text.Trim()!="")
           {
            WriteDataGrid2Excel();
           }
          }
}
}

 
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值