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); } }
public string GetRefundsReportFileLink(DateTime refundsReportDate, int contractorId, int managerId) { string relativePath = "../" + Constants.RefundsReportPath; string rootPath = Server.MapPath(relativePath); string reportFile = String.Empty; // Check if file already exists. // Than check Modified date of the file in file name string docPath = Path.Combine(rootPath, contractorId.ToString(), refundsReportDate.ToString("yyyy-MM-dd")); if(!Directory.Exists(docPath)) Directory.CreateDirectory(docPath); string dateSeparator = "-"; string extension = ".xls"; string mask = String.Format("{0}*{1}", Constants.RefundsReportPrefix, extension); var directory = new DirectoryInfo(docPath); // get last created file in directory var existingFile = directory.GetFiles(mask).OrderByDescending(f => f.LastWriteTime).FirstOrDefault(); // check if file is actual upon document modified date if (existingFile != null) { // Cache 1 minute if (existingFile.CreationTime.AddSeconds(10) > DateTime.Now) { // return cached file return String.Format("{0}/{1}/{2}/{3}", relativePath, contractorId.ToString(), refundsReportDate.ToString("yyyy-MM-dd"), existingFile.Name); } else { // delete outdate file existingFile.Delete(); } } var context = new SkladDataContext(); // Get refunds products by day var products = GetRefundsProductLines(context, refundsReportDate, contractorId, managerId); if (products.Count() > 0) { string fileName = String.Format("{0}{1}{4}{2}{4}{3}{5}", Constants.RefundsReportPrefix, refundsReportDate.Year, refundsReportDate.ToString("MM"), refundsReportDate.ToString("dd"), dateSeparator, extension); string clientName = String.Empty; if (contractorId > 0) { var contractor = context.Contractors.Where(x => x.ContractorId == contractorId).FirstOrDefault(); if (contractor != null) clientName = contractor.Code; } else clientName = "По всем клиентам"; string titleRight = "Возврат от " + refundsReportDate.ToString("dd.MM.yyyy"); if (managerId > 0) { var user = context.UserProfiles.Where(x => x.UserId == managerId).FirstOrDefault(); if (user != null) titleRight += ". Менеджер: " + user.DisplayName; } // create report ExcelReportInfo reportInfo = new ExcelReportInfo { CreatedOf = refundsReportDate, FileName = fileName, FilePath = docPath, DocumentSubject = "Возврат от " + refundsReportDate.ToString("dd.MM.yyyy"), SheetName = Constants.RefundsReportPrefix + refundsReportDate.ToString("dd.MM.yyyy"), TitleLeft = clientName, TitleCenter = refundsReportDate.ToString("dd.MM.yyyy"), TitleRight = titleRight }; ReportHelper.GenerateProductLinesReport(products.ToList(), reportInfo); // ../Reports/Document/1093/e4fmt/Report-2014-10-09.xls // /Reports/Refunds/RefundsReport-2014-10-09.xls reportFile = String.Format("{0}/{1}/{2}/{3}", relativePath, contractorId.ToString(), refundsReportDate.ToString("yyyy-MM-dd"), fileName); } return reportFile; }
// TODO: create ajax method which creates datacontextModel private string GetReportFile(SkladDataContext datacontextModel, Document document, EntityEnum.ReportType type) { if (document == null) { logger.ErrorFormat("GetReportFile. Document is null"); throw new ArgumentException("GetReportFile. Document is null"); } bool saveChanges = false; // check for folder names if (String.IsNullOrEmpty(document.SecureFolderName)) { document.SecureFolderName = GenerateFolderName(document.CommonFolderName); saveChanges = true; } if (String.IsNullOrEmpty(document.CommonFolderName)) { document.CommonFolderName = GenerateFolderName(document.SecureFolderName); saveChanges = true; } if (saveChanges) datacontextModel.SaveChanges(); string relativePath = "../" + Constants.DocumentReportPath; string rootPath = Server.MapPath(relativePath); string reportFile = String.Empty; if (type == EntityEnum.ReportType.SaleReport) { // Check if file already exists. // Than check Modified date of the file in file name string docPath = Path.Combine(rootPath, document.DocumentId.ToString(), document.SecureFolderName); if(!Directory.Exists(docPath)) Directory.CreateDirectory(docPath); string dateSeparator = "-"; string extension = ".xls"; string mask = String.Format("{0}*{1}", Constants.DocumentReportPrefix, extension); var directory = new DirectoryInfo(docPath); // get last created file in directory var existingFile = directory.GetFiles(mask).OrderByDescending(f => f.LastWriteTime).FirstOrDefault(); if (existingFile != null && !document.IsReportOutdated) { // check if file is actual upon document modified date reportFile = String.Format("{0}/{1}/{2}/{3}", relativePath, document.DocumentId.ToString(), document.SecureFolderName, existingFile.Name); } else { string fileName = String.Format("{0}{1}{4}{2}{4}{3}{5}", Constants.DocumentReportPrefix, DateTimeOffset.Now.Year, DateTimeOffset.Now.ToString("MM"), DateTimeOffset.Now.ToString("dd"), dateSeparator, extension); // create report ExcelReportInfo reportInfo = new ExcelReportInfo { CreatedOf = document.CreatedOf, FileName = fileName, FilePath = docPath, DocumentSubject = "Отчёт №" + document.Number + " от " + document.CreatedOf, SheetName = "Report-" + document.CreatedOf.ToString("dd.MM.yyyy"), TitleLeft = (document.Contractor != null) ? document.Contractor.Code : String.Empty, TitleCenter = document.CreatedOf.ToString("dd.MM.yyyy"), TitleRight = "Док. №" + document.Number + " от " + document.CreatedOf.ToString("dd.MM.yyyy") }; ReportHelper.GenerateProductLinesReport(document.Products, reportInfo); document.IsReportOutdated = false; datacontextModel.SaveChanges(); reportFile = String.Format("{0}/{1}/{2}/{3}", relativePath, document.DocumentId.ToString(), document.SecureFolderName, fileName); } } return reportFile; }
private string GetSaleReportByContractorFile(Contractor contractor, List<Document> documents, int grandTotalQuantity, DateTime? periodFrom, DateTime? periodTo) { string relativePath = "../" + Constants.SalesByContractorReportPath; string rootPath = Server.MapPath(relativePath); string reportFile = String.Empty; // Check if file already exists. // Than check Modified date of the file in file name string docPath = Path.Combine(rootPath, contractor.ContractorId.ToString()); if (!Directory.Exists(docPath)) Directory.CreateDirectory(docPath); // string dateSeparator = "-"; string extension = ".xls"; string mask = String.Format("{0}*{1}", Constants.SalesByContractorReportPrefix, extension); var directory = new DirectoryInfo(docPath); // get last created file in directory var existingFile = directory.GetFiles(mask).OrderByDescending(f => f.LastWriteTime).FirstOrDefault(); // check if file is actual upon document modified date if (existingFile != null) { // Cache 1 minute if (existingFile.CreationTime.AddSeconds(10) > DateTime.Now) { // return cached file return String.Format("{0}/{1}/{2}", relativePath, contractor.ContractorId.ToString(), existingFile.Name); } else { // delete outdate file existingFile.Delete(); } } string fileName = String.Format("{0}{1}", Constants.SalesByContractorReportPrefix, extension); // create report ExcelReportInfo reportInfo = new ExcelReportInfo { CreatedOf = DateTime.Now, FileName = fileName, FilePath = docPath, DocumentSubject = "Список документов для " + contractor.Name + "(" + contractor.Code + ")", SheetName = Constants.SalesByContractorReportPrefix, TitleLeft = contractor.Code, TitleCenter = (periodFrom != null ? "За период "+periodFrom.Value.ToString("dd.MM.yyyy") : "") + (periodTo != null ? "-" + periodTo.Value.ToString("dd.MM.yyyy") : "") + " Общее кол-во: " + grandTotalQuantity, TitleRight = "" }; ReportHelper.GenerateContractorSalesReport(documents, reportInfo); // ../Reports/Document/1093/e4fmt/Report-2014-10-09.xls // /Reports/Refunds/RefundsReport-2014-10-09.xls reportFile = String.Format("{0}/{1}/{2}", relativePath, contractor.ContractorId.ToString(), fileName); return reportFile; }
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; }
/// <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); }