I am using the code below to duplicate a range.
This works fine, almost. Column widths, formatting and values are carried over. Validation, however, does not.
In the attached Xlsx, there are two cells with data validation (yellow). When running the code on this document, the result will have lost the validation on the cells.
Thanks!
/Fredrik
/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;
}
}
}