Free Support Forum - aspose.com

API Changes issue regarding hiding sheets and data validations in Java

Hi,


Today we experienced two things that has changed since release 2.5.4.19.

1. Before version 7 it was possible to hide a sheet in a workbook even if it was the only one. We have a lot of code that starts by adding a sheet that is hidden and thereafter adds the visible sheets. This causes an exception to be thrown in version 7. Would it be possible to add a method for hiding a sheet that does not throw an exception if it is the only sheet in the workbook?

2. Validations with high limits are causing the following exception in version 7:
com.aspose.cells.CellsException: Invalid formula in data validation
settings.

at com.aspose.cells.vj.b(Unknown Source)

at com.aspose.cells.vj.a(Unknown Source)

at com.aspose.cells.vj.a(Unknown Source)

at com.aspose.cells.Validation.a(Unknown Source)

at com.aspose.cells.WorksheetCollection.am(Unknown Source)

at com.aspose.cells.WorksheetCollection.b(Unknown Source)

at com.aspose.cells.WorksheetCollection.a(Unknown Source)

at com.aspose.cells.WorksheetCollection.a(Unknown Source)

at com.aspose.cells.Workbook.a(Unknown Source)

at com.aspose.cells.Workbook.save(Unknown Source)

This happens if the limit is for example: 50000000

This did not happen in version 2.5.4.19.

Thanks & Best Regards,
Ulf Ekström

Hi,

1. Even in older versions, you cannot hide all the sheets, one sheet must be visible. It is also a behavior of Ms-Excel.

2. Please provide us your sample code replicating this problem. We will investigate it and provide you a fix asap.


Hi,

//1. The following code works fine prior to version 7:`
    Workbook wb = new Workbook();

    Worksheets sheets = wb.getWorksheets();
    Worksheet sheet = sheets.getSheet(0); //since Sheet 1 is automatically added we can take it here

    sheet.setVisible(false);

    sheets.addSheet(); //add a visible sheet before save

wb.save(“c:/users/ulf/test.xls”);

but the same code adjusted to version 7 throws an exception:
Workbook wb = new Workbook();



WorksheetCollection sheets = wb.getWorksheets();

Worksheet sheet = sheets.get(0); //since Sheet 1 is
automatically added we can take it here



sheet.setVisible(false);

sheets.add(“Visible”); //add a visible sheet before save



wb.save(“c:/users/ulf/test.xls”);

//2. The problem seems to be that version 7 does not support numbers in scientific notation for validations:

private static void validationTest() throws Exception {

Workbook wb = new Workbook();

Worksheet s = wb.getWorksheets().add(“Testing”);



ValidationCollection validations = s.getValidations();

Validation list = validations.get(validations.add());



list.setType(ValidationType.DECIMAL);

list.setInCellDropDown(false);

list.setIgnoreBlank(true);

list.setOperator(OperatorType.BETWEEN);



list.setFormula1(“12”);

list.setFormula2(Double.toString(50000000)); //crashes

// list.setFormula2(“50000000”); //works



CellArea area = getCellArea(0, 0, 2, 2);

list.addArea(area);



list.setErrorTitle(“Error Title”);

list.setErrorMessage(“Error Message”);



wb.save(…);

}

Regards,
Ulf

Hi,


1) I think you may interchange the lines of code to following if it suits your needs e.g:
Workbook wb = new Workbook();

WorksheetCollection sheets = wb.getWorksheets();
Worksheet sheet = sheets.get(0); //since Sheet 1 is automatically added we can take it here


sheets.add(“Visible”); //add a visible sheet before save
sheet.setVisible(false);

wb.save(“c:/users/ulf/test.xls”);


2) I have found the issue using your code as you mentioned, I have logged a ticket for the issue with an id: CELLSJAVA-30733. We will look into the issue soon.

Thank you.

Hi Amjad,


Yes I know that changing the lines of the sample makes the code work. However, it is not that simple in our product where things happen in very different places and contexts. The change in version 7 will force us to make big changes and refactorings. Can you please consider to implement a method like setVisible(boolean visible, boolean ignoreError) just like for formula calculations? Or throw the exception when saving a workbook with no visible sheets as you used to do in the previous version?

Regards,
Ulf

Hi,


OK, we will check the feasibility if we can support your needs. I have logged a ticket for it with an id: CELLSJAVA-30738. We will look into it soon.

Thank you.

Hi,

Please download: Aspose.Cells for Java v7.0.1.3

We have fixed the issue of setting formulas for validation. Also, for setting a worksheet hidden, we provide a new method:

public void setVisible(boolean isVisible, boolean ignoreError)

if ignoreError is true, we allow you to set sheet as hidden even if it is the only one worksheet. But please make sure you will add other visible sheets later, otherwise the resultant excel file may not work as expected.

That is great news!


Best regards,
Ulf

The issues you have found earlier (filed as 30738; 30733) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.