Question

请教各位高手,C#中想把dataset中的数据导成excel表该怎么操作,万分感谢!

Answer 1

///
<summary>
///
导出到Excel文件
///
</summary>
///
<param
name="schemaTable">表格式</param>
///
<param
name="tableName">表名</param>
///
<param
name="filePath">路径</param>
///
<param
name="source">需导出的表</param>
///
<returns>导出的记录数</returns>
public
int
ExportToExcel(DataTable
schemaTable,
string
tableName,
string
filePath,
DataTable
source)
{
string
connString
=
"Provider
=
Microsoft.Jet.OLEDB.4.0
;Persist
Security
Info=False;
Data
Source
=
"
+
filePath
+
";Extended
Properties=Excel
8.0;";//'HDR=Yes;IMEX=1";
if(System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
string
create
=
"Create
Table
[";
create
+=
tableName;
create
+=
"]
(";
string
sourceColumn
=
"(";
foreach(DataRow
dr
in
schemaTable.Rows)
{
string
columnName
=
dr["ColumnName"].ToString();
//create
string
create
+=
"[";
create
+=
columnName;
create
+=
"]";
sourceColumn
+=
"["
+
columnName
+
"],";
string
type
=
dr["DataType"].ToString();
switch(type)
{
case
"System.Int32":
case
"System.Int16":
case
"System.Int64":
case
"System.UInt32":
case
"System.UInt16":
case
"System.UInt64":
create
+=
"
INTEGER
NULL,";
break;
case
"System.Decimal":
case
"System.Double":
case
"System.Single":
create
+=
"
numeric
NULL,";
break;
case
"System.DateTime":
create
+=
"
datetime
NULL,";
break;
case
"System.Boolean":
create
+=
"
BINARY
NULL,";
break;
default:
create
+=
"
varchar(";
create
+=
dr["ColumnSize"].ToString();
create
+=
")
NULL,";
break;
}
}
create
=
create.Substring(0,
create.Length
-
1);
create
+=
")";
using(OleDbConnection
conn
=
new
OleDbConnection(connString))
{
conn.Open();
using(OleDbTransaction
trans
=
conn.BeginTransaction())
{
try
{
OleDbHelper.ExecuteNonQuery(trans,
create);//create
table
string
insertHead
=
"Insert
Into
["
+
tableName
+
"]"
+
sourceColumn.Substring(0,
sourceColumn.Length
-
1)
+
")
Values";
int
result
=
0;
foreach(DataRow
dr
in
source.Rows)
{
string
valueStr
=
"(";
for(int
j
=
0;
j
<
source.Columns.Count;
j++)
{
if(source.Columns[j].DataType
==
typeof(System.String))
{
valueStr
+=
"'"
+
dr[j].ToString().Replace("'",
"’")
+
"',";
}
else
if(source.Columns[j].DataType
==
typeof(System.DateTime))
{
string
date
=
dr[j].ToString();
if(date
==
string.Empty)
{
valueStr
+=
"null,";
}
else
{
valueStr
+=
"'"
+
date
+
"',";
}
}
else
{
valueStr
+=
dr[j].ToString()
+
",";
}
}
string
insert
=
insertHead
+
valueStr.Substring(0,
valueStr.Length
-
1)
+
")";
result
+=
OleDbHelper.ExecuteNonQuery(trans,
insert);
}
trans.Commit();
return
result;
}
catch
{
trans.Rollback();
throw;
}
}
}
}