Free Support Forum - aspose.com

Can't get InsertRange to work the way I want to

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;
}
}


Hi,


After an initial test using your sample code and template file, I can find the issue as you have mentioned by your output file. I have logged your issue with an id: CELLSNET-30582 for our investigation. We will look into it soon.

Thank you.

Hi,

Cells.InsertRange works as MS Excel.

If you want to keep the width of column K,L,M, Please use Cells.InsertColumns method.

C#



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);


cells.InsertColumns(range.FirstColumn, range.ColumnCount);


}

I can't use InsertColumns, because that would affect the entire sheet (i.e. rows that are above and below the range I am duplicating will also be affected).

Using InsertRange, with ShiftRight, I get the desired effect, except for the column widths, i.e. only the rows that the range span will get effected. For me, it seems weird that the columns to the right of the range has their widths changed.

By the way, you say that Aspose works like MS Excel in this regard. What is the Excel equivalent of InsertRange?

Thanks!

/Fredrik

Hello again!


I havent received an answer to this.

Thanks!
/Fredrik

Hi,

I have added your comments in our database. Hopefully, you will get an update asap.

Hi,

If you insert range in Ms-Excel, Ms-Excel will not copy the column width and style.

Yes, you are right. I had to manually "remember" the column widths to the right and reset them after the insertion had been made.

Thanks!

/Fredrik