Aspose.Cells for .NET license and excel(xlsx) format issue

Hi,

We have acquired Aspose.Cells for .NET back in 2013 with a version of 7.4.1.0.

But now it seems that it is having issues rendering xlsx (excel 2007 and above) files. we used to render older xls (2003) files so far. Now we need to start rendering newer xlsx format and it seems that old Aspose version is not able to generate the correct file format and we get error message when we try to open the generated file that “Excel found unreadable content…Do you want to recover the contents of this workbook…”

My first question is that whether Aspose 7.4.1 was able to render xlsx files or not?

Second option is if we want to use latest Aspose 8.8.3, do we need to get a new license file or can we use the existing license file?

Hi,

Thanks for your posting and using Aspose.Cells.

We think, 7.4.1.0 should render XLSX file fine. If you are having problem, then please share with us the simplest code that could replicate your issue. We will look into it and provide you a correct code. However, if it still could not resolve your issue, then you will have to use the latest version. Because we do not fix bugs in older version, we only fix bugs in latest version.

Besides, please try the latest version: Aspose.Cells for .NET 8.8.3 without setting any license and see if it fixes your issue or the issue still persists. In case, the issue still occurs, then report it to us, we will look into it and fix the issue asap.

Your license might have expired already because 7.4.1 is quite an old version, please check the license expiry date in your license file by opening it in a notepad. If your license expiry date has already passed, then you will have to acquire the new license to use the latest version by purchasing it.

Thanks for your prompt reply.


I tried latest version: 8.8.3 without setting the license and the generated excel report still has the file corrupted error.

Please advice any next steps. I will try to send you the simplest form of the code so you can debug but it might take some time to put together the code. Please let me know which pieces of the code you are interested to see?

Thanks.

Hi,

Thanks for your posting and using Aspose.Cells.

Please provide us the console application project that replicates your issue with the latest version: Aspose.Cells for .NET 8.8.3. Please remove all the unnecessary and irrelevant code that is not related to Aspose.Cells. For example, if you are retrieving data from database, then remove that code and use the hardcoded values in a list or datatable. In short, there should not be any dependency in your code except Aspose.Cells. Thanks for your cooperation in this regard. Have a good day.

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

Hi,

Thanks for using Aspose.Cells for .NET.

Please let us know the difference of these excel files.

  • Provider Group Name_Provider Group ID_Patient Care Opportunity Report_MOCKUP_v1.xlsx
  • Copy of JEAN LEE MD SC_271593202_Patient Care Opportunity Report 4 tabs.xlsx

Are they both output excel files? Or one of them is source excel file?

Which one of them is generated with SSRS2012?

I see, one of your excel file i.e Provider Group Name_Provider Group I… has the tab/worksheet named Member Adherence Rpt. Was this file generated using SSRS2012 or Aspose.Cells for .NET?

As you have said, so far, we have identified the problem because of Member Adherence Rpt, so you might be right, because there is a text slanting 45 degree. This might cause a corrupt excel file because there were some similar issues reported about text slanting. Will you please comment the code of text slanting and see, does it make any difference and generates correct excel file?

Please also provide some concise and useful information about your code files.

  • Default.aspx.cs
  • ReportFormatHelper.cs
  • ReportPropertyHelper.cs

All these things will shed some light on your problem and help us resolve this issue. Thanks for your cooperation in this regard.

  • Provider Group Name_Provider Group ID_Patient Care Opportunity Report_MOCKUP_v1.xlsx
  • This is the mockup that shows that the final ouput will be exactly like that once generated by SSRS and formatted by Aspose.
  • Copy of JEAN LEE MD SC_271593202_Patient Care Opportunity Report 4 tabs.xlsx
  • This is the actual file generated by SSRS 2012 and formatted using the latest version of Aspose 8.8.3 while we were testing the new version of Aspose and trying to resolve this issue.
The issue is also happening on Physician Summary Tab. So it is not just happening on Member Adherence tab. When we include the Physician Summary Tab, the Excel runs into the same issue but it does recover the file and Repair some error and is able to open the file. So there is something on the Physician summary that is also not working partially. Physician Summary tab does not have any slanting headers.
I am attaching the screenshot of the message which shows that the file was repaired which includes the Physician Summary tab. This doesn't include the Member Adherence Tab.

I am also attaching the file that is first generated by the SSRS 2012. So you will get an idea what the source file looks like before Aspose starts to apply any formatting to it. (Full Stars_PCOR_PG_March_2016.xlsx)

The "Provider Group Name_Provider Group I..." is again the mockup of what the final EXCEL will look like after Aspose would apply formatting.

The Default.aspx.cs is the default .NET page that is run when the application starts. This calls the methods from other two classes ReportFormatHelper and ReportPropertyHelper to apply formatting to each worksheet within Workbook.

I have tried to comment all the formatting and just render the report but that also fails when I include the Member Adherence section. So it seems that Aspose even fails to recognize the source even before it does any formatting.


I have a quick question. Our business wants to know if there is a way we can contact you via phone call. We are in the process of renewing our Enterprise License for the latest version. Is there a way we can contact technical support team on urgent basis while the process of renewing is underway? Please let us know if there is a possibility for talking with someone over the phone?


Thanks and regards,
Rizwan.

Hi,

Thanks for your posting and considering Aspose.Cells.

rizwan.atique:
I have a quick question. Our business wants to know if there is a way we can contact you via phone call. We are in the process of renewing our Enterprise License for the latest version. Is there a way we can contact technical support team on urgent basis while the process of renewing is underway? Please let us know if there is a possibility for talking with someone over the phone?

Thanks and regards,
Rizwan.


Please post this question in Aspose.Purchase forum. Enterprise users may get a phone call support. However, almost all the time, support is provided through forums. Aspose.Purchase team will guide you properly in this regard.

Hi,

Thanks for using Aspose.Cells.

We have logged your issue in our database for investigation. We will look into it and resolve your issue if possible. Once, there is some fix or other news for you, we will update you asap.

This issue has been logged as

  • CELLSNET-44587 - Output excel files corruption issue

Hi,

Thanks for using Aspose.Cells.

Please provide us your corrupted excel file. It will help us look into this issue and resolve it. Thanks for your cooperation in this regard. Have a good day.

Hi,

Please find the attached corrupted file that is not opening due to corruption.

Thanks,
Rizwan.

Hi Rizwan,

Thanks for providing us the needed file and using Aspose.Cells.

We have logged your provided file in our database for product team investigation. We will look into it and help you asap.

Hi,

Thanks for using Aspose.Cells.

Please clear all data before saving the file to the stream. Please see the following code for your reference.

C#
ms.SetLength(0);
workbook.Save(ms, SaveFormat.Xlsx);

Hi,



After I applied the above line of code the report was successfully created without any issues.



After that I reverted back to older version of Aspose 7.4.1 and tried to generate the file, it was giving the same error about corruption but it was able to recover the file and was able to open and display content. These are the error messages:



<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

error061120_07.xml

Errors were detected in file 'C:\Users\matique\Desktop\PCOR\E & I\Deidentified Mockups\hotfix 1 version 7 Patient Care Opportunity Report_Partial Success.xlsx’Repaired Records: String properties from /xl/sharedStrings.xml part (Strings)



Attached are the two files. One generated with Version 8.8.3 which has no issues at all. The other is with 7.4.1 which throws a error message but is able to open after you select Yes to recover.



What would you suggest? I think we will need to use the latest version of Aspose as it does not have any issues. That line of code is the permanent code fix that I should include then in my code?



Thanks for finding the solution so quick.

Regards,

Rizwan.

Hi,

Thanks for your feedback and using Aspose.Cells.

We are afraid, we cannot fix bugs in older versions. We always fix bugs in latest version so therefore you will have to use the latest version to resolve this issue at your end.

Thanks for your help. We will be getting the new version. But just confirm if this is the only line of code that we need to fix this current issue.



Also with new API, now I am seeing an existing

line of code is getting syntax error when adding :



currentSheet.VerticalPageBreaks.Add(pageBreakCell);



,seems like this function is obsolete or may require different parameter type:



///

/// Applies page breaks to the worksheet in locations where search text is found





///

/// Worksheet object





/// When search text is found, a page break will be inserted





public static void ApplyPageBreak(Worksheet currentSheet, string searchText)

{

//Refactored method and all existing references to add searchText variable. This is more dynamic for new code where different searchText is needed.



Cells sheetcells = currentSheet.Cells;



int rowCount = sheetcells.Rows.Count;

int colCount = sheetcells.Columns.Count;

int pageBreakIndexToRemove = 0;



Range targetRange = currentSheet.Cells.CreateRange(0, 0, rowCount, colCount);



for (int r = 0; r
{

for (int c = 0; c < colCount; c++)

{

string s = “”;

if (targetRange[r, c].Value != null)

{

s = (string)targetRange[r, c].Value.ToString();





if (s.Contains(searchText))

{

//get the cell name

string targetCell = targetRange.GetCellOrNull(r, c).Name.ToString();





string targetCol = targetCell.Substring(0, 1);

string targetCellNumber = targetCell.Substring(1);





//add 1 to the cell number so we can add a page break at the next line

int newCellNumber = Convert.ToInt32(targetCellNumber) + 1;





string pageBreakCell = (string)(targetCol + newCellNumber);



//add a page break after this cell

currentSheet.VerticalPageBreaks.Add(pageBreakCell);

currentSheet.HorizontalPageBreaks.Add(pageBreakCell);



pageBreakIndexToRemove = newCellNumber;

}

}

}

}

//need this line to remove the last page break just before the document number.



pageBreakIndexToRemove = pageBreakIndexToRemove - 1;



int pbCount = currentSheet.HorizontalPageBreaks.Count;



for (int z = 0; z < pbCount; z++)

{

int rindex = currentSheet.HorizontalPageBreaks[z].Row;



if (rindex == pageBreakIndexToRemove)

{

currentSheet.HorizontalPageBreaks.RemoveAt(z);



}

}

}

Hi,

Thanks for your posting and considering Aspose.Cells.

rizwan.atique:

Also with new API, now I am seeing an existing
line of code is getting syntax error when adding :


currentSheet.VerticalPageBreaks.Add(pageBreakCell);

seems like this function is obsolete or may require different parameter type:

///


/// Applies page breaks to the worksheet in locations where search text is found


I have tested this issue with the following sample code and it did not give me any error as you have mentioned. I tested it with the latest version:
Aspose.Cells for .NET v8.8.3.9 and attached the output excel file generated by the code for your reference.

C#
Workbook wb = new Workbook();

Worksheet currentSheet = wb.Worksheets[0];
string pageBreakCell = “E3”;
currentSheet.VerticalPageBreaks.Add(pageBreakCell);

wb.Save(“output.xlsx”);