ASP.NET用DataTable导出到Excel-CSDN博客

public static void IsExcel(DataTable dsBZDM)
        {
            //读取临时文件

            //GYYW.DA.Common.Base_SqlDataBase daBZDM = new GYYW.DA.Common.Base_SqlDataBase();

            //DataSet dsBZDM = daBZDM.GetDataSetBySql("select QCDM,MC,GG from WG_BZDM where QCDM like '02%'");
            //AlarmDataBLL bll = new AlarmDataBLL();
            //DataTable dsBZDM = bll.GetAlarmDataList("", "", "",
            //    "", "", "", "", "", "0");


            //同时将虚拟目录下的Data作为临时文件目录。

            string urlPath = HttpContext.Current.Request.ApplicationPath + "/DownLoad/";

            string physicPath = HttpContext.Current.Server.MapPath(urlPath);

            //string fileName = Guid.NewGuid() + ".Xls";

            string fileName = DateTime.Now.ToString("yyyyMMddHHmmss")+".Xls";
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + physicPath + fileName + ";Extended Properties=Excel 8.0;";



            OleDbConnection objConn = new OleDbConnection(connString);

            OleDbCommand objCmd = new OleDbCommand();

            objCmd.Connection = objConn;

            objCmd.Connection.Open();

            //建立表结构

            objCmd.CommandText = @"CREATE TABLE Sheet1(器材代码 varchar,名称 varchar, 规格 varchar)";

            objCmd.ExecuteNonQuery();



            //建立插入动作的Command

            objCmd.CommandText = "INSERT INTO Sheet1(器材代码, 名称,规格) VALUES (@QCDM, @MC, @GG)";

            objCmd.Parameters.Clear();

            objCmd.Parameters.Add(new OleDbParameter("@QCDM", OleDbType.VarChar));

            objCmd.Parameters.Add(new OleDbParameter("@MC", OleDbType.VarChar));

            objCmd.Parameters.Add(new OleDbParameter("@GG", OleDbType.VarChar));

            //遍历DataSet将数据插入新建的Excel文件中

            foreach (DataRow row in dsBZDM.Rows)
            {

                for (int i = 0; i < objCmd.Parameters.Count; i++)
                {

                    objCmd.Parameters[i].Value = row[i];

                }

                objCmd.ExecuteNonQuery();

            }

            objCmd.Connection.Close();



            //提供下载

            //清除临时文件

            HttpResponse response = HttpContext.Current.Response;

            response.Clear();

            //为输出作准备

            response.WriteFile(physicPath + fileName);

            string httpHeader = "attachment;filename="+DateTime.Now.ToString("yyyyMMddHHmmss")+".Xls";

            response.AppendHeader("Content-Disposition", httpHeader);

            response.Flush();

            //输出完毕后清除临时文件
            string strSaveDir = "../DownLoad/";

            string strFile = HttpContext.Current.Server.MapPath(strSaveDir + fileName).ToString();

            //string sss = urlPath + fileName;

            System.IO.File.Delete(strFile);//删除临时文件

            response.End();
        }