Excel table validation / formula not being copied to new table row

Question

I am currently using EPPlus library to export large amounts of data to several worksheets and tables in side each of those worksheets.

I have been able to create list validation and have it working via a lookup worksheet named range perfectly fine. However, I have come across some strange behaviour which I have been unable to figure out.

To begin:

I download the file. I open the file. I select a spreadsheet with a table, there are multiple rows in the table, there is a list validation column with Options Yes/No to select from a dropdown. Each row has this list validation.

Scenario 1:

I then create a new row in the excel table, by dragging from the bottom right corner of the excel table to create the new row. The formula was not copied to the new row. I have now lost the validation for a new row in my excel table.

Scenario 2:

I delete all existing rows in the excel table, except for the first row (which still contains list validation in the Yes/No column). I THEN create a new row in the excel table by dragging from the bottom right corner of the excel table to create the new row.

The formula IS copied to the new row, I can now insert new valid data into this row by using the provided validation.

The logic of my code:

Each cell has validation applied to it by a loop which gets the kind of validation the cell needs to have (i.e number, date, list, greater than, less than etc). List validation is accessed via a named table lookup address. There is NO XML output error and the file opens fine, I can access the list validation from the cells without any problem.

Things I have tried to fix this issue:

1) Fill the range of cells, THEN create the excel table from this range. - The idea behind this is, to first have a selection of data created, then select the range and just turn it into an excel table. Default behaviour would be for new rows in a table to just copy the fomula from the row above. So this solution seems logical.

2) Create an excel table on a range of non-filled cells, then fill this range. - The idea behind this is, there could have been a bug in the way EPPlus creates a table in the worksheet, or possibly there could be an issue with order of XML elements and really was simply just an experimental change.

The code:

   var strategy = Strategy.CreateTableFirst;
   ExcelRange subRowDataRange = null;
   ExcelTable table = null;

   if (strategy == Strategy.CreateTableFirst)
   {
       subRowDataRange = worksheet.Cells[headerRowIndex, worksheet.Dimension.Start.Column, ToRow: headerRowIndex + groupedRowData.Count(), ToCol: dataFields.Count()];
       table = worksheet.Tables.Add(subRowDataRange, Name: null); // Auto generate Excel table name
       table.TableStyle = TableStyles.Light13;  
   }

   foreach (var field in dataFields)
   {
       // Headers
       if (strategy == Strategy.CreateTableFirst)
       {
           table.Columns[dataFields.IndexOf(field)].Name = field.Name;  
       }
       else
       {
           worksheet.Cells[headerRowIndex, columnIndex].Value = field.Name;    
       }

       // Help Text
       if (field.HelpText.HasValue())
       {
           worksheet.Cells[headerRowIndex, columnIndex].AddComment(field.HelpText, Author: "System");
       }

       int dataRowIndex = headerRowIndex + 1; // First row in the datatable

       if (groupedRowData.None())
       {
           worksheet.Cells[dataRowIndex, columnIndex].Set(field, owner: owner, rowIndex: null, addValidation: true);
       }

       // Add SubRows
       foreach (var rowData in groupedRowData)
       {
           worksheet.Cells[dataRowIndex, columnIndex].Set(field, owner: owner, rowIndex: rowData.Key, addValidation: true);
           dataRowIndex++;
       }

       columnIndex++;
   }

   if (strategy == Strategy.CreateTableLast)
   {
       subRowDataRange = worksheet.Cells[headerRowIndex, worksheet.Dimension.Start.Column, ToRow: worksheet.Dimension.End.Row + 1, ToCol: dataFields.Count()];
       table = worksheet.Tables.Add(subRowDataRange, Name: null);
       table.TableStyle = TableStyles.Light13;  
   }

}

This is the output table in excel after the code:

Excel Table with Data exported to excel

The funny thing is, the cell validation is copied down to the next row fine if I create the table manually and have the first row set with the data, then drag down to make a new row and it copies over fine. I'm not sure how I am going to be able to export multiple rows of data and be assured that when a user inserts a new row, validation is copied down.

I downloaded the Microsoft XML SDK to compare the excel table with 1 row (which I am then able to drag down to create a second row with copied formula) and the original downloaded excel file with many rows in the excel table.

The results are almost identical with regards to the excel table in XML output.

Excel Table Comparison XML

Also nothing seems out of place after deleting the rows and saving the file for comparison.

Exported on the right, deleted rows and saved on the left

Any EPPlus gurus have an idea?

Update: 30/04/2015. Client understands the issue and accepts it for what it is. No solution has been found.

Schalton Schalton · Accepted Answer · 2015-11-02T15:56:49

I'm not familiar with EPPlus, but I've had this issue in VBA before and was able to force the table to fill by using VBA script that looks something like this:

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range(Cells(TopRowOfTable,ColumnOfTableRow1),Cells(LastRow,ColumnOfTableRow1).Filldown

Basically just finding the last row, then using the filldown command to force the field to fill.

Excel table validation / formula not being copied to new table row

1 Answers