I am using the code below to duplicate a range.
/Fredrik
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 Duplicate2
{
public static void DoIt()
{
WorkbookDesigner designer = new WorkbookDesigner();
Workbook workbook = new Workbook(Constants.sourcePath + “Duplicate2.xlsx”);
designer.Workbook = workbook;
Range range = workbook.Worksheets.GetRangeByName(“DUP_Project”);
Worksheet worksheet = range.Worksheet;
DataTable dt = CreateValidTable1();
// We need to remember all column widths to the right of the range, since inserting ranges reset the columns widths to the right
Cells cells = range.Worksheet.Cells;
List columnWidths = new List();
for (int columnIndex = range.FirstColumn + range.ColumnCount; columnIndex <= cells.MaxDataColumn; columnIndex++)
{
Column column = cells.Columns[columnIndex];
columnWidths.Add(column.Width);
}
for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
{
DataRow row = dt.Rows[rowIndex];
Range newRange = worksheet.Cells.CreateRange(range.FirstRow, range.FirstColumn + range.ColumnCount, range.RowCount, range.ColumnCount);
if (rowIndex < dt.Rows.Count - 1)
{
// Create a new range…
InsertRange(range);
// …and paste the current range into the new range
PasteOptions pos = new PasteOptions();
pos.PasteType = PasteType.ColumnWidths;
// We first must paste the column widths…
newRange.Copy(range, pos);
pos.PasteType = PasteType.Validation;
// …then the validation…this does not work, however…
newRange.Copy(range, pos);
// …then the actual values
newRange.Copy(range);
}
range = newRange;
}
// Reset the widths of the columns to the right
for (int i = 0; i < columnWidths.Count; i++)
{
Column column = cells.Columns[i + range.FirstColumn];
column.Width = columnWidths[i];
}
string output = Constants.destPath + “Duplicate2_result.xlsx”;
workbook.Save(output);
Process.Start(output);
}
private static void InsertRange2(Range range)
{
Cells cells = range.Worksheet.Cells;
//CellArea ca = new CellArea();
//ca.StartRow = range.FirstRow;
//ca.EndRow = cells.MaxRow;
//ca.StartColumn = range.FirstColumn + range.ColumnCount;
//ca.EndColumn = cells.MaxColumn;
// cells.InsertRange(ca, range.ColumnCount, ShiftType.Right, true);
cells.InsertColumns(range.FirstColumn, range.ColumnCount);
}
private static void InsertRange(Range range)
{
Cells cells = range.Worksheet.Cells;
CellArea ca = new CellArea();
ca.StartRow = range.FirstRow;
ca.EndRow = cells.MaxRow;
ca.StartColumn = range.FirstColumn + range.ColumnCount;
ca.EndColumn = cells.MaxColumn;
cells.InsertRange(ca, range.ColumnCount, ShiftType.Right, true);
}
private static 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;
}
}
}