使用ADO.net将数据导出到Excel并提供下载
转载自：互联网 作者：cd3c.com
您正在看的asp教程是:使用ADO.net将数据导出到Excel并提供下载。public string DataTableToExcel(DataTable dt,string excelPath)
{
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 ("); } for(int i=0;i { if(i < cols - 1) sb.Append("@" + dt.Columns[i].ColumnName + ","); else sb.Append("@" + dt.Columns[i].ColumnName + ")"); } #endregion //建立插入动作的Command objCmd.CommandText = sb.ToString(); OleDbParameterCollection param = objCmd.Parameters; for(int i=0;i { param.Add(new OleDbParameter("@" + dt.Columns[i].ColumnName, OleDbType.VarChar)); } //遍历DataTable将数据插入新建的Excel文件中 foreach (DataRow row in dt.Rows) { for (int i=0; i { param[i].Value = row[i]; } objCmd.ExecuteNonQuery(); } return "数据已成功导入Excel"; }//end using }
