DevExpress GridView匯入匯出Excel

1、Excel資料匯入到資料庫中： 在頁面放置1個按鈕控制元件(Button1)和1個檔案上傳控制元件(FileUpload1) protected void Page_Load(object sender, EventArgs e) { GridView1.Visible = false; } protected void Button1_Click(object sender, EventArgs e) { try { if (FileUpload1.PostedFile.FileName != "") { string filepath = FileUpload1.PostedFile.FileName; string filename = filepath.Substring(filepath.LastIndexOf("//") + 1); string sheetname = "Sheet1"; string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "$]", conn); DataSet ds = new DataSet(); oada.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); GridView1.Visible = true; } } catch (Exception error) { this.Label1.Text = "上傳發生錯誤！原因：" + error.ToString(); } } 注意把Excel檔案屬性中加入ASPNET使用者許可權，不然不能匯入 3、將資料庫中的資料匯入Excel中，在這裡我講敘自己實踐中的兩個方法： 方法一：從最基本的寫檔案入手，將從資料庫中讀出來的資料寫入到Excel中； //該方法實現將資料匯入到Excel檔案中，其中的DataTable dt就是你需要將資料寫入到Excel中的資料； public void ExportExcel( DataTable dt , StreamWriter w ) ...{ try ...{ for( int i = 0 ; i < dt.Columns.Count ; i ++ ) ...{ w.Write ( dt.Columns[i] ); w.Write( ' ' ); } w.Write ( " " ); object[] values = new object [dt.Columns.Count]; foreach ( DataRow dr in dt.Rows ) ...{ values = dr.ItemArray ; for ( int i = 0 ; i < dt.Columns.Count ; i++ ) ...{ w.Write ( values[i] ); w.Write ( ' ' ); } w.Write ( " " ); } w.Flush(); w.Close(); } catch ...{ w.Close(); } } StreamWriter w就是你自己建立的一個流，建立該資料流時，你需要自己指定需要將資料寫入到那個檔案，即指定檔案路徑，要實現下載的話，就可以簡單的用Response.Redirect ( "指定需要被下載的檔案路徑" ); 方法二：該方法實現的是將資料從DataGrid中匯入到Excel中： //filename為Excel的名字，ToExcelGrid就是資料來源，在此為DataGrid資料來源； private void ExportExcelFromDataGrid( string filename , System.Web.UI.WebControls.DataGrid ToExcelGrid ) ...{ Response.Clear(); Response.Buffer= true; Response.Charset="utf-8"; Response.AppendHeader("Content-Disposition","attachment;filename="+Server.UrlEncode ( filename ) ); Response.ContentEncoding=System.Text.Encoding.GetEncoding("utf-8");//設定輸出流為簡體中文 Response.ContentType = "application/ms-excel";//設定輸出檔案型別為excel檔案。 this.EnableViewState = false; System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true); System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); ToExcelGrid.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); Excel匯出： 頁面上放一個GridView控制元件，ID為GridView1，新增個匯出按鈕ID為Button2 protected void Button2_Click(object sender, EventArgs e) { Response.Clear(); Response.Buffer = true; Response.Charset = "GB2312"; Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls"); // 如果設定為 GetEncoding("GB2312");匯出的檔案將會出現亂碼！！！ Response.ContentEncoding = System.Text.Encoding.UTF7; Response.ContentType = "application/ms-excel";//設定輸出檔案型別為excel檔案。 System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); this.GridView1.RenderControl(oHtmlTextWriter); Response.Output.Write(oStringWriter.ToString()); Response.Flush(); Response.End(); } 頁面後臺必須重寫這兩個方法，不然出現錯誤！ public override void VerifyRenderingInServerForm( Control control ) { } protected override void Render(HtmlTextWriter writer) { if (Page != null) { Page.VerifyRenderingInServerForm(this); } base.Render(writer); } 將GridView列印出來，新增個列印按鈕，ID為Button4 protected void Button4_Click(object sender, EventArgs e) { Response.Write("<mce:script type="text/javascript"><!-- window.print() // --></mce:script>"); }