C#中導出EXcel的幾種方法

1、C#代碼1 #region 導出當前頁到Excel中 2 3 /按下導出按鈕 4 private void button7_Click(object sender, EventArgs e) 5 6 print(dataGridView1); 7 8 public void print(DataGridView dataGridView1) 9 10 /導出到execl 11 try 12 13 /沒有數(shù)據(jù)的話就不往下執(zhí)行 14 if (dataGridView1.Rows.Count = 0) 15 return; 16 /實例化一個Excel.Application對象 17 Micros

2、oft.Office.Interop.Excel.Application excel =new Microsoft.Office.Interop.Excel.Application(); 18 19 /讓后臺執(zhí)行設(shè)置為不可見，為true的話會看到打開一個Excel，然后數(shù)據(jù)在往里寫 20 excel.Visible =false; 21 22 /新增加一個工作簿，Workbook是直接保存，不會彈出保存對話框，加上Application會彈出保存對話框，值為false會報錯 23 excel.Application.Workbooks.Add(true); 24 /生成Excel中列頭名稱 2

3、5 for (int i = 0; i < dataGridView1.Columns.Count; i+) 26 27 excel.Cells1, i + 1 = dataGridView1.Columnsi.HeaderText; 28 29 /把DataGridView當前頁的數(shù)據(jù)保存在Excel中 30 for (int i = 0; i < dataGridView1.Rows.Count - 1; i+) 31 32 for (int j = 0; j < dataGridView1.Columns.Count; j+) 33 34 if (dataGridVie

4、w1j, i.ValueType =typeof(string) 35 36 excel.Cellsi + 2, j + 1 ="'" + dataGridView1j, i.Value.ToString(); 37 38 else 39 40 excel.Cellsi + 2, j + 1 = dataGridView1j, i.Value.ToString(); 41 42 43 44 45 /設(shè)置禁止彈出保存和覆蓋的詢問提示框 46 excel.DisplayAlerts =false; 47 excel.AlertBeforeOverwriting =fal

5、se; 48 49 /保存工作簿 50 excel.Application.Workbooks.Add(true).Save(); 51 /保存excel文件 52 excel.Save("D:" +"KKHMD.xls"); 53 54 /確保Excel進程關(guān)閉 55 excel.Quit(); 56 excel =null; 57 58 59 catch (Exception ex) 60 61 MessageBox.Show(ex.Message,"錯誤提示"); 62 63 64 #endregion 導出從數(shù)據(jù)庫中查詢到的所

6、有的數(shù)據(jù)到Excel中 C#代碼65 #region 導出全部數(shù)據(jù)到Excel中，可彈出保存對話框，但沒用SaveFileDialog 66 67 public void printAll(System.Data.DataTable dt) 68 69 /導出到execl 70 try 71 72 /沒有數(shù)據(jù)的話就不往下執(zhí)行 73 if (dt.Rows.Count = 0) 74 return; 75 /實例化一個Excel.Application對象 76 Microsoft.Office.Interop.Excel.Application excel =new Microsoft.Off

7、ice.Interop.Excel.Application(); 77 78 /新增加一個工作簿，Workbook是直接保存，不會彈出保存對話框，加上Application會彈出保存對話框，值為false會報錯 79 excel.Application.Workbooks.Add(true); 80 81 /讓后臺執(zhí)行設(shè)置為不可見，為true的話會看到打開一個Excel，然后數(shù)據(jù)在往里寫 82 excel.Visible =false; 83 /生成Excel中列頭名稱 84 for (int i = 0; i < dt.Columns.Count; i+) 85 86 excel.Ce

8、lls1, i + 1 = dataGridView1.Columnsi.HeaderText;/輸出DataGridView列頭名 87 88 89 /把DataGridView當前頁的數(shù)據(jù)保存在Excel中 90 if (dt.Rows.Count > 0) 91 92 for (int i = 0; i < dt.Rows.Count; i+)/控制Excel中行，上下的距離，就是可以到Excel最下的行數(shù)，比數(shù)據(jù)長了報錯，比數(shù)據(jù)短了會顯示不完 93 94 for (int j = 0; j < dt.Columns.Count; j+)/控制Excel中列，左右的距離

9、，就是可以到Excel最右的列數(shù)，比數(shù)據(jù)長了報錯，比數(shù)據(jù)短了會顯示不完 95 96 string str = dt.Rowsij.ToString(); 97 excel.Cellsi + 2, j + 1 ="'" + str;/i控制行，從Excel中第2行開始輸出第一行數(shù)據(jù)，j控制列，從Excel中第1列輸出第1列數(shù)據(jù)，"'" +是以string形式保存，所以遇到數(shù)字不會轉(zhuǎn)成16進制 98 99 100 101 /設(shè)置禁止彈出保存和覆蓋的詢問提示框 102 excel.DisplayAlerts =false; 103 excel.

10、AlertBeforeOverwriting =false; 104 105 /保存工作簿，值為false會報錯 106 excel.Application.Workbooks.Add(true).Save(); 107 /保存excel文件 108 excel.Save("D:" +"KKHMD.xls"); 109 110 /確保Excel進程關(guān)閉 111 excel.Quit(); 112 excel =null; 113 114 115 catch (Exception ex) 116 117 MessageBox.Show(ex.Message

11、,"錯誤提示"); 118 119 120 private void button1_Click(object sender, EventArgs e) 121 122 printAll(dt); 123 124 #endregion 125 private void menuItem16_Click(object sender, System.EventArgs e) ds.Tables"CurrentTable".AcceptChanges(); this.Cursor=Cursors.WaitCursor; Excel.Application my

12、Excel=new Excel.Application(); myExcel.Application.Workbooks.Add(true); myExcel.Visible=true; myExcel.Cells1,1=this.dataGrid1.CaptionText; /Object Dateds.Tables"CurrentTable".Rows.Countds.Tables"CurrentTable".Columns.Count; /生成標題 for(int i=0;i<ds.Tables"CurrentTable"

13、.Columns.Count;i+) myExcel.Cells2,i+1=ds.Tables"CurrentTable".Columnsi.Caption; /填充數(shù)據(jù) for(int i=0;i<ds.Tables"CurrentTable".Rows.Count;i+) for(int j=0;j<ds.Tables"CurrentTable".Columns.Count;j+) if(ds.Tables"CurrentTable".Rowsij.GetType()=typeof(System.S

14、tring)126 127 myExcel.Cellsi+3,j+1="'"+ds.Tables"CurrentTable".Rowsij.ToString(); else myExcel.Cellsi+3,j+1=ds.Tables"CurrentTable".Rowsij.ToString(); 128 this.Cursor=Cursors.Default; MessageBox.Show("導出成功！ ","恭喜",MessageBoxButtons.OK,MessageBoxI

15、con.Asterisk); 129 130 private void button5_Click_1(object sender, EventArgs e) /建立Excel對象 Excel.Application excel = new Excel.Application(); excel.Application.Workbooks.Add(true); /生成字段名稱 for (int i = 0; i < dataGridView2.ColumnCount; i+) excel.Cells1,i + 1 = dataGridView2.Columnsi.HeaderText; i

16、f (y = 0) y = 1; toolStripStatusLabel6.Text = "數(shù)據(jù)導入中，請等待!" /填充數(shù)據(jù) for (int i = 0; i < dataGridView2.RowCount - 1; i+) for (int j = 0; j < dataGridView2.ColumnCount; j+) if (dataGridView2j, i.Value = typeof(string) excel.Cellsi + 2, j + 1 = "" + dataGridView2i, j.Value.ToStri

17、ng(); else excel.Cellsi + 2, j + 1 = dataGridView2j, i.Value.ToString(); excel.Visible = true; / <summary>131 / </summary> / <param name="mydgv">控件 DataGridView </param> / <param name="dic">中英文對照的標題 </param> public static void ExportTasks(DataG

18、ridView mydgv, Dictionary <string, string> dic) / 定義要使用的Excel 組件接口 / 定義Application 對象,此對象表示整個Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null; / 定義Workbook對象,此對象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; / 定義Worksheet 對象,此對象表示Execel 中的一張工作表 Microsoft.Office.Inte

19、rop.Excel.Worksheet ws = null; /定義Range對象,此對象代表單元格區(qū)域 Microsoft.Office.Interop.Excel.Range range;132 int dcell = 1; int rowindex = 0; int colindex = 0;133 int rowcount = mydgv.Rows.Count; int colcount = mydgv.Columns.Count; int dispcolcount = dic.Count; try /初始化 Application 對象 excelApp excelApp = new

20、 Microsoft.Office.Interop.Excel.Application(); /在工作薄的第一個工作表上創(chuàng)建任務(wù)列表 workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); ws = (Worksheet)workBook.Worksheets1;134 / 命名工作表的名稱為 ws.Name = "Sheet1" /創(chuàng)建緩存 Object, objdata = new objectrowcount + 1, colcount; /創(chuàng)建標題 foreach (string s in

21、dic.Keys) objdatarowindex, colindex+ = dics.ToString(); /獲取數(shù)據(jù) for (int i = 0; i < rowcount; i+) dcell = 0; foreach (string ss in dic.Keys) for (int j = 0; j < colcount; j+) if (mydgv.Columnsj.Name = ss) objdatai + 1, dcell+ = mydgv.Rowsi.Cellsj.FormattedValue.ToString(); /得到樣式之后的值 /寫入Excel ran

22、ge = ws.get_Range(excelApp.Cells1, 1, excelApp.Cellsrowcount, dispcolcount); range.Value2 = objdata; System.Windows.Forms.Application.DoEvents(); /設(shè)置格式 excelApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft; /全局左對齊 excelApp.Cells.EntireColumn.AutoFit(); range = ws.get_R

23、ange(excelApp.Cells1, 1, excelApp.Cells1, colcount); range.Font.Bold = true; /標題粗體 /顯示 Excel excelApp.Visible = true;135 catch (Exception ex) throw ex; 136 # 137 using System;using System.Collections.Generic;using System.Text;using System.Web.UI.WebControls;using System.Data;using System.Web.UI.Html

24、Controls; namespace Common public class ExcelHelper / Excel導出 public static void Export(GridView dgExport, DataTable dtData) System.Web.HttpContext curContext = System.Web.HttpContext.Current; / IO用于導出并返回excel文件 System.IO.StringWriter strWriter = null; System.Web.UI.HtmlTextWriter htmlWriter = null;138 if (dtData != null) / 設(shè)置編碼和附件格式 curContext.Response.Clear(); curContext.Response.ClearContent(); curContext.Response.Buffer = true; curContext.R