?
C#中数据库数据如何导出至Excel表格
@H_301_7@
标签：?excel数据库c#insertstringnull
2011-12-13 16:21?
17693人阅读?
?
收藏?
举报
?
分类：
?
版权声明：本文为博主原创文章，未经博主允许不得转载。
??? 有时候需要将数据库的数据导出至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),248)"> ??col_work?VARCHAR2(32),153); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> ??col_mony?FLOAT??
- );??
数据：
copy
? 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_mony)??
values?(2,?'孙二',?24,?'JAVA',?4999);??
??
values?(3,?'张三',?'PHP',?5001);??
values?(4,?'李四',?26,?'DELPHI',?5002);??
values?(5,?'王五',?27,?'C++',?5003);??
values?(6,?'赵六',?'C',?4008);??
values?(7,?'燕七',?'数据库',?4007);??
values?(8,?'胡八',?'JSP',?5005);??
values?(9,?'钱九',?'ASP.NET',?4005);??
values?(10,?'沈十',?'VB',?4000);??
commit;??
2、C#代码实现
数据库操作的类：
[csharp]?
copy
??
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;??
- ????}??
- ?}??
copy
? 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的类：
copy
? public?class?DataChangeExcel??
??????????
??????????
???????????
?????????
?????????
????????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,248)"> ????????????????misValue,?Excel.XlSaveAsAccessMode.xlExclusive,153); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> ??????????????????
????????????dataTable?=?null;??
????????????workBook.Close(true,?misValue);??
????????????excel.Quit();??
????????????PublicMethod.Kill(excel);??
????????????releaseObject(workSheet);??
????????????releaseObject(workBook);??
????????????releaseObject(excel);??
????????private?static?void?releaseObject(object?obj)??
????????????try??
????????????{??
????????????????System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);??
????????????????obj?=?null;??
????????????catch??
????????????finally??
????????????????GC.Collect();??
????????}??
?}??
关闭进程的类:
copy
? 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();???
????????{?}??
???}??
}??
写好了如上的类，那么开始调用吧，调用：
copy
? DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(),???
@"F:outputFormDataBase.xls");??
这样成功将数据导出，如图。
? （编辑：北几岛）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
