private void GetDataFromBill(string path) { int beginrow = 0; int endrow = 0; bool beginflag = false; bool endflag = false; bills = new List <BillData>(); FileInfo file = new FileInfo(path); Raredata = new RareMutableData(); using (ExcelPackage exc = new ExcelPackage(file)) { ExcelWorksheet worksheet = exc.Workbook.Worksheets[1]; ExcelRange Dimension = worksheet.Cells; for (int i = 1; i < Dimension.End.Row; i++) { if (worksheet.Cells[i, 1].Value.ToString() == "Продавец:") { Raredata.User = worksheet.Cells[i, 3].Value.ToString(); } else if (worksheet.Cells[i, 1].Value.ToString().Contains("Пред-счет")) { Raredata.Date = worksheet.Cells[i, 1].Value.ToString().Replace("Пред-счет №б/н от ", "").Replace(" года", "").Trim(); } else if (worksheet.Cells[i, 1].Value.ToString() == "Покупатель:") { Raredata.Buyer = worksheet.Cells[i, 3].Value.ToString(); } if (worksheet.Cells[i, 1].Value.ToString() == "№") { beginrow = i; beginflag = true; } else if (worksheet.Cells[i, 1].Value.ToString() == "Итого") { endrow = i; endflag = true; } else if (beginflag & endflag) { break; } } if (beginflag && endflag) { for (int i = beginrow + 1; i < endrow; i++) { BillData loc = new BillData(); loc.Position = i; loc.BillPosition = Convert.ToInt32(worksheet.Cells[i, 1].Value); loc.Name = new BillPositionName(worksheet.Cells[i, 2].Value.ToString()); loc.Quantity = worksheet.Cells[i, 3].Value.ToString(); loc.Package = worksheet.Cells[i, 4].Value.ToString(); loc.Price = worksheet.Cells[i, 5].Value.ToString(); loc.Amount = worksheet.Cells[i, 6].Value.ToString(); loc.VatPercent = worksheet.Cells[i, 7].Value.ToString(); loc.AmountOfVat = worksheet.Cells[i, 8].Value.ToString(); loc.TotalAmount = worksheet.Cells[i, 9].Value.ToString(); bills.Add(loc); } } } }
public void FormBill(string path, List <BillData> outputbills, RareMutableData raredata) { FileInfo file = new FileInfo(path); using (ExcelPackage exc = new ExcelPackage(file)) { ExcelWorksheet sheet = exc.Workbook.Worksheets.Add($"bill {raredata.Buyer}"); exc.Workbook.CalcMode = ExcelCalcMode.Manual; sheet.Cells[1, 1].Value = "Продавец:"; using (ExcelRange r = sheet.Cells[1, 1, 1, 2]) { r.Merge = true; r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; r.Style.VerticalAlignment = ExcelVerticalAlignment.Center; r.Style.Font.Bold = true; } sheet.Cells[1, 3].Value = raredata.User; using (ExcelRange r = sheet.Cells[1, 3, 1, 9]) { r.Merge = true; r.Style.WrapText = true; sheet.Row(1).Height = 80; } sheet.Cells[2, 1].Value = raredata.Date; using (ExcelRange r = sheet.Cells[2, 1, 2, 9]) { r.Merge = true; r.Style.WrapText = true; r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; r.Style.VerticalAlignment = ExcelVerticalAlignment.Center; r.Style.Font.Bold = true; } sheet.Cells[3, 1].Value = "Покупатель:"; using (ExcelRange r = sheet.Cells [3, 1, 3, 2]) { r.Merge = true; r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; r.Style.VerticalAlignment = ExcelVerticalAlignment.Center; r.Style.Font.Bold = true; } sheet.Cells[3, 3].Value = raredata.Buyer; using (ExcelRange r = sheet.Cells[3, 3, 3, 9]) { r.Merge = true; r.Style.WrapText = true; sheet.Row(3).Height = 80; } sheet.Cells[4, 1].Value = "Счет действительн в течении" + " 5-ти банковских дней, включая день оформления"; using (ExcelRange r = sheet.Cells[4, 1, 4, 9]) { r.Merge = true; r.Style.WrapText = true; } sheet.Cells[5, 1].Value = "Ваш запрос будет обработан в" + " течение 1-3 дней. После проверки менеджером, Вам" + " будет выслан уточненный счет, т.к. указанные цены" + " могут отличаться от отпускных как в большую, так и" + " в меньшую сторону. В случае изменений в Вашем заказе," + " связанных с отсутствием товаров или увеличением сроков" + " доставки, менеджер свяжется с Вами."; using (ExcelRange r = sheet.Cells[5, 1, 5, 9]) { r.Merge = true; r.Style.WrapText = true; sheet.Row(5).Height = 80; } sheet.Cells[6, 1].Value = "№"; sheet.Cells[6, 2].Value = "Товар"; sheet.Cells[6, 3].Value = "Кол-во"; sheet.Cells[6, 4].Value = "Ед."; sheet.Cells[6, 5].Value = "Цена, Белорусский рубль"; sheet.Cells[6, 6].Value = "Сумма, Белорусский рубль"; sheet.Cells[6, 7].Value = "% НДС"; sheet.Cells[6, 8].Value = "Сумма НДС, Белорусский рубль"; sheet.Cells[6, 9].Value = "Сумма с НДС, Белорусский рубль"; ExcelRange tableheader = sheet.Cells[6, 1, 6, 9]; tableheader.Style.WrapText = true; tableheader.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; tableheader.Style.VerticalAlignment = ExcelVerticalAlignment.Center; tableheader.Style.Font.Bold = true; int count = 0; for (int i = 0; i < outputbills.Count; i++) { sheet.Cells[i + 7, 1].Value = outputbills[count].BillPosition; sheet.Cells[i + 7, 2].Value = outputbills[count].Name.FullName; sheet.Cells[i + 7, 2].AutoFitColumns(86); sheet.Cells[i + 7, 3].Value = outputbills[count].Quantity; sheet.Cells[i + 7, 4].Value = outputbills[count].Package; sheet.Cells[i + 7, 5].Value = outputbills[count].Price; sheet.Cells[i + 7, 6].Value = Convert.ToDecimal(outputbills[count].Amount); sheet.Cells[i + 7, 7].Value = outputbills[count].VatPercent; sheet.Cells[i + 7, 8].Value = Convert.ToDecimal(outputbills[count].AmountOfVat); sheet.Cells[i + 7, 9].Value = Convert.ToDecimal(outputbills[count].TotalAmount); ExcelRange row = sheet.Cells[i + 7, 1, i + 7, 9]; row.Style.WrapText = true; row.Style.Indent = 1; row.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; row.Style.VerticalAlignment = ExcelVerticalAlignment.Center; count = count + 1; } sheet.Cells[9, 1].Value = "Итого"; using (ExcelRange r = sheet.Cells[9, 1, 9, 5]) { r.Merge = true; r.Style.WrapText = true; r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; r.Style.VerticalAlignment = ExcelVerticalAlignment.Center; r.Style.Font.Bold = true; } sheet.Cells[9, 6].Formula = $"=SUM(F7:F{count + 6})"; sheet.Cells[9, 8].Formula = $"=SUM(H7:H{count + 6})"; sheet.Cells[9, 9].Formula = $"=SUM(I7:I{count + 6})"; exc.Workbook.Calculate(); sheet.Cells[10, 1].Value = $"Всего наименований {outputbills.Count}, на сумму {sheet.Cells[9, 9].Value.ToString()} белорусских рублей"; using (ExcelRange r = sheet.Cells[10, 1, 10, 9]) { r.Merge = true; r.Style.WrapText = true; r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; r.Style.VerticalAlignment = ExcelVerticalAlignment.Center; } //skip row = 11 sheet.Cells[12, 1].Value = "Индивидуальный предприниматель ____________________________________/И.А.Прусаков"; using (ExcelRange r = sheet.Cells[12, 1, 12, 9]) { r.Merge = true; r.Style.WrapText = true; r.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; r.Style.VerticalAlignment = ExcelVerticalAlignment.Center; } exc.Save(); } }