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