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