Chart to Image/PDF conversion does not export the contained textbox

Aspose.Cells Product Family
1

Hi,

I am using Aspose cells 8.4.2.5
In context of the attached excel document, we are converting Chart 1 in Sheet1 to PNG and PDF.It has been observed that a text box defined in chart containing data ‘Here is a text box’ doesn’t appear in image & pdf output of chart.

Here’s the API being used for converting chart to image :-
chart.toImage(outputFile.getAbsolutePath(),
getImageOrPrintOptions());

And code snippet for converting chart to PDF :-
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);
chart.calculate();
double chartWidthInInches = chart.getChartObject().getWidthInch();
double chartHeightInInches = chart.getChartObject().getHeightInch();
boolean defaultApproach = true;
if (chartWidthInInches > maxExcelColWidthInInches || chartHeightInInches > maxExcelRowHeightInInches) {
// The approach being followed here is to iterate as many columns as colwidth in pixels & set each column width equal = 1 pixel.
// Similarly for chart height, iterate through as many rows as row height & set each row height = 1 pixel.
// This is being done to fit in large charts that have height & width beyond maximum row/col dimensions & white spaces around
// the chart.

double chartWidthInPixels = chart.getChartObject().getWidth();
double chartHeightInPixels = chart.getChartObject().getHeight();
// Deliberately including one more column because Aspose considers the last column adjacent to chart which leads to white space
// on right of chart
for (int i = 0; i <= chartWidthInPixels; i++) {
worksheet.getCells().setColumnWidthPixel(i, 1);
}

// Deliberately including one more row because Aspose considers the last row adjacent to chart which leads to white space
// below the chart.
for (int i = 0; i <= chartHeightInPixels; i++) {
worksheet.getCells().setRowHeightPixel(i, 1);
}
defaultApproach = false;
} else {
worksheet.getCells().setColumnWidthInch(0, chartWidthInInches);
worksheet.getCells().setRowHeightInch(0, chartHeightInInches);
}

//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.getChartObject().setHeightInch(chartHeightInInches);
chart1.getChartObject().setWidthInch(chartWidthInInches);
//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);
//Setting Print area to first cell only as the chart is contained in the first cell (A1:A1)
if (defaultApproach) {
worksheet.getPageSetup().setPrintArea(“A1:A1”);
}
// Save the workbook
File outputPdfFile = OfficeResourceUtility.createTemporaryFile(".pdf");
workbook.save(outputPdfFile.getAbsolutePath(), pdfSaveOptions);
workbook.save(“c:\temp\test.xlsx”, SaveFormat.XLSX);
return outputPdfFile;
}


Also, it has been observed that if this chart is saved as PDF using MS Excel then the text box appears in output PDF.

Can you please verify this issue.

Attached the problematic excel document and outputs from Aspose & MS Excel for reference.

Thanks,
Jaspreet

2

Hi,


Thanks for providing us template files, sample code and details.

Please try our latest version/ fix: Aspose.Cells for Java v8.4.2.8

I have tested your scenario/ case using your template file and sample code below with v8.4.2.8, it works fine and as expected:
e.g
Sample code:

Workbook workbook = new Workbook(“Book2+text+box+missing+from+chart+pdf.xlsx”);

workbook.save(“out1.pdf”);


Let us know if you still find the issue.

Thank you.


3

Hi,

I tried with Aspose cells v8.4.2.8.
On converting excel doc to pdf using above code, I still get the pdf without text box contents.

Though, the whole document pdf is a valid use case but to be precise our scenario is to export the specific chart as image & pdf. The exported chart outputs must have text box contents.

Can you please verify my findings & suggest a solution.

For reference I have attached the pdf of whole excel document & pdf of Chart 1 of Sheet1.


Thanks,
Jaspreet


4

Hi,


Thanks for providing further details and sample files.

I am afraid, I still could not find your issue. I have to update your original code segment as I am not sure about some objects/ variables used in your original code, so I have to modify or update/ comment certain lines of code a bit accordingly. Here is my complete updated sample code which I run with v8.4.2.8 and please find attached the output Excel file and PDF file for your reference, both files do have text box inserted in the chart’s plot area fine. Mind you, I have used your file “Book2+text+box+missing+from+chart+pdf.xlsx” as template (input) file in the code segment:
e.g
Sample code:

Workbook workbook = new Workbook(“Book2+text+box+missing+from+chart+pdf.xlsx”);

WorksheetCollection ws = workbook.getWorksheets();
ChartShape cshape = ws.get(0).getCharts().get(0).getChartObject();
Chart chart = ws.get(0).getCharts().get(0);
int sheetIndex = ws.add();
com.aspose.cells.Worksheet worksheet = ws.get(sheetIndex);
chart.calculate();
double chartWidthInInches = chart.getChartObject().getWidthInch();
double chartHeightInInches = chart.getChartObject().getHeightInch();
boolean defaultApproach = true;
/
//if (chartWidthInInches > maxExcelColWidthInInches || chartHeightInInches > maxExcelRowHeightInInches) {
// The approach being followed here is to iterate as many columns as colwidth in pixels & set each column width equal = 1 pixel.
// Similarly for chart height, iterate through as many rows as row height & set each row height = 1 pixel.
// This is being done to fit in large charts that have height & width beyond maximum row/col dimensions & white spaces around
// the chart.

double chartWidthInPixels = chart.getChartObject().getWidth();
double chartHeightInPixels = chart.getChartObject().getHeight();
// Deliberately including one more column because Aspose considers the last column adjacent to chart which leads to white space
// on right of chart
for (int i = 0; i <= chartWidthInPixels; i++) {
worksheet.getCells().setColumnWidthPixel(i, 1);
}

// Deliberately including one more row because Aspose considers the last row adjacent to chart which leads to white space
// below the chart.
for (int i = 0; i <= chartHeightInPixels; i++) {
worksheet.getCells().setRowHeightPixel(i, 1);
}
defaultApproach = false;
} else {
/
worksheet.getCells().setColumnWidthInch(0, chartWidthInInches);
worksheet.getCells().setRowHeightInch(0, chartHeightInInches);
// }

//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.getChartObject().setHeightInch(chartHeightInInches);
chart1.getChartObject().setWidthInch(chartWidthInInches);
//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();
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();

worksheet.getPageSetup().setTopMargin(0);
worksheet.getPageSetup().setBottomMargin(0);
worksheet.getPageSetup().setRightMargin(0);
worksheet.getPageSetup().setLeftMargin(0);
//Setting Print area to first cell only as the chart is contained in the first cell (A1:A1)
if (defaultApproach) {
worksheet.getPageSetup().setPrintArea(“A1:A1”);
}
// Save the workbook
//File outputPdfFile = OfficeResourceUtility.createTemporaryFile(".pdf");
workbook.save(“outpdf11.pdf”, pdfSaveOptions);
workbook.save(“outtest11.xlsx”, SaveFormat.XLSX);

If you still find any issue, kindly provide complete JAVA program (runnable) as I have pasted the sample code above, so we could run and try to trace the issue on our end. Also attach your input Excel file if you use different file.

Thank you.


5

Hi,

Thanks for your feedback.
I have observed that the issue is reproducible with the above code if we use licensed v8.4.2.8 of Aspose Cells and not with evaluation version. Since we are using licensed version, it was reproducible with in our setup.

Attached here is the output generated using licensed version of Aspose Cells.

Thanks,
Jaspreet

6

Hi,


Thanks for providing further details and sample PDF file.

I observed the issue as you mentioned. I found that in the output PDF file, the TextBox shape is not contained in the chart any more. I can only find the issue while using/ setting a valid license, it works fine in evaluation (without using/ setting a valid license) mode though. Here is my complete updated sample code which I run with v8.4.2.8 with a valid license. The output Excel file does contain the Text Box in the chart but the PDF file does not have text box inserted in the chart’s plot area. I have used your file “Book2+text+box+missing+from+chart+pdf.xlsx” as template (input) file in the code segment:
e.g
Sample code:

License lic = new License();
lic.setLicense(“E:\Licenses\Aspose.Cells.lic”);


Workbook workbook = new Workbook(“Book2+text+box+missing+from+chart+pdf.xlsx”);

WorksheetCollection ws = workbook.getWorksheets();
ChartShape cshape = ws.get(0).getCharts().get(0).getChartObject();
Chart chart = ws.get(0).getCharts().get(0);
int sheetIndex = ws.add();

com.aspose.cells.Worksheet worksheet = ws.get(sheetIndex);

chart.calculate();

double chartWidthInInches = chart.getChartObject().getWidthInch();
double chartHeightInInches = chart.getChartObject().getHeightInch();

boolean defaultApproach = true;
worksheet.getCells().setColumnWidthInch(0, chartWidthInInches);
worksheet.getCells().setRowHeightInch(0, chartHeightInInches);


//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.getChartObject().setHeightInch(chartHeightInInches);
chart1.getChartObject().setWidthInch(chartWidthInInches);


//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 = new PdfSaveOptions();

worksheet.getPageSetup().setTopMargin(0);
worksheet.getPageSetup().setBottomMargin(0);
worksheet.getPageSetup().setRightMargin(0);
worksheet.getPageSetup().setLeftMargin(0);
//Setting Print area to first cell only as the chart is contained in the first cell (A1:A1)
if (defaultApproach) {
worksheet.getPageSetup().setPrintArea(“A1:A1”);
}
// Save the workbook
workbook.save(“outpdf1.pdf”, pdfSaveOptions); // it does not contain TextBox in the rendered chart.
workbook.save(“outtest1.xlsx”, SaveFormat.XLSX);

I have logged a ticket with an id “CELLSJAVA-41380” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
7

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells
for Java v8.5.0.2 and let us know your feedback.

8

The issues you have found earlier (filed as CELLSJAVA-41380) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.