private static void FillTechSpecSheet(IExcelService excel, Order orderItem, bool autoCounting) { int lotCount = 1; foreach (var item in orderItem.Auction.SupplierOrders[0].lots) { // Create new sheet and copy header for more then one lot if (lotCount > 1) { excel.AddSheet(string.Format("Тех. спец. по лоту №{0}", item.Number)); excel.SetSheetByIndex(lotCount); excel.SetSheetByIndex(lotCount + 1); excel.PasteRangeTo(1, "A", 3, "N", lotCount); } else { excel.SetSheetByIndex(lotCount + 1); } excel.ChangeSheetName(lotCount + 1, string.Format("Тех. спец. по лоту №{0}", item.Number)); // Header excel.SetCells(1, "A", string.Format("Техническая спецификация к лоту №{0} - {1}", lotCount, item.Number)); // Formula variables decimal endSum = 0; if (orderItem.Auction.Procuratories != null && orderItem.Auction.Procuratories.Count > 0) { var procSum = orderItem.Auction.Procuratories.First(p => p.lotId == item.Id); if (procSum != null) { endSum = procSum.MinimalPrice; } } //autoCouting if (autoCounting) { // Old KV //decimal difference = item.Sum - endSum; //decimal factor = difference / item.LotsExtended.Count; //foreach (var subItem in item.LotsExtended) { // subItem.endprice = subItem.price - factor / item.Quantity; // subItem.endsum = subItem.sum - factor; //} // New decimal difference = 100 - ((item.Sum - endSum) / (item.Sum / 100)); foreach (var subItem in item.LotsExtended) { subItem.endprice = subItem.price / 100 * difference; subItem.endsum = subItem.endprice * subItem.quantity; } } // Full sum excel.SetCells(5, "F", item.LotsExtended.Sum(l => l.sum)); excel.SetCells(5, "N", item.LotsExtended.Sum(l => l.endsum)); // Content int rowCount = 4; foreach (var subItem in item.LotsExtended) { if (rowCount != 4) { excel.InsertRow(rowCount); } excel.SetCells(rowCount, "A", subItem.serialnumber); excel.SetCells(rowCount, "B", subItem.name); excel.SetCellWrapText(rowCount, "B", true); excel.SetCells(rowCount, "C", subItem.unit); excel.SetCells(rowCount, "D", subItem.quantity); excel.SetCells(rowCount, "E", subItem.price); excel.SetCells(rowCount, "F", subItem.sum); excel.SetCells(rowCount, "G", subItem.country); excel.SetCellWrapText(rowCount, "G", true); excel.SetCells(rowCount, "H", subItem.techspec); excel.SetCellWrapText(rowCount, "H", true); excel.SetCells(rowCount, "I", subItem.terms); excel.SetCellWrapText(rowCount, "I", true); excel.SetCells(rowCount, "J", subItem.paymentterm); excel.SetCellWrapText(rowCount, "J", true); excel.SetCells(rowCount, "K", subItem.dks); excel.SetCells(rowCount, "L", subItem.contractnumber); excel.SetCells(rowCount, "M", subItem.endprice); excel.SetCells(rowCount, "N", subItem.endsum); for (var i = 1; i < 15; i++) { excel.SetCellFontVAlignment(rowCount, i, 2); excel.SetCellBorder(rowCount, i); } rowCount++; } lotCount++; } }