使用ADO.net将数据导出到Excel并提供下载

{

if(dt == null)

{

return "DataTable不能为空";

}

int rows = dt.Rows.Count;

int cols = dt.Columns.Count;

StringBuilder sb;

string connString;

if(rows == 0)

{

return "没有数据";

}

sb = new StringBuilder();

connString = string.Format(ConnectionString,excelPath);

//生成创建表的脚本

sb.Append("CREATE TABLE ");

sb.Append(dt.TableName + " ( ");

for(int i=0;i

{

if(i < cols - 1)

sb.Append(string.Format("{0} varchar,",dt.Columns[i].ColumnName));

else

sb.Append(string.Format("{0} varchar)",dt.Columns[i].ColumnName));

}

using(OleDbConnection objConn = new OleDbConnection(connString))

{

OleDbCommand objCmd = new OleDbCommand();

objCmd.Connection = objConn;

objCmd.CommandText = sb.ToString();

try

{

objConn.Open();

objCmd.ExecuteNonQuery();

}

catch(Exception e)

{

return "在Excel中创建表失败，错误信息：" + e.Message;

}

生成插入数据脚本#region 生成插入数据脚本

sb.Remove(0,sb.Length);

sb.Append("INSERT INTO ");

sb.Append(dt.TableName + " ( ");

for(int i=0;i

{

if(i < cols - 1)

sb.Append(dt.Columns[i].ColumnName + ",");

else

sb.Append(dt.Columns[i].ColumnName + ") values (");