public async Task <IActionResult> Export( [FromServices] BaseHandler json, [FromServices] XlsHandler xls, [FromServices] HtmlHandler html, [FromQuery] string format ) { try { IActionResult file = null; switch (format.ToLower()) { case "xlsx": case "xls": file = this.File(await xls.GetExportAsync(this.UserId), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); break; case "html": file = this.File(await html.GetExportAsync(this.UserId), "text/html"); break; default: file = this.File(await json.GetExportAsync(this.UserId), "application/json"); break; } return(file); } catch (Exception e) { return(this.Ok(e)); } }
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 WorkSheetHandler(XlsHandler xlsHandler1, XlsHandler xlsHandler2) { _xlsHandler1 = xlsHandler1; _xlsHandler2 = xlsHandler2; }