Cell format for newly added row/column

Cell format for newly added row/column

Posted by: Jose.Aguirre on 22 March 2021, 2:45 am EST

• 

  Jose.Aguirre

  • Post Options:
  • Link
    Copy

  Posted 22 March 2021, 2:45 am EST

  Hello,
  
  there is way how to set certain format for cells when row is added:

  spreadSheet.ActiveSheet.AddRows(spreadSheet.ActiveSheet.ActiveRowIndex + 1, 1);
  

  As in Excel, when a new row/colulmn is added, the cell format is automatically there.
  
  Thanks!

• 

  kartik.sagar

  

  • Post Options:
  • Link
    Copy

  Posted 22 March 2021, 8:41 pm EST

  Hi Jose,

  We are sorry but Spread doesn’t support this behavior by default. However, you can manually implement this functionality by copying the format from a specific Row/Column to the newly added Rows/Columns as follows:

  
  private void AddRow(object sender, RoutedEventArgs e)
  {
        int addIndex = spread.ActiveSheet.ActiveRowIndex; // position where a new row will be added
        int rowCount = 1; // Number of rows to add
        spread.ActiveSheet.AddRows(addIndex, rowCount);
        CopyRowsFormat(addIndex + 1, Enumerable.Range(addIndex, rowCount).ToList());
  }
  
  public void CopyRowsFormat(int rowFormatToCopy, List<int> copyToRows)
  {
          foreach(var copyToRow in copyToRows)
          {
                for (int col = 0; col < spread.ActiveSheet.Columns.Count - 1; col++)
                {
                      var formatter = spread.ActiveSheet.Cells[rowFormatToCopy, col].Formatter;
  
                      if (formatter == null)
                          formatter = spread.ActiveSheet.Rows[rowFormatToCopy].Formatter;
  
                      if (formatter == null)
                          continue;
  
                      spread.ActiveSheet.Cells[copyToRow, col].Formatter = formatter;
                }
          }
  }
  
  

  Please refer to the same from attached sample. (see SpreadCellFormat.zip)

  JFYI, this seems like a valid requirement so I have raised an enhancement request for the same. (Internal Tracking Id - SPNET-16131)

  Best Regards,
  
  Kartik

  SpreadCellFormat.zip

• 

  kartik.sagar

  

  • Post Options:
  • Link
    Copy

  Posted 23 March 2021, 2:50 pm EST

  Hi Jose,

  I forgot to mention that the above workaround only copies the format of a cell. In case you want to copy other properties like background/font etc then you also copy them accordingly.

  Best Regards,
  
  Kartik