Hi,
It will be very complicated to include the hard coded data within the .net code and time consuming. We are trying to meet the deadline.
These Excel reports are generated from SSRS 2012 engine. After that in .NET we apply formatting.
As an alternative we are attaching the output of the generated Excel reports in the form of a mockups. These mockups are exactly what the actual file would look like when run through .NET code. These reports are SSRS reports and are being generated by Reporting services 2012 and then we apply formatting, filters page breaks, column slanting, merging of columns to these tabs.
The idea behind giving you the actual output is to see if you can catch anything obvious in the reports that Aspose may be not able to handle correctly.
So far we have identified that the issue is happening on the tab called “Member Adherence Tab”. In this tab we are slanting the column headers to 45 degree angle, applying filters, merging and setting new range for column headers in the .net code.
The thing is that the code doesn’t throw any runtime error. The error only pops up when we try to open the generated Excel file which says the file is corrupted.
This is the code section where the formatting happens within Default.aspx.cs:
else if (reporttype == "Copy of Stars_PCOR_PG_March_2016")
{
PropertyHelper.ApplyPrintFormatting(workbook.Worksheets["Group Summary"], "$1:$12"); //"Group Summary" Tab
ReportPropertyHelper.ApplySheetFilter(workbook, workbook.Worksheets["Group Summary"], "A12:K12", 12, 0, true);
}
//PropertyHelper.ApplyPrintFormatting(workbook.Worksheets["Physician Summary"], "$1:$5"); //"Physician Summary" Tab
//ReportPropertyHelper.ApplySheetFilter(workbook, workbook.Worksheets["Physician Summary"], "A5:I5", 5, 0, true);
PropertyHelper.ApplyPrintFormatting(workbook.Worksheets["Member Adherence Rpt"], "$1:$4"); //"Member Adherence Rpt" Tab
if (workbook.Worksheets["Member Adherence Rpt"].Cells.GetCell(3, 11).StringValue.Contains("HQPAF"))
{
//Seven HQPAF Columns ARE included in this report, so adjust the ranges
ReportFormatHelper.SetFormatRotatedHeader(workbook, workbook.Worksheets["Member Adherence Rpt"], "R4", "AU4", true);
ReportPropertyHelper.ApplySheetFilter(workbook, workbook.Worksheets["Member Adherence Rpt"], "A4:AT4", 4, 0, true);
ReportFormatHelper.SetNewMergeRange(workbook.Worksheets["Member Adherence Rpt"], "A3", "R3", "A3", "O3"); //Re-merge Patient Information Header
ReportFormatHelper.SetNewMergeRange(workbook.Worksheets["Member Adherence Rpt"], "S3", "AH3", "P3", "AA3"); //Re-merge HEDIS Measures Header
ReportFormatHelper.SetNewMergeRange(workbook.Worksheets["Member Adherence Rpt"], "AI3", "AP3", "AB3", "AK3"); //Re-merge Pharmacy Header
ReportFormatHelper.SetNewMergeRange(workbook.Worksheets["Member Adherence Rpt"], "AQ3", "AT3", "AL3", "AR3"); //Re-merge Member Header
}
else
{
//Seven HQPAF Columns are NOT included in this report, so adjust the ranges
// ReportFormatHelper.SetFormatRotatedHeader(workbook, workbook.Worksheets["Member Adherence Rpt"], "K4", "AK4", true);
//ReportPropertyHelper.ApplySheetFilter(workbook, workbook.Worksheets["Member Adherence Rpt"], "A4:AJ4", 4, 0, true);
//ReportFormatHelper.SetNewMergeRange(workbook.Worksheets["Member Adherence Rpt"], "A3", "K3", "A3", "H3", true); //Re-merge Patient Information Header
//ReportFormatHelper.SetNewMergeRange(workbook.Worksheets["Member Adherence Rpt"], "L3", "X3", "I3", "S3", false); //Re-merge HEDIS Measures Header
//ReportFormatHelper.SetNewMergeRange(workbook.Worksheets["Member Adherence Rpt"], "Y3", "AF3", "T3", "AB3", false); //Re-merge Pharmacy Header
//ReportFormatHelper.SetNewMergeRange(workbook.Worksheets["Member Adherence Rpt"], "AG3", "AL3", "AC3", "AJ3", false); //Re-merge Member Header
}
}
------------------------
So far we are able to successfully export Excel with four tabs: Description, Group Summary, HRM Alternative, Glossary. Attached is the generated file that has no issues so far.
(Copy of JEAN LEE MD SC_271593202_Patient Care Opportunity Report 4 tabs.xlsx.)
As soon as we add Physician Summary Tab or Member Adherence Tab, it throws error.
Attached are the code files.
Default.aspx.cs
ReportFormatHelper.cs
ReportPropertyHelper.cs