導(dǎo)出Excel方法大全

1、 C#導(dǎo)出EXCEL 方法大全C#中常需要把報表數(shù)據(jù)導(dǎo)出Excel,下面詳細介紹6種方法：第一種方法比較羅嗦，主要介紹如何調(diào)用，含注釋給不熟導(dǎo)出EXCEL者知其原理第二方法直接套用，代碼簡單明了。個人推薦這種，直接套用，非常方便。第三到第六種就不一一介紹了。方法1頁面增加一個按鈕，單擊事件添加如下方法：protected void Button1_Click(object sender, EventArgs e) Export("application/ms-excel", "學(xué)生信息列表.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 的窗體標記內(nèi) 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 學(xué)生信息表名" sqlcon = new SqlConnection(strCon); SqlDataAdapter myda = new SqlDataAdapter(sqlstr, sqlcon); DataSet myds = new DataSet(); sqlcon.Open(); myda.Fill(myds, "學(xué)生信息表名"); GridView1.DataSource = myds; GridView1.DataKeyNames = new string "學(xué)號" ; GridView1.Da

7、taBind(); sqlcon.Close(); protected void Button1_Click(object sender, EventArgs e) Export("application/ms-excel", "學(xué)生信息列表.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="學(xué)號" HeaderText="學(xué)號" ReadOnly="True" /> <asp:BoundField DataField=&

11、quot;姓名" HeaderText="姓名" /> <asp:BoundField DataField="出生日期" HeaderText="出生日期" /> <asp:BoundField DataField="專業(yè)" HeaderText="專業(yè)" /> <asp:BoundField DataField="學(xué)院" HeaderText="學(xué)院" /> </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="導(dǎo)出" />讀取Excel數(shù)據(jù)的代碼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，直接調(diào)用頁面增加一個按鈕btnExcel，單擊btnExcel事件添加如下方法：主要替換GridView名稱和寫你的GridView數(shù)據(jù)綁定方法BindGrid()就行

16、了。 protected void btnExcel_Click(object sender, EventArgs e) ToExcel(this.GridView1);/ GridView1為要導(dǎo)出數(shù)據(jù)的GridView名稱 protected void ToExcel(GridView gv) HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=導(dǎo)出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綁定方法，把數(shù)據(jù)綁定到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();/建立表結(jié)構(gòu)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將數(shù)據(jù)插入新建的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 = "正在導(dǎo)出數(shù)據(jù)，已導(dǎo)出" + perc

23、ent.ToString("0.00") + "%."if1)/this.FM.CaptionText.Text = "請稍后."System.Windows.Forms .Application.DoEvents();objConn.Close();/this.FM.CaptionText.Text = ""方法4：此方法速度也是超級快，只不過導(dǎo)出的格式非標準的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：此方法調(diào)用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 = "正在導(dǎo)出數(shù)據(jù)，已導(dǎo)出" + 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