Excel spreadsheet validation / formula is not copied to a new row in the table

Question

I am currently using the EPPlus library to export large amounts of data to several tables and tables on each of these worksheets.

I managed to create a list check and make it work using the lookup table named range perfectly. However, I came across some strange behavior that I could not understand.

To get started:

I am uploading a file. I am opening a file. I select a table with a table, there are several rows in the table, there is a column for checking the list with Yes / No options to select from the drop-down list. Each row has this check.

Scenario 1:

Then I create a new row in the excel table by dragging it from the lower right corner of the excel table to create a new row. The formula was not copied to a new line. I lost confirmation 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 a list check in the Yes / No column). I THEN create a new row in the excel table by dragging it from the lower right corner of the excel table to create a new row.

The IS formula is copied to a new line, now I can insert new valid data into this line using the provided check.

The logic of my code is:

, , , (, , , , , ..). . XML, , .

:

1) , excel . - , , excel. , fomula . .

2) excel , . - , , EPPlus , , , XML, .

:

   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;  
   }

}

excel :

, , , , , . , , , .

XML XML SDK, excel 1 ( , ), excel excel.

excel XML- .

, .

EPPlus ?

: 30/04/2015. , . .

Answer 1

EPPlus, VBA , VBA script, :

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

, filldown .