Create a PdfBookmarkEntry for a Chart Sheet

Hello,

Is it possible to create a PdfBookmarkEntry for a Chart Sheet (i.e. when Worksheet.getType() == SheetType.CHART ) for PDF output?

The setDestination API (in PdfBookmarkEntry) only allows a Cell instance to be specified. However, a Chart Sheet does not have any Cells.

We’ve also explored the setDestinationName API, but it doesn’t seem to do much (under Aspose Cells for Java. 17.12). The documentation doesn’t clearly explain what the API expects for the String parameter.

Is there any way to create a PdfBookmarkEntry for a Chart Sheet?

Thanks.

@oraspose

Thanks for using Aspose APIs.

We were able to observe this issue and found that this feature is not supported. We have logged a New Feature request in our database for product team investigation. We will look into it and implement this feature if possible. Once, there is some news for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-42498 - Create a PdfBookmarkEntry for a Chart Sheet

Java

//Note: The following code will break up if you will add worksheet of type SheetType.CHART

//--------------------------------------------
//--------------------------------------------

Workbook wb = new Workbook();
wb.getWorksheets().add(); //SheetType.CHART will cause exception
wb.getWorksheets().add();
wb.getWorksheets().add();

Worksheet ws1 = wb.getWorksheets().get(0);
Worksheet ws2 = wb.getWorksheets().get(1);
Worksheet ws3 = wb.getWorksheets().get(2);
Worksheet ws4 = wb.getWorksheets().get(3);

Cell cell1 = ws1.getCells().get("G5");
cell1.putValue("G5");

Cell cell2 = ws2.getCells().get("B3");
cell2.putValue("B3");

Cell cell3 = ws3.getCells().get("C7");
cell3.putValue("C7");

Cell cell4 = ws4.getCells().get("O9");
cell4.putValue("O9");

PdfBookmarkEntry en1 = new PdfBookmarkEntry();
en1.setDestination(cell1);
en1.setText("Text1");

PdfBookmarkEntry en2 = new PdfBookmarkEntry();
en2.setDestination(cell2);
en2.setText("Text2");

PdfBookmarkEntry en3 = new PdfBookmarkEntry();
en3.setDestination(cell3);
en3.setText("Text3");

PdfBookmarkEntry en4 = new PdfBookmarkEntry();
en4.setDestination(cell4);
en4.setText("Text4");

ArrayList lst = new ArrayList();
en1.setSubEntry(lst);
lst.add(en2);
lst.add(en3);
lst.add(en4);

PdfSaveOptions opts = new PdfSaveOptions();
opts.setBookmark(en1);

wb.save(dirPath + "out.pdf", opts);

C#

//Note: The following code will break up if you will add worksheet of type SheetType.Chart

//--------------------------------------------
//--------------------------------------------

Workbook wb = new Workbook();
wb.Worksheets.Add(); //SheetType.Chart will cause exception
wb.Worksheets.Add();
wb.Worksheets.Add();

Worksheet ws1 = wb.Worksheets[0];
Worksheet ws2 = wb.Worksheets[1];
Worksheet ws3 = wb.Worksheets[2];
Worksheet ws4 = wb.Worksheets[3];

Cell cell1 = ws1.Cells["G5"];
cell1.PutValue("G5");

Cell cell2 = ws2.Cells["B3"];
cell2.PutValue("B3");

Cell cell3 = ws3.Cells["C7"];
cell3.PutValue("C7");

Cell cell4 = ws4.Cells["O9"];
cell4.PutValue("O9");

PdfBookmarkEntry en1 = new PdfBookmarkEntry();
en1.Destination = cell1;
en1.Text = "Text1";

PdfBookmarkEntry en2 = new PdfBookmarkEntry();
en2.Destination = cell2;
en2.Text = "Text2";

PdfBookmarkEntry en3 = new PdfBookmarkEntry();
en3.Destination = cell3;
en3.Text = "Text3";

PdfBookmarkEntry en4 = new PdfBookmarkEntry();
en4.Destination = cell4;
en4.Text = "Text4";

ArrayList lst = new ArrayList();
en1.SubEntry = lst;
lst.Add(en2);
lst.Add(en3);
lst.Add(en4);

PdfSaveOptions opts = new PdfSaveOptions();
opts.Bookmark = en1;

wb.Save("out.pdf", opts);

Hi Shakeel,

Thank you for your response. I just want to be clear about this issue.

In the sample code you provided, the exception happens (when Sheet1 is a Chart), because “ws1” tries to get cell “G5”. However, if instead we change the code to:
  Cell cell1 = ws1.getCells().get(“A1”);
there is no exception since “A1” seems to be a valid “cell” in Chart sheets.

The real problem is that - even though we can create a PdfBookmarkEntry from cell A1 in the Chart sheet, the PDF is opened, the bookmark for the Chart sheet is ignored.

For example, use the uploaded “ChartSheet.xlsx” Workbook and the following code:

final String fileName = [path] + "ChartSheet.xlsx";
final String[] sheetNames = { "Sheet1" , "Sheet2" };

Workbook wb = new Workbook(fileName);

for (String sheetName : sheetNames) {

  Worksheet sheet = wb.getWorksheets().get(sheetName);

  for (Iterator i = wb.getWorksheets().iterator(); i.hasNext(); ) {
  Worksheet ws = i.next();
      boolean visible = sheetName.equals(ws.getName());
      ws.setVisible(visible, true);
    }

    PdfSaveOptions saveOpts = new PdfSaveOptions();
    saveOpts.setImageType(ImageFormat.getPng());
    saveOpts.setCheckFontCompatibility(true);
    saveOpts.setCreateDirectory(false);
    PdfBookmarkEntry bookmark = new PdfBookmarkEntry();
    bookmark.setText(sheet.getName());

    // NOTE: The following will NOT generate an exception for 
    //       the Chart sheet (i.e. Sheet2)
    Cell cell = sheet.getCells().get(sheet.getFirstVisibleRow(), 
                                     sheet.getFirstVisibleColumn());
    bookmark.setDestination(cell);

    saveOpts.setBookmark(bookmark);

    String pdfFile = fileName.replace(".xlsx", "_" + sheetName + ".pdf");
    wb.save(pdfFile, saveOpts);
  }

Attachment: ChartSheet.zip (12.7 KB)

Thanks

@oraspose,

Thanks for sharing your findings.

I confirmed it using your sample code and template file. The bottom line is Aspose.Cells does not support to create Bookmarks for the chart sheet, it works fine for normal sheet type. So, no matter if it does not give exception, the Bookmarks created for the chart sheet would be lost. Moreover, we did log your issue as new feature request (CELLSJAVA-42498) into our database. I also logged your findings against your existing issue.

Once we support the feature or we have some other update on it, we will let you know here.

@oraspose

Thanks for using Aspose APIs.

This is to inform you that we have fixed your issue CELLSJAVA-42498 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

@oraspose

Thanks for using Aspose APIs.

Please download and try the following fix and let us know your feedback.

FYI:
You can now create a PdfBookmarkEntry from cell A1 in the Chart Sheet.

The issues you have found earlier (filed as CELLSJAVA-42498) have been fixed in this Aspose.Cells for Java 18.1 update.

Please also check the following document/blog for your reference: