Excel文件处理Demo

1.BLL业务逻辑代码

/// <summary>
        /// 处理“店铺竞品销售数据”导入文件
        /// </summary>
        /// <param name="filePath">文件物理路径</param>
        /// <param name="ownerId">品牌Id</param>
        /// <param name="saleYM">竞品数据销售年月</param>
        /// <param name="msg">处理过程中出现的错误消息</param>
        /// <returns></returns>
        public bool HandleImportExcel(string filePath, string ownerId, string saleYM, out string msg)
        {
            using (DaoTransactionScope ts = new DaoTransactionScope(true))
            {
                msg = "";    // 返回给UI层的错误消息            

                #region 1.0 设置连接Excel组件的连接字符串
                // 获取导入文件的扩展名
                string fileExt = Path.GetExtension(filePath).ToLower();
                // Excel组件连接字符串
                string excelConnStr = string.Empty;
                if (fileExt.Equals(".xlsx"))
                {
                    // "HDR=Yes;" 表示第一行是标题行, 不是数据。"HDR=No;" 则表示从第一行开始就是数据。 
                    excelConnStr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=No;IMEX=1'", filePath);
                }
                else if (fileExt.Equals(".xls"))
                {
                    excelConnStr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=No;IMEX=1'", filePath);
                }
                else
                {
                    // "不是Excel文件，请重新选择文件导入！";   
                    msg = this.GetResourceString("NotExcelFile");
                    return false;
                } 
                #endregion

                string loginUser = this.LoginUser;
                // 总部营业日期
                string hqAccountDate = Convert.ToDateTime(new BLSystemInfo().GetSysValue(2033, 1)).ToString("yyyyMM");    
                DLSalesOtherMakerSearch dal = new DLSalesOtherMakerSearch();

                OleDbConnection oleConn = null;     // 连接Excel文件的连接对象
                DataSet ds = null;                  
                DataTable dtExcel = null;           // 存储Excel文件数据的内存表
                DataTable dtInsert = null;          // 存储处理后的Excel文件数据的内存表
                try
                {
                    #region 2.0 通过OleDbConnection对象从Excel文件中获取数据并存放在内存DataTable中（变量：dtExcel）

                    oleConn = new OleDbConnection(excelConnStr);
                    oleConn.Open();
                    DataTable schemaTable = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    // 获取Excel中sheet名称
                    string tableName = string.Format("[{0}]", schemaTable.Rows[0][2].ToString().Trim());
                    string sql = string.Format("select * from {0}", tableName);
                    ds = new DataSet();
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(sql, oleConn))
                    {                        
                        adapter.Fill(ds, tableName);
                    }
                    
                    // 第1，2,3行都是标题行
                    dtExcel = ds.Tables[tableName];
                    if (dtExcel.Rows.Count <= 3)
                    {
                        // "选择的文件没有需要导入的数据，请重新选择文件导入！";
                        msg = this.GetResourceString("ExcelFileNoData");
                        return false;
                    }

                    // 判断Excel中是否存在相同的店铺号，若存在则提示用户，不导入
                    // 根据店号进行分组
                    var storeList = from s in dtExcel.AsEnumerable()
                                    where s.Field<string>(0) != null
                                    group s by new { f = s.Field<string>(0).ToUpper() } into g                                    
                                    select new
                                    {
                                        StoreId = g.Key.f,
                                        StoreCount = g.Count()
                                    };
                    // 查出有多行店号的数据
                    storeList = storeList.Where(item => item.StoreCount > 1);
                    if (storeList.Count() > 0)
                    {
                        string storeIds = string.Empty;
                        storeList.ToList().ForEach(item => storeIds += item.StoreId + ",");
                        // [{0}]店铺存在多行销售数据，请删除多余行的数据，每个店铺只保留一行！
                        msg = this.GetResourceString("StoreSalesDataRepeated", storeIds.Substring(0, storeIds.Length - 1));
                        return false;
                    }

                    #endregion

                    #region 3.0 判断Excel表中的数据是否合法并转换存放在符合数据库表结构的内存表中（变量：dtInsert）

                    // 创建要插入到数据库中的内存表dtInsert
                    dtInsert = dal.GetOtherMakerSaleTblScheme();

                    // Excel中的第三行是竞品品牌名称，故从第四行开始循环遍历dtExcel（注意下标为3）
                    DataRow rowExcel;
                    DataRow rowInsert;

                    // 获取所有竞品品牌主档数据
                    List<OtherMakerMasTbl> otherMakerMasList = dal.GetOtherMakerMasList();
                    if (otherMakerMasList == null || otherMakerMasList.Count <= 0)
                    {
                        // 没有竞品品牌主档数据，请联系系统管理员重新导入！
                        msg = this.GetResourceString("OtherMakerMasDataNotExist");
                        return false;
                    }

                    // 循环遍历Excel中的第三行竞品品牌名称，获取对应的竞品品牌代码，并替换掉竞品品牌名称
                    // 从第二列开始循环遍历竞品品牌
                    OtherMakerMasTbl otherMakerMasModel = null;
                    string notExistOtherMakerStr = string.Empty;
                    Dictionary<string, string> dicOtherMakerMas = new Dictionary<string, string>();
                    for (int j = 1; j < dtExcel.Columns.Count; j++)
                    {
                        otherMakerMasModel = otherMakerMasList.SingleOrDefault(item => item.OtherMakerNm.Trim().Equals(dtExcel.Rows[2][j].ToString().Trim()));
                        if (otherMakerMasModel == null)
                        {
                            // 在竞品品牌主档中未找到 {0} 该品牌对应的数据，请仔细核对数据重新导入！
                            //msg = this.GetResourceString("OtherMakerNotFound", dtExcel.Rows[2][j].ToString().Trim());
                            notExistOtherMakerStr += "[" + dtExcel.Rows[2][j].ToString().Trim() + "],";
                        }
                        else
                        {
                            dicOtherMakerMas.Add(otherMakerMasModel.OtherMakerCd, otherMakerMasModel.OtherMakerNm.Trim());
                            dtExcel.Rows[2][j] = otherMakerMasModel.OtherMakerCd;
                        }
                    }
                    if (!string.IsNullOrEmpty(notExistOtherMakerStr))
                    {
                        msg = this.GetResourceString("OtherMakerNotFound", notExistOtherMakerStr.Substring(0, notExistOtherMakerStr.Length - 1));
                        return false;
                    }

                    for (int i = 3; i < dtExcel.Rows.Count; i++)
                    {
                        rowExcel = dtExcel.Rows[i];
                        // 从第二列开始循环遍历竞品品牌
                        for (int j = 1; j < dtExcel.Columns.Count; j++)
                        {
                            // 如果店号为空，则Continue
                            if (dtExcel.Rows[i][0] == null || string.IsNullOrEmpty(dtExcel.Rows[i][0].ToString()))
                            {
                                continue;
                            }

                            // 如果金额数字为空，则Continue
                            if (dtExcel.Rows[i][j] == null || string.IsNullOrEmpty(dtExcel.Rows[i][j].ToString()))
                            {
                                continue;
                            }
                            rowInsert = dtInsert.NewRow();
                            rowInsert["OwnerId"] = ownerId;
                            rowInsert["CustomerCd"] = dtExcel.Rows[i][0];
                            rowInsert["OtherMakerCd"] = dtExcel.Rows[2][j].ToString(); 
                            rowInsert["OtherSaleYM"] = saleYM;
                            try
                            {
                                rowInsert["OtherSaleMoney"] = Convert.ToDecimal(dtExcel.Rows[i][j]);
                            }
                            catch (Exception)
                            {
                                // 店号为{0}的竞品品牌{1}填写的金额数字{2}有误，请重新导入
                                msg += this.GetResourceString("SaleAmoutError", dtExcel.Rows[i][0], dicOtherMakerMas[dtExcel.Rows[2][j].ToString()], dtExcel.Rows[i][j]) + "<br/>";
                                continue;
                            }
                            rowInsert["SCHInput"] = "1";
                            rowInsert["DayEndNo"] = dtExcel.Rows[i][0] + hqAccountDate;
                            rowInsert["BCCD"] = loginUser;
                            rowInsert["UpdateMan"] = loginUser;
                            rowInsert["UpdateTime"] = DateTime.Now;
                            rowInsert["Origsys"] = "2";

                            dtInsert.Rows.Add(rowInsert);
                        }

                    }

                    if (!string.IsNullOrEmpty(msg))
                    {
                        return false;
                    }

                    #endregion

                    #region 4.0 创建数据库临时表#temp，并把dtInsert内存表中的数据插入到临时表，再判断数据有效性并导入到正式表中

                    msg = dal.InsertOtherMakerSaleToTempTable(dtInsert);                    
                    if (!string.IsNullOrEmpty(msg))
                    {
                        return false;
                    }

                    #endregion

                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    #region 资源释放
                    if (oleConn != null)
                    {
                        oleConn.Dispose();
                        oleConn.Close();
                    }
                    if (dtInsert != null)
                    {
                        dtInsert.Dispose();
                    }
                    if (dtExcel != null)
                    {
                        dtExcel.Dispose();
                    }
                    if (ds != null)
                    {
                        ds.Dispose();
                    } 
                    #endregion
                }

                ts.Complete();                
                return true;
            }
        }

2.DAO数据库操作代码

/// <summary>
        /// 创建“竞品品牌销售数据临时表”，判断数据有效性，导入到正式表中
        /// </summary>
        /// <param name="dtInsert">店铺竞品品牌销售数据</param>
        /// <returns></returns>
        public string InsertOtherMakerSaleToTempTable(DataTable dtInsert)
        {
            string msg = "";
            string tempTableName = "#OtherMakerSaleTemp";
            StringBuilder sql = new StringBuilder();
            // 1 先删除临时表
            sql.AppendFormat(" if object_id(N'{0}',N'U') is not null drop table {1}", tempTableName, tempTableName).AppendLine();
            this.DataAccessClient.ExecuteNonQuery(sql.ToString());

            // 2 创建临时表
            sql.Remove(0, sql.Length);            
            sql.AppendFormat(" select * into {0} from OtherSaleMonthTbl where 1=0 ;", tempTableName).AppendLine();            
            // 2.1 为临时表添加主键
            sql.AppendFormat(" alter table {0} add primary key(ownerid,customercd,othermakercd,othersaleym); ", tempTableName).AppendLine();
            this.DataAccessClient.ExecuteNonQuery(sql.ToString());

            // 3.往临时表中插入数据
            #region DeleteCode
            /*
            string connString = Inf.DevLib.Data.DataAccess.DataAccessFactory.ConnectionString;
            using (SqlConnection conn = new SqlConnection(connString))
            {
                if (conn.State != System.Data.ConnectionState.Open)
                {
                    conn.Open();
                }
                
                using (SqlBulkCopy bc = new SqlBulkCopy(conn))
                {
                    bc.BulkCopyTimeout = 300;
                    bc.DestinationTableName = tempTableName;
                    bc.WriteToServer(dtInsert);
                }
                conn.Close();
            }
            */
            #endregion

            this.DataAccessClient.BatchInsert(dtInsert, tempTableName, null, 100);

            // 4.判断临时表中的店号在ShopRelateMakerTBL表中是否存在，若不存在则给予提示并返回
            sql.Remove(0, sql.Length);
            sql.AppendFormat("select distinct a.CustomerCd from {0} a", tempTableName).AppendLine();
            sql.AppendLine("where not exists");
            sql.AppendLine("(select b.CustomerCd from ShopRelateMakerTBL b where a.OwnerId = b.OwnerId and a.CustomerCd = b.CustomerCd )");
            // 在商店别他品牌对应表（ShopRelateMakerTBL）中不存在的店号
            DataTable notExistsTable = this.DataAccessClient.ExecuteDataTable(sql.ToString());
            if (notExistsTable != null && notExistsTable.Rows.Count > 0)
            {
                foreach (DataRow row in notExistsTable.Rows)
                {
                    msg += row[0] + ",";
                }             
                // 店号在商店别他品牌对应表中不存在，请仔细核对数据重新导入！
                msg = this.GetResourceString("StoreIdNotExist", msg.Substring(0, msg.Length - 1));
                return msg;
            }

            // 5.判断临时表中店铺所有的竞品品牌在ShopRelateMakerTBL表中是否存在，若不存在则给予提示并返回
            sql.Remove(0, sql.Length);
            sql.AppendLine("select t.*,mst.OtherMakerNm from ");
            sql.AppendLine("(");
            sql.AppendLine("    select omst.OwnerId, omst.CustomerCd,omst.OtherMakerCd");
            sql.AppendFormat("    from {0} omst", tempTableName).AppendLine();
            sql.AppendLine("    where not exists");
            sql.AppendLine("    (select srmt.OtherMakerCd from ShopRelateMakerTBL srmt ");
            sql.AppendLine("    where omst.OwnerId = srmt.OwnerId and omst.CustomerCd = srmt.CustomerCd and omst.OtherMakerCd = srmt.OtherMakerCd )");
            sql.AppendLine(") t ");
            sql.AppendLine("inner join OtherMakerMasTbl mst");
            sql.AppendLine("         on t.OwnerId = mst.OwnerId");
            sql.AppendLine("         and t.OtherMakerCd = mst.OtherMakerCd");
            List<ShopRelateMakerTBL> errorOtherMakerMasList = new List<ShopRelateMakerTBL>();
            this.DataAccessClient.FillQuery(errorOtherMakerMasList, sql.ToString());
            if (errorOtherMakerMasList.Count > 0)
            {
                var q = from e in errorOtherMakerMasList
                        group e by new { e.OwnerId, e.CustomerCd } into g
                        select new
                        {
                            StoreId = g.Key.CustomerCd,
                            OtherMakerNm = string.Join(",", g.Select(i => i.OtherMakerNm.Trim()))
                        };
                string str = string.Empty;
                q.ToList().ForEach(item => str += string.Format("[{0}：{1}]", item.StoreId, item.OtherMakerNm) + ",");
                // 店号{0}的竞品品牌主档数据在商店别他品牌对应表中不存在，请仔细核对数据重新导入！
                msg = this.GetResourceString("StoreOtherMakerNotExist", str.Substring(0, str.Length - 1));
                return msg;
            }

            // 6.把临时表中的数据插入到正式表OtherSaleMonthTbl（先删再插）
            sql.Remove(0, sql.Length);
            // 6.1 先删除 正式表中未确认的店铺精品销售数据
            sql.AppendLine("delete A ");
            sql.AppendLine("from OtherSaleMonthTbl A");
            sql.AppendFormat("inner join {0} B", tempTableName).AppendLine();
            sql.AppendLine("        on A.OwnerId = B.OwnerId");
            sql.AppendLine("        and A.CustomerCd = B.CustomerCd");
            sql.AppendLine("        and A.OtherSaleYM = B.OtherSaleYM");
            sql.AppendLine(" where ( A.ConfirmFlg != '1' or A.ConfirmFlg is null) "); // 未确认
            this.DataAccessClient.ExecuteNonQuery(sql.ToString());

            // 6.2 再删除 临时表中在正式表中已确认过的店铺销售数据
            sql.Remove(0, sql.Length);
            sql.AppendLine("delete B ");
            sql.AppendFormat("from {0} B", tempTableName).AppendLine();
            sql.AppendLine(" inner join OtherSaleMonthTbl A");            
            sql.AppendLine("        on A.OwnerId = B.OwnerId");
            sql.AppendLine("        and A.CustomerCd = B.CustomerCd");
            sql.AppendLine("        and A.OtherSaleYM = B.OtherSaleYM");
            sql.AppendLine(" where A.ConfirmFlg = '1'  "); // 已确认
            this.DataAccessClient.ExecuteNonQuery(sql.ToString());

            // 6.3 最后把临时表中的数据全部插入正式表
            sql.Remove(0, sql.Length);
            sql.AppendLine("INSERT INTO OtherSaleMonthTbl");
            sql.AppendLine("select * ");
            sql.AppendFormat("from {0} ", tempTableName).AppendLine();
            this.DataAccessClient.ExecuteNonQuery(sql.ToString());

            // 7.删除临时表
            sql.Remove(0, sql.Length);
            sql.AppendFormat("drop table {0}", tempTableName).AppendLine();
            this.DataAccessClient.ExecuteNonQuery(sql.ToString());

            return msg;
        }