c# 代码创建Excel文件 并且 将数据导出Excel

2. 导入Microsoft.Office.Interop.Excel.dll
4. using System.IO;
6.  #region 创建Excel
7.     /// <summary>
8.     /// 创建Excel
9.     /// </summary>
10.     /// <param name="fileName">文件名称</param>
11.     /// <param name="sheetName">sheet名称</param>
12.     private void CreateExcel(string fileName,string sheetName)
13.     {
14.         Microsoft.Office.Interop.Excel.ApplicationClass my = new Microsoft.Office.Interop.Excel.ApplicationClass();
16.         my.Visible = true;
17.         object objMissing = System.Reflection.Missing.Value;
21.         //打开工作簿   
22.         Microsoft.Office.Interop.Excel.Workbook mybook = (Microsoft.Office.Interop.Excel.Workbook)my.Workbooks.Add(1);   //   1表示只建一个表   
25.         //mybook.Worksheets.Add(objMissing,objMissing,1,objMissing);//添加sheet   
26.         if (sheetName == "")
27.         {
28.             sheetName = "steet1";
29.         }
30.         else
31.         {
32.             ((Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1]).Name = sheetName;//将sheet1的名称改为zhu   
33.         } 
34.         Microsoft.Office.Interop.Excel.Worksheet mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1];
35.         ((Microsoft.Office.Interop.Excel.Range)mysheet.Cells[2, 3]).EntireRow.Insert(0,0);     //添加行   
38.         //保存   
39.         mybook.SaveAs("d://" + fileName + ".xls", objMissing, objMissing, objMissing,   //Excel.XlSaveAsAccessMode.xlShared   
40.         objMissing, objMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
41.         objMissing, objMissing, objMissing,
42.         objMissing, objMissing);
43.         mybook = null;
44.         my.Quit();
45.         my = null;
46.     }
47.     #endregion
49. //------------------------------------------------------------------
50.  #region 导入Excel
51.     /// <summary>
52.     /// 导入Excel
53.     /// </summary>
54.     /// <param name="dv">数据源</param>
55.     /// <param name="path">路径</param>
56.     /// <param name="title1">标题</param>
57.     /// <param name="heji">合计</param>
58.     public void inputExcel(DataSet dv, string path, string title1, string heji)
59.     {
60.         try
61.         {
62.             FileInfo fi = new FileInfo(path);
63.             fi.Delete();
64.         }
65.         catch
66.         {
67.         }
69.         Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
70.         object oMissing = System.Reflection.Missing.Value;
71.         Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Add(oMissing);
73.         int rowIndex = 3;
74.         int colIndex = 0;
76.         excel.Cells[1, 1] = title1;
79.         foreach (DataColumn dc in dv.Tables[0].Columns)
80.         {
81.             colIndex++;
82.             excel.Cells[rowIndex, colIndex] = dc.ColumnName;
83.             ((Microsoft.Office.Interop.Excel.Range)excel.Cells[rowIndex, colIndex]).ColumnWidth = 18.8;
84.         }
86.         foreach (DataRow row in dv.Tables[0].Rows)
87.         {
88.             rowIndex++;
89.             colIndex = 0;
90.             foreach (DataColumn dc in dv.Tables[0].Columns)
91.             {
92.                 colIndex++;
93.                 excel.Cells[rowIndex, colIndex] = row[dc.ColumnName].ToString();
94.                 ((Microsoft.Office.Interop.Excel.Range)excel.Cells[rowIndex, colIndex]).ColumnWidth = 18.8;
95.             }
96.         }
97.         excel.Cells[rowIndex + 1, colIndex] = heji;
99.         Microsoft.Office.Interop.Excel.Sheets sheets = book.Worksheets;
100.         Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);
102.         Microsoft.Office.Interop.Excel.Range range;
103.         range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, colIndex]);
104.         range.MergeCells = true;//合并   
105.         range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中   
106.         range.Font.Bold = true;
107.         range.Font.Size = 14;
110.         book.Saved = true;
111.         excel.UserControl = false;
113.         worksheet.SaveAs(path, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
116.         excel.Quit();
117.         oMissing = null;
118.         sheets = null;
119.         worksheet = null;
120.         book = null;
121.         excel = null;
122.         GC.Collect();
123.     }
124.     #endregion
126. //---------------------------------------------------------------
127.     #region 绑定数据
128.     private DataSet bangding()
129.     {
130.         using (SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=;database=pubs"))
131.         {
132.             conn.Open();
133.             string sql = "select job_id as 编号,job_desc as 排序,min_lvl as 最小, max_lvl as 最大 from jobs";
134.             SqlDataAdapter dar = new SqlDataAdapter(sql, conn);
135.             DataSet ds = new DataSet();
136.             dar.Fill(ds);
138.             this.GridView1.DataSource = ds;
139.             this.GridView1.DataBind();
140.             return ds;
141.         }
142.     }
143.     #endregion
144. //------------------------------------------------------------------
146. #region 获取合计
147.     private int num()
148.     {
149.         int num = 0;
150.         for (int i = 0; i < bangding().Tables[0].Rows.Count; i++)
151.         {
152.             num += Convert.ToInt32(bangding().Tables[0].Rows[i][3].ToString());
153.         }
154.         return num;
155.     }
156.     #endregion
158. //-------------------------------------------------------------------