/// <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;
}
}
/// <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;
}
}
