Extra white space on right side of PDF of a excel chart

Aspose.Cells Product Family
1

Hi,

I am using aspose-cells-8.3.2.4 jar.
There is difference between PDF & image of a chart using below code. There is extra white space on right side in chart PDF.


private File chartToImage(Workbook workbook, com.aspose.cells.Chart chart, NameValue[] outputFormatProperties) throws IOException, Exception {
ExcelDataOutputGenerator outputGenerator = excelDataOutputRegistry.getExcelDataOutputGenerator(ExcelDataObjectTypes.CHART);
if (outputGenerator != null) {
try {
File outfile = outputGenerator.getChartImage(chart, outputFormatProperties);
// In case no output is generated by the OutputGenerator, generate the output using Aspose
if (outfile != null) {
return outfile;
}
} catch (Exception e) {
logger.error(“Error thrown by output generator”, e);
}
}
if(isOutputPdf(outputFormatProperties)){
return chartToPdf(workbook, chart, outputFormatProperties);
}
else{
String fileExtension = getImageFileExtension(outputFormatProperties);
File outputFile = OfficeResourceUtility.createTemporaryFile("." + fileExtension);
chart.toImage(outputFile.getAbsolutePath(),
getImageOrPrintOptions(ExcelDataObjectTypes.CHART, outputFormatProperties, true, false));
return outputFile;
}
}

private File chartToPdf(Workbook workbook, com.aspose.cells.Chart chart, NameValue[] outputFormatProperties) throws IOException,Exception {
WorksheetCollection ws = workbook.getWorksheets();
ChartShape cshape = chart.getChartObject();
int sheetIndex = ws.add();
com.aspose.cells.Worksheet worksheet = ws.get(sheetIndex);
//Setting the name of the newly added worksheet
String transientSheetName = “TransientWorksheet”;
worksheet.setName(transientSheetName);
//Copy the Chart to Second Worksheet
worksheet.getShapes().addCopy(cshape, 0, 0, 0, 0);
//Get the new chart and set its height and width accordingly
com.aspose.cells.Chart chart1 = worksheet.getCharts().get(0);
chart1.getChartArea().setHeight(cshape.getHeight());
chart1.getChartObject().setWidth(cshape.getWidth());
//Make remaining worksheets invisible so that they are not part of the output pdf
for (int i = 0; i < ws.getCount(); i++) {
String sheetName = ws.get(i).getName();
if(!transientSheetName.equalsIgnoreCase(sheetName)){
ws.get(i).setVisible(false);
}
}
PdfSaveOptions pdfSaveOptions = getPdfSaveOptions();
worksheet.getPageSetup().setTopMargin(0);
worksheet.getPageSetup().setBottomMargin(0);
worksheet.getPageSetup().setRightMargin(0);
worksheet.getPageSetup().setLeftMargin(0);
// Save the workbook
File outputPdfFile = OfficeResourceUtility.createTemporaryFile(".pdf");
workbook.save(outputPdfFile.getAbsolutePath(), pdfSaveOptions);

return outputPdfFile;
}

Could you please validate this behaviour. Ideally the outputs shoud be same as chart.
I have tested with chart 1 on sheet: Bar and line

Thanks,
Jaspreet
2

Hi Jaspreet,


Thank you for contacting Aspose support.

The behavior mentioned in your post is actually correct. While rendering the chart to image, the API only renders the chart contents regardless of the underlying cells whereas during the conversion to PDF the API renders the cell area specified as PrintArea for the particular worksheet. As per your provided PDF, the blank area at the right of the chart is actually the cell area M3:M13.

Please check the attached sample spreadsheet in which I have manually reduced the width of the column adjacent to the right of the chart. Also attached is the resultant PDF generated with the following piece of code. You will notice there is no blank area on the right side of the chart in the PDF.

Java

Workbook workbook = new Workbook(“D:/book1.xlsx”);
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.get(0);
worksheet.setVisible(true);
worksheet.getPageSetup().setTopMargin(0);
worksheet.getPageSetup().setBottomMargin(0);
worksheet.getPageSetup().setRightMargin(0);
worksheet.getPageSetup().setLeftMargin(0);
worksheet.getPageSetup().setPrintArea(“o1:u12”);

PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
pdfSaveOptions.setOnePagePerSheet(true);
workbook.save(“D:/output.pdf”, pdfSaveOptions);
3

Hi,

I used worksheet.getCells().setStandardWidth(0.1); API to set column width minimal.
But for information, will I also have to do similar thing for height as well to avoid top & bottom white spaces in chart pdf Or is it that the row height does not matter while creating chart PDF?
worksheet.getCells().setStandardHeight(0.1);

Thanks,
Jaspreet

4

Hi Jaspreet,


The Cells.setStandardHeight method will set the height for all the cells in a given worksheet. I believe you do not intend to do that. In order to minimize the spare area around the desired area, please use the code snippet provided in my previous response. If you are facing any issue or have any concerns then please provide us details of the problem.
5

Hi,

Here, the scenario is that we copy the chart from source excel to new excel so that we can create PDF of desired chart.
Thus, simply changing columns widths in source excel file will not help.
Since the column widths reflect in the PDF output, now I am trying to set the first cell’s height & width equal to the chart’s plot area height & width so that chart fits perfectly into the cell A1. Could you suggest an optimum way to do this…



WorksheetCollection ws = workbook.getWorksheets();
ChartShape cshape = chart.getChartObject();
int sheetIndex = ws.add();
com.aspose.cells.Worksheet worksheet = ws.get(sheetIndex);
// How do I ensure same units conversion while height/width get & set here?
chart.calculate();
int width = chart.getPlotArea().getWidth();
int height = chart.getPlotArea().getInnerHeight();
worksheet.getCells().setColumnWidth(0,width);
worksheet.getCells().setRowHeight(0,height);
//Setting the name of the newly added worksheet
String transientSheetName = “TransientWorksheet”;
worksheet.setName(transientSheetName);
//Copy the Chart to Second Worksheet
worksheet.getShapes().addCopy(cshape, 0, 0, 0, 0);
//Get the new chart and set its height and width accordingly
com.aspose.cells.Chart chart1 = worksheet.getCharts().get(0);
chart1.getChartArea().setHeight(cshape.getHeight());
chart1.getChartObject().setWidth(cshape.getWidth());
//Make remaining worksheets invisible so that they are not part of the output pdf
for (int i = 0; i < ws.getCount(); i++) {
String sheetName = ws.get(i).getName();
if(!transientSheetName.equalsIgnoreCase(sheetName)){
ws.get(i).setVisible(false);
}
}
PdfSaveOptions pdfSaveOptions = getPdfSaveOptions();
worksheet.getPageSetup().setTopMargin(pdfOutputTopMargin);
worksheet.getPageSetup().setBottomMargin(pdfOutputBottomMargin);
worksheet.getPageSetup().setRightMargin(pdfOutputRightMargin);
worksheet.getPageSetup().setLeftMargin(pdfOutputLeftMargin);
// Save the workbook
File outputPdfFile = OfficeResourceUtility.createTemporaryFile(".pdf");
workbook.save(outputPdfFile.getAbsolutePath(), pdfSaveOptions);
workbook.save(“c:\temp\test.xlsx”, SaveFormat.XLSX);
return outputPdfFile;


Thanks,
Jaspreet
6

Hi Jaspreet,


Please check the attached spreadsheet for 2nd worksheet and confirm, is that what you require in spreadsheet format? In case, I haven’t understood you correctly then please share a spreadsheet with your desired results (you may create it manually with Excel) and share it here. We will help you in replicating the same results with Aspose.Cells APIs.
7

Hi,

I require the chart (with borders) to fit perfectly in the first cell so that when we generate PDF output, we don’t get empty spaces around the chart.

See Sheet2 in the attached Excel.
Also attached the incorrect output.
Observer the white spaces on right & below the chart.

Thanks,
Jaspreet
8

Hi Jaspreet,


Thank you for the elaboration.

Please try the following approach as I haven’t found any flaws with it. I have commented the source code so you may change it according to your needs, however, the main concept is to convert the chart to image (EMF for better quality) and insert the image to a new worksheet before rendering it to PDF format.

Java

Workbook book = new Workbook(input);
//Accessing the Chart object to apply the borders
Chart chart = book.getWorksheets().get(0).getCharts().get(0);
chart.getChartArea().getBorder().setVisible(true);
chart.getChartArea().getBorder().setColor(Color.getRed());
chart.getChartArea().getBorder().setWeightPt(2);
chart.calculate();

//Converting the chart to EMF image
ImageOrPrintOptions options = new ImageOrPrintOptions();
options.setImageFormat(ImageFormat.getEmf());
chart.toImage(“D:/output.emf”, options);

//Adding a new worksheet
Worksheet sheet = book.getWorksheets().add(“final”);

//Adding the chart image from previous step to the A1 cell
int index = sheet.getPictures().add(0, 0, “D:/output.emf”);
Picture picture = sheet.getPictures().get(index);

//Retrieving the picture size
int height = picture.getHeight();
int width = picture.getWidth();
System.out.println(height+":"+width);

//Expanding the A1 cells according to the picture size
sheet.getCells().setColumnWidthPixel(0, width);
sheet.getCells().setRowHeightPixel(0, height);

//Setting margins for final worksheet
sheet.getPageSetup().setTopMargin(0);
sheet.getPageSetup().setBottomMargin(0);
sheet.getPageSetup().setRightMargin(0);
sheet.getPageSetup().setLeftMargin(0);
//Setting print area for final worksheet
sheet.getPageSetup().setPrintArea(“A1:A1”);

//Hiding all worksheets except final
for(int i=0; i<book.getWorksheets().getCount(); i++)
{
if(book.getWorksheets().get(i).getName() != “final”)
{
book.getWorksheets().get(i).setVisible(false);
}
}

//Creating an object of PdfSaveOptions
PdfSaveOptions exportOptions = new PdfSaveOptions();
//Setting image format to EMF
exportOptions.setImageType(ImageFormat.getEmf());
//Setting OnePagePerSheet option to true
exportOptions.setOnePagePerSheet(true);

//Export to PDF format
book.save(“D:/output.pdf”, exportOptions);