Hi Aspose team,
I’m encountering an issue when using Aspose.Cells to generate screenshots from an Excel template. The behaviour of the chart’s red border seems inconsistent depending on how rows are handled:
- When no rows are hidden, the red border in the chart is rendered and exported correctly.
- When some middle rows are deleted, the red border is still rendered and exported correctly.
- However, when some middle rows are hidden, the red border in the chart goes missing in the generated screenshot.
ImageOrPrint options we’re using to generate the image (partial):
// set print options
final ImageOrPrintOptions options = new ImageOrPrintOptions();
options.setOnePagePerSheet(true);
options.setImageType(imageType.PNG);
options.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
options.setRenderingHint(RenderingHints.KEY_TEXT_ANTIALIASING, RenderingHints.VALUE_TEXT_ANTIALIAS_ON);
options.setHorizontalResolution(1000);
options.setVerticalResolution(900);
options.setTransparent(true);
Images (generated vs what seen in Excel):
-
Hide Row:
Hide Row Excel.png (10.1 KB)
Hide Row Image.png (13.4 KB)
-
Unhide Row:
Unhide Row Excel.png (12.8 KB)
Unhide Row Image.png (13.7 KB)
-
No Inserted Row:
No inserted Excel.png (11.6 KB)
No inserted Row Image.png (12.6 KB)
This inconsistency is quite surprising. I’d appreciate any insights into why this might be happening, and whether there’s a workaround or fix available.
Thanks in advance!
@frankhuang05
It sounds like you’re experiencing an issue with the chart border disappearing when certain rows are hidden in your Excel template. This behavior can occur due to how Aspose.Cells handles rendering charts based on the visibility of rows.
Cause: The issue may stem from the chart’s properties or how the data is being refreshed after hiding rows.
Solution: Here are several steps to troubleshoot and potentially resolve this issue:
-
Check Chart Properties: Ensure that the chart’s properties are set correctly. Specific settings related to visibility or data range can affect how the chart is rendered.
-
Update Chart Data: After hiding rows, refresh the chart data. Use the refreshPivotData()
method if your chart is based on a pivot table, or recalculate the chart if it’s based on regular data.
-
Adjust ImageOrPrintOptions: Review the options used to generate the image. Experiment with different settings, such as resolution or image type, to see if that affects the rendering of the chart border.
-
Test with Different Data: Create a simplified version of your Excel file with just the chart and a few rows of data. Test hiding rows in this simplified version to see if the issue persists. This can help isolate whether the problem is with the specific data or the chart itself.
-
Update Aspose.Cells: Ensure you are using the latest version of Aspose.Cells, as updates often include bug fixes and improvements that could resolve your issue.
Next Steps: Here’s a minimal code snippet to refresh the chart data:
// Assuming 'chart' is your Chart object
chart.refreshPivotData(); // If it's a pivot chart
chart.calculate(); // For regular charts
If these steps do not resolve the issue, please provide more details about your chart setup, and I will assist you further. Thank you for your patience, and I look forward to helping you resolve this!
Here is the PageSetup we’re using to generate the image:
// Set all margins as 0
setup.setLeftMargin(0);
setup.setRightMargin(0);
setup.setTopMargin(0);
setup.setBottomMargin(0);
And we’re using the latest version:
[INFO] ± com.aspose:aspose-cells:jar:25.3:compile
The red border should remain visible regardless of whether rows are hidden or not.
The issue persisted, thanks.
@frankhuang05,
Thank you for providing the screenshots and details.
To accurately evaluate your issue on our side, please share your template Excel file that includes the chart and has some middle rows hidden. Kindly zip the file and attach it here. We will check your issue soon.
Hello @amjad.sahi,
Thank you for your response!
This is the .zip file including the test_excel template (row 2 is hidden)
test_excel.xlsx.zip (69.9 KB)
@frankhuang05,
Thanks for the template Excel file.
After initial testing, I am able to reproduce the issue as you mentioned via screenshots by using your template Excel file and following sample code. I found the issue with missing chart border when hiding rows in Excel template.
Workbook workbook = new Workbook("d:\\files\\test_excel.xlsx");
// set print options
final ImageOrPrintOptions options = new ImageOrPrintOptions();
options.setOnePagePerSheet(true);
options.setImageType(com.aspose.cells.ImageType.PNG);
options.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
options.setRenderingHint(RenderingHints.KEY_TEXT_ANTIALIASING, RenderingHints.VALUE_TEXT_ANTIALIAS_ON);
options.setHorizontalResolution(1000);
options.setVerticalResolution(900);
options.setTransparent(true);
Worksheet worksheet = workbook.getWorksheets().get("Sheet1");
PageSetup setup = worksheet.getPageSetup();
// Set all margins as 0
setup.setLeftMargin(0);
setup.setRightMargin(0);
setup.setTopMargin(0);
setup.setBottomMargin(0);
SheetRender sheetRender = new SheetRender(worksheet, options);
sheetRender.toImage(0, "d:\\files\\out1.png");
out1.zip (198.4 KB)
We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-46484
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
@amjad.sahi
Hey team, I’m writing to follow up on the question I raised 12 days ago regarding the missing red border issue. I haven’t received a response yet, and I wanted to check if there’s any update or if additional information is needed from my side to help move things forward.
We’re currently blocked on this item, so I’d really appreciate it if you could prioritize a response or let me know the expected timeline for investigation.
Thanks in advance for your help!
@frankhuang05,
We apologize for not being able to analyze your issue in detail earlier due to other pressing priorities or tasks. However, I have now asked the relevant developer to review and schedule the ticket. We hope to provide you with (new) updates or an estimated timeline soon.
Rest assured, as soon as we have any updates, we will inform you here.
@frankhuang05 ,
When a row is hidden, the borders are messed up. It is complex to determine the final display border in various situations. It is a known issue, We have tried various solutions, but no one can handle all the situations.
We will continue to investigate it and update here if we have some progress.
@amjad.sahi @peyton.xu
Hi Team,
Thanks for your previous response regarding the missing red border issue when rows are hidden. I understand this is a known and complex issue, and I appreciate your continued investigation.
However, our customer is currently blocked by this and urgently needs a workaround. Could you please let us know:
- If there’s any temporary solution or workaround we can apply to mitigate the issue or let our customer use?
- Whether there are specific formatting practices we should avoid or adjust to prevent the border from disappearing?
Any guidance you can provide would be greatly appreciated, even if it’s not a complete fix.
Looking forward to your response. Thanks again!
@frankhuang05,
Thank you for sharing your concerns.
We apologize for any inconvenience caused. We will try to explore potential workarounds to address the issue temporarily (if possible) while we work on a permanent solution. We will get back to you soon.
@frankhuang05
If you only want to convert to image or pdf, you can delete hidden rows as the following :
Cells cells = c.Worksheets[1].Cells;
for (IEnumerator ie = cells.Rows.GetEnumerator(true, false); ie.MoveNext();)
{
Row r = ie.Current as Row;
if (r.IsHidden)
cells.DeleteRow(r.Index);
}