public IHttpActionResult ExportGroupedSalesToExcel(FilterSales filter) { var response = new DataResponse <string>(); try { #region Sales Default Export NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport"); NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper(); string[] headers = new string[] { filter.GroupBy == 1 ? "Practice Name" : filter.GroupBy == 2 ? "Rep Name" : "Sales Team", "Sales", "Last Activity On" }; var headerRow = sheet.CreateRow(0); //create header for (int i = 0; i < headers.Length; i++) { sheet.DefaultColumnWidth = 20; XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFColor colorToFill = new XSSFColor(Color.Aqua); style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua); headerRow.RowStyle = style; var cell = headerRow.CreateCell(i); cell.SetCellValue(headers[i]); } string[] allowedRoles = { "RDSLS" }; string[] superRoles = { "RDSLSALL" }; bool hasSuperRight = HasRight(superRoles); bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" }); if (HasRight(allowedRoles) || hasSuperRight) { var repository = new RepositorySales(); var dataResponse = repository.GetAllGroupedSales(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath); List <GroupedSales> salesList = dataResponse.Model.List.ToList(); int recordCount = dataResponse.Model.Pager.TotalCount; //fill content var rowIndex = 0; for (int i = 0; i < recordCount; i++) { rowIndex++; var row = sheet.CreateRow(rowIndex); var objSalesItem = salesList[i]; if (objSalesItem == null) { continue; } var font = workbook.CreateFont(); font.FontHeightInPoints = 11; font.FontName = "Calibri"; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; var cell1 = row.CreateCell(0); cell1.CellStyle = workbook.CreateCellStyle(); cell1.CellStyle.SetFont(font); cell1.SetCellValue(string.IsNullOrEmpty(objSalesItem.KeyName) ? "Missing Information" : objSalesItem.KeyName); var cell2 = row.CreateCell(1); cell2.CellStyle = workbook.CreateCellStyle(); cell2.CellStyle.SetFont(font); cell2.SetCellValue(objSalesItem.Count); var cell3 = row.CreateCell(2); cell3.CellStyle = workbook.CreateCellStyle(); cell3.CellStyle.SetFont(font); cell3.SetCellValue(Convert.ToString(objSalesItem.LastActivityOn)); } string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports"); string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory)); if (!Directory.Exists(fileUri)) { Directory.CreateDirectory(fileUri); } string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now), extension = "xlsx"; string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension)); int count = 1; isExist: if (File.Exists(filePath)) { fileName = string.Format("{0}{1}{2}", fileName, count, extension); filePath = Path.Combine(fileUri, fileName); count++; goto isExist; } using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { workbook.Write(stream); } response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension)); } return(Ok <DataResponse>(response)); #endregion } catch (Exception ex) { ex.Log(); } response.Message = "Internal Server Error"; return(Ok <DataResponse>(response)); }
public IHttpActionResult ExportAllToExcel(FilterSales filter) { var response = new DataResponse <string>(); try { if (filter.GroupBy <= 0) { #region Sales Default Export NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport"); NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper(); string[] columnname = filter.DynamicFilters.Where(a => a.IsVisible == true).Select(a => a.ColumnName).ToArray(); string[] headers, columns = null; headers = columns = columnname; columns = columns.Select(x => x.Replace("SalesTeam", "RepGroup")).ToArray(); columns = columns.Select(x => x.Replace("CollectedDate", "CollectionDate")).ToArray(); //byte[] rgb = new byte[3] { 22, 183, 223 }; //XSSFCellStyle HeaderCellStyle = (XSSFCellStyle)workbook.CreateCellStyle(); //HeaderCellStyle.SetFillBackgroundColor(new XSSFColor(rgb)); var headerRow = sheet.CreateRow(0); //headerRow.RowStyle.FillBackgroundColor= (short)ColorTranslator.ToWin32(Color.Aqua); //create header for (int i = 0; i < headers.Length; i++) { sheet.DefaultColumnWidth = 20; XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle(); XSSFColor colorToFill = new XSSFColor(Color.Aqua); style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua); headerRow.RowStyle = style; var cell = headerRow.CreateCell(i); cell.SetCellValue(headers[i]); } string[] allowedRoles = { "RDSLS" }; string[] superRoles = { "RDSLSALL" }; bool hasSuperRight = HasRight(superRoles); bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" }); if (HasRight(allowedRoles) || hasSuperRight) { var repository = new RepositorySales(); var dataResponse = repository.GetAllList(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath); List <EntitySales> salesList = dataResponse.Model.List.ToList(); int recordCount = dataResponse.Model.Pager.TotalCount; //fill content var rowIndex = 0; for (int i = 0; i < recordCount; i++) { rowIndex++; var row = sheet.CreateRow(rowIndex); for (int j = 0; j < columns.Length; j++) { var font = workbook.CreateFont(); font.FontHeightInPoints = 11; font.FontName = "Calibri"; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; var cell = row.CreateCell(j); cell.CellStyle = workbook.CreateCellStyle(); cell.CellStyle.SetFont(font); string value = null; var objSalesItem = salesList[i]; var objStaticItem = objSalesItem.GetType().GetTypeInfo().GetProperty(columns[j]); if (objStaticItem != null) { var property = salesList[i].GetType().GetRuntimeProperties().FirstOrDefault(p => string.Equals(p.Name, objStaticItem.Name, StringComparison.OrdinalIgnoreCase)); if (property != null) { value = Convert.ToString(property.GetValue(salesList[i], null)); } } else { var objDynamicItem = objSalesItem.ReportColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]); if (objDynamicItem != null) { value = objDynamicItem.Value; } } cell.SetCellValue(value); } bool isFinanceDataCount = salesList[i].FinanceDataRecordCount > 1; if (isFinanceDataCount) { for (int k = 0; k < salesList[i].FinanceDataRecordCount; k++) { rowIndex++; var financeRow = sheet.CreateRow(rowIndex); for (int j = 0; j < columns.Length; j++) { var cell = financeRow.CreateCell(j); string value = null; var objSalesItem = salesList[i].FinanceDataList.ToList()[k]; var objStaticItem = objSalesItem.GetType().GetProperty(columns[j]); if (objStaticItem != null) { value = Convert.ToString(objStaticItem.GetValue(objSalesItem, null)); } else { var objDynamicItem = objSalesItem.FinanceColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]); if (objDynamicItem != null) { value = objDynamicItem.Value; } } cell.SetCellValue(value); } } } } string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports"); string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory)); if (!Directory.Exists(fileUri)) { Directory.CreateDirectory(fileUri); } string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now), extension = "xlsx"; string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension)); int count = 1; isExist: if (File.Exists(filePath)) { fileName = string.Format("{0}{1}{2}", fileName, count, extension); filePath = Path.Combine(fileUri, fileName); count++; goto isExist; } using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write)) { workbook.Write(stream); } response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension)); } return(Ok <DataResponse>(response)); #endregion } #region Sales Grouped Report return(ExportGroupedSalesToExcel(filter)); #endregion } catch (Exception ex) { ex.Log(); } response.Message = "Internal Server Error"; return(Ok <DataResponse>(response)); }