C#中数据库数据如何导出至Excel表格

 有时候需要将数据库的数据导出至Excel表格表格，以便进行查看和分析，那么如何导出呢？下面用代码来实现。
    首先，新建一个工程，需要添加引用Microsoft.Office.Interop.Excel.dll，以Oracle数据库为例（只要读出DataTable或DataSet就行了，哪种数据库没关系）。

1、创建一个表格，并插入如下数据。

view plain copy to clipboard print ?

1. drop table TABLETESTEXCEL;  
2. create table TABLETESTEXCEL  
3. (  
4.   col_id   NUMBER not null,  
5.   col_name VARCHAR2(32),  
6.   col_age  NUMBER,  
7.   col_sex  VARCHAR2(4),  
8.   col_work VARCHAR2(32),  
9.   col_mony FLOAT  
10. );  

drop table TABLETESTEXCEL; create table TABLETESTEXCEL ( col_id NUMBER not null, col_name VARCHAR2(32), col_age NUMBER, col_sex VARCHAR2(4), col_work VARCHAR2(32), col_mony FLOAT );

数据：

view plain copy to clipboard print ?

1. insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  
2. values (1, '吴一', 25, '男', '.NET', 5000);  
3.   
4. insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  
5. values (2, '孙二', 24, '男', 'JAVA', 4999);  
6.   
7. insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  
8. values (3, '张三', 25, '男', 'PHP', 5001);  
9.   
10. insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  
11. values (4, '李四', 26, '男', 'DELPHI', 5002);  
12.   
13. insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  
14. values (5, '王五', 27, '男', 'C++', 5003);  
15.   
16. insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  
17. values (6, '赵六', 25, '男', 'C', 4008);  
18.   
19. insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  
20. values (7, '燕七', 25, '男', '数据库', 4007);  
21.   
22. insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  
23. values (8, '胡八', 25, '男', 'JSP', 5005);  
24.   
25. insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  
26. values (9, '钱九', 25, '男', 'ASP.NET', 4005);  
27.   
28. insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)  
29. values (10, '沈十', 25, '男', 'VB', 4000);  
30. commit;  

insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony) values (1, '吴一', 25, '男', '.NET', 5000); insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony) values (2, '孙二', 24, '男', 'JAVA', 4999); insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony) values (3, '张三', 25, '男', 'PHP', 5001); insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony) values (4, '李四', 26, '男', 'DELPHI', 5002); insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony) values (5, '王五', 27, '男', 'C++', 5003); insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony) values (6, '赵六', 25, '男', 'C', 4008); insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony) values (7, '燕七', 25, '男', '数据库', 4007); insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony) values (8, '胡八', 25, '男', 'JSP', 5005); insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony) values (9, '钱九', 25, '男', 'ASP.NET', 4005); insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony) values (10, '沈十', 25, '男', 'VB', 4000); commit;

2、C#代码实现

数据库操作的类：

view plain copy to clipboard print ?

1. public class DataBaseHelper  
2.  {  
3.     public static DataTable ExecuterQuery(string connectionString, string commandSql)  
4.     {  
5.         DataTable dataTable = new DataTable();  
6.   
7.         try  
8.         {  
9.           using (OracleConnection oracleConnection =   
10.           new OracleConnection(connectionString))  
11.           {  
12.             oracleConnection.Open();  
13.   
14.             using (OracleDataAdapter oracleDataAdapter =   
15.             new OracleDataAdapter(commandSql,oracleConnection))  
16.                 {  
17.                    oracleDataAdapter.Fill(dataTable);  
18.                 }  
19.   
20.                 oracleConnection.Close();  
21.           }  
22.         }  
23.         catch  
24.         {  
25.           return null;  
26.         }  
27.   
28.      return dataTable;  
29.     }  
30.  }  

public class DataBaseHelper { public static DataTable ExecuterQuery(string connectionString, string commandSql) { DataTable dataTable = new DataTable(); try { using (OracleConnection oracleConnection = new OracleConnection(connectionString)) { oracleConnection.Open(); using (OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(commandSql,oracleConnection)) { oracleDataAdapter.Fill(dataTable); } oracleConnection.Close(); } } catch { return null; } return dataTable; } }

view plain copy to clipboard print ?

1. public class DataBaseDao  
2. {  
3.    public static DataTable GetDataBaseTable()  
4.    {  
5.       string sql = " SELECT * FROM tableTestExcel";  
6.   
7.       return DataBaseHelper.ExecuterQuery("User ID=downsoft;Password=sys;Data Source=orcl", sql);  
8.    }  
9. }  

public class DataBaseDao { public static DataTable GetDataBaseTable() { string sql = " SELECT * FROM tableTestExcel"; return DataBaseHelper.ExecuterQuery("User ID=downsoft;Password=sys;Data Source=orcl", sql); } }

 导出Excel的类：

view plain copy to clipboard print ?

1. public class DataChangeExcel  
2. {  
3.         /// <summary>   
4.         /// 数据库转为excel表格   
5.          /// </summary>   
6.        /// <param name="dataTable">数据库数据</param>   
7.        /// <param name="SaveFile">导出的excel文件</param>   
8.         public static void DataSetToExcel(DataTable dataTable, string SaveFile)  
9.         {  
10.             Excel.Application excel;  
11.   
12.             Excel._Workbook workBook;  
13.   
14.             Excel._Worksheet workSheet;  
15.   
16.             object misValue = System.Reflection.Missing.Value;  
17.   
18.             excel = new Excel.ApplicationClass();  
19.   
20.             workBook = excel.Workbooks.Add(misValue);  
21.   
22.             workSheet = (Excel._Worksheet)workBook.ActiveSheet;  
23.   
24.             int rowIndex = 1;  
25.   
26.             int colIndex = 0;  
27.   
28.             //取得标题   
29.             foreach (DataColumn col in dataTable.Columns)  
30.             {  
31.                 colIndex++;  
32.   
33.                 excel.Cells[1, colIndex] = col.ColumnName;  
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.                     //设置表格内容居中对齐   
52.                       workSheet.get_Range(excel.Cells[rowIndex, colIndex],   
53.                           
54.                         excel.Cells[rowIndex, colIndex]).HorizontalAlignment =   
55.                           
56.                         Excel.XlVAlign.xlVAlignCenter;  
57.                 }  
58.             }  
59.   
60.             excel.Visible = false;  
61.   
62.             workBook.SaveAs(SaveFile, Excel.XlFileFormat.xlWorkbookNormal, misValue,   
63.   
64.                 misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive,   
65.                   
66.                 misValue, misValue, misValue, misValue, misValue);  
67.   
68.             dataTable = null;  
69.   
70.             workBook.Close(true, misValue, misValue);  
71.   
72.             excel.Quit();  
73.   
74.             PublicMethod.Kill(excel);//调用kill当前excel进程   
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.         }  
100.  }  

public class DataChangeExcel { /// <summary> /// 数据库转为excel表格 /// </summary> /// <param name="dataTable">数据库数据</param> /// <param name="SaveFile">导出的excel文件</param> public static void DataSetToExcel(DataTable dataTable, string SaveFile) { Excel.Application excel; Excel._Workbook workBook; Excel._Worksheet workSheet; object misValue = System.Reflection.Missing.Value; excel = new Excel.ApplicationClass(); workBook = excel.Workbooks.Add(misValue); workSheet = (Excel._Worksheet)workBook.ActiveSheet; int rowIndex = 1; int colIndex = 0; //取得标题 foreach (DataColumn col in dataTable.Columns) { colIndex++; excel.Cells[1, colIndex] = col.ColumnName; } //取得表格中的数据 foreach (DataRow row in dataTable.Rows) { rowIndex++; colIndex = 0; foreach (DataColumn col in dataTable.Columns) { colIndex++; excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim(); //设置表格内容居中对齐 workSheet.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; } } excel.Visible = false; workBook.SaveAs(SaveFile, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); dataTable = null; workBook.Close(true, misValue, misValue); excel.Quit(); PublicMethod.Kill(excel);//调用kill当前excel进程 releaseObject(workSheet); releaseObject(workBook); releaseObject(excel); } private static void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch { obj = null; } finally { GC.Collect(); } } }

关闭进程的类:

view plain copy to clipboard print ?

1. public class PublicMethod  
2. {  
3.    [DllImport("User32.dll", CharSet = CharSet.Auto)]  
4.   
5.    public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);  
6.   
7.    public static void Kill(Microsoft.Office.Interop.Excel.Application excel)  
8.    {  
9.        try  
10.        {  
11.          IntPtr t = new IntPtr(excel.Hwnd);  
12.   
13.          int k = 0;  
14.   
15.          GetWindowThreadProcessId(t, out k);  
16.   
17.          System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);  
18.                  
19.          p.Kill();   
20.         }  
21.         catch  
22.         { }  
23.    }  
24. }  

public class PublicMethod { [DllImport("User32.dll", CharSet = CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); public static void Kill(Microsoft.Office.Interop.Excel.Application excel) { try { IntPtr t = new IntPtr(excel.Hwnd); int k = 0; GetWindowThreadProcessId(t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); } catch { } } }

写好了如上的类，那么开始调用吧，调用：

view plain copy to clipboard print ?

1. DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(),   
2. @"F:\outputFormDataBase.xls");  

DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(), @"F:\outputFormDataBase.xls");

这样成功将数据导出，如图。

 

分享到：