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