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

C#中数据库数据如何导出至Excel表格 有时候需要将数据库的数据导出至Excel表格表格，以便进行查看和分析，那么如何导出呢？下面用代码来实现。 首先，新建一个工程，需要添加引用Microsoft.Office.Interop.Excel.dll，以Oracle数据库为例（只要读出DataTable或DataSet就行了，哪种数据库没关系）。1、创建一个表格，并插入如下数据。sql view plaincopyprint?1. droptableTABLETESTEXCEL;2. createtableTABLETESTEXCEL3. (4. col_idNUMBERnotnull,5. col_nameVARCHAR2(32),6. col_ageNUMBER,7. col_sexVARCHAR2(4),8. col_workVARCHAR2(32),9. col_monyFLOAT10. );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 plaincopyprint?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. 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;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 plaincopyprint?1. 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. 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 plaincopyprint?1. publicclassDataBaseDao2. 3. publicstaticDataTableGetDataBaseTable()4. 5. stringsql=SELECT*FROMtableTestExcel;6. 7. returnDataBaseHelper.ExecuterQuery(UserID=downsoft;Password=sys;DataSource=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的类：csharp view plaincopyprint?1. publicclassDataChangeExcel2. 3. / 4. /数据库转为excel表格 5. / 6. /数据库数据 7. /导出的excel文件 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. 28. /取得标题 29. foreach(DataColumncolindataTable.Columns)30. 31. colIndex+;32. 33. excel.Cells1,colIndex=col.ColumnName;34. 35. 36. /取得表格中的数据 37. foreach(DataRowrowindataTable.Rows)38. 39. rowIndex+;40. 41. colIndex=0;42. 43. foreach(DataColumncolindataTable.Columns)44. 45. colIndex+;46. 47. excel.CellsrowIndex,colIndex=48. 49. rowcol.ColumnName.ToString().Trim();50. 51. /设置表格内容居中对齐 52. workSheet.get_Range(excel.CellsrowIndex,colIndex,53. 54. excel.CellsrowIndex,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. privatestaticvoidreleaseObject(objectobj)85. 86. try87. 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. 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.Cells1, colIndex = col.ColumnName; /取得表格中的数据 foreach (DataRow row in dataTable.Rows) rowIndex+; colIndex = 0; foreach (DataColumn col in dataTable.Columns) colIndex+; excel.CellsrowIndex, colIndex = rowcol.ColumnName.ToString().Trim(); /设置表格内容居中对齐 workSheet.get_Range(excel.CellsrowIndex, colIndex, excel.CellsrowIndex, 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 plaincopyprint?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=System.Diagnostics.Process.GetProcessById(k);18. 19. p.Kill();20. 21. catch22. 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 写好了如上的类，那么开始调用吧，调用：csharp view plaincopyprint?1. DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(),2. F:outputFormDataBase.xls);DataChangeExcel.DataSetToExcel(DataBaseDao.GetDataBaseTable(), F:outputFormDataBase.xls);这样成功将数据导出，如图。C#从数据库中提取数据生成Excel表格最近因为需要学习了一下使用C#操作Excel表格，现在把我使用C#如何定制表格的过程提供给需要的兄弟：/*从数据库提取数据*/string strconn=packet size=4096;user id=sa;data source=localhost;persist security info=True;initial catalog=Database;password=sa;SqlConnection sqlconn=new SqlConnection(strconn);sqlconn.Open();SqlDataAdapter sqldataAdapter=new SqlDataAdapter(Select * from 数据表,sqlconn);DataSet myds=new DataSet();sqldataAdapter.Fill(myds);/*在Execl中建立“成果表”的格式表格*/Excel.ApplicationClass excel=new Excel.ApplicationClass();excel.Application.Workbooks.Add(true);excel.Cells1,1 = 单元名称 ; excel.Cells1,2=指标及其描述、特征值和权重;excel.Cells1,23=属性;excel.Cells2,2=D;excel.Cells2,5=R;excel.Cells2,8=A;excel.Cells2,11=S;excel.Cells2,14=T;excel.Cells2,17=I;excel.Cells2,20=C;excel.Cells2,23=结果;excel.Cells2,24=等级;excel.Cells3,2=描述;excel.Cells3,3=特征值;excel.Cells3,4=权重;excel.Cells3,5=描述;excel.Cells3,6=特征值;excel.Cells3,7=权重;excel.Cells3,8=描述;excel.Cells3,9=特征值;excel.Cells3,10=权重;excel.Cells3,11=描述;excel.Cells3,12=特征值;excel.Cells3,13=权重;excel.Cells3,14=描述;excel.Cells3,15=特征值;excel.Cells3,16=权重;excel.Cells3,17=描述;excel.Cells3,18=特征值;excel.Cells3,19=权重;excel.Cells3,20=描述;excel.Cells3,21=特征值;excel.Cells3,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_Rang