/// <summary>
        /// Generate a local report
        /// </summary>
        private void GenerateLocalReport()
        {
            List<SqlParameter> parameters = new List<SqlParameter>();

            parameters.Add(new SqlParameter("BudgetAllocationSetId", this.BudgetAllocationSetId));
            parameters.Add(new SqlParameter("GBSAccounts", this.GBSOnly));
            parameters.Add(new SqlParameter("StartPeriod", this.StartPeriod));
            parameters.Add(new SqlParameter("EndPeriod", this.EndPeriod));
            parameters.Add(new SqlParameter("Sources", string.Join("|", this.Sources)));

            IList<LocalValidationReportItem> results = this.ReportDao.GetLocalValidationReport(parameters.ToArray());

            new License().SetLicense("Aspose.Cells.lic");

            string[] categorizations = { "US Property", "US Fund", "US Development", "EU Property", "EU Fund", "UNKNOWN" };

            try
            {
                ZipPackageManager packageManager = new ZipPackageManager();

                Workbook book = new Workbook(FileFormatType.Xlsx);
                book.Worksheets.Clear();

                MemoryStream fileStream = new MemoryStream();

                Aspose.Cells.Style dateStyle = book.Styles[book.Styles.Add()];
                dateStyle.Number = 14;

                // Separate tab for each regional database
                foreach (string categorization in categorizations)
                {
                    Worksheet sheet = book.Worksheets.Add(categorization);

                    List<LocalValidationReportItem> currentSheetData = results.Where(item => item.LocalCategorization == categorization).ToList();

                    string[] fieldNames = new string[]
                            {
                                "ResolvedBy",
                                "SourceCode",
                                "Period",
                                "Ref",
                                "Item",
                                "EntityId",
                                "EntityName",
                                "GLAccountCode",
                                "GLAccountName",
                                "Department",
                                "DepartmentDescription",
                                "JobCode",
                                "JobCodeDescription",
                                "Amount",
                                "Description",
                                "EnterDate",
                                "Reversal",
                                "Status",
                                "Basis",
                                "UserId",
                                "CorporateDepartmentCode",
                                "Source",
                                "GlobalGLAccountCode",
                                "GlobalGLAccountName",
                                "LocalCategorization",
                                "LocalFinancialCategoryName",
                                "LocalMajorCategoryName",
                                "LocalMinorCategoryName",
                                "InflowOutflow",
                                "GrReportingEntityName",
                                "GrActivityTypeName",
                                "GrOriginatingSubRegionName",
                                "GrAllocationSubRegionName",
                                "GrFunctionalDepartmentName"
                            };

                    sheet.Cells.ImportCustomObjects(
                        currentSheetData,
                        fieldNames,
                        true,
                        0,
                        0,
                        currentSheetData.Count(),
                        false,
                        "dd/mm/yyyy",
                        false);

                    sheet.Cells.Columns[15].ApplyStyle(dateStyle, new StyleFlag() { NumberFormat = true });

                    ListObject sheetTable = sheet.ListObjects[sheet.ListObjects.Add(0, 0, Math.Max(sheet.Cells.MaxRow, 1), Math.Max(sheet.Cells.MaxColumn, 1), true)];
                    sheetTable.TableStyleType = TableStyleType.TableStyleLight1;

                    sheet.AutoFitColumns();
                }

                book.Save(fileStream, SaveFormat.Xlsx);

                fileStream.Position = 0;

                packageManager.Add("Local_Validation_Report-" + DateTime.Now.ToString("yyyyMMdd") + "-" + DateTime.Now.ToString("hhmmss")  + ".xlsx", fileStream);

                MemoryStream zipFileStream = packageManager.Save();

                Response.Clear();
                Response.Buffer = false;
                Response.AppendHeader("Content-Disposition", "attachment;filename=Local Validation Report - " + DateTime.Now.ToString("yyyyMMdd") + " - " + DateTime.Now.ToString("hhmmss") + ".zip");
                Response.AppendHeader("ContentType", "application/zip");

                byte[] fileBytes = new byte[zipFileStream.Length];

                Response.BinaryWrite(zipFileStream.ToArray());
                Response.End();
            }
            catch
            {
                Response.Clear();
                throw;
            }
        }
Exemplo n.º 2
0
        /// <summary>
        /// Exports Reports to Excel spreadsheet
        /// </summary>
        /// <param name="reports">The report results to put into the excel spreadsheet</param>
        public void ExportReports(List<ReportParametersDTO> reports)
        {
            new License().SetLicense("Aspose.Cells.lic");

            // Generate File
            try
            {
                // We use our own custom zip logic using System.IO.Packaging
                ZipPackageManager packageManager = new ZipPackageManager();

                // Create Spreadsheet
                Workbook book = new Workbook(FileFormatType.Xlsx);
                book.Worksheets.Clear();
                MemoryStream fileStream = new MemoryStream();

                Aspose.Cells.Style dateStyle = book.Styles[book.Styles.Add()];
                dateStyle.Number = 14;

                Worksheet sheet = book.Worksheets.Add("Reports");

                string[] fieldNames = new string[1];

                // Set which fields to import based on search filter report type
                switch ((Enums.ReportType)this._SelectedReportTypeId)
                {
                    case Enums.ReportType.BudgetOriginatorJobCodeDetailsReport:
                        fieldNames = this.GetJobCodeReportFields();
                        break;
                    case Enums.ReportType.BudgetOriginatorReport:
                        fieldNames = this.GetBudgetOriginatorReportFields();
                        break;
                    case Enums.ReportType.BudgetOwnerReport:
                        fieldNames = this.GetBudgetOwnerReportFields();
                        break;
                    case Enums.ReportType.ExpenseCzarReport:
                        fieldNames = this.GetExpenseCzarReportFields();
                        break;
                    case Enums.ReportType.ProfitabilityReport:
                        fieldNames = this.GetProfitabilityReportFields();
                        break;
                    default:
                        fieldNames = this.GetGenericReportFields();
                        break;
                }

                // Import data
                sheet.Cells.ImportCustomObjects(
                reports,
                fieldNames,
                true,
                0,
                0,
                reports.Count(),
                false,
                "dd/mm/yyyy",
                false);

                // Update header fields text
                Cells cells = sheet.Cells;
                FindOptions findOptions = new FindOptions();

                CellArea cellArea = new CellArea();
                cellArea.StartRow = 0;
                cellArea.StartColumn = 0;
                cellArea.EndRow = 0;
                cellArea.EndColumn = 20;

                findOptions.SetRange(cellArea);

                findOptions.SearchNext = true;
                findOptions.SeachOrderByRows = true;

                Cell cell;

                cell = cells.Find("ReportId", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Report ID");
                }

                cell = cells.Find("ReportName", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Report Name");
                }

                cell = cells.Find("ReportType", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Report Type");
                }

                cell = cells.Find("CurrencyCode", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Currency");
                }

                cell = cells.Find("ReportReceivers", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Report Receiver");
                }

                cell = cells.Find("EntityTypes", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Reporting Entity Type");
                }

                cell = cells.Find("EntityList", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Reporting Entity");
                }

                cell = cells.Find("SensitizeMRIPayrollData", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Show Sensitized Payroll Information");
                }

                cell = cells.Find("CalculationMethod", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Gross / Net Mode");
                }

                cell = cells.Find("GLCategorization", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Categorization");
                }

                cell = cells.Find("FunctionalDepartments", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Functional Department");
                }

                cell = cells.Find("AllocationSubRegions", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Allocation Sub Region");
                }

                cell = cells.Find("OriginatingSubRegions", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Originating Sub Region");
                }

                cell = cells.Find("EntityTypes", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Reporting Entity Type");
                }

                cell = cells.Find("EntityList", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Reporting Entity");
                }

                cell = cells.Find("SensitizeMRIPayrollData", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Show Sensitized Payroll Information");
                }

                cell = cells.Find("FinancialCategories", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Financial Category");
                }

                cell = cells.Find("MajorCategories", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Major Category");
                }

                cell = cells.Find("MinorCategories", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Minor Category");
                }

                cell = cells.Find("ActivityTypes", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Activity Type");
                }

                cell = cells.Find("IncludeGrossNonPayrollExpenses", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Include Gross Non-Payroll Expenses");
                }

                cell = cells.Find("IncludeFeeAdjustments", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Include Fee Adjustment");
                }

                cell = cells.Find("DisplayOverheadBy", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Display Overhead By");
                }

                cell = cells.Find("OverheadOriginatingSubRegion", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Overhead Originating Sub Region");
                }

                cell = cells.Find("ConsolidationRegions", null, findOptions);

                if (cell != null)
                {
                    cell.PutValue("Consolidation Region");
                }

                // Set styling
                ListObject sheetTable = sheet.ListObjects[sheet.ListObjects.Add(0, 0, Math.Max(sheet.Cells.MaxRow, 1), Math.Max(sheet.Cells.MaxColumn, 1), true)];
                sheetTable.TableStyleType = TableStyleType.TableStyleLight1;

                sheet.AutoFitColumns();

                book.Save(fileStream, SaveFormat.Xlsx);

                fileStream.Position = 0;

                packageManager.Add("Reports.xlsx", fileStream);

                MemoryStream zipFileStream = packageManager.Save();

                Response.Clear();

                Response.Buffer = false;
                Response.AppendHeader("Content-Disposition", "attachment;filename=Report_Parameters_" + DateTime.Now.ToString("yyyyMMdd") + "-" + DateTime.Now.ToString("hhmmss") + ".zip");
                Response.AppendHeader("ContentType", "application/zip");

                byte[] fileBytes = new byte[zipFileStream.Length];

                Response.BinaryWrite(zipFileStream.ToArray());
                Response.End();
            }
            catch
            {
                Response.Clear();
                throw;
            }
        }
        /// <summary>
        /// Generate a global report
        /// </summary>
        private void GenerateGlobalReport()
        {
            List<SqlParameter> parameters = new List<SqlParameter>();

            parameters.Add(new SqlParameter("BudgetAllocationSetId", this.BudgetAllocationSetId));
            parameters.Add(new SqlParameter("GBSAccounts", this.GBSOnly));
            parameters.Add(new SqlParameter("StartPeriod", this.StartPeriod));
            parameters.Add(new SqlParameter("EndPeriod", this.EndPeriod));
            parameters.Add(new SqlParameter("Sources", string.Join("|", this.Sources)));

            IList<GlobalValidationReportItem> results = this.ReportDao.GetGlobalValidationReport(parameters.ToArray());

            new License().SetLicense("Aspose.Cells.lic");

            Response.Clear();
            Response.Buffer = false;
            Response.AppendHeader("Content-Disposition", "attachment;filename=Global ValidationReport - " + DateTime.Now.ToString("yyyyMMdd") + " - " + DateTime.Now.ToString("hhmmss") + ".zip");
            Response.AppendHeader("ContentType", "application/zip");

            string[] resolveBy = { "Corporate Finance - Mapping", "Accounting - Re-class", "Both Corporate Finance and Accounting" };
            string[] regions = { "US", "EU", "BR", "IN", "CN" };
            string[] databases = { "PROP", "CORP" };

            try
            {
                ZipPackageManager packageManager = new ZipPackageManager();

                foreach (string resolve in resolveBy)
                {
                    Workbook book = new Workbook(FileFormatType.Xlsx);
                    book.Worksheets.Clear();
                    MemoryStream fileStream = new MemoryStream();

                    Aspose.Cells.Style dateStyle = book.Styles[book.Styles.Add()];
                    dateStyle.Number = 14;

                    // Separate tab for each regional database
                    foreach (string region in regions)
                    {
                        foreach (string database in databases)
                        {
                            bool isCorp = database == "CORP";
                            string source = isCorp ? string.Format("{0}C", region[0]) : region;
                            Worksheet sheet = book.Worksheets.Add(string.Format("{0} {1}", region, database));

                            List<GlobalValidationReportItem> currentSheetData = results.Where(
                                item => item.ResolvedBy == resolve &&
                                    item.SourceCode == source).ToList();

                            string[] fieldNames = new string[]
                            {
                                "ResolvedBy",
                                "SourceCode",
                                "Period",
                                "Ref",
                                "Item",
                                "EntityId",
                                "EntityName",
                                "GLAccountCode",
                                "GLAccountName",
                                "Department",
                                "DepartmentDescription",
                                "JobCode",
                                "JobCodeDescription",
                                "Amount",
                                "Description",
                                "EnterDate",
                                "Reversal",
                                "Status",
                                "Basis",
                                "UserId",
                                "CorporateDepartmentCode",
                                "Source",
                                "HasActivityTypeUnknown",
                                "HasFunctionalDepartmentUnknown",
                                "HasOriginatingRegionUnknown",
                                "HasReportingEntityUnknown",
                                "HasAllocationRegionUnknown",
                                "HasGLAccountUnknown",
                                "InvalidOriginatingRegionAndFunctionalDepartment",
                                "InvalidActivityTypeAndEntity",
                                "InvalidGLAccountAndFunctionalDepartment",
                                "PropertyParentAccountTotal",
                                "CorporateTotalByDescription",
                                "GrCategorization",
                                "GrFinancialCategoryName",
                                "GrMajorCategoryName",
                                "GrMinorCategoryName",
                                "InflowOutflow",
                                "GrReportingEntityName",
                                "GrActivityTypeName",
                                "GrOriginatingSubRegionName",
                                "GrAllocationSubRegionName",
                                "GrFunctionalDepartmentName"
                            };

                            sheet.Cells.ImportCustomObjects(
                                currentSheetData,
                                fieldNames,
                                true,
                                0,
                                0,
                                currentSheetData.Count(),
                                false,
                                "dd/mm/yyyy",
                                false);

                            sheet.Cells.Columns[15].ApplyStyle(dateStyle, new StyleFlag() { NumberFormat = true });
                            sheet.Cells.Columns[33].IsHidden = !isCorp;
                            sheet.Cells.Columns[32].IsHidden = isCorp;

                            ListObject sheetTable = sheet.ListObjects[sheet.ListObjects.Add(0, 0, Math.Max(sheet.Cells.MaxRow, 1), Math.Max(sheet.Cells.MaxColumn, 1), true)];
                            sheetTable.TableStyleType = TableStyleType.TableStyleLight1;

                            sheet.AutoFitColumns();
                        }
                    }

                    // Stream one file at a time
                    book.Save(fileStream, SaveFormat.Xlsx);

                    fileStream.Position = 0;

                    packageManager.Add(resolve + ".xlsx", fileStream);
                }

                MemoryStream zipFileStream = packageManager.Save();

                Response.Clear();

                Response.Buffer = false;
                Response.AppendHeader("Content-Disposition", "attachment;filename=Global_Validation_Report-" + DateTime.Now.ToString("yyyyMMdd") + "-" + DateTime.Now.ToString("hhmmss") + ".zip");
                Response.AppendHeader("ContentType", "application/zip");

                byte[] fileBytes = new byte[zipFileStream.Length];

                Response.BinaryWrite(zipFileStream.ToArray());
                Response.End();
            }
            catch
            {
                Response.Clear();
                throw;
            }
        }