public PriceInputPage(List <SummedItem> summedItemList, List <InputItem> inputItemList, string saveDirectoryPrefix) { InitializeComponent(); this.summedItemList = summedItemList; this.inputItemList = inputItemList; this.saveDirectoryPrefix = saveDirectoryPrefix; this.needBuckle = NeedBuckleItem(this.summedItemList); if (this.needBuckle) { buckleItem = new BuckleItem(); buckleItem.Quantity = CalculationService.CalculateBuckleItemQuantity(summedItemList); buckleSection.Visibility = Visibility.Visible; } else { buckleItem = null; buckleSection.Visibility = Visibility.Collapsed; } this.priceCollectorList = CalculationService.SummedItemsToPriceCollectors(summedItemList); this.priceCollectorList.Sort(new PriceCollectorComparer()); DataContext = this; }
private void Testing2() { List <InputItem> inputItemList = new List <InputItem>(); inputItemList.Add(new InputItem("s-802锦绣纹", "b", "300*3600", "2+4", "顶", "东南卧室", "0.9", "东西铺贴")); inputItemList.Add(new InputItem("s-802锦绣纹", "b", "400*3600", "5.9+4.7", "顶", "南西卧室", "3.6", "南北铺贴")); inputItemList.Add(new InputItem("s-803锦绣纹", "b", "300*3600", "5.9+4.7", "顶", "南西卧室", "3.6", "南北铺贴")); inputItemList.Add(new InputItem("s-802锦绣纹", "b", "300*3600", "5.9+4.7", "顶", "南西卧室", "3.6", "南北铺贴")); inputItemList.Add(new InputItem("s-802锦绣纹", "b", "300*800", "5.9+4.7", "顶", "南西卧室", "0.2", "南北铺贴")); inputItemList.Add(new InputItem("s-603金橡木", "x", "100*3000", "5.9+4.7+5.9+4.7", "踢脚线", "南西卧室")); inputItemList.Add(new InputItem("s-603金橡木", "x", "100*3000", "5.8", "踢脚线", "北卧室")); inputItemList.Add(new InputItem("s-802锦绣纹", "b", "400*800", "5.9+4.7", "顶", "南西卧室", "0.2", "南北铺贴")); List <SummedItem> summedItemList = CalculationService.MergeInputItem(inputItemList); foreach (SummedItem item in summedItemList) { item.BuyPrice = "15"; item.SellPrice = "20"; double buyPrice = StringParserService.ParsePrice(item.BuyPrice); double sellPrice = StringParserService.ParsePrice(item.SellPrice); //TODO: figure out why 15 * 46.66 gives you 656.5999999991 only here but not in csharpplayground item.TotalBuyPrice = CalculationService.CalculateTotalPricesByAreaOrLength(buyPrice, item.TotalAreaOrLength); item.TotalSellPrice = CalculationService.CalculateTotalPricesByAreaOrLength(sellPrice, item.TotalAreaOrLength); } BuckleItem buckleItem = new BuckleItem(); buckleItem.Quantity = CalculationService.CalculateBuckleItemQuantity(summedItemList); buckleItem.BuyPrice = "1"; buckleItem.SellPrice = "1.5"; double buckleBuyPrice = StringParserService.ParsePrice(buckleItem.BuyPrice); double buckleSellPrice = StringParserService.ParsePrice(buckleItem.SellPrice); buckleItem.TotalBuyPrice = CalculationService.CalculateTotalPricesByQuantity(buckleBuyPrice, buckleItem.Quantity); buckleItem.TotalSellPrice = CalculationService.CalculateTotalPricesByQuantity(buckleSellPrice, buckleItem.Quantity); IOService.GenerateInputCheckingSheet(@"C:\Users\JZhang\Desktop\输入校对表.xlsx", inputItemList); IOService.GenerateConstructionDetailSheet(@"C:\Users\JZhang\Desktop\施工详单.xlsx", inputItemList); IOService.GenerateMaterialTotalSheet(@"C:\Users\JZhang\Desktop\材料清单1.xlsx", summedItemList, buckleItem, true); IOService.GenerateMaterialTotalSheet(@"C:\Users\JZhang\Desktop\材料清单2.xlsx", summedItemList, buckleItem, false); List <InputItem> readInput = IOService.ReadInputCheckingSheetToInputItemList(@"C:\Users\JZhang\Desktop\输入校对表.xlsx"); }
private static void FillSheetDataWithSummedItemListAndBuckleItem(SheetData sheetData, List <SummedItem> summedItems, BuckleItem buckleItem, bool buyPriceNotSellPrice) { // Sum price for all items double totalPrice = 0; // Fill in header Row rHeader = new Row() { RowIndex = (UInt32Value)1u }; Cell cHeader1 = new Cell(); cHeader1.DataType = CellValues.String; cHeader1.CellValue = new CellValue("序号"); Cell cHeader2 = new Cell(); cHeader2.DataType = CellValues.String; cHeader2.CellValue = new CellValue("产品名称"); Cell cHeader3 = new Cell(); cHeader3.DataType = CellValues.String; cHeader3.CellValue = new CellValue("规格"); Cell cHeader4 = new Cell(); cHeader4.DataType = CellValues.String; cHeader4.CellValue = new CellValue("数量"); Cell cHeader5 = new Cell(); cHeader5.DataType = CellValues.String; cHeader5.CellValue = new CellValue("平方"); Cell cHeader6 = new Cell(); cHeader6.DataType = CellValues.String; cHeader6.CellValue = new CellValue("单价"); Cell cHeader7 = new Cell(); cHeader7.DataType = CellValues.String; cHeader7.CellValue = new CellValue("合计"); Cell cHeader8 = new Cell(); cHeader8.DataType = CellValues.String; cHeader8.CellValue = new CellValue("位置"); Cell cHeader9 = new Cell(); cHeader9.DataType = CellValues.String; cHeader9.CellValue = new CellValue("备注"); rHeader.Append(cHeader1); rHeader.Append(cHeader2); rHeader.Append(cHeader3); rHeader.Append(cHeader4); rHeader.Append(cHeader5); rHeader.Append(cHeader6); rHeader.Append(cHeader7); rHeader.Append(cHeader8); rHeader.Append(cHeader9); sheetData.Append(rHeader); // Fill in summedItemList uint rowIndex = 2u; foreach (SummedItem item in summedItems) { Row row = new Row() { RowIndex = (UInt32Value)rowIndex }; Cell cell1 = new Cell(); cell1.DataType = CellValues.Number; cell1.CellValue = new CellValue((rowIndex - 1).ToString()); Cell cell2 = new Cell(); cell2.DataType = CellValues.String; cell2.CellValue = new CellValue(item.ProductName); Cell cell3 = new Cell(); cell3.DataType = CellValues.String; cell3.CellValue = new CellValue(item.Type); Cell cell4 = new Cell(); cell4.DataType = CellValues.Number; cell4.CellValue = new CellValue(item.Quantity.ToString()); Cell cell5 = new Cell(); cell5.DataType = CellValues.Number; cell5.CellValue = new CellValue(item.TotalAreaOrLength.ToString()); Cell cell6 = new Cell(); cell6.DataType = CellValues.Number; cell6.CellValue = new CellValue(buyPriceNotSellPrice ? item.BuyPrice: item.SellPrice); Cell cell7 = new Cell(); cell7.DataType = CellValues.Number; double itemTotalPrice = buyPriceNotSellPrice ? item.TotalBuyPrice : item.TotalSellPrice; cell7.CellValue = new CellValue(itemTotalPrice.ToString()); totalPrice += itemTotalPrice; Cell cell8 = new Cell(); cell8.DataType = CellValues.String; cell8.CellValue = new CellValue(item.Memo); Cell cell9 = new Cell(); cell9.DataType = CellValues.String; cell9.CellValue = new CellValue(""); row.Append(cell1); row.Append(cell2); row.Append(cell3); row.Append(cell4); row.Append(cell5); row.Append(cell6); row.Append(cell7); row.Append(cell8); row.Append(cell9); sheetData.Append(row); rowIndex++; } // Fill in buckleItem if we need buckle if (buckleItem != null) { Row rBuckle = new Row() { RowIndex = (UInt32Value)rowIndex }; Cell cBuckle1 = new Cell(); cBuckle1.DataType = CellValues.Number; cBuckle1.CellValue = new CellValue((rowIndex - 1).ToString()); Cell cBuckle2 = new Cell(); cBuckle2.DataType = CellValues.String; cBuckle2.CellValue = new CellValue(buckleItem.ProductName); Cell cBuckle3 = new Cell(); cBuckle3.DataType = CellValues.String; cBuckle3.CellValue = new CellValue(""); Cell cBuckle4 = new Cell(); cBuckle4.DataType = CellValues.Number; cBuckle4.CellValue = new CellValue(buckleItem.Quantity.ToString()); Cell cBuckle5 = new Cell(); cBuckle5.DataType = CellValues.String; cBuckle5.CellValue = new CellValue(""); Cell cBuckle6 = new Cell(); cBuckle6.DataType = CellValues.Number; cBuckle6.CellValue = new CellValue(buyPriceNotSellPrice ? buckleItem.BuyPrice : buckleItem.SellPrice); Cell cBuckle7 = new Cell(); cBuckle7.DataType = CellValues.Number; double buckleTotalPrice = buyPriceNotSellPrice ? buckleItem.TotalBuyPrice : buckleItem.TotalSellPrice; cBuckle7.CellValue = new CellValue(buckleTotalPrice.ToString()); totalPrice += buckleTotalPrice; Cell cBuckle8 = new Cell(); cBuckle8.DataType = CellValues.String; cBuckle8.CellValue = new CellValue(""); Cell cBuckle9 = new Cell(); cBuckle9.DataType = CellValues.String; cBuckle9.CellValue = new CellValue(""); rBuckle.Append(cBuckle1); rBuckle.Append(cBuckle2); rBuckle.Append(cBuckle3); rBuckle.Append(cBuckle4); rBuckle.Append(cBuckle5); rBuckle.Append(cBuckle6); rBuckle.Append(cBuckle7); rBuckle.Append(cBuckle8); rBuckle.Append(cBuckle9); sheetData.Append(rBuckle); rowIndex++; } // Fill in total price Row rTotal = new Row() { RowIndex = (UInt32Value)rowIndex }; Cell cTotal1 = new Cell(); cTotal1.DataType = CellValues.String; cTotal1.CellValue = new CellValue(""); Cell cTotal2 = new Cell(); cTotal2.DataType = CellValues.String; cTotal2.CellValue = new CellValue(""); Cell cTotal3 = new Cell(); cTotal3.DataType = CellValues.String; cTotal3.CellValue = new CellValue(""); Cell cTotal4 = new Cell(); cTotal4.DataType = CellValues.String; cTotal4.CellValue = new CellValue(""); Cell cTotal5 = new Cell(); cTotal5.DataType = CellValues.String; cTotal5.CellValue = new CellValue(""); Cell cTotal6 = new Cell(); cTotal6.DataType = CellValues.String; cTotal6.CellValue = new CellValue("总计"); Cell cTotal7 = new Cell(); cTotal7.DataType = CellValues.Number; cTotal7.CellValue = new CellValue(totalPrice.ToString()); Cell cTotal8 = new Cell(); cTotal8.DataType = CellValues.String; cTotal8.CellValue = new CellValue(""); Cell cTotal9 = new Cell(); cTotal9.DataType = CellValues.String; cTotal9.CellValue = new CellValue(""); rTotal.Append(cTotal1); rTotal.Append(cTotal2); rTotal.Append(cTotal3); rTotal.Append(cTotal4); rTotal.Append(cTotal5); rTotal.Append(cTotal6); rTotal.Append(cTotal7); rTotal.Append(cTotal8); rTotal.Append(cTotal9); sheetData.Append(rTotal); rowIndex++; }
/// <summary> /// /// </summary> /// <param name="filepath"></param> /// <param name="summedItems"></param> /// <param name="buckleItem">null means we don't need buckle</param> /// <param name="buyPriceNotSellPrice"> Set to true will generate sheet with buy price showing on it; Set to false will generate sheet with sell price showing on it </param> public static void GenerateMaterialTotalSheet(string filepath, List <SummedItem> summedItems, BuckleItem buckleItem, bool buyPriceNotSellPrice) { // Create a spreadsheet document by supplying the filepath. // By default, AutoSave = true, Editable = true, and Type = xlsx. SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook); // Add a WorkbookPart to the document. WorkbookPart workbookPart = spreadSheet.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); // Add Sheets to the Workbook. Sheets sheets = spreadSheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); // Create new worksheet part with actual data WorksheetPart newWorksheetPart = workbookPart.AddNewPart <WorksheetPart>(); Worksheet newWorksheet = new Worksheet(); SheetData newSheetData = new SheetData(); // If sheet is used for merchandise showing buy price, we'd like item in the sheet to be sorted if (buyPriceNotSellPrice) { List <SummedItem> sortedSummedItems = SortSummedItemByNameAndType(summedItems); FillSheetDataWithSummedItemListAndBuckleItem(newSheetData, sortedSummedItems, buckleItem, buyPriceNotSellPrice); } else { FillSheetDataWithSummedItemListAndBuckleItem(newSheetData, summedItems, buckleItem, buyPriceNotSellPrice); } newWorksheet.Append(newSheetData); newWorksheetPart.Worksheet = newWorksheet; // Create new sheet metadata corresponds to the new worksheet created above Sheet newSheet = new Sheet() { Id = workbookPart.GetIdOfPart(newWorksheetPart), SheetId = 1, Name = "材料清单" }; sheets.Append(newSheet); // Save the new worksheet. newWorksheet.Save(); workbookPart.Workbook.Save(); // Close the document. spreadSheet.Close(); }