OLEDB 导出EXCEL

最新推荐文章于 2022-10-10 17:26:44 发布

Apocalypse

最新推荐文章于 2022-10-10 17:26:44 发布

阅读量1k

点赞数

分类专栏： c# 文章标签： excel string sql properties exception path

本文链接：https://blog.csdn.net/memorylose/article/details/7717312

版权

c# 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

   //export excel
        private void GetExcel(DataTable dt, string RealPath,string ModelPath)
        {         
            string FormerPath = System.Web.HttpContext.Current.Server.MapPath(RealPath);
            //model excel
            string Path = System.Web.HttpContext.Current.Server.MapPath(ModelPath);
            try
            {
                if (File.Exists(FormerPath))
                {
                    File.Delete(FormerPath);
                }
                File.Copy(Path, FormerPath);

            }
            catch (Exception er)
            { 
                
            }
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FormerPath + ";Extended Properties='Excel 12.0;HDR=YES;'";
            for (int row = 0; row < dt.Rows.Count; row++)
            {
                StringBuilder sb = new StringBuilder();
                string sql = "";
                //the same as excel column
                sql = " insert into [sheet1$]([PU],[Month],[Inter Company Revenue],[Direct Revenue],[Onsite Revenue],[Local Revenue],[Inter Company Person Month],[Direct and Local Person Month],[Onsite Person Month],[Stat],[MIS] ) values ( ";

                //for (int col = 1; col < dt.Columns.Count; col++)
                //{
                //    if (col < dt.Columns.Count - 1)
                //        sql += dt.Columns[col].ColumnName + ",";
                //    else
                //        sql += dt.Columns[col].ColumnName + ") values (";
                //}
                sb.Append(sql);
                for (int col = 1; col < dt.Columns.Count; col++)
                {
                    if (col < dt.Columns.Count - 1)
                        sb.Append("'" + dt.Rows[row][col].ToString() + "',");
                    else
                        sb.Append("'" + dt.Rows[row][col].ToString() + "')");
                }
                using (OleDbConnection objConn = new OleDbConnection(strConn))
                {
                    objConn.Open();
                    OleDbCommand ocmd = new OleDbCommand(sb.ToString(), objConn);
                    ocmd.ExecuteNonQuery();           
                }                
            }           
            System.Web.HttpContext.Current.Response.Redirect(RealPath);
        }