Cell format for newly added row/column

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

  • 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!
  • Replied 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
  • Replied 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
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels