public MaxIndexPair LookupFromOtherSheetByProductId() { var columns = excelHandler.GetAllColumnTitle(1); var productIds = excelHandler.GetSpecifiedColumnAllCellValue(1, "商品主號"); int rowIndex = productIds.Count + 1; Console.WriteLine($"已取得所有商品主號,共 {productIds.Count} 個"); int columnIndex = 0; foreach (var column in columns) { columnIndex = column.Key; var columnTitle = column.Value; if (!excelHandler.ExistSheet(columnTitle)) { columnIndex--; break; } try { var groupId = excelHandler.GetSpecifiedCellValue(columnTitle, 2, 1); Dictionary <int, string> productIdsOnSheet = excelHandler.GetSpecifiedColumnAllCellValue(columnTitle, "商品主號"); var intersectIds = productIds.Values.Intersect(productIdsOnSheet.Values); foreach (var intersectId in intersectIds) { var mappingRowIndex = productIds.FirstOrDefault(x => x.Value == intersectId).Key; excelHandler.SetCellValue(1, columnIndex, mappingRowIndex, "," + groupId); } } catch (Exception) { Console.WriteLine($"ERROR!! {columnTitle} 那行查表時出錯!!"); } excelHandler.Save(); Console.WriteLine($"'{columnTitle}' 查表完成"); } return(new MaxIndexPair { MaxColumnIndex = columnIndex, MaxRowIndex = rowIndex }); }
public void OutputDuplicateItems() { var collectionIds1 = _xlsHandler1.GetSpecifiedColumnAllCellValue("整理", "商品主號"); var collectionIds2 = _xlsHandler2.GetSpecifiedColumnAllCellValue("整理", "商品主號"); var intervalIds1 = _xlsHandler1.GetSpecifiedColumnAllCellValue("周間", "商品主號"); var intervalIds2 = _xlsHandler2.GetSpecifiedColumnAllCellValue("周間", "商品主號"); var groupId1 = _xlsHandler1.GetSpecifiedColumnAllCellValue("團購", "商品主號"); var groupId2 = _xlsHandler2.GetSpecifiedColumnAllCellValue("團購", "商品主號"); var allSourceAndIdLookupSet = new Dictionary <Tuple <IExcelHandler, string>, Dictionary <int, string> > { { new Tuple <IExcelHandler, string>(_xlsHandler1, "整理"), collectionIds1 }, { new Tuple <IExcelHandler, string>(_xlsHandler2, "整理"), collectionIds2 }, { new Tuple <IExcelHandler, string>(_xlsHandler1, "周間"), intervalIds1 }, { new Tuple <IExcelHandler, string>(_xlsHandler2, "周間"), intervalIds2 }, { new Tuple <IExcelHandler, string>(_xlsHandler1, "團購"), groupId1 }, { new Tuple <IExcelHandler, string>(_xlsHandler2, "團購"), groupId2 } }; var pids = new List <string>(); pids.AddRange(collectionIds1.Values); pids.AddRange(collectionIds2.Values); pids.AddRange(intervalIds1.Values); pids.AddRange(intervalIds2.Values); pids.AddRange(groupId1.Values); pids.AddRange(groupId2.Values); var duplicatePids = pids.GroupBy(x => x).Where(g => g.Count() > 1).Select(y => y.Key).ToList(); Console.WriteLine($"重複的共有 {duplicatePids.Count} 個品項"); var exePath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); var newFilePath = Path.Combine(exePath, "重複總整理.xls"); XlsHandler.CreateNewFile(newFilePath); IExcelHandler newXlsHandler = new XlsHandler(newFilePath); newXlsHandler.AddWorksheet("", "重複項目"); newXlsHandler.CreateColumnAhead("來源"); newXlsHandler.CreateColumnAhead("網路價"); newXlsHandler.CreateColumnAhead("商品名稱"); const int PRODUCT_COLUMNINDEX = 1; const int PRICE_COLUMNINDEX = 2; const int SOURCE_COLUMNINDEX = 3; var currRowIndex = 2; foreach (var pid in duplicatePids) { Console.Write("."); var minPrice = int.MaxValue; var minPriceRowIndex = 0; var minPriceIsMM = false; foreach (var sourceAndIdLookup in allSourceAndIdLookupSet) { if (sourceAndIdLookup.Value.ContainsValue(pid)) { var allIdLookup = sourceAndIdLookup.Value.Where(x => x.Value == pid); foreach (var lookup in allIdLookup) { var rowIndex = lookup.Key; var sourcePair = sourceAndIdLookup.Key; var xlsHandler = sourcePair.Item1; var sheetName = sourcePair.Item2; var productName = xlsHandler.GetSpecifiedCellValue(sheetName, "商品名稱", rowIndex); var price = xlsHandler.GetSpecifiedCellValue(sheetName, "網路價", rowIndex); var sourceFileStr = xlsHandler.Name.Substring(0, 2); var sourceStr = sourceFileStr + "-" + sheetName; newXlsHandler.SetCellValue(1, PRODUCT_COLUMNINDEX, currRowIndex, productName); newXlsHandler.SetCellValue(1, PRICE_COLUMNINDEX, currRowIndex, price); newXlsHandler.SetCellValue(1, SOURCE_COLUMNINDEX, currRowIndex, sourceStr); newXlsHandler.SetCellColor(1, PRODUCT_COLUMNINDEX, currRowIndex, Color.Silver); newXlsHandler.SetCellColor(1, PRICE_COLUMNINDEX, currRowIndex, Color.Silver); newXlsHandler.SetCellColor(1, SOURCE_COLUMNINDEX, currRowIndex, Color.Silver); int priceInt = int.MaxValue; try { priceInt = (int)double.Parse(price); } catch (Exception) { Console.WriteLine($"ERROR!! 存在有非數字的網路價錢: {xlsHandler.Name}.{sheetName}.{rowIndex}=>{price}"); newXlsHandler.SetCellColor(1, PRICE_COLUMNINDEX, currRowIndex, Color.Red); } if (sourceFileStr == "MM") { if (priceInt <= minPrice) { minPrice = priceInt; minPriceRowIndex = currRowIndex; minPriceIsMM = true; } } else//PF { if (!minPriceIsMM && priceInt < minPrice) { minPrice = priceInt; minPriceRowIndex = currRowIndex; minPriceIsMM = false; } } currRowIndex++; } } } if (minPriceRowIndex > 0) { newXlsHandler.SetCellColor(1, PRODUCT_COLUMNINDEX, minPriceRowIndex, Color.White); newXlsHandler.SetCellColor(1, PRICE_COLUMNINDEX, minPriceRowIndex, Color.White); newXlsHandler.SetCellColor(1, SOURCE_COLUMNINDEX, minPriceRowIndex, Color.White); } } Console.WriteLine(""); newXlsHandler.Save(); }
public void GroupTenItemsViaPid(string oriSheetName, string newSheetName) { var newSheetIndex = _excelHandler.AddWorksheet(oriSheetName, newSheetName); var pidLookup = _excelHandler.GetSpecifiedColumnAllCellValue(oriSheetName, "pid"); var eventLookup = _excelHandler.GetSpecifiedColumnAllCellValue(oriSheetName, "活動名稱"); var rankLookup = _excelHandler.GetSpecifiedColumnAllCellValue(oriSheetName, "rank"); var productNameLookup = _excelHandler.GetSpecifiedColumnAllCellValue(oriSheetName, "商品名稱"); const int PRODUCT_COLUMNINDEX = 1; const int RANK_COLUMNINDEX = 2; const int EVENT_COLUMNINDEX = 3; const int PID_COLUMNINDEX = 4; const int PIDS_COLUMNINDEX = 5; _excelHandler.CreateColumnAhead("pid"); _excelHandler.CreateColumnAhead("活動名稱"); _excelHandler.CreateColumnAhead("rank"); _excelHandler.CreateColumnAhead("商品名稱"); _excelHandler.SetCellValue(newSheetIndex, PIDS_COLUMNINDEX, 1, "pids"); var rowIndex = 2; while (pidLookup.ContainsKey(rowIndex)) { _excelHandler.SetCellValue(newSheetIndex, PRODUCT_COLUMNINDEX, rowIndex, productNameLookup[rowIndex]); _excelHandler.SetCellValue(newSheetIndex, RANK_COLUMNINDEX, rowIndex, rankLookup[rowIndex]); _excelHandler.SetCellValue(newSheetIndex, EVENT_COLUMNINDEX, rowIndex, eventLookup[rowIndex]); _excelHandler.SetCellValue(newSheetIndex, PID_COLUMNINDEX, rowIndex, pidLookup[rowIndex]); rowIndex++; } var groupRowIndex = 0; for (int i = 2; i < rowIndex; i++) { groupRowIndex++; var currEventCell = _excelHandler.GetSpecifiedCellValue(newSheetIndex, EVENT_COLUMNINDEX, i); var nextEventCell = _excelHandler.GetSpecifiedCellValue(newSheetIndex, EVENT_COLUMNINDEX, i + 1); var sameGroup = currEventCell == nextEventCell; if (sameGroup && groupRowIndex == 10) { groupRowIndex = 0; var groupListString = ""; for (int j = 9; j >= 0; j--) { groupListString += _excelHandler.GetSpecifiedCellValue(newSheetIndex, PID_COLUMNINDEX, i - j); if (j > 0) { groupListString += ","; } } _excelHandler.SetCellValue(newSheetIndex, PIDS_COLUMNINDEX, i, groupListString); } else if (!sameGroup || (i == rowIndex - 1))//Last One { groupRowIndex = 0; var groupListString = _excelHandler.GetSpecifiedCellValue(newSheetIndex, PID_COLUMNINDEX, i);; for (int j = 1; j <= 9; j++) { var prevPidsCell = _excelHandler.GetSpecifiedCellValue(newSheetIndex, PIDS_COLUMNINDEX, i - j); if (prevPidsCell != null) { break; } groupListString = "," + groupListString; groupListString = _excelHandler.GetSpecifiedCellValue(newSheetIndex, PID_COLUMNINDEX, i - j) + groupListString; } _excelHandler.SetCellValue(newSheetIndex, PIDS_COLUMNINDEX, i, groupListString); } } _excelHandler.Save(); }