We are using Aspose.Cells for .NET to create a series of reports, by opening a template (Excel file) and updating the data within it. When we ask for the reports in Excel format, the resulting Excel file that is produced displays the conditional formatting as it is defined in the report template. However, when we request the report output as a pdf, the conditional formatting is not displayed.
See the two attached sample reports, one in Excel format, the other a pdf created by Aspose.Cells for .NET. Compare worksheet 6 and pdf page 14 to see the conditional formatting, which is displayed in the Excel file but missing in the pdf.
I have seen many posts in the forum on applying conditional formatting rules via Aspose.Cells, but all of them define the conditional formatting rules in code. We do not want the conditional formatting rules to reside in code, since they already exist in the template and are maintained and updated by staff members who do not develop with the Aspose tools. Is there a way to apply the existing conditional formatting that is already in the template to the cells before converting the file to a pdf?
Hi Deb,
Thank you for providing your source and resultant PDF files for investigation.
We are able replicate the problem of conditional formatting not getting preserved in the resultant PDF file while using the latest version of Aspose.Cells for .NET 8.2.0.2. The problem has been logged in our bug tracking system under the ticket CELLSNET-42969 for further investigation & correction purposes. Please spare us little time to properly analyze the problem cause, and to provide a fix for it at earliest possible. In the meanwhile, we will keep you posted with updates in this regard.
We have attached the snapshots of the problematic area for your reference. Please confirm that you have mentioned the same issue in your original post. In case you have observed other similar issues in the resultant PDF (generated with Aspose.Cells for .NET 8.2.0.2 & attached), please provide the snapshot so we may look into them.
C#
var book = new Workbook(“D:/temp/sample2.xlsx”);
book.CalculateFormula();
book.Save(“D:/temp/output.pdf”, SaveFormat.Pdf);
Please feel free to write back in case you have further questions or concerns.
Thank you for your prompt confirmation of the conditional formatting problem that I’m seeing.
I have reviewed the snapshots that you attached and want to confirm that you have correctly identified the problematic area of the spreadsheet. Specifically, some cells in the table should be formatted in a light gray font color with a ~ in front of the numeric value to indicate that the number is an estimate. In the .xlsx file this formatting is displayed properly, but in the pdf file that is created by Aspose.cells all the values in the table are displayed in a black font color with no ~ in front of any value. The lines of C# code that you used to convert my sample .xlsx file to pdf are the same lines that I used to create the pdf output from Aspose.cells.
I appreciate your help resolving this problem. Please let me know when you have a fix that you would like me to try.
Hi Deb,
Thank you for the confirmation. Sure, we will keep you posted with updates regarding the reported problem.
Hello,
I’m checking to see if you have an estimate of how long it might be until you have a fix for me to test.
Thanks!
Deb
Hi Deb,
Unfortunately, we haven’t yet received any updates in this regard. We will try to get an estimated release schedule of the fix on next working day.
Please accept our apologies for the inconvenience.
Hello,
Just checking to see if you have an estimate of when you might have a fix for the conditional formatting in pdf problem for me to test. I appreciate any information you can provide.
Thanks!
Deb
Hi Deb,
I am afraid, the ticket attached to this thread has not been fixed yet nor we have received further updates in this regard. We have now raised the priority of the ticket and requested the core development team to share an insight of this problem, and if possible, an estimated release schedule for the fix. Please spare us little more time in this regard.
Please accept our apologies for the inconvenience caused to you.
Thank you for raising the priority on this ticket - full support for conditional formatting is an important feature for our application of Aspose.Cells. As we have continued testing, we have found that conditional formatting is working in some cases when results are written to a pdf. For example, in Worksheet 7 of the attached Excel file, the red & orange conditional formatting is correctly displayed when the attached workbook is saved as a pdf using the code:
wb.Save(“pdfFormat.pdf”, Aspose.Cells.SaveFormat.Pdf)
Apparently, light gray font color in conditional formatting isn’t properly displayed in pdf format, but red & orange font colors are correctly displayed. I hope this observation helps you to provide us with a fix for this problem quickly. Without reliable conditional formatting, the reports we produce using Aspose tools will not be accurate, and we cannot move forward with our application until our reports provide reliable, accurate information.
Thank you for your assistance with this issue!
Hi Deb,
Thank you for providing further information for us to properly investigate the problem cause for the presented scenario. Unfortunately, we haven’t yet received any updates in reference to the ticket logged earlier as CELLSNET-42969, so we are currently waiting for the remarks of core development team on this. As soon as we receive any news, we will post here for your kind reference. Please accept our apologies for the delay in this regard.
I’m hoping that you were able to reproduce the correct display of conditional formatting when using red & orange colors in the color scheme that we brought to your attention last week. Conditional formatting with a gray-scale font color seems to be the problem. We are quite anxious to have an update on when we might expect conditional formatting to be fully functional for all font colors. I appreciate your attention to this matter, and will welcome any information you can provide.
Hi Deb,
Yes, we were able to notice the correct results when orange & red colors are used for the conditional formatting, and we have already recorded our observations to the ticket associated with this thread. Unfortunately, the problem seems to be complex to resolve that is the reason we are taking time to schedule the fix. As soon as we receive more updates in this regard, we will post here for your kind reference.
We have done some additional testing on the conditional formatting problem in pdf output, and would like to bring our results to your attention. It appears that conditional formatting settings that appear in the “Font” tab of the “Format Cells” dialog in Excel are applied correctly, while settings that appear in the “Number” tab of the “Format Cells” dialog in Excel are not. For example, font color and italics are correctly applied, while symbol and number of decimal changes are not. See the attached example.
I hope that these additional examples will help you to provide us with a solution very soon.
Hi Deb,
Thanks for your posting and using Aspose.Cells.
We were able to observe this issue after converting your Excel file into PDF using the latest version: Aspose.Cells for .NET 8.2.2 with the following code. Conditional formatting related to Number format is not being applied to Output PDF generated by Aspose.Cells.
We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.
This issue has been logged as
- CELLSNET-43105 - Conditional formatting related to Number format is not being applied to Output PDF
I have attached the output PDF and screenshot highlighting this issue for a reference.
C#
String filePath = @“F:\Shak-Data-RW\Downloads\conditional-format.xlsx”;
Workbook workbook = new Workbook(filePath);
workbook.Save(filePath + “.out.pdf”);
Hi Deb,
This is just to update you that we have fixed the problem logged earlier as CELLSNET-43105 in our bug tracking system. The fix will be delivered in couple of days after passing it through the QA. As soon as the new revision of the API is ready for public use, we will notify you here with the download to the upgraded API.
Thank you for your patience with us.
Thank you for your attention to this problem. I look forward to trying your fix and hope that it will resolve our problem, as your fixes have in the past. Please let me know as soon as it is available for me to test.
Hi Deb,
Thank you for your patience with us.
Please try the latest version of
Aspose.Cells for .NET 8.2.2.1 against the issue logged earlier as CELLSNET-43105. Attached is the resultant PDF generated on my end.
Please feel free to write back in case you have any concerns.
Thank you for providing us with a fix to test. Our preliminary testing results are very promising, and we will let you know early next week whether this release of Aspose.Cells has resolved all the problems with conditional formatting when creating a pdf document from Cells.Net. I appreciate your assistance and will be in touch on our next business day.
Hi Deb,
Thank you for your positive feedback. Please take your time to thoroughly test the new build on your side, and please feel free to contact us back in case you face any difficulty or have concerns.
We have tested the fix to conditional formatting in pdfs and found that it matches the conditional formatting generated by Excel in each case. Thanks so much for the fix!