OleDb匯出標準格式EXCEL,

燕兒歸發表於2015-08-25

應用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);
        }


相關文章