When copying and reorder some columns if contains cells that intersect with the existing data validation range will behave unexpectedly and corrupt the file

Aspose.Cells Product Family
1

Try the following:

  1. Create a new xlsx file by Excel.
  2. Select a row and set this row with a data validation(any validation).
  3. Save file and close Excel, open this file with Aspose.Cells.
  4. Create a new worksheet, and looping copy some column(Worksheet.Cells.CopyColumn) in reversed.
    • Check the changes of Worksheet.Validations of new worksheet in this step.
  5. Save the file and try open it in Excel, you will got a error that tell you this file has been broken.

Core example code:

var wb = new Workbook(xlsxFilePath);
var sheet = wb.Worksheets[0];
Print(sheet.Validations[0]);
var newSheet = wb.Worksheets.Add("Reorder");
for (var i = 0; i < 5; i++)
{
    newSheet.Cells.CopyColumn(sheet.Cells, 5 - i, i);
    Console.WriteLine("Copied validation count:" + newSheet.Validations.Count);
    Console.Write("Copied validation area:");
    Print(newSheet.Validations[newSheet.Validations.Count - 1].Areas);
}
wb.Save(wb.FileName + ".edited.xlsx");

According to the exception information, it is speculated that have not checked the sheet boundary when copy validation, create a CellArea with out of sheet boundary.

You can also try test “Cells.CopyColumns”, “Cells.CopyRow”, “Cells.CopyRows”.

BTW: If not reorder when copy, file will not broken. But reopen the output file and check the validation of new sheet, you will found that the same validation with overlapping range not merged.

var wb = new Workbook(xlsxFilePath + ".edit.xlsx");
var newSheet = wb.Worksheets[1];
foreach(Validation va in newSheet.Validations)
{
    Console.Write("Copied validation area:");
    Print(va.Areas);
}
wb.Dispose();
2

@Flithor,

Thanks for the details.

I have tested your scenario/case using our latest version/fix: Aspose.Cells for .NET v22.8 (Download | NuGet), it works fine. I created a sample Excel file in MS Excel manually as per your instructions. I applied whole number data validation to the second row and saved the file. Now I use the following sample code to open the template file and generate the output file:
e.g.
Sample code:

            var wb = new Workbook("e:\\test2\\Bk_wholenumvalidations1.xlsx");
            var sheet = wb.Worksheets[0];
            Console.WriteLine(sheet.Validations[0]);
            var newSheet = wb.Worksheets.Add("Reorder");
            for (var i = 0; i < 5; i++)
            {
                newSheet.Cells.CopyColumn(sheet.Cells, 5 - i, i);
                Console.WriteLine("Copied validation count:" + newSheet.Validations.Count);
                Console.Write("Copied validation area:");
                Console.WriteLine(newSheet.Validations[newSheet.Validations.Count - 1].Areas);
            }
            wb.Save("e:\\test2\\Bk_wholenumvalidations1_edited.xlsx");

The output file is fine tuned and is not corrupted. MS Excel opens it fine.
Please find attached the zipped archive containing both input and output files for your reference.
files1.zip (14.9 KB)

3

Yes, it looks works well.

4

@Flithor,

Good to know that your issue is sorted by the new version/fix. Feel free to write us back if you have further queries or issue, we will be happy to assist you soon.