Maintain formatting while changing cells alignment and encrypt or decrypt ODS files using Aspose.Cells for Java 18.7

Share on FacebookTweet about this on TwitterShare on LinkedIn

It is pleasure for us to announce the release of Aspose.Cells for Java v18.7 with lot of new utility features which are added for common users. Important fixes and other enhancements are also part of this new release. For a detailed information about this release, visit the release notes providing details about the enhancements, bugs which are fixed and handling of variety of exceptions. For an easy access and utilization, Aspose for Java APIs can be directly installed from Maven repository for which check document.

Change Cells Alignment and Keep Existing Formatting

It is quite often that we need to change the alignment of multiple cells but at the same time need to maintain the existing formatting as well. StyleFlag.Alignments property is provided by Aspose.Cells to achieve this functionality. Please note, StyleFlag object is passed as a parameter to Range.applyStyle() method which actually applies the formatting to your desired range of cells. The following sample code loads the sample Excel file, creates the range and center aligns it horizontally and vertically and keeps the existing formatting intact.

// Load sample Excel file containing cells with formatting.
Workbook wb = new Workbook("sampleChangeCellsAlignmentAndKeepExistingFormatting.xlsx");

// Access first worksheet.
Worksheet ws = wb.getWorksheets().get(0);

// Create cells range.
Range rng = ws.getCells().createRange("B2:D7");

// Create style object.
Style st = wb.createStyle();

// Set the horizontal and vertical alignment to center.
st.setHorizontalAlignment(TextAlignmentType.CENTER);
st.setVerticalAlignment(TextAlignmentType.CENTER);

// Create style flag object.
StyleFlag flag = new StyleFlag();

// Set style flag alignments true. It is most crucial statement.
// Because if it will be false, no changes will take place.
flag.setAlignments(true);

// Apply style to range of cells.
rng.applyStyle(st, flag);

// Save the workbook in XLSX format.
wb.save("outputChangeCellsAlignmentAndKeepExistingFormatting.xlsx", SaveFormat.XLSX);

Please see the following article for more detail on this topic for your reference.

Find Maximum Rows and Columns supported by XLS and XLSX formats

For your information, there are different number of rows and columns supported by different MS Excel file formats. For example, XLS supports up to 65536 rows and 256 columns while XLSX supports 1048576 rows and 16384 columns. If you want to know how many rows and columns are supported by a given format, you can use Workbook.getSettings().getMaxRow() and Workbook.getSettings().getMaxColumn() methods. Please see the following article for more detail on this topic for your reference:

Specify Author while Write Protecting Workbook

You can specify author name while write protecting your workbook using Aspose.Cells API. Please use Workbook.getSettings().getWriteProtection().setAuthor() method for this purpose. Please see the following article for more detail on this topic for your reference:

Encrypt/decrypt an ODS file

Aspose.Cells allows you to encrypt and decrypt an ODS file. Decrypted ODS file can be opened both in MS Excel and OpenOffice, however encrypted ODS file can only be opened by OpenOffice after providing the password. Excel cannot open the encrypted ODS file and may raise warning message. For encrypting an ODS file, load the file and set the WorkbookSettings.Password value to the actual password before saving it. Please see the following article for more detail on this topic for your reference:

Encrypt ODS File

// Open an ODS file
Workbook workbook = null;
workbook = new Workbook("Book1.ods");

// Password protect the file
workbook.getSettings().setPassword("1234");

// Save the ODS file
workbook.save("encryptedBook1.out.Java.ods");
Decrypt ODS File
// Open an encrypted ODS file
LoadOptions loadOptions = new LoadOptions(LoadFormat.ODS);

// Set original password
loadOptions.setPassword("1234");

// Load the encrypted ODS file with the appropriate load options
Workbook workbook = null;
workbook = new Workbook("encryptedBook1.out.Java.ods", loadOptions);
// Unprotect the workbook
workbook.unprotect("1234");

// Set the password to null
workbook.getSettings().setPassword(null);

// Save the decrypted ODS file
workbook.save("DencryptedBook1.out.Java.ods");

Other Enhancements and Fixes

There are some other enhancements included and a few exceptions handled in the new release for the users. A few of the worth mentioning features and other improvements are as follows.

  • Support multiple values when using class style
  • Correction in Smart Art Image extraction
  • Resolve hyperlinks issue when viewing the output Excel file in MS Excel Japanese version
  • Handled “Getting #NUM for a cell having IRR function”
  • HTML to XLS – CSS style ignored
  • Handled  “java.lang.NumberFormatException” while loading an Excel file
  • Handled “java.lang.NullPointerException” while calculating formula

In Aspose.Cells 18.7, we fixed several important bugs and other issues. For example, issues around Workbooks with XL4 macros (XLSM), fetching comment text instead of alternative text, handling exceptions like “IndexOutOfBoundsException” while calling Chart.calculate() and  “Invalid encoding: null” while loading the XLS file. Similarly features are added to Change Cells Alignment and Keep Existing Formatting, Find Maximum Rows and Columns supported by XLS and XLSX formats and Specify Author while Write Protecting Workbook.

Changes to the Public API

The following is a list of any changes made to the public API such as added, renamed, removed or deprecated members as well as any non-backward compatible change made to Aspose.Cells for .NET:

  • Adds enum StyleFlag.Alignments, it represents all the settings of alignment.
  • Adds WorkbookSettings.MaxRow and WorkbookSettings.MaxColumn properties, these attributes gets the max row and column indexes of the workbook.
  • Adds WriteProtection.Author property, it gets and sets the author of the write protection.

Aspose.Cells for Java Resources

The resources, you may need to accomplish your tasks: