I need to create an image snapshot of a pivot table in an excel spreadsheet. I used the toImage() method of a chart to create an image of a chart, but I don’t see a comparable method on pivot table.
Hi,
Thanks for your posting and considering Aspose.Cells.
You can get the image of entire worksheet or part of worksheet. So, if you want to get the image of your pivot table, you will have to take the image of the part of worksheet where your pivot table lies.
For example, your pivot table lies in a range A1:F20, then you can set the print area to A1:F20 and get the image of entire worksheet.
Please read this article that explains how to get the image of the range of worksheet.
I’m not going to know ahead of time what the cell range is for the pivot table. Is there a way I can determine the cell range using the pivot table name?
Hi,
Thanks for your posting and considering Aspose.Cells.
Yes, you can find the range of your entire pivot table using the PivotTable.getTableRange1() method.
Hi,
I need more information about this feature, I tried the following code, with an hardcoded range of Cells (Java String) containing an Excel Pivot Table object :
public void exportRangeToImg(Workbook workbook, String sheetName, String cellsRange, String targetPath, String targetFilename) {
// Access the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(sheetName);
worksheet.getPageSetup().setPrintArea(cellsRange);//range containing pivot table
// Set all margins as 0
worksheet.getPageSetup().setLeftMargin(0);
worksheet.getPageSetup().setRightMargin(0);
worksheet.getPageSetup().setTopMargin(0);
worksheet.getPageSetup().setBottomMargin(0);
// Set OnePagePerSheet option as true
ImageOrPrintOptions options = new ImageOrPrintOptions();
options.setOnePagePerSheet(true);
options.setImageType(ImageType.JPEG);
try {
// Take the image of your worksheet
SheetRender sr = new SheetRender(worksheet, options);
String fileFullPath = targetPath+targetFilename;
sr.toImage(0, fileFullPath);
} catch (Exception e) {
//@TODO Log Exception
}
}
Execution :
exportRangeToImg(workbook, "PIVOT", "A5:N28", Global.testPath, "PIVOT.jpeg");
Unfortunately, the pivot table format is not kept properly in the JPEG result. For example PivotStyle and table grid disappear completely, but data is still here :
source (“PIVOT” tab) : sampledatainsurance.zip (100.7 KB)
result : PIVOT.jpeg (53.3 KB)
result expected : PIVOT_expected.JPG (44.8 KB)
Is it normal or do I do something wrong ?
Thank you
Could you please zip and attach your template Excel file containing the pivot table which you are exporting to image, we will check it soon.
Hi, I modified my post above within attachments
Thanks for template Excel file and screenshots (attached to your previous post).
We evaluated your scenario/case a bit. You need to refresh the pivot table before rendering to image format. Please add a line to your code segment, the output image will be rendered as expected.
e.g.
Sample code:
…
Worksheet worksheet = workbook.getWorksheets().get(sheetName);
worksheet.refreshPivotTables();
worksheet.getPageSetup().setPrintArea(cellsRange);//range containing pivot table// Set all margins as 0
worksheet.getPageSetup().setLeftMargin(0);
worksheet.getPageSetup().setRightMargin(0);
worksheet.getPageSetup().setTopMargin(0);
worksheet.getPageSetup().setBottomMargin(0);
…
I hope this helps you.
1 Like
Thank you it is far better. However if I may, the format is not completely kept.
PIVOT2.jpeg (51.1 KB)
PIVOT2_expected.JPG (30.5 KB)
The font is not the same in this case. It is a detail but is it possible to fix it ?
Thanks for the screenshots.
The font is same but I think you are talking about quality of the image. You may simply enhance the resolutions and other attributes for your requirements. See sample code segment that you may try.
e.g.
Sample code:
ImageOrPrintOptions options = new ImageOrPrintOptions();
options.setOnePagePerSheet(true);
options.setImageType(ImageType.JPEG);
options.setHorizontalResolution(300);
options.setVerticalResolution(300);
options.setQuality(100);
.......
1 Like
You’re right, changing the resolution made a perfect match
Thank you very much
Hello,
How to keep the dimensions scale from the displayed Excel sheet to the exported IMG ?
It looks like there is an auto-fitting applied on rows/columns.
I use the same code as above, but I modified manually dimensions of some rows/columns on “PIVOT” sheet sampledatainsurance.zip (106.3 KB)
exportRangeToImg(workbook, "PIVOT", "A7:N33", Global.testPath, "PIVOT.jpeg");
and here is the IMG : PIVOT.jpeg (494.7 KB)
Yes, this is expected as you are refreshing pivot table using the line of code:
worksheet.refreshPivotTables(); → this line will refresh pivot tables in the sheet.
You need to avoid refreshing pivot table in the sheet, so it won’t scale the widths/dimensions. I tried the following sample code and it works fine (see the attached output image for your reference).
e.g.
Sample code:
Workbook workbook = new Workbook("f:\\files\\sampledatainsurance.xlsx");
// Access the first worksheet
Worksheet worksheet = workbook.getWorksheets().get("PIVOT");
worksheet.getPageSetup().setPrintArea("A7:N33");//range containing pivot table
// Set all margins as 0
worksheet.getPageSetup().setLeftMargin(0);
worksheet.getPageSetup().setRightMargin(0);
worksheet.getPageSetup().setTopMargin(0);
worksheet.getPageSetup().setBottomMargin(0);
// Set OnePagePerSheet option as true
ImageOrPrintOptions options = new ImageOrPrintOptions();
options.setOnePagePerSheet(true);
options.setImageType(ImageType.JPEG);
options.setHorizontalResolution(300);
options.setVerticalResolution(300);
options.setQuality(100);
try
{
// Take the image of your worksheet
SheetRender sr = new SheetRender(worksheet, options);
//String fileFullPath = targetPath + targetFilename;
String fileFullPath = "f:\\files\\out1.jpeg";
sr.toImage(0, fileFullPath);
}
catch (Exception e)
{
//@TODO Log Exception
}
I am using latest version, i.e., Aspose.Cells for Java v23.3, so if it did work with your current version, you need to upgrade to latest version of the API.
out1.jpeg (775.7 KB)
Mmmh it sounds contradicting to me. As we discussed before, I use worksheet.refreshPivotTables() to keep the Pivot Table format in the rendered image.
So if I follow you, to export into image the mentioned range, I have to choose between keeping the Pivot table format or keeping the columns/rows size of the Worksheet.
FYI I can’t see your .jpeg result attached since it is private. And yes I use the last 23.3 release as well.
@GVA32156
If you do not want to autofit columns when refreshing pivot table,please remove setting about atofitting columns as the following codes:
Workbook workbook = new Workbook(dir +"sampledatainsurance.xlsx");
// Access the first worksheet
Worksheet worksheet = workbook.getWorksheets().get("PIVOT");
PivotTable pt = worksheet.getPivotTables().get(0);
pt.setAutoFormat(false);
worksheet.refreshPivotTables();
···Tested, but it did not fix my problem unfortunately… : PIVOT.jpeg (541.6 KB)
From from I see, setting the autoFormat to “false” avoids the Pivot to be refreshed and get the PivotTableStyleType.
One cancels the other, I got almost the same result by removing these 2 actions : PIVOT2.jpeg (494.6 KB)
We noticed the issue as you mentioned. By removing auto-fit settings on pivot table columns, it also removes the pivot table’s style/formatting. We need to investigate the issue in details.
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-45323
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.
1 Like
This is to inform you that your issue (logged earlier as “CELLSJAVA-45323”) has been resolved now. The fix will be included in our upcoming release (Aspose.Cells v23.5) which is scheduled in the first half of May 2023. You will be notified once the next release is published.
1 Like
The issues you have found earlier (filed as CELLSJAVA-45323) have been fixed in Aspose.Cells for Java 23.5.