Formatting in UWP Spreadsheet (SfSpreadsheet)

23 Jul 20268 minutes to read

This section explains the formatting options similar to Excel in SfSpreadsheet.

Styles and formats defined in an Excel file are automatically imported. You can also apply these settings to cells at run time. The following are the formatting attributes for a cell.

  • Cell font settings (font name, size, color, style, etc.)
  • Cell background
  • Cell content alignment (vertical and horizontal alignment, indent and text wrapping)
  • Cell borders
  • Number Formatting
  • Merge Cells
  • Built-in Styles
  • Table Formats

Cell Background

To apply background color to the cells at runtime in SfSpreadsheet, set the color index for the particular XlsIO range and invalidate the range in order to update the view in SpreadsheetGrid.

For a single cell,

IRange range = spreadsheet.ActiveSheet.Range["A5"];
range.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.Blue;
spreadsheet.ActiveGrid.InvalidateCell(range.Row, range.Column);

For a selected range of cells,

var selectedRanges = spreadsheet.ActiveGrid.SelectedRanges;

foreach (var range in selectedRanges)
{
  string cell = GridExcelHelper.ConvertGridRangeToExcelRange(range, spreadsheet.ActiveGrid);
  spreadsheet.ActiveSheet.Range[cell].CellStyle.ColorIndex = ExcelKnownColors.Blue;
  spreadsheet.ActiveGrid.InvalidateCell(range, true);
}

Font

SfSpreadsheet allows the user to apply font settings such as font color, font name, font size, and so on, for a particular cell or a range of cells.

IRange range = spreadsheet.Workbook.Worksheets[0].Range["A1:B5"];
var gridRange = GridExcelHelper.ConvertExcelRangeToGridRange(range);

//Setting the Font Family Name,
range.CellStyle.Font.FontName = "Arial Black";

//Setting the Font Styles,
range.CellStyle.Font.Bold = true;
range.CellStyle.Font.Italic = true;

//Setting the Font Size,
range.CellStyle.Font.Size = 18;

//Setting the Font Effects,
range.CellStyle.Font.Strikethrough = true;

//Setting the UnderLine Types,
range.CellStyle.Font.Underline = ExcelUnderline.Single;

//Setting the Font Color,
range.CellStyle.Font.Color = ExcelKnownColors.Blue;

//Invalidating the range to update the view,
spreadsheet.ActiveGrid.InvalidateCell(gridRange, true);

Cell Borders

SfSpreadsheet allows the user to apply borders at runtime for a particular cell or range of cells,

//For a single cell,
IRange range = spreadsheet.Workbook.Worksheets[0].Range["A5"];
range.Borders.LineStyle = ExcelLineStyle.Dash_dot;
range.Borders.Color = ExcelKnownColors.Gold;
spreadsheet.ActiveGrid.InvalidateCell(range.Row, range.Column);

//For a range of cells,
IRange excelRange = spreadsheet.Workbook.Worksheets[0].Range["C3:D8"];
excelRange.BorderAround(ExcelLineStyle.Double, ExcelKnownColors.Green);
excelRange.BorderInside(ExcelLineStyle.Dotted, ExcelKnownColors.Tan);
var gridRange = GridExcelHelper.ConvertExcelRangeToGridRange(excelRange);
spreadsheet.ActiveGrid.InvalidateCell(gridRange, true);

Cell Alignment

SfSpreadsheet allows the user to align the content of the cell. The alignment options include Horizontal Alignment, Vertical Alignment, Indentation, Orientation etc.

//Applying Horizontal Alignment for the cell "A2",
spreadsheet.Workbook.Worksheets[0].Range["A2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
spreadsheet.ActiveGrid.InvalidateCell(2,1);

//Applying Vertical Alignment for the cell "B2",
spreadsheet.Workbook.Worksheets[0].Range["B2"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignBottom;
spreadsheet.ActiveGrid.InvalidateCell(2,2);

//Applying Orientation for the selected cell or ranges,
spreadsheet.FormatOrientation(90);

//For Indentation,

//Increase the indent for the selected ranges or cell,
spreadsheet.FormatIndent(true);

//Decrease the indent for the selected ranges or cell,
spreadsheet.FormatIndent(false);

//Level of indent for selected ranges or cell,
spreadsheet.FormatIndentLevel(3);

Wrap Text

SfSpreadsheet allows the user to wrap the text in a cell when the text is too large.

spreadsheet.ActiveSheet.Range["C4"].Text = "Wrapping the content in the cell";
spreadsheet.ActiveSheet.Range["C4"].WrapText = true;
spreadsheet.ActiveSheet.AutofitRow(4);
spreadsheet.ActiveGrid.SetRowHeight(4, 4, spreadsheet.ActiveSheet.GetRowHeightInPixels(4));
spreadsheet.ActiveGrid.InvalidateCell(4, 3);

Merge Cells

Merge

SfSpreadsheet provides support to merge two or more cells. When a group of cells is merged, the contents of the upper-left cell are taken as the content of the merged cell, and the rest are deleted.

To merge the cells in SfSpreadsheet, add the CoveredCellInfo to the CoveredCells collection of SpreadsheetGrid and merge the range using the Merge method in XlsIO. To update the view, invalidate the cells in the SpreadsheetGrid.

var gridRange = spreadsheet.ActiveGrid.SelectedRanges.ActiveRange;
var excelRange = gridRange.ConvertGridRangeToExcelRange(spreadsheet.ActiveGrid);
var coverCell = new CoveredCellInfo(gridRange.Top, gridRange.Left, gridRange.Bottom, gridRange.Right);

spreadsheet.ActiveGrid.CoveredCells.Add(coverCell);
spreadsheet.ActiveSheet.Range[excelRange].Merge();
spreadsheet.ActiveGrid.InvalidateCell(gridRange, true);

Unmerge

You can also unmerge the merged cells in SfSpreadsheet.

To unmerge the cells in SfSpreadsheet, clear the CoveredCells from the SpreadsheetGrid and unmerge the range using the UnMerge method in XlsIO. To update the view, invalidate the cells in the SpreadsheetGrid.

var gridRange = spreadsheet.ActiveGrid.SelectedRanges.ActiveRange;
var excelRange = gridRange.ConvertGridRangeToExcelRange(spreadsheet.ActiveGrid);

spreadsheet.ActiveGrid.CoveredCells.Clear(gridRange);
spreadsheet.ActiveSheet.Range[excelRange].UnMerge();
spreadsheet.ActiveGrid.InvalidateCell(gridRange, true);

Number Format

SfSpreadsheet allows the user to view the numbers in cells with different formats, including currency, percentage, date-time, scientific, and so on.

//Applying Percentage format for the selected ranges at runtime,
spreadsheet.Workbook.ActiveSheet.Range["C3"].NumberFormat = "0.00%";
spreadsheet.ActiveGrid.InvalidateCell(3,3);

//Applying Date format for the selected ranges at runtime,
spreadsheet.Workbook.ActiveSheet.Range["D1"].NumberFormat = "m/d/yyyy";
spreadsheet.ActiveGrid.InvalidateCell(1,4);
  
//Applying Time format for the selected ranges at runtime,
spreadsheet.Workbook.ActiveSheet.Range["D4"].NumberFormat = "[$-F400]h:mm:ss AM/PM";
spreadsheet.ActiveGrid.InvalidateCell(3,4);

The different types of number formats with their notation are

Formats Notation
General General
Number 0.00
Currency $* #,##0.00
Accounting $* (#,##0.00);$* -??;@
Short Date m/d/yyyy
Long Date [$-F800]dddd, mmmm dd, yyyy
Time [$-F400]h:mm:ss AM/PM
Percentage 0.00%
Fraction #?/?
Scientific 0.00E+00

Built-in Styles

SfSpreadsheet supports some predefined built-in styles of XlsIO. BuiltInStyles is an enum that contains different styles for formatting a cell or range of cells.

spreadsheet.Workbook.ActiveSheet.Range["A3"].BuiltInStyle = BuiltInStyles.Heading2;
spreadsheet.ActiveGrid.InvalidateCell(3, 1);

Format as Table

SfSpreadsheet allows the user to format a table with the built-in table styles (TableBuiltInStyles) of XlsIO.

// Creating a table
IListObject table = spreadsheet.Workbook.ActiveSheet.ListObjects.Create("Table1", spreadsheet.Workbook.ActiveSheet.Range["C1:G5"]);

// Formatting table with a built-in style
table.BuiltInTableStyle = TableBuiltInStyles.TableStyleLight6;
spreadsheet.ActiveGrid.InvalidateCells();

For more information regarding formatting options, please go through XlsIO.

NOTE

Users need to refresh the view after the formatting is applied on the XlsIO range to update the styles in SpreadsheetGrid.

Clear formatting

SfSpreadsheet provides support to clear the contents of a cell along with its formatting. You can also specify the required clear options using the ExcelClearOptions enum, which specifies the possible directions to clear the cell formats, content, comments, conditional formats, data validation, or clear all of them.

//To clear the contents along with its formatting in the range,   
spreadsheet.Workbook.Worksheets[0].Range[4, 5].Clear(true);

//To clear the range with specified ExcelClearOptions,
spreadsheet.Workbook.Worksheets[0].Range[4, 5].Clear(ExcelClearOptions.ClearConditionalFormats);