Thanks a lot for helping out with the issue in <a href="https://forum.aspose.com/t/122591.
I still have two problems, though. I am reporting both of them in this post, since the repro-case is the same.
I am duplicating ranges vertically. In the attached “Duplicate3.xlsx”, see the two named ranges, Range1 and Range2. These are duplicated three times each, vertically. Range1 is located above Range2.
Problem 1 (see output document Range1_Range2.xlsx)
If I first duplicate Range1, then Range2, the Range2 will have “moved” after Range1 is duplicated. It seems as if Cells.InsertRange doesn’t properly update the ranges that are outside the range which is being inserted. The effect is that when duplicating Range2, the set of rows that will be duplicated is not the intended.
Problem 2 (see output document Range2_Range1.xlsx)
If I first duplicate Range2, then Range1, I overcome problem #1. However, in this case, the data validation in Range2 gets moved up, one row for each duplication of Range1 (the data validation is a List validation, in column C).
Here is the code: (Entry point is DoIt)
class Duplicate3
{
private WorkbookDesigner wbd;
private Range GetRange(Workbook wb, string rangeName, bool throwIfNotFound)
{
Range range = wb.Worksheets.GetRangeByName(rangeName);
if (range == null && throwIfNotFound)
{
throw new ApplicationException(string.Format(“No range exists with name ‘{0}’”, rangeName));
}
return range;
}
public enum RangeRepeatDirection
{
Horizontal,
Vertical
}
///
/// Used to specify repeating of a range.
///
public class RepeatRange
{
///
/// The name of the name Range that should be repeated
///
public string RangeName = null;
///
/// The direction of repeating
///
public RangeRepeatDirection RepeatDirection = RangeRepeatDirection.Horizontal;
}
private void PerformRangeRepeat(DataTable tbl, RepeatRange repeatRange)
{
Range range = GetRange(wbd.Workbook, repeatRange.RangeName, true);
Cells cells = range.Worksheet.Cells;
List columnWidths = new List();
if (repeatRange.RepeatDirection == RangeRepeatDirection.Horizontal)
{
// We need to remember all column widths to the right of the range, since inserting ranges reset the columns widths to the right
for (int columnIndex = range.FirstColumn + range.ColumnCount; columnIndex <= cells.MaxDataColumn; columnIndex++)
{
Aspose.Cells.Column column = cells.Columns[columnIndex];
columnWidths.Add(column.Width);
}
}
for (int rowIndex = 0; rowIndex < tbl.Rows.Count; rowIndex++)
{
DataRow row = tbl.Rows[rowIndex];
int rowOffset = repeatRange.RepeatDirection == RangeRepeatDirection.Vertical ? range.RowCount : 0;
int columnOffset = repeatRange.RepeatDirection == RangeRepeatDirection.Horizontal ? range.ColumnCount : 0;
Range newRange = cells.CreateRange(range.FirstRow + rowOffset, range.FirstColumn + columnOffset, range.RowCount, range.ColumnCount);
if (rowIndex < tbl.Rows.Count - 1)
{
ShiftType shiftType = repeatRange.RepeatDirection == RangeRepeatDirection.Vertical ? ShiftType.Down : ShiftType.Right;
InsertRange(range, rowOffset, columnOffset, shiftType);
PasteOptions pos = new PasteOptions();
pos.PasteType = PasteType.ColumnWidths; // For some reason, we must first copy the column widths…
newRange.Copy(range, pos);
newRange.Copy(range); // …and then the actual values
}
ReplaceTags(cells, range, row);
range = newRange;
}
if (repeatRange.RepeatDirection == RangeRepeatDirection.Horizontal)
{
// Reset the widths of the columns to the right
for (int i = 0; i < columnWidths.Count; i++)
{
Aspose.Cells.Column column = cells.Columns[i + range.FirstColumn];
column.Width = columnWidths[i];
}
}
}
private static void ReplaceTags(Cells cells, Range range, DataRow row)
{
}
private static void InsertRange(Range range, int rowOffset, int columnOffset, ShiftType shiftType)
{
Cells cells = range.Worksheet.Cells;
CellArea ca = new CellArea();
ca.StartRow = range.FirstRow + rowOffset;
ca.EndRow = cells.MaxRow;
ca.StartColumn = range.FirstColumn + columnOffset;
ca.EndColumn = cells.MaxColumn;
cells.InsertRange(ca, rowOffset + columnOffset, shiftType, true);
}
public void DoIt()
{
wbd = new WorkbookDesigner();
Workbook workbook = new Workbook(Constants.sourcePath + “Duplicate3.xlsx”);
wbd.Workbook = workbook;
DataTable dt = CreateValidTable1();
RepeatRange repeatRange = new RepeatRange();
repeatRange.RangeName = “Range1”;
repeatRange.RepeatDirection = RangeRepeatDirection.Vertical;
PerformRangeRepeat(dt, repeatRange);
repeatRange.RangeName = “Range2”;
PerformRangeRepeat(dt, repeatRange);
string output = Constants.destPath + “Duplicate3_result.xlsx”;
workbook.Save(output);
Process.Start(output);
}
private DataTable CreateValidTable1()
{
DataTable dt = new DataTable(“MyDataSource”);
dt.Columns.Add(“Id”, typeof(int));
dt.Columns.Add(“Additional”, typeof(string));
dt.Rows.Add(1001, “Additional1”);
dt.Rows.Add(1002, “Additional2”);
dt.Rows.Add(1003, “Additional3”);
// dt.Rows.Add(“val2”, “val2”, 4);
//dt.Rows.Add(“val3”, “val3”, 4);
return dt;
}
}