有时候需要将数据库的数据导出至Excel表格表格,以便进行查看和分析,那么如何导出呢?下面用代码来实现。
首先,新建一个工程,需要添加引用Microsoft.Office.Interop.Excel.dll,以Oracle数据库为例(只要读出DataTable或DataSet就行了,哪种数据库没关系)。
1、创建一个表格,并插入如下数据。
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 );
数据:
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#代码实现
数据库操作的类:
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; } }
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的类:
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(); } } }
关闭进程的类:
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 { } } }
写好了如上的类,那么开始调用吧,调用:
DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(), @"F:\outputFormDataBase.xls");
这样成功将数据导出,如图。
转载于:https://blog.51cto.com/yangyoushan/1276598