DataSet导出到EXCEL

string physicPath = HttpContext.Current.Server.MapPath(this.Context.Request.Path);//獲取路徑
        string fileName = Guid.NewGuid() + ".Xls";
        string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + physicPath + fileName + ";Extended Properties=Excel 8.0;";
        ToExcel(ds.Tables[0], connString);
        Response.Clear();
        Response.WriteFile(physicPath + fileName);
        string httpHeader = "attachment;filename=" + "iniu因你服饰" + "_" + string.Format("{0:yyyyMMdd}", DateTime.Today) +

string.Format("{0:00}", DateTime.Now.Hour) + string.Format("{0:00}", DateTime.Now.Minute) + ".xls";
        Response.ContentType = "application/vnd.ms-excel";
        Response.AppendHeader("Content-Disposition", httpHeader);
        Response.Flush();
        System.IO.File.Delete(physicPath + fileName);//下載后刪除創建的EXCEL
        Response.End();

public void ToExcel(DataTable dt, string connString)
    {
        int rows = dt.Rows.Count;
        int cols = dt.Columns.Count;
        StringBuilder sb = new StringBuilder();
        sb.Append("CREATE TABLE ");
        sb.Append(dt.TableName + " ( ");
        for (int i = 0; i < cols; 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();
            objConn.Open();
            objCmd.ExecuteNonQuery();

            #region 開始處理資料內容的新增
            sb.Remove(0, sb.Length);
            sb.Append("INSERT INTO ");
            sb.Append(dt.TableName + " ( ");
            for (int i = 0; i < cols; i++)
            {
                if (i < cols - 1)
                    sb.Append(dt.Columns[i].ColumnName + ",");
                else
                    sb.Append(dt.Columns[i].ColumnName + ") values (");
            }
            for (int i = 0; i < cols; i++)
            {
                if (i < cols - 1)
                    sb.Append("@" + dt.Columns[i].ColumnName + ",");
                else
                    sb.Append("@" + dt.Columns[i].ColumnName + ")");
            }
            #endregion
            objCmd.CommandText = sb.ToString();
            OleDbParameterCollection param = objCmd.Parameters;
            for (int i = 0; i < cols; i++)
            {
                param.Add(new OleDbParameter("@" + dt.Columns[i].ColumnName, OleDbType.VarChar));
            }
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < param.Count; i++)
                {
                    param[i].Value = row[i];
                }
                objCmd.ExecuteNonQuery();
            }
        }
    }

转载于:https://www.cnblogs.com/GerryGe/archive/2011/07/13/2105221.html