C#中数据库数据如何导出至Excel表格Word文件下载.docx
《C#中数据库数据如何导出至Excel表格Word文件下载.docx》由会员分享,可在线阅读,更多相关《C#中数据库数据如何导出至Excel表格Word文件下载.docx(17页珍藏版)》请在冰豆网上搜索。
);
数据:
1.insert
into
(col_id,
col_name,
col_age,
col_sex,
col_work,
col_mony)
2.values
(1,
'
吴一'
25,
男'
.NET'
5000);
3.
4.insert
5.values
(2,
孙二'
24,
JAVA'
4999);
7.insert
8.values
(3,
张三'
PHP'
5001);
10.insert
11.values
(4,
李四'
26,
DELPHI'
5002);
12.
13.insert
14.values
(5,
王五'
27,
C++'
5003);
15.
16.insert
17.values
(6,
赵六'
C'
4008);
18.
19.insert
20.values
(7,
燕七'
数据库'
4007);
21.
22.insert
23.values
(8,
胡八'
JSP'
5005);
24.
25.insert
26.values
(9,
钱九'
ASP.NET'
4005);
27.
28.insert
29.values
(10,
沈十'
VB'
4000);
30.commit;
insertintoTABLETESTEXCEL(col_id,col_name,col_age,col_sex,col_work,col_mony)
values(1,'
25,'
'
5000);
values(2,'
24,'
4999);
values(3,'
5001);
values(4,'
26,'
5002);
values(5,'
27,'
5003);
values(6,'
4008);
values(7,'
4007);
values(8,'
5005);
values(9,'
4005);
values(10,'
4000);
commit;
2、C#代码实现
数据库操作的类:
[csharp]viewplaincopyprint?
1.public
class
DataBaseHelper
2.
{
public
static
DataTable
ExecuterQuery(string
connectionString,
string
commandSql)
dataTable
=
new
DataTable();
try
using
(OracleConnection
oracleConnection
10.
OracleConnection(connectionString))
11.
oracleConnection.Open();
13.
14.
(OracleDataAdapter
oracleDataAdapter
OracleDataAdapter(commandSql,oracleConnection))
16.
17.
oracleDataAdapter.Fill(dataTable);
}
19.
20.
oracleConnection.Close();
22.
23.
catch
25.
return
null;
26.
28.
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;
DataBaseDao
2.{
GetDataBaseTable()
sql
"
SELECT
*
FROM
tableTestExcel"
;
DataBaseHelper.ExecuterQuery("
User
ID=downsoft;
Password=sys;
Data
Source=orcl"
sql);
9.}
publicclassDataBaseDao
{
publicstaticDataTableGetDataBaseTable()
stringsql="
SELECT*FROMtableTestExcel"
returnDataBaseHelper.ExecuterQuery("
UserID=downsoft;
DataSource=orcl"
sql);
}
导出Excel的类:
DataChangeExcel
///
<
summary>
数据库转为excel表格
/summary>
param
name="
dataTable"
>
数据库数据<
/param>
SaveFile"
导出的excel文件<
void
DataSetToExcel(DataTable
dataTable,
SaveFile)
Excel.Application
excel;
Excel._Workbook
workBook;
Excel._Worksheet
workSheet;
object
misValue
System.Reflection.Missing.Value;
excel
Excel.ApplicationClass();
workBook
excel.Workbooks.Add(misValue);
workSheet
(Excel._Worksheet)workBook.ActiveSheet;
int
rowIndex
1;
colIndex
0;
//取得标题
foreach
(DataColumn
col
in
dataTable.Columns)
31.
colIndex++;
32.
33.
excel.Cells[1,
colIndex]
col.ColumnName;
34.
35.
36.
//取得表格中的数据
37.
(DataRow
row
dataTable.Rows)
38.
39.
rowIndex++;
40.
41.
42.
43.
44.
45.
46.
47.
excel.Cells[rowIndex,
48.
49.
row[col.ColumnName].ToString().Trim();
50.
51.
//设置表格内容居中对齐
52.
workSheet.get_Range(excel.Cells[rowIndex,
colIndex],
53.
54.
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.
Excel.XlSaveAsAccessMode.xlExclusive,
65.
66.
misValue);
67.
68.
69.
70.
workBook.Close(true,
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
releaseObject(object
obj)
85.
86.
87.
88.
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
89.
obj
90.
91.
92.
93.
94.
95.
finally
96.
97.
GC.Collect();
98.
99.
100.
publicclassDataChangeExcel
///<
///数据库转为excel表格
paramname="
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;
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;