如何实现dataset导出到excel，并且能保证相应的效率。。

[iyranly]

如何实现dataset导出到excel，并且能保证相应的效率。。 非常急，着急得很....解决立即给分...

[沅江汐水]

/// <summary>
/// 导出数据
/// </summary>
/// <param name="dset">将要导出的DataSet，其中的每一个Table必须有一个明确的名字</param>
/// <param name="filepath">目的文件的路径，该数据库必须存在且允许internet来宾用户写入</param>
/// <param name="maxlenth">DataSet中字段的最大长度</param>
/// <returns></returns>
public static bool importToAccess( DataSet dset/*DataTable dt*/,string filepath,/*string tableName,*/int maxlenth)
{
bool ret = false;
if(!Path.GetExtension(filepath).ToLower().Equals(".mdb"))
return ret;
if(!File.Exists(filepath))
// File.Create(filepath);
{
throw new Exception("文件不存在！");
}
if(dset != null && dset.Tables.Count > 0)
{
//2008-01-16
//OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;User Id=admin;jet OleDB:Database Password=sva!@#$%^&*();Data Source=" + filepath);

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;User Id=admin;Data Source=" + filepath);
//2008-01-16

try
{
conn.Open();



for(int i = 0; i<dset.Tables.Count; i++)
{
try
{
OleDbCommand cmd = new OleDbCommand("Select * from " + dset.Tables[i].TableName/*tableName*/,conn);
cmd.ExecuteNonQuery();
cmd.CommandText = "Drop Table " + dset.Tables[i].TableName; /*tableName;*/
cmd.ExecuteNonQuery();
cmd.Dispose();
}
catch
{

}

string str = "Create Table " + /*tableName*/ dset.Tables[i].TableName + "(" ;
for(int j=0;j<dset.Tables[i].Columns.Count;j++)
{
str = str + "[" + dset.Tables[i].Columns[j].ColumnName + "] "; // + dt.Columns[i].DataType.ToString() + ",";
string type = dset.Tables[i].Columns[j].DataType.ToString();
switch(dset.Tables[i].Columns[j].DataType.ToString())
{
case "System.String":
str = str + "varchar(" + maxlenth + "),";
break;
case "System.Int32":
str = str + "int,";
break;
case "System.DateTime":
str = str + "DateTime,";
break;
default:
str =str+"varchar(" + maxlenth + "),";
break;
}
}
str = str.Substring(0,str.Length-1) + ")";

OleDbCommand o = new OleDbCommand(str,conn);
o.ExecuteNonQuery();
o.Dispose();
}



for(int i = 0;i<dset.Tables.Count;i++)
{
OleDbDataAdapter da = new OleDbDataAdapter();
OleDbCommand command = new OleDbCommand();

command.Connection = conn;

int col = dset.Tables[i].Columns.Count;
//int col = dt.Columns.Count;
string commandText = "Insert into " + dset.Tables[i].TableName /*tableName*/ + " values(";
ArrayList va = new ArrayList();
for(int j=0;j<col;j++)
{
va.Add(dset.Tables[i].Columns[j].ColumnName);
commandText = commandText + /*"@" + va[i].ToString()*/ "?" + ",";
}



commandText = commandText.Remove(commandText.LastIndexOf(","),1);
commandText = commandText + ")";
//command.CommandType = CommandType
command.CommandText = commandText;
for(int k=0;k<va.Count;k++)
{
OleDbParameter old = new OleDbParameter("@" + va[k].ToString(),OleDbType.Variant);


old.SourceColumn = dset.Tables[i].Columns[va[k].ToString()].ColumnName;
old.SourceVersion = DataRowVersion.Original;
command.Parameters.Add(old);


}
DataSet ds = new DataSet();


DataTable d = new DataTable();
d = dset.Tables[i].Clone();

// ds.Tables.Add(d);

for(int n=0;n<dset.Tables[i].Rows.Count;n++)
{
DataRow r = d.NewRow();
r.ItemArray = dset.Tables[i].Rows[n].ItemArray;
d.Rows.Add(r);
//
// command.Parameters
}
ds.Tables.Add(d);
da.InsertCommand = command;
da.Update(ds,ds.Tables[0].TableName);

command.Dispose();
da.Dispose();

}

ret = true;
}
catch(Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
//command.Dispose();
// da.Dispose();
conn.Close();
conn.Dispose();
}
}
return ret;
}

[iyranly]

不错，收藏！

[michaelwangwh]

加office的refrence，用xlapp，xlworksheet, cells[i][j]=datatable[i][j]
效率方面理论上用这样的方法了最快了，不清楚lz的效率指的什么？