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; }
