Regression: Excel bookmarks not generated when converted to PDF

Aspose.Cells Product Family
1

Hi Team,

PDF bookmarks were generated when using 7.5.0.3 along with below code. But with Latest Aspose.Cells 8.0.0 this bookmarks are not generated.

I believe this is a regression.

Code:

Workbook doc = new Workbook(excel_file);
com.aspose.cells.PdfSaveOptions saveOptions = new com.aspose.cells.PdfSaveOptions();

PdfBookmarkEntry bookmarkEntry = new PdfBookmarkEntry();

Cell firstCell = workbook.getWorksheets().get(0).getCells().getFirstCell();
bookmarkEntry.setText(workbook.getFileName().substring(workbook.getFileName().lastIndexOf("\\") + 1));
bookmarkEntry.setDestination(firstCell);
bookmarkEntry.setOpen(false);

Cell cell = null;
PdfBookmarkEntry subBookmarks = null;
ArrayList list = new ArrayList();
for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
cell = workbook.getWorksheets().get(i).getCells().getFirstCell();

subBookmarks = new PdfBookmarkEntry();
subBookmarks.setText(workbook.getWorksheets().get(i).getName());
subBookmarks.setDestination(cell);

list.add(subBookmarks);
}
bookmarkEntry.setSubEntry(list);
pso.setPdfBookmark(bookmarkEntry); or pso.setBookmark(bookmarkEntry).

Please let me know if I am missing something or there needs any code changes for latest Aspose.Cells 8.0.0.

Attached PDF output from 7.5.0.3

Thanks,
Satyendra

2

Hi Satyendra,

Thanks for your posting and using Aspose.Cells.

It seems, it is a regression issue. However, we need to investigate it further and see if any code change is needed to make it work properly.

We have logged this issue in our database for investigation. We will look into it and resolve this issue. Once, the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40806.

3

So do you mean that CELLSJAVA-40806 is raised as a Regression?

Thanks,
Satyendra

4

Hi Satyendra,

Thanks for your posting and using Aspose.Cells.

Yes, it is currently logged as regression. The older version is working fine and newer version does not seem to work properly.

Hopefully, this issue will be fixed soon. We will let you know, once, there is some update for you.

5

Thank you for quick responses.
This is an important feature for our customers and needs to be addressed urgently.

Can you please provide an ETA for this?

Thanks,
Satyendra

6

Hi,


As we just found the issue (1-2 hours before), the issue is currently pending for analysis. Our concerned developer will evaluate your issue soon. Once we do the evaluation, we will be in better position to update on it or provide an eta (if possible). Moreover, if an issue is not so complex, it will take 3-5 days to fix it, but if it is a complex issue, it will take a couple of weeks or more. Hopefully your issue would be fixed soon.

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

Thank you.

7

Hi,


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

We have fixed your issue now.

Let us know your feedback.

Thank you.

8

Hi Amjad,

I tested the scenario with this latest release. But again I dont see the generated bookmarks in PDF.
With the same code + Aspose. 7.5.0 I see the generated bookmarks.

Am I missing something here?
Have you tested this on your side?

Thanks,
Satyendra

9

Hi Satyendra,

Thanks for your posting and using Aspose.Cells.

Yes, we have tested the latest version (v8.0.1.4) and it generates correct pdf with bookmark.

I have attached the test.xlsx file and the output pdf generated by the following code for your reference. Please also see the screenshot attached, as you can see bookmarks have been generated fine inside it.

Java


String filePath =“F:\Shak-Data-RW\Downloads\test.xlsx”;


Workbook workbook = new Workbook(filePath);


PdfBookmarkEntry bookmarkEntry = new PdfBookmarkEntry();


Cell firstCell = workbook.getWorksheets().get(0).getCells().getFirstCell();

bookmarkEntry.setText(workbook.getFileName().substring(workbook.getFileName().lastIndexOf("\") + 1));

bookmarkEntry.setDestination(firstCell);

bookmarkEntry.setOpen(false);


Cell cell = null;

PdfBookmarkEntry subBookmarks = null;

ArrayList list = new ArrayList();

for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {

cell = workbook.getWorksheets().get(i).getCells().getFirstCell();


subBookmarks = new PdfBookmarkEntry();

subBookmarks.setText(workbook.getWorksheets().get(i).getName());

subBookmarks.setDestination(cell);


list.add(subBookmarks);

}

bookmarkEntry.setSubEntry(list);

// pso.setPdfBookmark(bookmarkEntry); //or


com.aspose.cells.PdfSaveOptions pso = new com.aspose.cells.PdfSaveOptions();

pso.setBookmark(bookmarkEntry);


workbook.save(filePath + “.out.pdf”, pso);



10

Hi,


I used the code segment from the article in the Wiki Docs, it also works fine. I used the following sample code. Also find attached the output PDF file for your reference:
e.g
Sample code:

//Instantiate a new workbook.
Workbook workbook = new Workbook();
//Get the worksheets in the workbook.
WorksheetCollection worksheets = workbook.getWorksheets();
//Add a sheet to the workbook.
worksheets.add(“1”);
//Add 2nd sheet to the workbook.
worksheets.add(“2”);
//Add the third sheet.
worksheets.add(“3”);
//Get cells in different worksheets.
Cell cellInPage1 = worksheets.get(0).getCells().get(“A1”);
Cell cellInPage2 = worksheets.get(1).getCells().get(“A1”);
Cell cellInPage3 = worksheets.get(2).getCells().get(“A1”);

//Add a value to the A1 cell in the first sheet.
cellInPage1.setValue(“a”);
//Add a value to the A1 cell in the second sheet.
cellInPage2.setValue(“b”);
//Add a value to the A1 cell in the third sheet.
cellInPage3.setValue(“c”);

//Create the PdfBookmark entry object.
PdfBookmarkEntry pbeRoot = new PdfBookmarkEntry();
//Set its text.
pbeRoot.setText(“root”);
//Set its destination source page.
pbeRoot.setDestination(cellInPage1);
//Set the bookmark collapsed.
pbeRoot.setOpen(false);
//Add a new PdfBookmark entry object.
PdfBookmarkEntry subPbe1 = new PdfBookmarkEntry();
//Set its text.
subPbe1.setText(“1”);
//Set its destination source page.
subPbe1.setDestination(cellInPage2);

//Add another PdfBookmark entry object.
PdfBookmarkEntry subPbe2 = new PdfBookmarkEntry();
//Set its text.
subPbe2.setText(“2”);
//Set its destination source page.
subPbe2.setDestination(cellInPage3);
//Create an array list.
ArrayList subEntryList = new ArrayList();
//Add the entry objects to it.
subEntryList.add(subPbe1);
subEntryList.add(subPbe2);
pbeRoot.setSubEntry(subEntryList);

//Set the pdf bookmarks, you need to specify the root object here.
workbook.getSaveOptions().setPdfBookmark(pbeRoot);

//Save the pdf file.
workbook.save(“f:\files\PdfBookmarks_test1.pdf”, FileFormatType.PDF);

Thank you.
11

Hi Amjad,

Yes the bookmarks are created with this new release. But the bookmarks fail to get created when one of the sheet in the workbook is dynamically setVisible(false).

Code:
Code:

Workbook doc = new Workbook(excel_file);
com.aspose.cells.PdfSaveOptions saveOptions = new com.aspose.cells.PdfSaveOptions();

PdfBookmarkEntry bookmarkEntry = new PdfBookmarkEntry();

Cell firstCell = workbook.getWorksheets().get(0).getCells().getFirstCell();
bookmarkEntry.setText(workbook.getFileName().substring(workbook.getFileName().lastIndexOf("\\") + 1));
bookmarkEntry.setDestination(firstCell);
bookmarkEntry.setOpen(false);

Cell cell = null;
PdfBookmarkEntry subBookmarks = null;
ArrayList list = new ArrayList();
for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
cell = workbook.getWorksheets().get(i).getCells().getFirstCell();

subBookmarks = new PdfBookmarkEntry();
subBookmarks.setText(workbook.getWorksheets().get(i).getName());
subBookmarks.setDestination(cell);

list.add(subBookmarks);

workbook.getWorksheets().get(2).setVisible(false);
}
bookmarkEntry.setSubEntry(list);
pso.setPdfBookmark(bookmarkEntry); or pso.setBookmark(bookmarkEntry);

With above highlighted code, the bookmarks are not created:

Expected Behaviour:
The bookmarks needs to be created for the active printed sheets, even though some sheets are set to invisible dynamically.

Please let me know if you need more details.

Thanks,
Satyendra

12

Hi,


I tested your sample code with a template file and observed your findings. Well, you mean to say Bookmarks should be added for the hidden sheets as well. But, could you tell if we add these bookmarks for the hidden sheet (e.g Sheet3 in your example), where it would go when you click on the Bookmark in Adobe acrobat, it would surely go nowhere.

Thanks,
13

Hi,


Furthermore, we think the behavior is ok for the hidden sheets. The reason is simple, we do not render hidden sheets in the output PDF, so bookmarks for the hidden sheets cannot be referred to any place.

Thanks for your understanding!
14

Hi Amjad,

You got me wrong.
Let me clarify:
Lets say we have 5 sheets in a workbook.
And sheet 1 3 5 are selected(you can select sheets using ctrl + select) for printing. ie. user wants only sheet 1 3 & 5 to be printed to PDF.
Now in the code, I am making sheet 2 & sheet 4 invisible which should not be printed to PDF.

When I do this, none of the bookmarks are created not even the bookmarks for sheet 1 3 & 5 are not getting created at all.
Bookmarks for sheet 1 3 &5 should be created, & bookmark for sheet 2 & 4 should NOT be created. Thats our requirement.
Am i clear in explaining the issue? Please feel free to let me know if I am not clear yet.

Ex: PFA - Bug_review.xls. If you open this, then you can see sheet 2 & 3 are selected. Sheet 1 is not selected. So We want sheet 1 to be ignored while printing to PDF. That is why I make it invisible. But none of the bookmarks are created.
Thanks,
Satyendra

15

Hi Satyendra,


Thanks for providing further details and sample file.

I have evaluated your issue further. As there are some worksheets are grouped or selected. Also you are making the first worksheet invisible, so you are getting his issue. As a workaround, you may unselect the selected worksheets and move your first worksheet to last or other location (if you want to hide this).

I have written/updated the sample code (see the lines of code in bold) that works fine with your attached file for your needs. I have also attached the output file for your reference.
e.g
Sample code:

Workbook workbook = new Workbook(“Bug_Review.xls”);

//Unselect all the sheets.
for (int i = 0; i < workbook.getWorksheets().getCount(); i++)
{
if (!workbook.getWorksheets().get(i).isSelected())
workbook.getWorksheets().get(i).setSelected(false);
}


//Set the first worksheet invisible.
workbook.getWorksheets().get(0).moveTo(2);
workbook.getWorksheets().get(2).setVisible(false);

com.aspose.cells.PdfSaveOptions saveOptions = new com.aspose.cells.PdfSaveOptions();
PdfBookmarkEntry bookmarkEntry = new PdfBookmarkEntry();

Cell firstCell = workbook.getWorksheets().get(0).getCells().getFirstCell();
bookmarkEntry.setText(workbook.getFileName().substring(workbook.getFileName().lastIndexOf("\") + 1));
bookmarkEntry.setDestination(firstCell);
bookmarkEntry.setOpen(false);

Cell cell = null;
PdfBookmarkEntry subBookmarks = null;
ArrayList list = new ArrayList();
for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
cell = workbook.getWorksheets().get(i).getCells().getFirstCell();

subBookmarks = new PdfBookmarkEntry();
subBookmarks.setText(workbook.getWorksheets().get(i).getName());
subBookmarks.setDestination(cell);

list.add(subBookmarks);

//
}
bookmarkEntry.setSubEntry(list);

com.aspose.cells.PdfSaveOptions pso = new com.aspose.cells.PdfSaveOptions();

pso.setBookmark(bookmarkEntry);

// pso.setPdfBookmark(bookmarkEntry); //or pso.setBookmark(bookmarkEntry);
workbook.save(“outBugReview1.out1.pdf”, pso);

Hope, this helps a bit.

Thank you.

16

Ok, Now I get it. If the first sheet is not selected or hidden, then there is issue in creating PDF bookmarks.
But hiding and bookmarks are 2 different cases I believe. Though the workaround you provided works, I believe Aspose.cells should fix this in their code. Looks simple.


Can you please raise a bug for this?

Thanks,
Satyendra
17

Ok, I get it now. So if the 1st sheet is hidden, then there is an issue creating bookmarks as a whole.

Though the workaround you provided works fine, I believe this is an issue and needs ot be fixed in Aspose.cells code. Looks like a simple one for me. Correct me if I am wrong.

Can you please raise a bug to resolve this? Many customer may face this issue and workaround is the last resort always :)

Thanks,
Satyendra

18

Hi,


I think you are right, this issue should be fixed. The issue exists when the first worksheet is set to hidden in the workbook. I have logged it and reopened your original issue “CELLSJAVA-40806” again. Our concerned developer will look into it soon.

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

Thank you.
19

Hi Satyendra,

Thanks for using Aspose.Cells.

We have looked into this issue further. If the parent bookmark is set to first cell in sheet 1, when sheet 1 is invisible, then parent bookmark should not be shown and it’s child bookmarks will also not be shown.

20

Hi,


I get it now. So if the first sheet is the parent bookmark and if it is hidden, then no other bookmarks will be displayed. That explains and sounds logical.

Appreciate your assistance here. I will change my code accordingly.

Thanks,
Satyendra