EPPlus
EPPlus copied to clipboard
Worksheet.InsertColumn interaction with Worksheet.DeleteColumn unclear with respect to DataValidation
Ran into this while trying to reuse and modify templates to retain data validation.
If data validation exists on column C (3), and ws.InsertColumn(3,1)
Column C still has the validation while 4 does not.
Not certain if this is specific to using cross-worksheet validation (I have another sheet that just contains ranges to be referenced)
Trying to use the following code
MoveDataValidation(ws, 'C', 'D');
private static void MoveDataValidation(ExcelWorksheet ws, char fromCol, char toCol)
{
var to = $"{toCol}:{toCol}";
var source = fromCol + "2";
var validation = ws.Cells[source].DataValidation as IExcelDataValidationList;
//Copy, errors
var toValidation = ws.DataValidations.AddListValidation(to);
}
is giving me indication that there is a data validation collision with existing data, and validation
is similarly null, so I'm not certain where the problem exists, as it appears that it is being successfully moved, but is not retained on save.
full context of use
....
{
using (var package = new ExcelPackage(ms))
{
using (var excelFile = new ExcelPackage(new FileInfo(path)))
{
foreach (var ws in excelFile.Workbook.Worksheets.ToList())
{
package.Workbook.Worksheets.Add(ws.Name, ws);
}
}
UpdateTemplate(package.Workbook.Worksheets.First());
package.Workbook.Worksheets.First().Select();
package.Save();
}
}
private static void UpdateTemplate(ExcelWorksheet ws)
{
ws.Comments.Remove(ws.Cells["D1"].Comment);
ws.DeleteColumn(4);
ws.Cells["A1"].Value = ImportConstants.PendingPromptIdentifier;
ws.Cells["B1"].Value = "Asset Name";
//Insert TRC
ws.InsertColumn(3, 1);
var cellRef = ws.Cells["C1"];
cellRef.SetDefaultHeaderStyle();
cellRef.Value = "TRC";
cellRef = ws.Cells["D1"];
cellRef.Value = ImportConstants.PendingAssetType;
var lastCol = ws.Dimension.End.Column;
ws.Cells[1, lastCol].Value = ImportConstants.PendingFmvReeval;
//Insert Current 704b
ws.InsertColumn(lastCol, 1);
cellRef = ws.Cells[1, lastCol];
cellRef.Value = ImportConstants.PendingCurrentBasis;
cellRef.SetDefaultHeaderStyle();
cellRef.Style.Fill.BackgroundColor.SetColor(0, 217, 217, 217);
cellRef.Style.Font.Color.SetColor(Color.Black);
MoveDataValidation(ws, 'C', 'D');
}
Interrogating the cells shows that it has all the expected values without attempting to move data validation, but the output file has them on the position of the inserted cell
In an attempt to work around this, I have added the inserted cell to my template as a hidden field, and now the data validation is correct in one case, but every other data validation has been shifted right once.