C#中数据库数据如何导出至Excel表格.docx
- 文档编号:4793279
- 上传时间:2022-12-09
- 格式:DOCX
- 页数:17
- 大小:32.68KB
C#中数据库数据如何导出至Excel表格.docx
《C#中数据库数据如何导出至Excel表格.docx》由会员分享,可在线阅读,更多相关《C#中数据库数据如何导出至Excel表格.docx(17页珍藏版)》请在冰豆网上搜索。
C#中数据库数据如何导出至Excel表格
C#中数据库数据如何导出至Excel表格
有时候需要将数据库的数据导出至Excel表格表格,以便进行查看和分析,那么如何导出呢?
下面用代码来实现。
首先,新建一个工程,需要添加引用Microsoft.Office.Interop.Excel.dll,以Oracle数据库为例(只要读出DataTable或DataSet就行了,哪种数据库没关系)。
1、创建一个表格,并插入如下数据。
[sql]viewplaincopyprint?
1.drop table TABLETESTEXCEL;
2.create table TABLETESTEXCEL
3.(
4. col_id NUMBER not null,
5. col_name VARCHAR2(32),
6. col_age NUMBER,
7. col_sex VARCHAR2(4),
8. col_work VARCHAR2(32),
9. col_mony FLOAT
10.);
droptableTABLETESTEXCEL;
createtableTABLETESTEXCEL
(
col_idNUMBERnotnull,
col_nameVARCHAR2(32),
col_ageNUMBER,
col_sexVARCHAR2(4),
col_workVARCHAR2(32),
col_monyFLOAT
);
数据:
[sql]viewplaincopyprint?
1.insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
2.values (1, '吴一', 25, '男', '.NET', 5000);
3.
4.insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
5.values (2, '孙二', 24, '男', 'JAVA', 4999);
6.
7.insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
8.values (3, '张三', 25, '男', 'PHP', 5001);
9.
10.insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
11.values (4, '李四', 26, '男', 'DELPHI', 5002);
12.
13.insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
14.values (5, '王五', 27, '男', 'C++', 5003);
15.
16.insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
17.values (6, '赵六', 25, '男', 'C', 4008);
18.
19.insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
20.values (7, '燕七', 25, '男', '数据库', 4007);
21.
22.insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
23.values (8, '胡八', 25, '男', 'JSP', 5005);
24.
25.insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
26.values (9, '钱九', 25, '男', 'ASP.NET', 4005);
27.
28.insert into TABLETESTEXCEL (col_id, col_name, col_age, col_sex, col_work, col_mony)
29.values (10, '沈十', 25, '男', 'VB', 4000);
30.commit;
insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)
values(1,'吴一',25,'男','.NET',5000);
insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)
values(2,'孙二',24,'男','JAVA',4999);
insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)
values(3,'张三',25,'男','PHP',5001);
insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)
values(4,'李四',26,'男','DELPHI',5002);
insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)
values(5,'王五',27,'男','C++',5003);
insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)
values(6,'赵六',25,'男','C',4008);
insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)
values(7,'燕七',25,'男','数据库',4007);
insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)
values(8,'胡八',25,'男','JSP',5005);
insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)
values(9,'钱九',25,'男','ASP.NET',4005);
insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)
values(10,'沈十',25,'男','VB',4000);
commit;
2、C#代码实现
数据库操作的类:
[csharp]viewplaincopyprint?
1.public class DataBaseHelper
2. {
3. public static DataTable ExecuterQuery(string connectionString, string commandSql)
4. {
5. DataTable dataTable = new DataTable();
6.
7. try
8. {
9. using (OracleConnection oracleConnection =
10. new OracleConnection(connectionString))
11. {
12. oracleConnection.Open();
13.
14. using (OracleDataAdapter oracleDataAdapter =
15. new OracleDataAdapter(commandSql,oracleConnection))
16. {
17. oracleDataAdapter.Fill(dataTable);
18. }
19.
20. oracleConnection.Close();
21. }
22. }
23. catch
24. {
25. return null;
26. }
27.
28. return dataTable;
29. }
30. }
publicclassDataBaseHelper
{
publicstaticDataTableExecuterQuery(stringconnectionString,stringcommandSql)
{
DataTabledataTable=newDataTable();
try
{
using(OracleConnectionoracleConnection=
newOracleConnection(connectionString))
{
oracleConnection.Open();
using(OracleDataAdapteroracleDataAdapter=
newOracleDataAdapter(commandSql,oracleConnection))
{
oracleDataAdapter.Fill(dataTable);
}
oracleConnection.Close();
}
}
catch
{
returnnull;
}
returndataTable;
}
}
[sql]viewplaincopyprint?
1.public class DataBaseDao
2.{
3. public static DataTable GetDataBaseTable()
4. {
5. string sql = " SELECT * FROM tableTestExcel";
6.
7. return DataBaseHelper.ExecuterQuery("User ID=downsoft;Password=sys;Data Source=orcl", sql);
8. }
9.}
publicclassDataBaseDao
{
publicstaticDataTableGetDataBaseTable()
{
stringsql="SELECT*FROMtableTestExcel";
returnDataBaseHelper.ExecuterQuery("UserID=downsoft;Password=sys;DataSource=orcl",sql);
}
}
导出Excel的类:
[csharp]viewplaincopyprint?
1.public class DataChangeExcel
2.{
3. ///
4. /// 数据库转为excel表格
5. ///
6. /// 数据库数据
7. /// 导出的excel文件
8. public static void DataSetToExcel(DataTable dataTable, string SaveFile)
9. {
10. Excel.Application excel;
11.
12. Excel._Workbook workBook;
13.
14. Excel._Worksheet workSheet;
15.
16. object misValue = System.Reflection.Missing.Value;
17.
18. excel = new Excel.ApplicationClass();
19.
20. workBook = excel.Workbooks.Add(misValue);
21.
22. workSheet = (Excel._Worksheet)workBook.ActiveSheet;
23.
24. int rowIndex = 1;
25.
26. int colIndex = 0;
27.
28. //取得标题
29. foreach (DataColumn col in dataTable.Columns)
30. {
31. colIndex++;
32.
33. excel.Cells[1, colIndex] = col.ColumnName;
34. }
35.
36. //取得表格中的数据
37. foreach (DataRow row in dataTable.Rows)
38. {
39. rowIndex++;
40.
41. colIndex = 0;
42.
43. foreach (DataColumn col in dataTable.Columns)
44. {
45. colIndex++;
46.
47. excel.Cells[rowIndex, colIndex] =
48.
49. row[col.ColumnName].ToString().Trim();
50.
51. //设置表格内容居中对齐
52. workSheet.get_Range(excel.Cells[rowIndex, colIndex],
53.
54. excel.Cells[rowIndex, 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. private static void releaseObject(object obj)
85. {
86. try
87. {
88. System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
89. obj = null;
90. }
91. catch
92. {
93. obj = null;
94. }
95. finally
96. {
97. GC.Collect();
98. }
99. }
100. }
publicclassDataChangeExcel
{
///
///数据库转为excel表格
///
///
///
publicstaticvoidDataSetToExcel(DataTabledataTable,stringSaveFile)
{
Excel.Applicationexcel;
Excel._WorkbookworkBook;
Excel._WorksheetworkSheet;
objectmisValue=System.Reflection.Missing.Value;
excel=newExcel.ApplicationClass();
workBook=excel.Workbooks.Add(misValue);
workSheet=(Excel._Worksheet)workBook.ActiveSheet;
introwIndex=1;
intcolIndex=0;
//取得标题
foreach(DataColumncolindataTable.Columns)
{
colIndex++;
excel.Cells[1,colIndex]=col.ColumnName;
}
//取得表格中的数据
foreach(DataRowrowindataTable.Rows)
{
rowIndex++;
colIndex=0;
foreach(DataColumncolindataTable.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;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- C# 数据库 数据 如何 导出 Excel 表格
![提示](https://static.bdocx.com/images/bang_tan.gif)