| C#中数据库数据如何导出至Excel表格 | 您所在的位置：网站首页 › linux登陆数据库后怎么将查询数据导出成表格 › C#中数据库数据如何导出至Excel表格 |
C#中数据库数据如何导出至Excel表格
C#中数据库数据如何导出至Excel表格
标签： excel数据库c#insertstringnull
2011-12-13 16:21
17693人阅读
评论(9)
收藏
举报
分类：
版权声明：本文为博主原创文章，未经博主允许不得转载。 有时候需要将数据库的数据导出至Excel表格表格，以便进行查看和分析，那么如何导出呢？下面用代码来实现。 首先，新建一个工程，需要添加引用Microsoft.Office.Interop.Excel.dll，以Oracle数据库为例（只要读出DataTable或DataSet就行了，哪种数据库没关系）。 1、创建一个表格，并插入如下数据。 [sql] view plain copy print ? 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 );数据： [sql] view plain copy print ? 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#代码实现 数据库操作的类： [csharp] view plain copy print ? 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; } } [sql] view plain copy print ? 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的类： [csharp] view plain copy print ? public class DataChangeExcel { /// /// 数据库转为excel表格 /// /// 数据库数据 /// 导出的excel文件 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(); } } }关闭进程的类: [csharp] view plain copy print ? 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 { } } }写好了如上的类，那么开始调用吧，调用： [csharp] view plain copy print ? DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(), @"F:\outputFormDataBase.xls");这样成功将数据导出，如图。
|
【本文地址】
公司简介
联系我们