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

C#C#Excel有时候需要将数据库的数据导出至Excel首先，新建一个工程，需要添加引用Microsoft.Office.Interop.Excel.dll，以Oracle数据库为例（只要读出DataTable或DataSet就行了，哪种数据库没关系）。1、创建一个表格，并插入如下数据。[sql]viewplaincopyprint?droptableTABLETESTEXCEL;droptableTABLETESTEXCEL;createtableTABLETESTEXCEL3.(4.col_idNUMBERnotnull,5.col_nameVARCHAR2(32),6.col_ageNUMBER,7.col_sexVARCHAR2(4),8.col_workVARCHAR2(32),9.col_monyFLOAT10.);数据：[sql]viewplaincopyprint?1.insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)2.values(1,'吴一',25,'男','.NET',5000);3.4.insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)5.values(2,'孙二',24,'男','JAVA',4999);6.7.insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)8.values(3,'张三',25,'男','PHP',5001);9.9.10.insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)11.values(4,'李四',26,'男','DELPHI',5002);12.13.insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)14.values(5,'王五',27,'男','C++',5003);15.16.insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)17.values(6,'赵六',25,'男','C',4008);18.19.insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)20.values(7,'燕七',25,'男','数据库',4007);21.22.insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)23.values(8,'胡八',25,'男','JSP',5005);24.25.insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)26.values(9,'钱九',25,'男','ASP.NET',4005);27.28.insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)29.values(10,'沈十',25,'男','VB',4000);30.commit;22、C#代码实现数据库操作的类：[csharp]viewplaincopyprint?1.publicclass1.publicclassDataBaseHelper2. {3. publicstaticDataTableExecuterQuery(stringconnectionString,stringcommandSql)4.{5.DataTabledataTable=newDataTable();6.7.try8.{9.using(OracleConnectionoracleConnection=10.newOracleConnection(connectionString))11.{12.oracleConnection.Open();13.14.using(OracleDataAdapteroracleDataAdapter=15.newOracleDataAdapter(commandSql,oracleConnection))16.{17.oracleDataAdapter.Fill(dataTable);18.}19.20.oracleConnection.Close();21.}22.}23.catch24.{25.returnnull;26.}27.28.returndataTable;29.}30.}[sql]view[sql]viewplaincopyprint?1.publicclassDataBaseDao2.{3.3.publicstaticDataTableGetDataBaseTable()4.{5.stringsql="SELECT*FROMtableTestExcel";6.7. returnDataBaseHelper.ExecuterQuery("UserID=downsoft;Password=sys;DataSource=orcl",sql);8.}9.}ExcelExcel的类：[csharp]viewplaincopyprint?1.publicclassDataChangeExcel2.{3.///<summary>4.///excel表格5.///</summary>6.///<paramname="dataTable">数据库数据</param>7.///<paramname="SaveFile">excel文件</param>8.publicstaticvoidDataSetToExcel(DataTabledataTable,stringSaveFile)9.{10.Excel.Applicationexcel;11.12.Excel._WorkbookworkBook;13.14.Excel._WorksheetworkSheet;15.16.objectmisValue=System.Reflection.Missing.Value;17.18.excel=newExcel.ApplicationClass();19.20.workBook=excel.Workbooks.Add(misValue);21.22.workSheet=(Excel._Worksheet)workBook.ActiveSheet;23.24. introwIndex=1;25.26. intcolIndex=0;27.//取得标题foreach(DataColumncolindataTable.Columns)30. {31. colIndex++;32.33. excel.Cells[1,colIndex]=col.ColumnName;34. }35.//取得表格中的数据foreach(DataRowrowindataTable.Rows)38. {39. rowIndex++;40.41. colIndex=0;42.43. foreach(DataColumncolindataTable.Columns)44. {45. colIndex++;46.47. excel.Cells[rowIndex,colIndex]=48.49. 50.//设置表格内容居中对齐workSheet.get_Range(excel.Cells[rowIndex,colIndex], excel.Cells[rowIndex,colIndex]).HorizontalAlignment=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. 73.74. PublicMethod.Kill(excel);//killexcel进程75.76. 77.78. 79.80. 81.82. }83.84. privatestaticvoidreleaseObject(objectobj)85. {86. try87. {88.88.System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);89.obj=null;90.}91.catch92.{93.obj=null;94.}95.finally96.{97.GC.Collect();98.}99.}100.}关闭进程的类:关闭进程的类:[csharp]viewplaincopyprint?1.publicclassPublicMethod2.{3.[DllImport("User32.dll",CharSet=CharSet.Auto)]4.5. publicstaticexternintGetWindowThreadProcessId(IntPtrhwnd,outintID);6.7.publicstaticvoidKill(Microsoft.Office.Interop.Excel.Applicationexcel)8.{9.try10.{11.IntPtrt=newIntPtr(excel.Hwnd);12.13.intk=0;14.15.GetWindowThreadProcessId(t,outk);16.17. System.Diagnostics.Processp=16.17. System.Diagnostics.Processp=System.Diagnostics.Process.GetProcessById(k);18.19.p.Kill();这样成功将数据导出，如图。C#Excel表格20.}21.catch22.{}23.}24.}写好了如上的类，那么开始调用吧，调用：[csharp]写好了如上的类，那么开始调用吧，调用：[csharp]viewplaincopyprint?DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(),@"F:\outputFormDataBase.xls");最近因为需要学习了一下使用C#操作Excel表格，现在把我使用C#如何定制表格的过程提供给需要的兄弟：/*从数据库提取数据*/string strconn="packet size=4096;user id=sa;data source=localhost;persist info=True;initialcatalog=Database;password=sa";SqlConnectionsqlconn=newSqlConnection(strconn);sqlconn.Open();SqlDataAdaptersqldataAdapter=newSqlDataAdapter("Select*from[数据表]",sqlconn);DataSetmyds=newDataSet();sqldataAdapter.Fill(myds);/*在Execl中建立“成果表”的格式表格*/Excel.ApplicationClassexcel=newExcel.ApplicationClass();excel.Application.Workbooks.Add(true);excel.Cells[1,1]="单元名称";excel.Cells[1,2]="指标及其描述、特征值和权重";excel.Cells[1,23]="属性";excel.Cells[2,2]="D";excel.Cells[2,5]="R";excel.Cells[2,8]="A";excel.Cells[2,11]="S";excel.Cells[2,14]="T";excel.Cells[2,17]="I";excel.Cells[2,20]="C";结果";等级";excel.Cells[3,2]="描述";excel.Cells[3,3]="特征值";权重";描述";excel.Cells[3,6]="特征值";权重";描述";excel.Cells[3,9]="特征值";权重";excel.Cells[3,11]="描述";excel.Cells[3,12]="excel.Cells[3,13]="权重";excel.Cells[3,14]="描述";excel.Cells[3,15]="excel.Cells[3,16]="权重";excel.Cells[3,17]="描述";excel.Cells[3,18]="excel.Cells[3,19]="权重";excel.Cells[3,20]="描述";excel.Cells[3,21]="excel.Cells[3,22]="权重";excel.get_Range(excel.Cells[1,1],excel.Cells[3,1]).MergeCells=true;excel.get_Range(excel.Cells[1,2],excel.Cells[1,22]).MergeCells=true;excel.get_Range(excel.Cells[1,23],excel.Cells[1,24]).MergeCells=true;excel.get_Range(excel.Cells[2,2],excel.Cells[2,4]).MergeCells=true;excel.get_Range(excel.Cells[2,5],excel.Cells[2,7]).MergeCells=true;excel.get_Range(excel.Cells[2,8],excel.Cells[2,10]).MergeCells=true;excel.get_Range(excel.Cells[2,11],excel.Cells[2,13]).MergeCells=true;excel.get_Range(excel.Cells[2,14],excel.Cells[2,16]).MergeCells=true;excel.get_Range(excel.Cells[2,17],excel.Cells[2,19]).MergeCells=true;excel.get_Range(excel.Cells[2,20],excel.Cells[2,22]).MergeCells=true;excel.get_Range(excel.Cells[2,23],excel.Cells[3,23]).MergeCells=true;excel.get_Range(excel.Cells[2,24],excel.Cells[3,24]).MergeCells