Formatting in Windows Forms Spreadsheet
9 Oct 20238 minutes to read
This section explains about the formatting options similar to excel in Spreadsheet.
Styles and formats defined in an Excel file are automatically imported. Users can also apply these settings to cells during run time. The following are the formatting attributes for the 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
For applying background color for the cells at runtime in Spreadsheet, set the color index for the particular XlsIO range and invalidate the range in order to update the view in SpreadsheetGrid.
For single cell
IRange range = spreadsheet.ActiveSheet.Range["A5"];
range.CellStyle.ColorIndex = Syncfusion.XlsIO.ExcelKnownColors.Blue;
spreadsheet.ActiveGrid.InvalidateCell(range.Row, range.Column);For 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
Spreadsheet allows the user to apply the font settings such as font color, font name ,font size etc., 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 in view,
spreadsheet.ActiveGrid.InvalidateCell(gridRange, true);Cell Borders
Spreadsheet allows the user to apply the borders at runtime for 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
Spreadsheet allows the user to align the content of the cell. The alignment options includes 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
Spreadsheet allows the user to wrap the text in the cell, if 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
Spreadsheet provides support to merge two or more cells. When a group of cells is merged, the contents of the upper-left cell will be taken as the content of the merged cell, rest will be deleted.
For merging the cells in Spreadsheet, you need to add the CoveredCellInfo into CoveredCells collection of SpreadsheetGrid and merge the range using Merge method in XlsIO. Also to update the view, you need to 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 Spreadsheet.
For unmerging the cells in Spreadsheet, you need to clear the CoveredCells from the SpreadsheetGrid and unmerge the range using UnMerge method in XlsIO. Also to update the view, you need to 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
Spreadsheet allows the user to view the numbers in the cells with different formats which includes currency, percentage, datetime, scientific etc.,
//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 its 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
Spreadsheet supports some predefined built in styles of XlsIO. BuiltInStyles is an enum which 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
Spreadsheet allows the users to format a table with built in styles of table (i.e.) 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
Spreadsheet provides support to clear the contents of a cell along with its formatting or by specifying the required clear options using ExcelClearOptions enum which specifies the possible directions to clear the cell formats, content, comments,conditional format,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);