I recently reported a similar problem, but this one is new:
If I used cells.DeleteRange with ShiftType.Up, data validation that exist on cells below the range which is being deleted will not shift up. Values and formatting does, however.
See the attached designer document (DeleteRange2.xlsx) and the result (DeleteRange2_result.xlsx).
In the designer, cell D19 has data validation (List). After the range “DUP_Project” (C4:G5) has been deleted with ShiftUp, the D19 cell has been shifted up two rows. The value (“Please select a value”) and formatting is now in cell D17, but the data validation is left in D19.
The code below:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using System.Data;
using System.Diagnostics;
namespace Infoweaver.Forms.AsposeTest
{
class DeleteRange
{
public enum DeleteRangeEnum
{
ShiftCellsLeft,
ShiftCellsUp,
DeleteEntireColumns,
DeleteEntireRows
}
public static void DoIt()
{
WorkbookDesigner designer = new WorkbookDesigner();
Workbook workbook = new Workbook(Constants.sourcePath + “DeleteRange2.xlsx”);
designer.Workbook = workbook;
Range range = workbook.Worksheets.GetRangeByName(“DUP_Project”);
Worksheet worksheet = range.Worksheet;
Cells cells = range.Worksheet.Cells;
DeleteRangeEnum mode = DeleteRangeEnum.ShiftCellsUp;
switch (mode)
{
case DeleteRangeEnum.ShiftCellsLeft:
case DeleteRangeEnum.ShiftCellsUp:
ShiftType shiftType = mode == DeleteRangeEnum.ShiftCellsLeft ? ShiftType.Left : ShiftType.Up;
cells.DeleteRange(range.FirstRow, range.FirstColumn, range.FirstRow + range.RowCount - 1, range.FirstColumn + range.ColumnCount - 1, shiftType);
break;
case DeleteRangeEnum.DeleteEntireColumns:
cells.DeleteColumns(range.FirstColumn, range.ColumnCount, true);
break;
case DeleteRangeEnum.DeleteEntireRows:
cells.DeleteRows(range.FirstRow, range.RowCount, true);
break;
default:
throw new InvalidOperationException(string.Format(“Unexpected DeleteRangeEnum: ‘{0}’.”, mode));
}
string output = Constants.destPath + “DeleteRange2_result.xlsx”;
workbook.Save(output);
Process.Start(output);
}
}
}