Using Styles and SheetViews in Workbooks for .NET

Learn more about Xeed Workbooks for .NET

 

Now that we have covered the basics of creating an xlsx document, and learned how to add to and modify the contents of a Worksheet including Cells and Tables, let's look into how to make it look nice by taking advantage of Styles and SheetViews!

Note:

• At the time of this writing, more options have been added to the Style and SheetView classes and will be included in a future release of Xceed Workbooks for .NET.
We will cover these new options in a part 2 article once they are available.

 

What is a Style?

Simply put, a Style is a group of settings to change how cells look, these can be applied to a specific Cell, the cells in a Column, or the cells in a Row.

Specifically, these settings currently include:

• Font: the Font to use for the current Style.

• Alignment: the horizontal and vertical alignments used to align the content of a cell.

• CustomFormat: the format used to display the content of a Cell (null by default, setting this property will overwrite the PredefinedNumberFormatId property)

• PredefinedNumberFormatId: the predefined number format with an Id (0 by default, setting this property will overwrite the CustomFormat property)

Taking advantage of Styles is a great way to have consistent formatting in your Workbooks and Worksheets.

 

Applying a Style

A Style can be specified either on a Cell, cells from a Column, or cells from a Row.

This however brings up the following question: what if we have a Cell affected by more than one Style? For example, we have a Style on Column B and also a Style on the 5th Row, in this situation which Style would be used for the Cell at "B5"? The priority is determined in the following order: Cell, Row, Column. This means that in our example, if there is no Style applied directly to the Cell in "B5", the one at the Row level would be used.

 

The examples below assume the following initial code layout:

using( var document = Workbook.Load( "testDoc.xlsx" ));
{
	var worksheet = document.Worksheets[ 0 ];
		
	// Styling a specific Cell using its address
	worksheet.Cells[ "B2" ].Style. [...]

	// Styling a specific Cell using its row and column index
	worksheet.Cells[ 1, 1 ].Style. [...]

	// Styling the Cells of a Column
	worksheet.Columns[ 2 ].Style. [...]

	// Styling the Cells of a Row
	worksheet.Rows[ 3 ].Style. [...]

	document.Save();
}

 

Font (available properties here)

// Specific Cell
worksheet.Cells[ "B1" ].Style.Font = new Font() { Bold = true, Size = 15.5d };

// Column
worksheet.Columns[ 2 ].Style.Font = new Font() { Name = "Lucida Fax", Italic = true, Underline = true, UnderlineType = UnderlineType.Double };

// Row
worksheet.Rows[ 3 ].Style.Font = new Font() { Strikethrough = true, Size = 18d };

 

Alignment (available values for HorizontalAlignment and VerticalAlignment)

// Specific Cell
worksheet.Cells[ "B1" ].Style.Alignment.Horizontal = HorizontalAlignment.Center;
worksheet.Cells[ "B1" ].Style.Alignment.Vertical = VerticalAlignment.Center;

// Column
worksheet.Columns[ 2 ].Style.Alignment = new Alignment( HorizontalAlignment.Justify, VerticalAlignment.Center);

// Row
worksheet.Rows[ 3 ].Style.Alignment.Horizontal = HorizontalAlignment.CenterAcrossSelection;

 

CustomFormat

// Cell B2 contains 25.6 displayed as "$25.6000"
worksheet.Cells[ 1, 1 ].Value = 25.6;
worksheet.Cells[ 1, 1 ].Style.CustomFormat = "$0.0000";

// Cell C3 contains a date formatted to display as "10:22 AM"
worksheet.Cells[ 2, 2 ].Value = new DateTime( 2021, 7, 1, 10, 22, 33 );
worksheet.Cells[ 2, 2 ].Style.CustomFormat = "h:mm AM/PM";

// Column
worksheet.Columns[ 2 ].Style.CustomFormat = "h:mm AM/PM";

// Row
worksheet.Rows[ 3 ].Style.CustomFormat = "$0.0000";

 

PredefinedNumberFormatId (available values here)

// Cell D4 contains 33.5 set to format "0.00" to display the value as "33.50"
worksheet.Cells[ "D4" ].Value = 33.5;
worksheet.Cells[ "D4" ].Style.PredefinedNumberFormatId = 2;

// Column with dates formatted as mm-dd-yy
worksheet.Columns[ 2 ].Style.PredefinedNumberFormatId = 14;

// Row with time formatted as [h]:mm:ss
worksheet.Rows[ 3 ].Style.PredefinedNumberFormatId = 46;

 

What is a SheetView?

A SheetView is a way of allowing the creation of customized views in a Worksheet.

The SheetView class represents a single sheet view from a Worksheet.

Note:

• At the time of writing, only one SheetView per Worksheet is supported.

 

Using a SheetView

The SheetView class currently supports the following properties:

• ActiveCellAddress: the active Cell in the Worksheet, which is the one with focus.

• TopLeftCellAddress: the Cell to be displayed as the top left position in the Worksheet view.

 

// Setting a SheetView
	using( var document = Workbook.Load( "testDoc.xlsx" ));
	{
		var worksheet = doc.Worksheets[ 0 ];        
		worksheet.SheetView.ActiveCellAddress = "C2";
		worksheet.SheetView.TopLeftCellAddress = "B1";
		document.Save();
	}

 

For more information, please refer to the documentation.