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
//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(…);
}
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.
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?
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.