I am facing an issue where aspose is not emulating excel. The file i am using is: Students.zip (31.6 KB)
If you open the file and see the dropdown values in each cell from C8 to C19 contain different number of elements. Example, C9 has 80 elements while C17 has 2. There is a single validation attached to this area but due to the nature of the function, the number of elements in each drop down is different.
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSJAVA-42738 - Wrong count of validation values is read from XLSX
I am facing another issue with this file. I am copying the values and validations of this file into another file with this code:
Workbook book = new Workbook("/Users/model/Students.xlsx");
Workbook newWb = new Workbook();
Worksheet oldWs = book.getWorksheets().get(0);
Worksheet newWs = newWb.getWorksheets().get(0);
ValidationCollection oldValidationCollection = oldWs.getValidations();
ValidationCollection newValidationCollection = newWs.getValidations();
int vCount = oldValidationCollection.getCount();
for(int i=0;i<vCount;i++) {
Validation oldValidation = oldValidationCollection.get(i);
newValidationCollection.add(oldValidation);
}
Cells oldCells = oldWs.getCells();
Cells newCells = newWs.getCells();
int maxRow = oldCells.getMaxDataRow();
int maxCol = oldCells.getMaxDataColumn();
StyleFlag styleFlag = new StyleFlag();
styleFlag.setAll(true);
for (int r=0; r<=maxRow ; r++) {
for (int c = 0; c <= maxCol; c++) {
Cell oldCell = oldCells.get(r,c);
Cell newCell = newCells.get(r,c);
int type = oldCell.getType();
switch(type) {
case CellValueType.IS_NUMERIC:
newCell.setValue(oldCell.getDoubleValue());
break;
case CellValueType.IS_DATE_TIME:
newCell.setValue(oldCell.getDateTimeValue());
break;
case CellValueType.IS_STRING:
newCell.setValue(oldCell.getDisplayStringValue());
}
newCell.setStyle(oldCell.getDisplayStyle());
}
}
newWb.save("/Users/models/gen.xlsx", SaveFormat.XLSX);
The last line throws this error:
at java.util.ArrayList.rangeCheck(ArrayList.java:653)
at java.util.ArrayList.get(ArrayList.java:429)
at com.aspose.cells.NameCollection.get(Unknown Source)
at com.aspose.cells.zabc.f(Unknown Source)
at com.aspose.cells.zabc.b(Unknown Source)
at com.aspose.cells.Validation.a(Unknown Source)
at com.aspose.cells.zwq.a(Unknown Source)
at com.aspose.cells.zwq.q(Unknown Source)
at com.aspose.cells.zwq.a(Unknown Source)
at com.aspose.cells.zui.a(Unknown Source)
at com.aspose.cells.ze.a(Unknown Source)
at com.aspose.cells.ze.a(Unknown Source)
at com.aspose.cells.ze.d(Unknown Source)
at com.aspose.cells.ze.a(Unknown Source)
at com.aspose.cells.zwn.a(Unknown Source)
at com.aspose.cells.Workbook.a(Unknown Source)
at com.aspose.cells.Workbook.save(Unknown Source)
at com.aspose.cells.Workbook.save(Unknown Source)
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSJAVA-42740 - Validation cannot be copied from one Workbook to another one
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-42740”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
We have added Validation.Copy method and obsoleted ValidationCollection.Add(Validation) method in the new fix/version.
Once the fix is available for public use, we will share the Download link here.
The method Cell.GetValidation() is designed for providing a convenient way to get the Validation object whose areas contain this cell. In your template file, cells in C8:C19 belong to the same Validation object, so Cell.GetValidation() gives the same Validation object for them and then the Validation.Value1 also gives the same value. There is another method to check the validated result for specific cell: Cell.GetValidationValue(). If you need to get the values list of Validation corresponding with specific cell. However, we will make further investigation to provide new APIs for such kind of purpose.
Hey,
Thanks for getting back to me. getValidationValue returns a boolean is not of any help in this issue. Please let me know if there is anyway currently to get the actual validation values in an array or list.
I have used your idea and have been able to get the valid values. Though a direct API would be ideal but this would do for the time being. Can you please let me know about the status of the bug where the file could not be saved once validations are copied?
Good to know that you workaround your issue (“CELLSJAVA-42738”) for the time being. We have logged your concerns against the issue into our database. The issue is not resolved yet and once it is resolved or have some other updates, we will let you know.
The workaround does not work well enough I am sorry to say. The reason being that Aspose evaluates the first cell in the range, C8 in this case, and applies that to every cell. Now that is fine if all the cells after that has less values than the first, but the cell C11 should have more values than C8 which does not even show up in Validation.getValue1() function because it gives me the values on C8 only. So I do still need CELLSJAVA-42738 to be solved. I hoped the workaround would work but unfortunately is does not.
Thank you for the feedback. We have recorded your comments about CELLSJAVA-42738 for later reference. Regarding CELLSJAVA-42740, as informed earlier it may take about a week to provide the updated version. We will provide download link here once it is released for public use.
For the issue “CELLSJAVA-42740”, please use Validation.Copy method to copy validation with the attached fix/version: Aspose.Cells for Java v18.10.3. We have obsoleted ValidationCollection.Add(Validation) method:
e.g Sample code:
for (int i = 0; i < vCount; i++)
{
Validation oldValidation = oldValidationCollection.get(i);
// newValidationCollection.Add(oldValidation);
CellArea[] areas = oldValidation.getAreas();
int index = newValidationCollection.add(areas[0]);
Validation newValidation = newValidationCollection.get(i);
newValidation.copy(oldValidation, null);
}
I am no longer getting an exception when saving the file. Thanks for getting this done but the new file shows a warning that there are links to another XLSX file when opening it. Can we get around that?
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-42738”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
Please try our latest version/fix: Aspose.Cells for Java v18.10.5 (attached)
Your issue “CELLSJAVA-42738” should be fixed in it.
Let us know your feedback.
We provide new method for your requirements:
Validation.GetListValue(int row, int column)
This method will return the object by which the list of Validation can be produced based on the specified cell. The returned object can be a ReferredArea object(when the list references to a cell range), or object[], or a simple object. If the type of this Validation is not List, or the given cell is not in the areas of this Validation, null will be returned.
Good to know that your issue is sorted out by the new fix/version. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.
The issues you have found earlier (filed as CELLSJAVA-42738) have been fixed in Aspose.Cells for Java 18.11. You can also get the latest Aspose.Cells for Java version from Maven repos. with simple configurations. Please see the document for your reference: Installation|Documentation
This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi