Thursday 6 February 2014

OpenXML how to copy cells with Validation

It seems that blank cells with data validation (no value, only data validation) are not retaining the data validation after being copied to other place (e.g. another worksheet). As soon as I try to copy a cell with some value + data validation, everything gets copied correctly.

Scenario
If don't choose any value in the drop down list, when you copy the cell or the row to other place using code, the validation will not retain.
What I have done is put a space on top the values list (named range, refer to previous post for how to add named range, and how to put drop down list validation), and in my template, I choose the space value in it. and then when I copy the cell to other place, the validation is retained.

My Partial Code:

                var wb = new XLWorkbook(exportDest);
                var wsData = wb.Worksheet("Data");
                wb.NamedRange("CorpGrade").SetRefersTo("Data!$B$3:$B$15");
                wb.NamedRange("Category").SetRefersTo("Data!$D$3:$D$10");
                var ws = wb.Worksheet(2);
                int i = startRow;
                while (i < 100)
                {
                    ws.Row(i).InsertRowsAbove(1);
                    var row = ws.Row(i);
                    ws.Row(i + 1).CopyTo(row);
                    //var range = ws.Range(i + 1, 1, i + 1, 50).CopyTo(row.Cell(1));
                    i++;
                }

                wb.Save();

No comments:

Post a Comment