C# DataSet数据导入Excel 修正版- .net FrameWork 4.0以上 

 引入  Microsoft.Office.Interop.Excel.dll
 格式：标题加了下划线，单元格内容居中
 1 using System;
 2 using System.Data;
 3 using System.Data.SqlClient;
 4 using System.Web.UI;    
 5 
 6      public static void DataSetToExcel(DataTable dataTable, string SaveFile)  
 7             {  
 8                 Microsoft.Office.Interop.Excel.Application excel;  
 9 
10                 Microsoft.Office.Interop.Excel._Workbook workBook;  
11 
12                 Microsoft.Office.Interop.Excel._Worksheet workSheet;  
13 
14                 object misValue = System.Reflection.Missing.Value;  
15 
16                 excel = new Microsoft.Office.Interop.Excel.Application();
17 
18                 workBook = excel.Workbooks.Add(misValue);  
19 
20                 workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet;  
21 
22                 int rowIndex = 1;  
23 
24                 int colIndex = 0;
25                
26                 //取得标题  
27                 foreach (DataColumn col in dataTable.Columns)  
28                 {  
29                     colIndex++;  
30 
31                     excel.Cells[1, colIndex] = col.ColumnName;
32                     Microsoft.Office.Interop.Excel.Range range = workSheet.Range[workSheet.Cells[1, colIndex],workSheet.Cells[1,colIndex]];
33                     range.Font.Underline = true;
34                 }
35 
36                 //取得表格中的数据  
37                 foreach (DataRow row in dataTable.Rows)  
38                 {  
39                     rowIndex++;  
40 
41                     colIndex = 0;  
42 
43                     foreach (DataColumn col in dataTable.Columns)  
44                     {  
45                        colIndex++;  
46 
47                        excel.Cells[rowIndex, colIndex] =   
48 
49                             row[col.ColumnName].ToString().Trim();
50 
51                        Microsoft.Office.Interop.Excel.Range range = workSheet.Range[excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]];
52                        range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
53                     }  
54                 }  
55 
56                 excel.Visible = false;
57                 excel.DisplayAlerts = false;
58 
59                 if (workSheet != null)
60                 {
61                     workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue,
62 
63                         misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
64 
65                         misValue, misValue, misValue, misValue, misValue);
66                 }
67  
68 
69                 dataTable = null;  
70 
71                 workBook.Close(true, misValue, misValue);  
72 
73                 excel.Quit();  
74 
75 
76                 releaseObject(workSheet);  
77 
78                 releaseObject(workBook);  
79 
80                 releaseObject(excel);  
81 
82             }  
83 
84             private static void releaseObject(object obj)  
85             {  
86                 try  
87                 {  
88                     System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);  
89                     obj = null;  
90                 }  
91                 catch  
92                 {  
93                     obj = null;  
94                 }  
95                 finally  
96                 {  
97                     GC.Collect();  
98                 }  
99             }

Call funxction example:

  DataSetToExcel(ds.Tables[0], @"D:\ZhaoNick\Desktop\test.xls");

这次需要做一个windowServices，主要的功能就是从local db提取数据存入ftp excel file中，在提取数据成功后存入dataset中，在导入到excel时要给标题加下划线和内容设置居中时，发现报了“Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: “object”未包含“get_Range”的定义”这么一段错误，后来才发现是.net版本问题或者说是写法有问题。。不过最后还是解决了这个坑。

posted @ 2017-07-24 17:51  ~扎克伯格  阅读(2291)  评论(0)  编辑  收藏  举报