Copy VBA Project having User Forms in Excel using Java

Aspose.Cells for Java

We are pleased to announce the release of Aspose.Cells for Java 17.9. This release includes some of valuable features and enhancements along with critical bug fixes that further improve the overall stability of the APIs. We also recommend our clients to use the powerful Aspose for Java APIs directly in their Maven Projects with simple configurations. Please check the release notes in order to get an idea about what is new and what has been enhanced or fixed with this revision of Aspose.Cells for Java. Here is a look at the major features in this release.

Copy VBA Project having User Form from Template to Target Workbook

Aspose.Cells allows you to copy VBA project from one Excel file into another Excel file. VBA project consists of various types of modules i.e. Document, Procedural, Designer etc. All modules can be copied with simple code but for Designer module, there is some extra data called Designer Storage needs to be accessed or copied. The following two methods deal with Designer Storage.

  • VbaModuleCollection.getDesignerStorage()
  • VbaModuleCollection.addDesignerStorage()

Please see the following article for more detail and sample code.

Send Shape Front or Back inside the Worksheet

When there are multiple shapes present in the same location then how will they be visible is decided by their z-order positions. Aspose.Cells provides Shape.toFrontOrBack() method which changes the z-order position of the shape. If you want to send shape to back you will use negative number like -1, -2, -3 etc. and if you want to send shape to front, you will use positive number like 1, 2, 3 etc.

This screenshot shows the effect of the code in the above article on the source workbook. As you can see, the z-order positions of shapes have been changed as per sample code.

GridWeb displays Comment as Tooltip like Microsoft Excel

GridWeb provides the feature to add, remove or get comments inside the worksheet. However, now it also displays comments as Tooltip like Microsoft Excel displays as shown in this screenshot.

Sort Data in Column with Custom Sort List

You can sort data in the column using custom list. This can be done using DataSorter.addKey(int key, SortOrder order, String customList) method. However, this method works only if the items in custom list do not have commas inside them. If they have commas like “USA,US”, “China,CN” etc., then you must use DataSorter.addKey(int key, SortOrder order, String[] customList) method. Here, the last parameter is not String but Array of Strings.

//Load the source Excel file
Workbook wb = new Workbook(dirPath + "sampleSortData_CustomSortList.xlsx");
  
//Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);
  
//Specify cell area - sort from A1 to A40
CellArea ca = CellArea.createCellArea("A1", "A40");
  
//Create Custom Sort list
String[] customSortList = new String[] { "USA,US", "Brazil,BR", "China,CN", "Russia,RU", "Canada,CA" };
  
//Add Key for Column A, Sort it in Ascending Order with Custom Sort List
wb.getDataSorter().addKey(0, SortOrder.ASCENDING, customSortList);
wb.getDataSorter().sort(ws.getCells(), ca);
  
//Save the output Excel file
wb.save(dirPath + "outputSortData_CustomSortList.xlsx");

Add PDF Bookmarks with Named Destinations

Named Destinations are special kinds of bookmarks or links in PDF that do not depend on PDF pages. It means, if pages are added or deleted from PDF, bookmarks may become invalid but named destinations will remain intact. To create Named Destination, please use the PdfBookmarkEntry.setDestinationName() method.

Control loading of External Resources in MS Excel Workbook while rendering to PDF

Your Excel file may contain external resources e.g. linked images or objects. When you convert your Excel file to Pdf, Aspose.Cells retrieves these external resources and renders them to Pdf. But sometimes, you do not want to load these external resources and more than that, you want to manipulate them. You can do this using PdfSaveOptions.StreamProvider which implements the IStreamProvider interface.

Aspose.Cells for Java Resources

The resources, you may need to accomplish your tasks: