导出Excel方法大全

1、 C#导出EXCEL 方法大全C#中常需要把报表数据导出Excel,下面详细介绍6种方法：第一种方法比较罗嗦，主要介绍如何调用，含注释给不熟导出EXCEL者知其原理第二方法直接套用，代码简单明了。个人推荐这种，直接套用，非常方便。第三到第六种就不一一介绍了。方法1页面增加一个按钮，单击事件添加如下方法：protected void Button1_Click(object sender, EventArgs e) Export("application/ms-excel", "学生信息列表.xls"); private void Export(strin

2、g FileType, string FileName) Response.Charset = "GB2312" Response.ContentEncoding = System.Text.Encoding.UTF7; Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString(); Response.ContentType = FileTyp

3、e; this.EnableViewState = false; StringWriter tw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(tw); GridView1.RenderControl(hw); Response.Write(tw.ToString(); Response.End(); /如果没有下面方法会报错类型“GridView”的控件“GridView1”必须放在具有 runat=server 的窗体标记内 public override void VerifyRenderingInServerF

4、orm(Control control) 还有由于是文件操作所以要引入名称空间IO和Text后台代码：using System;using System.Data;using System.Configuration;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.Data.SqlC

5、lient;using System.Drawing;using System.IO;using System.Text; SqlConnection sqlcon; SqlCommand sqlcom; string strCon = "Data Source=(local);Database=education;Uid=sa;Pwd=sa" protected void Page_Load(object sender, EventArgs e) if (!IsPostBack) bind(); public void bind() string sqlstr = &qu

6、ot;select * from 学生信息表名" sqlcon = new SqlConnection(strCon); SqlDataAdapter myda = new SqlDataAdapter(sqlstr, sqlcon); DataSet myds = new DataSet(); sqlcon.Open(); myda.Fill(myds, "学生信息表名"); GridView1.DataSource = myds; GridView1.DataKeyNames = new string "学号" ; GridView1.Da

7、taBind(); sqlcon.Close(); protected void Button1_Click(object sender, EventArgs e) Export("application/ms-excel", "学生信息列表.xls"); private void Export(string FileType, string FileName) Response.Charset = "GB2312"ncoding.UTF7; Response.AppendHeader("Content-Dispositio

8、n", "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.ToStr

9、ing(); Response.End(); public override void VerifyRenderingInServerForm(Control control) 前台：<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" BackColor="White" BorderColor="#CCCCCC" BorderStyle="

10、;None" BorderWidth="1px" Font-Size="12px" > <FooterStyle BackColor="White" ForeColor="#000066" /> <Columns> <asp:BoundField DataField="学号" HeaderText="学号" ReadOnly="True" /> <asp:BoundField DataField=&

11、quot;姓名" HeaderText="姓名" /> <asp:BoundField DataField="出生日期" HeaderText="出生日期" /> <asp:BoundField DataField="专业" HeaderText="专业" /> <asp:BoundField DataField="学院" HeaderText="学院" /> </Columns> <R

12、owStyle ForeColor="#000066" /> <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" CssClass="ms-formlabel DataGridFi

13、xedHeader"/> <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" /> </asp:GridView> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导出" />读取Excel数据的代码private DataSet

14、CreateDataSource() string strCon; strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("excel.xls") + " Extended Properties=Excel 8.0;" OleDbConnection olecon = new OleDbConnection(strCon); OleDbDataAdapter myda = new OleDbDataAdapter("SELECT *

15、 FROM Sheet1$", strCon); DataSet myds = new DataSet(); myda.Fill(myds); return myds; protected void Button1_Click(object sender, EventArgs e) GridView1.DataSource = CreateDataSource(); GridView1.DataBind(); 方法2，直接调用页面增加一个按钮btnExcel，单击btnExcel事件添加如下方法：主要替换GridView名称和写你的GridView数据绑定方法BindGrid()就行

16、了。 protected void btnExcel_Click(object sender, EventArgs e) ToExcel(this.GridView1);/ GridView1为要导出数据的GridView名称 protected void ToExcel(GridView gv) HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=导出EXCEL名字.xls"); HttpContext.Current.Respons

17、e.Charset = "utf-8" HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); HttpContext.Current.Response.ContentType = "application/ms-excel"/image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword gv.AllowPaging = false; BindGrid();/ GridVi

18、ew1绑定方法，把数据绑定到GridView中去。nableViewState = false; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); gv.RenderControl(hw); HttpContext.Current.Response.Write(tw.ToString(); HttpContext.Current.Response.End(); gv.AllowPagin

19、g = true; BindGrid(); 方法3: int Id=0;string Name="测试"string FileName="d:abc.xls"System.Data.DataTable dt=new System.Data.DataTable();long totalCount=dt.Rows.Count;long rowRead=0;float percent=0;OleDbParameter parm=new OleDbParameterdt.Columns.Count;string connString = "Provid

20、er=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName +"Extended Properties=Excel 8.0;"OleDbConnection objConn = new OleDbConnection(connString);OleDbCommand objCmd = new OleDbCommand();objCmd.Connection = objConn;objConn.Open();/建立表结构objCmd.CommandText = "CREATE TABLE Sheet1(序号 In

21、teger,名称 varchar)"objCmd.ExecuteNonQuery();/建立插入动作的CommandobjCmd.CommandText = "INSERT INTO Sheet1("+Id+","+Name+")"parm0=new OleDbParameter("Id", OleDbType.Integer);objCmd.Parameters.Add(parm0);parm1=new OleDbParameter("Company", OleDbType.VarC

22、har);objCmd.Parameters.Add(parm1);/遍历DataTable将数据插入新建的Excel文件中for(int i=0;i<dt.Rows.Count;i+) parm0.Value=i+1;for(int j=1;j<parm.Length;j+)parmj.Value =dt.Rowsij;objCmd.ExecuteNonQuery();rowRead+;percent=(float)(100*rowRead)/totalCount; /this.FM.CaptionText.Text = "正在导出数据，已导出" + perc

23、ent.ToString("0.00") + "%."if1)/this.FM.CaptionText.Text = "请稍后."System.Windows.Forms .Application.DoEvents();objConn.Close();/this.FM.CaptionText.Text = ""方法4：此方法速度也是超级快，只不过导出的格式非标准的Excel格式，默认工作表名与文件名相同string FileName="d:abc.xls"System.Data.DataTabl

24、e dt=new System.Data.DataTable();FileStream objFileStream;StreamWriter objStreamWriter;string strLine=""objFileStream = new FileStream(FileName,FileMode.OpenOrCreate,FileAccess.Write);objStreamWriter = new StreamWriter(objFileStream,System.Text.Encoding.Unicode);for(int i=0;i<dt.Columns

25、.Count;i+)strLine=strLine+dt.Columnsi.ColumnName.ToString()+Convert.ToChar(9);objStreamWriter.WriteLine(strLine);strLine=""for(int i=0;i<dt.Rows.Count;i+)strLine=strLine+(i+1)+Convert.ToChar(9);for(int j=1;j<dt.Columns.Count;j+)strLine=strLine+dt.Rowsij.ToString()+Convert.ToChar(9);o

26、bjStreamWriter.WriteLine(strLine);strLine=""objStreamWriter.Close();objFileStream.Close();方法5：此方法调用com组件，速度都慢于以上3个方法using Excel;System.Data.DataTable dt=new System.Data.DataTable();string FileName="d:abc.xls"long totalCount=dt.Rows.Count;long rowRead=0;float percent=0;Excel.Appli

27、cation xlApp=null;xlApp=new Excel.Application();Excel.Workbooks workbooks=xlApp.Workbooks;Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets1;/取得sheet1Excel.Range range;/写入字段 for(int i=0;i<dt.Columns.Count;i+)w

28、orksheet.Cells1,i+1=dt.Columnsi.ColumnName; range=(Excel.Range)worksheet.Cells1,i+1; for(int r=0;r<dt.Rows.Count;r+)worksheet.Cellsr+2,1=r+1;for(int i=0;i<dt.Columns.Count;i+)/worksheet.Cellsr+2,i+1=dt.Rowsri;if(i+1!=dt.Columns.Count)worksheet.Cellsr+2,i+2=dt.Rowsri+1;rowRead+;percent=(float)(

29、100*rowRead)/totalCount; /this.FM.CaptionText.Text = "正在导出数据，已导出" + percent.ToString("0.00") + "%."System.Windows.Forms .Application.DoEvents();range=worksheet.get_Range(worksheet.Cells2,1,worksheet.Cellsdt.Rows.Count+2,dt.Columns.Count);workbook.Saved =true;workbook.Sa

30、veCopyAs(FileName);/this.FM.CaptionText.Text = ""方法6：利用剪贴板 ,有人说此方法很快，但是我用时，这种方法最慢，请高手指点.System.Data.DataTable dt=new System.Data.DataTable();string filePath="d:abc.xls"object oMissing = System.Reflection.Missing.Value;Excel.ApplicationClass xlApp = new Excel.ApplicationClass();tryxlApp.Visible = false;xlApp.DisplayAlerts = false;Excel.Workbooks oBooks = xlApp.Workbooks;Excel._Workbook xlWorkbook = null;xlWorkbook = oBooks.Open(filePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMi