I am using Aspose.Cells to export a pivot table to a PNG image.
This works well, but some labels added automatically by Excel are not exported in the same language to the image.
Here is my Excel source pivot table: image.png (1,4 Ko)
And this is the result of the export : image.png (6,0 Ko)
Note that the Excel source file was not created using Aspose.Cells.
If I change manually in Excel the labels, it is correctly taken into account in the exported image. But the defauilt text isn’t.
Here is my code :
Workbook asposeWorkbook = new Workbook(workbookPath);
asposeWorkbook.CalculateFormula();
var asposeSheet = workbook.Worksheets[0];
foreach (var pivotTable in asposeSheet.PivotTables)
{
pivotTable.RefreshData();
pivotTable.CalculateData();
}
var range = asposeSheet.Cells.CreateRange(rangeAddress);
ImageOrPrintOptions options = new ImageOrPrintOptions()
{
ImageType = ImageType.Png,
OnlyArea = true,
Transparent = true,
HorizontalResolution = 600,
VerticalResolution = 600
};
asposeSheet.PageSetup.PrintArea = range.RefersTo;
asposeSheet.PageSetup.CustomPaperSize(range.Width * 72, range.Height * 72);
var renderer = new SheetRender(asposeSheet, options);
renderer.ToImage(0, imagePath);
It appears that the issue you are encountering with the language of the labels in the exported image may stem from how Aspose.Cells manages the default text in pivot tables. Since these labels are generated automatically by Excel, they might not be correctly localized during the export process.
To ensure that the labels are exported in the correct language, you can follow these steps:
Set the Culture Info: Before exporting the pivot table, set the culture info for the workbook. This can help ensure that the labels are generated in the desired language.
Refresh the Pivot Table: After setting the culture info, make sure to refresh the pivot table to update the labels accordingly.
Here’s an updated version of your code incorporating these suggestions:
Workbook asposeWorkbook = new Workbook(workbookPath);
asposeWorkbook.CalculateFormula();
// Set the culture info for the workbook
asposeWorkbook.SetCultureInfo(new System.Globalization.CultureInfo("fr-FR")); // Change to your desired culture
var asposeSheet = asposeWorkbook.Worksheets[0];
foreach (var pivotTable in asposeSheet.PivotTables)
{
pivotTable.RefreshData();
pivotTable.CalculateData();
}
var range = asposeSheet.Cells.CreateRange(rangeAddress);
ImageOrPrintOptions options = new ImageOrPrintOptions()
{
ImageType = ImageType.Png,
OnlyArea = true,
Transparent = true,
HorizontalResolution = 600,
VerticalResolution = 600
};
asposeSheet.PageSetup.PrintArea = range.RefersTo;
asposeSheet.PageSetup.CustomPaperSize(range.Width * 72, range.Height * 72);
var renderer = new SheetRender(asposeSheet, options);
renderer.ToImage(0, imagePath);
Make sure to replace "fr-FR" with the appropriate culture code for your language. This should assist in exporting the pivot table with the correct language labels.
If the issue persists, consider manually updating the labels in Excel before exporting, as you have noted that this approach works correctly.
Thanks for the answers, but I don’t know which culture should I use.
The workbooks can come from anywhere, so I need to figure out the actual texts displayed.
I would have expected that the Shared String Table would be used by default to retrieve the correct labels.
My purpose is to export the range to an image as close as a screenshot would have done.
I read it, but i don’t thinks it helps much.
I don’t want to customize the globalize settings, I want to use the default labels that are present in the workbook (visible in the Shared String Table). Or find a way to get the culture of the workbook as it was created.
As said in previous message, I don’t know a priori where the workbooks are from, I just need to create an image that represents what the user is seeing when he opens the workbook in Excel.
We request some time to thoroughly evaluate the workbook’s source data and determine whether your desired feature can be supported. We have created the following new ticket(s) in our internal issue tracking system and will provide updates in accordance with the terms outlined in Free Support Policies.
Issue ID(s): CELLSNET-59105
Once we have updates on it, we will let you know here.
Thanks for the response and the creation of the ticket.
I don’t know if you need it, but in case it will help you, here is my workbook: Classeur1.zip (17,4 Ko)
Thank you for providing the Excel template, which may assist in evaluating your requirements and figuring out the task. We have recorded it in our database under your existing ticket.
@Gradlon
If customize globalization is not set, we will refresh the pivot tables with English region.
We can not simply reuse current labels in the file because we can not the current table does not contain all labels of Pivot table and what the current cell values represent.
Without region setting and customize globalization , we can not refresh pivot table as your excepted local view.
If you only to export as a screenshot , please remove codes about refreshing pivottable now.
I am unsure to understand why you cannot reuse the current labels in the file. Excel is using them, and correctly renders them when printing, for instance.
Is there a way to get the region setting from the worksheet, as it was created?
@Gradlon
1,If you refresh pivot table in an English environment in MS Excel, you will find these labels will be changed as English labels.
2,There is no region setting in xlsx file.
3,These display texts are value of the cells, not the setting the pivot table. When refreshing pivottables, we will enter labels (such as “Row Labels”, “Grand Total”) to the cells. These local labels should be stored in Excel language packs. Excel will display different labels in different regions.
@Gradlon
If formats are lost without refreshing pivot table in the result svg , please share a sample file, we will check it soon. We can only apply style of pivot table to the cells with inner feature.
@Gradlon
We will auto refresh style without updating data when converting to image in the next version.
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): CELLSNET-59114
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.
Thanks for the ticket and for your support. I look forward to get the new version!
“If formats are lost without refreshing pivot table in the result svg , please share a sample file, we will check it soon.”
→ I shared a workbook in a previous message of this thread.
@Gradlon
We have supported auto refreshing style of pivot table when converting range to image.
Please check the attached image generated by inner hot fix without explicit calling refreshing PivotTable: