Here’s what I want to do:
I am retrieving a DataTable from the database. For each row in the data-table, I want to duplicate a named range in a sheet, horizontally (this works a little like smart markers). The duplication should include values, formatting as well as column widths.
This works fine - except for one thing: The columns to the right of the new columns will have their widths set to something else than their original widths.
See the attached documents, Duplicate2.xlsx is the “designer”, Duplicate2_result.xlsx is the resulting document. When running the code below, the named region DUP_Project should be repeated three times, to the right. The columns to the right of the named region should be moved to the right - with their widths intact.
As you can see, the columns K, L and M in Duplicate2_result.xlsx have column widths 8.43, but they should be 10, 15 and 20, respectively.
Any ideas?
/Fredrik
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();
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);
// …then the actual values
newRange.Copy(range);
}
range = newRange;
}
string output = Constants.destPath + “Duplicate2_result.xlsx”;
workbook.Save(output);
Process.Start(output);
}
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;
}
}