InsertRows does not preserve validation/styling

In Microsoft Excel, if a user (manually) Inserts a new row in a worksheet that has validation/styling applied, the row insertion applies the validation/styling of the previous row.

Using Aspose Excel .NET version 3.6.2.1, I tried to do the same thing programmatically using the InsertRows method of the Cells object. The row insertion works exception that the validation/styling is not preserved.

Is there an easy way to make this work? Thanks.

P.S. - I am in the process of upgrading to the latest version of Aspose.Cells. Not sure if that makes a difference in you response. Thanks.

Please try this attached fix. InsertRows method will push down validations now.

Thanks, Laurence. Is this fix a branch of Aspose.Excel 3.6.2.1 ? If not, I can test your fix in a separate "test" project, but I will not be able to integrate it into our real application without some rigorous integration testing. (This is where detailed release notes for the Aspose DLL would be extremely helpful.)

It’s a fix for v3.7.2. You can test it in a test project before deploying. For release note, you can check http://www.aspose.com/Community/Blogs/laurence.chen/archive/category/1020.aspx.

The following code does not work as I expected at the InsertRows call (below) using the DLL that you provided in a prior post. IU was expecting InsertRows to produce 10 rows having "drop-down" options in the second column, followed by an "end of data" marker on the very last line of the worksheet. Instead, the "end of data" marker is row 3.

[TestMethod]

public void InsertRows_1() // doesn't work in 3.7.2.2

{

Excel excel = new Excel();

Cells cells = excel.Worksheets[0].Cells;

Range range = null;

ArrayList headings = new ArrayList();

headings.Add("Empty Column");

headings.Add("Heading1");

cells.ImportArrayList(headings, 0, 0, false);

cells[1, 1].PutValue("value[1,1]");

int indexOfLastDataColumn = cells.MaxDataColumn;

int indexOfLastDataRow = cells.MaxDataRow;

Worksheet worksheet = excel.Worksheets[0];

Styles styles = excel.Styles;

Validations validations = worksheet.Validations;

CellArea cellArea = new CellArea();

ArrayList list = new ArrayList();

int i = 0;

string rangeName = ("MyRange");

i = excel.Worksheets.Add();

Worksheet sheet = excel.WorksheetsIdea [I];

Cells listing = sheet.Cells;

ArrayList items = new ArrayList();

items.Add("Apple");

items.Add("Banana");

items.Add("Carrot");

listing.ImportArrayList(items, 0, 0, true);

range = listing.CreateRange(0, 0, items.Count, 1);

range.Name = rangeName;

sheet.AutoFitColumn(0);

i = validations.Add();

validationsIdea [I].Type = ValidationType.List;

validationsIdea [I].Operator = OperatorType.None;

validationsIdea [I].ShowInput = false;

validationsIdea [I].IgnoreBlank = true;

validationsIdea [I].InCellDropDown = true;

validationsIdea [I].AlertStyle = ValidationAlertType.Warning;

validationsIdea [I].ErrorMessage = "Value not in list";

validationsIdea [I].Formula1 = string.Format("={0}", rangeName);

cellArea = new CellArea();

cellArea.StartRow = 1;

cellArea.EndRow = indexOfLastDataRow;

cellArea.StartColumn = 1;

cellArea.EndColumn = 1;

list = validationsIdea [I].AreaList;

list.Add(cellArea);

for (int j = 0; j < 2; j++)

{

worksheet.AutoFitColumn(j);

}

// This does not insert rows!

cells.InsertRows(2, 10);

i = styles.Add();

stylesIdea [I].Font.IsBold = true;

stylesIdea [I].ForegroundColor = System.Drawing.Color.Yellow;

stylesIdea [I].Font.Color = System.Drawing.Color.Blue;

// Required by Aspose.Cells 3.7.2 for background colors to work:

stylesIdea [I].Pattern = BackgroundType.Solid;

int lastRowNumber = cells.MaxDataRow + 1;

Row row = cells.Rows[lastRowNumber];

row.Style = stylesIdea [I];

Cell cell = cells[lastRowNumber, 0];

cell.PutValue("End of data");

excel.Save("InsertRowsTests_1.xls");

}

Oh my. That was an ugly post of mine. Sorry. I attached a text file that can easily be hosted by a cs file.

I tried your code. Attached is output file in my machine.

1. Please click on B2-B12. You will see the dropdownlist of data validations.

2. Data validations are validations, not data. So MaxDataRow returns 2, not 12.

I attached my latest version here.