InsertRows does not preserve validation/styling


#1

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.


#2

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.


#3

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


#4

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


#5

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 .


#6

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

}


#7

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


#8

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.


#9

I attached my latest version here.