OleDb导出标准格式EXCEL，

应用oledb导出标准格式EXCEL，可以直接返回去导入的那种

        /// <summary>
        /// 导出excel
        /// </summary>
        /// <param name="dt">DataTable格式的数据</param>
        /// <param name="FileName">要导出的文件名</param>
        private void ExportExcel(DataTable dt,string FileName)
        {
            if (dt == null || dt.Rows.Count < 0) //dt.Rows.Count=0导出列名。
            {
                return;
            }
            //临时文件
            string tempFile = Server.MapPath("~/download/" + Guid.NewGuid() + ".xls");
            //File.Copy(Server.MapPath("download/demo.xls"), tempFile);
            //使用OleDb连接
            OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + tempFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'");
            StringBuilder head = new StringBuilder();
            StringBuilder values = new StringBuilder();
            using (con)
            {
                con.Open();
                //创建Sheet,创建列
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    head.Append(dt.Columns[i].ColumnName + " VarChar,");
                    values.Append("@" + dt.Columns[i].ColumnName + ",");
                }
                //string head = "[ID] VarChar,[名称] VarChar,[时间] VarChar";
                OleDbCommand cmdCreate = new OleDbCommand("CREATE TABLE Sheet1 (" + head.ToString().TrimEnd(',') + ")", con);
                cmdCreate.ExecuteNonQuery();

                //插入数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    OleDbCommand cmd = new OleDbCommand(@"INSERT INTO [Sheet1$] VALUES(" + values.ToString().TrimEnd(',') + ")", con);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        cmd.Parameters.AddWithValue("@" + dt.Columns[j].ColumnName, dt.Rows[i][j].ToString());
                    }
                    cmd.ExecuteNonQuery();
                }
            }
            Response.ContentType = "application/ms-excel";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(FileName) + ".xls");
            Response.Charset = "GB2312";
            GC.GetTotalMemory(false);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.GetTotalMemory(true);
            Response.BinaryWrite(File.ReadAllBytes(tempFile));
            File.Delete(tempFile);
        }

转载于:https://www.cnblogs.com/yanergui/p/5014302.html