public static void GenerateContractorSalesReport(List <Document> documents, ExcelReportInfo reportInfo) { SheetStyle style = InitializeWorkbookAndGenerateStandartHeader(reportInfo); foreach (Document document in documents) { GenerateDocumentInfoRow(document, style); CreateProductLineTable(style, document.ProductsFilteredList, false); string pathToFile = Path.Combine(reportInfo.FilePath, reportInfo.FileName); WriteToFile(style.Workbook, pathToFile); } }
private static void GenerateDocumentInfoRow(Document document, SheetStyle style) { string documentInfo = "Док. №" + document.Number + ". " + document.Contractor.Code + " от " + document.CreatedOf.ToString("dd.MM.yyyy"); IRow row = style.Sheet.CreateRow(style.CurrentRowNum); ICell contractorCodeCell = row.CreateCell(0); contractorCodeCell.CellStyle = style.StyleBold; contractorCodeCell.SetCellValue(documentInfo); contractorCodeCell.RichStringCellValue.ApplyFont(0, documentInfo.Length, style.Font); contractorCodeCell.CellStyle = style.StyleBold; style.CurrentRowNum += 1; }
public static void SheetStyleInit(IWorkbook workBook) { if (!SheetStyleInitFlag) { SheetStyle.SheetTitleStyle(workBook); SheetStyle.SheetHeadStyle(workBook); SheetStyle.SheetNormalStyle(workBook); SheetStyle.SheetSubTotalStyle(workBook); SheetStyle.SheetSumTotalStyle(workBook); } SheetStyleInitFlag = true; }
public IHttpActionResult PostStyle(int id, [FromBody] SheetStyle data) { data.Sheet = id; var oldData = db.SheetStyles.Find(data.Name, data.Sheet); if (oldData != null) { oldData.Text = data.Text; } else { db.SheetStyles.Add(data); } db.SaveChanges(); return(Ok("ok")); }
private static SheetStyle InitializeWorkbookAndGenerateStandartHeader(ExcelReportInfo reportInfo) { SheetStyle result = new SheetStyle(); result.Workbook = InitializeWorkbook(reportInfo.DocumentSubject); result.Sheet = result.Workbook.CreateSheet(reportInfo.SheetName); result.Sheet.SetColumnWidth(0, 5000); result.StyleBold = result.Workbook.CreateCellStyle(); result.Font = result.Workbook.CreateFont(); result.Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; result.StyleBold.SetFont(result.Font); result.StyleAlignCenter = CreateTableStyle(result.Workbook, true, false); result.StyleTableBold = CreateTableStyle(result.Workbook, true, true); result.StyleTable = CreateTableStyle(result.Workbook, true, false); result.StyleLeftBottom = CreateLeftBottomBorderStyle(result.Workbook); result.FirstRow = result.Sheet.CreateRow(0); if (!String.IsNullOrEmpty(reportInfo.TitleLeft)) { ICell contractorCodeCell = result.FirstRow.CreateCell(0); contractorCodeCell.CellStyle = result.StyleBold; contractorCodeCell.SetCellValue(reportInfo.TitleLeft); contractorCodeCell.RichStringCellValue.ApplyFont(0, reportInfo.TitleLeft.Length, result.Font); contractorCodeCell.CellStyle = result.StyleBold; } if (!String.IsNullOrEmpty(reportInfo.TitleCenter)) { ICell dateCreatedCell = result.FirstRow.CreateCell(1); dateCreatedCell.SetCellValue(reportInfo.TitleCenter); } if (!String.IsNullOrEmpty(reportInfo.TitleRight)) { result.FirstRow.CreateCell(3).SetCellValue(reportInfo.TitleRight); } CreateHeader(result.Workbook, result.Sheet, 2); result.CurrentRowNum = 3; return(result); }
public void StyleFormat(Worksheet xlWorkSheet, SheetStyle sheetStyle, string KPIType, string KPITypeValue) { switch (sheetStyle) { case SheetStyle.SheetStyle1: { SetStyle1(xlWorkSheet, KPIType, KPITypeValue); } break; case SheetStyle.SheetStyle2: { SetStyle2(xlWorkSheet, KPIType, KPITypeValue); } break; case SheetStyle.SheetStyle3: { SetStyle3(xlWorkSheet); } break; case SheetStyle.SheetStyle4: { SetStyle4(xlWorkSheet, KPIType, KPITypeValue); } break; case SheetStyle.SheetStyle5: { SetStyle5(xlWorkSheet, KPIType, KPITypeValue); } break; default: break; } }
/// <summary> /// Отчёт по товарам /// </summary> /// <param name="products"></param> /// <param name="createdOf"></param> /// <param name="subject"></param> /// <param name="path"></param> /// <param name="fileName"></param> public static void GenerateProductLinesReport(ICollection <ProductLine> products, ExcelReportInfo reportInfo) { SheetStyle style = InitializeWorkbookAndGenerateStandartHeader(reportInfo); //InitializeWorkbook(reportInfo.DocumentSubject); //ISheet sheet1 = hssfworkbook.CreateSheet(reportInfo.SheetName); //sheet1.SetColumnWidth(0, 5000); //ICellStyle styleBold = hssfworkbook.CreateCellStyle(); ////create a font style //IFont font = hssfworkbook.CreateFont(); //font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; //styleBold.SetFont(font); //ICellStyle styleAlignCenter = CreateTableStyle(true, false); //ICellStyle styleTableBold = CreateTableStyle(false, true); //ICellStyle styleTable = CreateTableStyle(false, false); //ICellStyle styleLeftBottom = CreateLeftBottomBorderStyle(); //IRow row1 = sheet1.CreateRow(0); //if (!String.IsNullOrEmpty(reportInfo.TitleLeft)) //{ // ICell contractorCodeCell = row1.CreateCell(0); // contractorCodeCell.CellStyle = styleBold; // contractorCodeCell.SetCellValue(reportInfo.TitleLeft); // contractorCodeCell.RichStringCellValue.ApplyFont(0, reportInfo.TitleLeft.Length, font); // contractorCodeCell.CellStyle = styleBold; //} //if (!String.IsNullOrEmpty(reportInfo.TitleCenter)) //{ // ICell dateCreatedCell = row1.CreateCell(1); // dateCreatedCell.SetCellValue(reportInfo.TitleCenter); //} //if (!String.IsNullOrEmpty(reportInfo.TitleRight)) // row1.CreateCell(3).SetCellValue(reportInfo.TitleRight); //CreateHeader(sheet1, 2); CreateProductLineTable(style, products); //var priceQuery = // from prod in products // group prod by prod.SupplierId into grouping // select new // { // grouping.Key, // grouping, // Count = grouping.Count(), // TotalQuantity = grouping.Sum(p => p.Quantity), // TotalPurchasePrice = grouping.Sum(p => p.PurchasePrice), // TotalSalePrice = grouping.Sum(p => p.SalePrice), // TotalSum = grouping.Sum(p => p.Sum), // TotalSaleSum = grouping.Sum(p => p.SaleSum) // }; ////int rowCounter = 3; //double grandTotalSum = 0, grandTotalSaleSum = 0; //int grandTotalQuantity = 0; //foreach (var grp in priceQuery) //{ // bool firstRow = true; // int supplierRowId = rowCounter; // foreach (var gr in grp.grouping) // { // //supplierId; // IRow rowData = style.Sheet.CreateRow(rowCounter); // if (firstRow) // { // string supplierCode = "Фабрика не известна"; // if (grp.Key != null) // supplierCode = products.Where(x => x.SupplierId == grp.Key).FirstOrDefault().SupplierCode; // ICell supplierCell = rowData.CreateCell(0); // supplierCell.SetCellValue(supplierCode); // supplierCell.CellStyle = style.StyleAlignCenter; // // sheet1.AddMergedRegion(new CellRangeAddress(rowCounter, grp.Count, 0, 0)); // } // else // { // var cell = rowData.GetCell(0) ?? rowData.CreateCell(0); // cell.CellStyle = style.StyleAlignCenter; // } // ICell productArticleCell = rowData.CreateCell(1); // productArticleCell.SetCellValue(gr.ProductArticle); // productArticleCell.CellStyle = style.StyleTable; // ICell quantityCell = rowData.CreateCell(2); // quantityCell.SetCellValue(gr.Quantity); // quantityCell.CellStyle = style.StyleTable; // ICell purchasePriceCell = rowData.CreateCell(3); // purchasePriceCell.SetCellValue((double)gr.PurchasePrice); // purchasePriceCell.CellStyle = style.StyleTable; // ICell salePriceCell = rowData.CreateCell(4); // salePriceCell.SetCellValue((double)gr.SalePrice); // salePriceCell.CellStyle = style.StyleTable; // ICell sumCell = rowData.CreateCell(5); // sumCell.SetCellValue((double)gr.Sum); // sumCell.CellStyle = style.StyleTable; // ICell saleSumCell = rowData.CreateCell(6); // saleSumCell.SetCellValue((double)gr.SaleSum); // saleSumCell.CellStyle = style.StyleTable; // rowData.CreateCell(7).SetCellValue(gr.Comment); // rowCounter++; // firstRow = false; // } // CellRangeAddress region = new CellRangeAddress(supplierRowId, grp.Count + supplierRowId, 0, 0); // style.Sheet.AddMergedRegion(region); // IRow rowTotal = style.Sheet.CreateRow(rowCounter); // ICell totalCell0 = rowTotal.GetCell(0) ?? rowTotal.CreateCell(0); // totalCell0.CellStyle = style.StyleLeftBottom; // ICell totalCell2 = rowTotal.CreateCell(1); // totalCell2.CellStyle = style.StyleTableBold; // grandTotalQuantity += grp.TotalQuantity; // ICell quantityTotalCell = rowTotal.CreateCell(2); // quantityTotalCell.SetCellValue(grp.TotalQuantity); // quantityTotalCell.CellStyle = style.StyleTableBold; // ICell totalCell3 = rowTotal.CreateCell(3); // totalCell3.CellStyle = style.StyleTableBold; // ICell totalCell4 = rowTotal.CreateCell(4); // totalCell4.CellStyle = style.StyleTableBold; // grandTotalSum += (double)grp.TotalSum; // ICell sumTotalCell = rowTotal.CreateCell(5); // sumTotalCell.SetCellValue((double)grp.TotalSum); // sumTotalCell.CellStyle = style.StyleTableBold; // grandTotalSaleSum += (double)grp.TotalSaleSum; // ICell sumSaleTotalCell = rowTotal.CreateCell(6); // sumSaleTotalCell.SetCellValue((double)grp.TotalSaleSum); // sumSaleTotalCell.CellStyle = style.StyleTableBold; // // итоги // rowCounter = rowCounter + 2; //} //// итоговые суммы //IRow rowGrandTotal = style.Sheet.CreateRow(rowCounter); //ICell granTotalCell0 = rowGrandTotal.GetCell(0) ?? rowGrandTotal.CreateCell(0); //granTotalCell0.SetCellValue("Итого:"); //granTotalCell0.CellStyle = style.StyleTableBold; //ICell quantityGrandTotalCell = rowGrandTotal.CreateCell(2); //quantityGrandTotalCell.SetCellValue(grandTotalQuantity); //quantityGrandTotalCell.CellStyle = style.StyleTableBold; //ICell sumGrandTotalCell = rowGrandTotal.CreateCell(5); //sumGrandTotalCell.SetCellValue(grandTotalSum); //sumGrandTotalCell.CellStyle = style.StyleTableBold; //ICell sumGrandSaleTotalCell = rowGrandTotal.CreateCell(6); //sumGrandSaleTotalCell.SetCellValue(grandTotalSaleSum); //sumGrandSaleTotalCell.CellStyle = style.StyleTableBold; //for (int i = 0; i <= rowCounter; i++) //{ // IRow row = sheet1.GetRow(i) ?? sheet1.CreateRow(i); // var cell = row.GetCell(0) ?? row.CreateCell(0); // cell.CellStyle = styleAlignCenter; //} string pathToFile = Path.Combine(reportInfo.FilePath, reportInfo.FileName); WriteToFile(style.Workbook, pathToFile); }
private static void CreateProductLineTable(SheetStyle style, ICollection <ProductLine> products, bool showGrandTotal = true) { var priceQuery = from prod in products group prod by prod.SupplierId into grouping select new { grouping.Key, grouping, Count = grouping.Count(), TotalQuantity = grouping.Sum(p => p.Quantity), TotalPurchasePrice = grouping.Sum(p => p.PurchasePrice), TotalSalePrice = grouping.Sum(p => p.SalePrice), TotalSum = grouping.Sum(p => p.Sum), TotalSaleSum = grouping.Sum(p => p.SaleSum) }; // int rowCounter = 3; double grandTotalSum = 0, grandTotalSaleSum = 0; int grandTotalQuantity = 0; foreach (var grp in priceQuery) { bool firstRow = true; int supplierRowId = style.CurrentRowNum; foreach (var gr in grp.grouping) { //supplierId; IRow rowData = style.Sheet.CreateRow(style.CurrentRowNum); if (firstRow) { string supplierCode = "Фабрика не известна"; if (grp.Key != null) { supplierCode = products.Where(x => x.SupplierId == grp.Key).FirstOrDefault().SupplierCode; } ICell supplierCell = rowData.CreateCell(0); supplierCell.SetCellValue(supplierCode); supplierCell.CellStyle = style.StyleAlignCenter; // sheet1.AddMergedRegion(new CellRangeAddress(rowCounter, grp.Count, 0, 0)); } else { var cell = rowData.GetCell(0) ?? rowData.CreateCell(0); cell.CellStyle = style.StyleAlignCenter; } ICell productArticleCell = rowData.CreateCell(1); productArticleCell.SetCellValue(gr.ProductArticle); productArticleCell.CellStyle = style.StyleTable; ICell quantityCell = rowData.CreateCell(2); quantityCell.SetCellValue(gr.Quantity); quantityCell.CellStyle = style.StyleTable; ICell purchasePriceCell = rowData.CreateCell(3); purchasePriceCell.SetCellValue((double)gr.PurchasePrice); purchasePriceCell.CellStyle = style.StyleTable; ICell salePriceCell = rowData.CreateCell(4); salePriceCell.SetCellValue((double)gr.SalePrice); salePriceCell.CellStyle = style.StyleTable; ICell sumCell = rowData.CreateCell(5); sumCell.SetCellValue((double)gr.Sum); sumCell.CellStyle = style.StyleTable; ICell saleSumCell = rowData.CreateCell(6); saleSumCell.SetCellValue((double)gr.SaleSum); saleSumCell.CellStyle = style.StyleTable; rowData.CreateCell(7).SetCellValue(gr.Comment); style.CurrentRowNum++; firstRow = false; } CellRangeAddress region = new CellRangeAddress(supplierRowId, grp.Count + supplierRowId, 0, 0); style.Sheet.AddMergedRegion(region); IRow rowTotal = style.Sheet.CreateRow(style.CurrentRowNum); ICell totalCell0 = rowTotal.GetCell(0) ?? rowTotal.CreateCell(0); totalCell0.CellStyle = style.StyleLeftBottom; ICell totalCell2 = rowTotal.CreateCell(1); totalCell2.CellStyle = style.StyleTableBold; grandTotalQuantity += grp.TotalQuantity; ICell quantityTotalCell = rowTotal.CreateCell(2); quantityTotalCell.SetCellValue(grp.TotalQuantity); quantityTotalCell.CellStyle = style.StyleTableBold; ICell totalCell3 = rowTotal.CreateCell(3); totalCell3.CellStyle = style.StyleTableBold; ICell totalCell4 = rowTotal.CreateCell(4); totalCell4.CellStyle = style.StyleTableBold; grandTotalSum += (double)grp.TotalSum; ICell sumTotalCell = rowTotal.CreateCell(5); sumTotalCell.SetCellValue((double)grp.TotalSum); sumTotalCell.CellStyle = style.StyleTableBold; grandTotalSaleSum += (double)grp.TotalSaleSum; ICell sumSaleTotalCell = rowTotal.CreateCell(6); sumSaleTotalCell.SetCellValue((double)grp.TotalSaleSum); sumSaleTotalCell.CellStyle = style.StyleTableBold; // итоги style.CurrentRowNum = style.CurrentRowNum + 2; } if (showGrandTotal) { // итоговые суммы IRow rowGrandTotal = style.Sheet.CreateRow(style.CurrentRowNum); ICell granTotalCell0 = rowGrandTotal.GetCell(0) ?? rowGrandTotal.CreateCell(0); granTotalCell0.SetCellValue("Итого:"); granTotalCell0.CellStyle = style.StyleTableBold; ICell quantityGrandTotalCell = rowGrandTotal.CreateCell(2); quantityGrandTotalCell.SetCellValue(grandTotalQuantity); quantityGrandTotalCell.CellStyle = style.StyleTableBold; ICell sumGrandTotalCell = rowGrandTotal.CreateCell(5); sumGrandTotalCell.SetCellValue(grandTotalSum); sumGrandTotalCell.CellStyle = style.StyleTableBold; ICell sumGrandSaleTotalCell = rowGrandTotal.CreateCell(6); sumGrandSaleTotalCell.SetCellValue(grandTotalSaleSum); sumGrandSaleTotalCell.CellStyle = style.StyleTableBold; } }