Aspose validator corrupts excel file when saved


#1

After saving aspose-generated workbook with validators to xlsx when trying to open it with Excel - program shows warning " Removed Feature: Data validation from /xl/worksheets/sheet2.xml part "
All data seems to be ok except validators missing

Aspose 19.10 used


#2

@komloshij,

Thanks for providing details.

Could you provide a simple console application (runnable), zip the project and provide us to reproduce the issue, we will check it soon. Also, provide your sample files (input file (if any), output file (corrupt), etc.). This will help us to evaluate your issue precisely to consequently figure it out soon.


#3
 Workbook workbook = new Workbook();
        var sheet = workbook.Worksheets[0];
        var sheet2 = workbook.Worksheets.Add("Sheet 2");

        sheet.Cells["A1"].Value = "A";
        sheet.Cells["B1"].Value = "B";
        sheet.Cells["C1"].Value = "A+B";
        sheet.Cells["A2"].Value = "10";
        sheet.Cells["B2"].Value = "100";
        sheet.Cells["A3"].Value = "20";
        sheet.Cells["B3"].Value = "200";
        int index = sheet.ListObjects.Add(0, 0, sheet.Cells.MaxDataRow, sheet.Cells.MaxDataColumn, true);
        var table = sheet.ListObjects[index];
        table.DisplayName = "_~";
        table.DisplayName = "Table1";
        table.ListColumns["A+B"].Formula = "=[A]+[B]";


        sheet2.Cells["A1"].Value = "X";
        sheet2.Cells["B1"].Value = "A+B+X";
        sheet2.Cells["A2"].Value = "5";
        sheet2.Cells["A3"].Value = "8";
        int index2 = sheet2.ListObjects.Add(0, 0, sheet2.Cells.MaxDataRow, sheet2.Cells.MaxDataColumn, true);
        var table2 = sheet2.ListObjects[index2];
        table2.ListColumns["A+B+X"].Formula = "=Table1[A+B]+[X]";

        workbook.CalculateFormula();


   

        CellArea ca = new CellArea();
        ca.StartRow = 1;
        ca.EndRow = 2;
        ca.StartColumn = 0;
        ca.EndColumn = 0;

        ValidationCollection validations = sheet2.Validations;
        Validation validation = validations[validations.Add(ca)];
        validation.Type = ValidationType.Custom;
        validation.Formula1 = "Table1[A+B] >= 150";
        validation.ShowError = false;
        validation.ErrorMessage = "Value is less than 150";

        Console.WriteLine();
        Console.WriteLine(GetValidationMessage(sheet2.Cells["A2"]));
        Console.WriteLine(GetValidationMessage(sheet2.Cells["A3"]));

        workbook.Save(@"c:\tmp\exported.xlsx",SaveFormat.Xlsx);

#4

@komloshij,

Thanks for the sample code segment…

After an initial test, I am able to observe the issue as you mentioned by using your sample code. I found by applying custom data validation (involving List object/Table) corrupts Excel file when saved. After saving the workbook to XLSX with data validations when trying to open it with MS Excel, it shows a warning message “…Removed Feature: Data validation from /xl/worksheets/sheet2.xml part”. I have logged a ticket with an id “CELLSNET-46985” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.


#5

@komloshij,

We evaluated your issue further.
By our thorough test, we found it is not allowed to use table references in data validation. If you can create such a validation in MS Excel manually, please send us the manually created template file by MS Excel so we could investigate it further and try to support this feature.