Adding validation above some threshold (around 18701 plus) is causing corrupted xls file

Aspose.Cells Product Family
1

We have an urgent production issue which needs to be resolved ASAP (as we live).

Java version: 1.6.0_25
Production aspose version in use: aspose-cells-7.1.2.jar
Tested with: aspose-cells-7.1.2.jar and aspose-cells-7.3.4.3.jar

The use-case is, we have a base response template which would be populated (attached as “BaseResponseTemplate_en_GB.xls” ) with some values. We would be writing data to this template with chosen columns (need to write to all rows). Below is the sample code which I wrote to replicate the issue.
The below code would write from row 7 till row 452 (0 based index) in all the columns selected in the array COLS. And in last column from BQ453 to BQ464 we will write count value, but when we try to write values after BQ465 we end up in corrupt file (18701 is the magic/threshold number).
When you run the sample application below you would end up in corrupt file, if you want to have a valid file just comment out the line having the statement “TODO comment me if we want to have a valid file”, this line contains the code which would write 18702 to the cell BQ465.



private static final String FILE_NAME = “BaseResponseTemplate_en_GB.xls”;

private static final int[] COLS = { 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, //
33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, //
62, 63, 64, 65, 66, 67, 68, 69 //
};

public static void main(String[] args) throws Exception
{
worksheetEvalutionException();
}

static void worksheetEvalutionException() throws Exception
{
InputStream templateAsStream = AposeExeMain.class.getClassLoader().getResourceAsStream(FILE_NAME);
Workbook workbook = new Workbook(templateAsStream, new LoadOptions(LoadFormat.AUTO));

Worksheet worksheet = workbook.getWorksheets().get(“SheetWithIssue”);
String fileName = System.currentTimeMillis() + “_” + FILE_NAME;

writeContentToLastValidCell(worksheet);

workbook.save(fileName);
}

static void writeContentToLastValidCell(Worksheet worksheet)
{
int count = 0;
for (int index = 0; index < COLS.length; index++) {
int column = COLS[index];
int rows = 8;
for (; rows < 453; rows++) {
writeToCellWithValidation(count++, worksheet, rows - 1, column - 1);
}
if (index == COLS.length - 1) {
/* we are in the last COLS index i.e. BQ column, we have written values ranging from 0 to 18689
* in the cells and the last value of 18689 is written in cell BQ452
*
* now writing some 12 values in BQ cells after BQ452 (BQ453 to BQ464) the last value to be
* written would be 18701
*/
int i = 0;
for (; i < 12; i++) {
writeToCellWithValidation(count++, worksheet, (rows + i - 1), column - 1);
}
// now trying to write a single value to cell BQ465 (value would be 18702)
// from here we get into issue
writeToCellWithValidation(count++, worksheet, (rows + i - 1), column - 1); // TODO comment me if we want to have a valid file
}
}
}

private static void writeToCellWithValidation(int content, Worksheet worksheet, int rows, int column)
{
Cell cell = worksheet.getCells().get(rows, column);

Validation val = worksheet.getValidations().get(worksheet.getValidations().add());
val.setType(ValidationType.DECIMAL);
val.setOperator(OperatorType.BETWEEN);

double lower = -99999999999999.1;
double upper = 99999999999999.1;

val.setFormula1("" + lower);
val.setFormula2("" + upper);
CellArea currentCellArea = new CellArea();
currentCellArea.StartColumn = cell.getColumn();
currentCellArea.EndColumn = cell.getColumn();
currentCellArea.StartRow = cell.getRow();
currentCellArea.EndRow = cell.getRow();

val.getAreaList().add(currentCellArea);
cell.putValue(content);
}


As we are live on the production, we would be looking out for a quick patch.

2
Hi,

Thanks for the sample file and sample code.

After an initial test, I can notice the issue. I got a corrupted file using your code with your attached template file that is generated by Aspose.Cells, so when I open the file into MS Excel 2007, MS Excel gives me an error message and consequently some formulas and formatting are lost too.

I have logged a ticket with an id "CELLSJAVA-40415" for your issue. I also think that it might be some limitation put forth by MS Excel regarding validations and their count and areas for the cells in the rows/columns, could you also try to perform the task manually (as per your codes) in MS Excel, if you could be able to save the file with your desired number of validations for the areas/cells, if you could do it then it is an issue with our product surely.

Anyways, as we have logged a ticket so we will look into your issue for any case.

Thank you.
3

Hi,

Thanks for your posting and using Aspose.Cells for Java.

In fact there are lots of validations in your template file already. When you add about 18702 new validations into the worksheet, the total count of validations in this worksheet is about 65532. It is the MS-Excel’s limit for validation count in one worksheet. You can confirm this in MS-Excel by adding more validations manually. The saved xls file by MS-Excel also will give error when open it again.

For your situation, if you need to create all validations again in your application, please remove all existing validations firstly. Also, we do not think you need to create so many validations for every cell. If those cells in a range have the same criteria, please create only one validation for this range. Thus you can avoid such kind of problem, also you can get better performance and smaller resultant file.

4

Hi,
Thanks for the giving a fix for our issue. We surely need to avoid the validation on each cell and add validation using range criteria instead. We can close the discussion as we have a fix for our issue.

Apart from the issue reported, I do have something to say. If you open the file manually (“BaseResponseTemplate_en_GB.xls”) and go the sheet and if you check the cell BQ1122, it does have a validation already, and total validation of the sheet is 46830, now when we open this file with aspose and add a validation on this cell (just one), and when you look at the total validation of this sheet is 46831 (using code worksheet.getValidations().getCount()), there is an increase of count 1, but as that cell already had a validation and we are just editing it I think there should not be any increase of count. This is just an observation I made not sure if this is an issue, its up to you to take a call :slight_smile:

Once again thanks for the help and giving a fix for our issue.

thank you,
mpujari

5

And one more thing, as we were not aware of the validation limits, we
had really hard time to converge to the point where adding a validation
was causing an issue. To avoid such issue for others (who don’t know
this) may be aspose would give some logging warning when we try to add a
validation which exceeds a limit (it can be applied to any other limit and not just on validation limits).



thanks,

mpujari

6

Hi,

Thanks for your posting and using Aspose.Cells for Java

We have logged your queries i.e 435947 and 435948 in our database against the issue id: CELLSJAVA-40415

Please spare us some more time. Once, we will have some advice for you, we will share it with you asap.