Pagebreaks and Named ranges are not moving when I insert rows. This happens only on copied worksheets

Aspose.Cells Product Family
1

Excel named ranges and page breaks are not moving down/up when I insert/delete rows in a workshet. Looks like this occurs on when I make a copy fo the worksheet.

See the below code snippet and attached excel file to reproduce the problem.

Workbook book = new Workbook();

book.Open(@"C:\temp\test.xls");

// This works fine.

Worksheet sheet = book.Worksheets[0];

Range r1 = book.Worksheets.GetRangeByName("TA");

Console.WriteLine("Row Index of the range {0} : {1}", "TA", r1.FirstRow);

Console.WriteLine("Inserting 10 rows after the range 'TA'");

sheet.Cells.InsertRows(r1.FirstRow - 10, 5);

Range r2 = book.Worksheets.GetRangeByName("TA");

Console.WriteLine("Row Index of the range {0} : {1}", "TA", r2.FirstRow);

//This Section doesn't work as expected

// Make a copy of the worksheet

Console.WriteLine("Make a copy of the worksheet");

int idx = book.Worksheets.AddCopy(0);

Worksheet sheet2 = book.Worksheets[idx];

Range r3 = book.Worksheets.GetRangeByName("TA");

Console.WriteLine("Row Index of the range {0} : {1}", "TA", r3.FirstRow);

Console.WriteLine("Inserting 10 rows after the range 'TA'");

sheet2.Cells.InsertRows(r3.FirstRow - 10, 5);

Range r4 = book.Worksheets.GetRangeByName("TA");

Console.WriteLine("Row Index of the range {0} : {1}", "TA", r4.FirstRow);

Console.ReadLine();

//book.Save(@"C:\temp\PolicyStatement.xls");

2

Hi,

Well, I think the reason is simple, when there are global named ranges in a worksheet (that can be accessible in other worksheets too) and you add a copy of that worksheet, the named ranges would become local named ranges (named ranges specific to the copied worksheet only). This is the case with your scenario.

You need to access a named range in the copied sheet in the following way as mentioned in the updated code:

Suppose we defined a global named range in header worksheet on the cell “A25”

Workbook book = new Workbook();

book.Open(@“e:\test\PolicyStatement.xls”);

// This works fine.
Worksheet sheet = book.Worksheets[0];
Range r1 = book.Worksheets.GetRangeByName(“TA”);
MessageBox.Show("Row Index of the range : " + "TA " + r1.FirstRow.ToString());
MessageBox.Show(“Inserting 10 rows after the range TA”);
sheet.Cells.InsertRows(r1.FirstRow - 10, 5);
Range r2 = book.Worksheets.GetRangeByName(“TA”);

MessageBox.Show("Row Index of the range : " + "TA " + r2.FirstRow.ToString());




// Make a copy of the worksheet
MessageBox.Show(“Make a copy of the worksheet”);
int idx = book.Worksheets.AddCopy(0);
Worksheet sheet2 = book.Worksheets[idx];
Range r3 = book.Worksheets.GetRangeByName(sheet2.Name + “!TA”);
MessageBox.Show("Row Index of the range : " + sheet2.Name + "!TA " + r3.FirstRow.ToString());
MessageBox.Show(“Inserting 10 rows after the range local TA”);
sheet2.Cells.InsertRows(r3.FirstRow - 10, 5);
Range r4 = book.Worksheets.GetRangeByName(sheet2.Name + “!TA”);
MessageBox.Show("Row Index of the range : " + sheet2.Name + "!TA " + r4.FirstRow.ToString());

book.Save(“e:\test\out.xls”);

You may conduct a test manually in MS Excel and can see that Aspose.Cells works in the same way as MS Excel.

Thank you.

3

That fixed my issue. Thanks for the quick resposnse.