Skip to content
This repository has been archived by the owner on Mar 9, 2020. It is now read-only.

Worksheet.InsertColumn interaction with Worksheet.DeleteColumn unclear with respect to DataValidation #601

Open
Prinsn opened this issue Jan 3, 2020 · 0 comments

Comments

@Prinsn
Copy link

Prinsn commented Jan 3, 2020

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.

@Prinsn Prinsn changed the title Worksheet.InsertColumn doesn't shift DataValidation Worksheet.InsertColumn interaction with Worksheet.DeleteColumn unclear with respect to DataValidation Jan 3, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant