Version

Applying Styles to Cells

Before You Begin

The ability to apply styles to cells is one of the ways the Infragistics.Documents.Excel assembly allows you to customize your worksheets. Every aspect of the cell can be customized and each cell can appear differently. You can control the font used in the cell, the call background and borders, and the placement and rotation of text. You can even use different formats for different pieces of text in the same cell.

Most styles can be applied by setting properties on the CellFormat property of WorksheetCell , WorksheetRow , WorksheetColumn , and WorksheetMergedCellsRegion .

What You Will Accomplish

This walkthrough will show you how to apply a variety of styles to the cells of a worksheet.

Follow these Steps

  1. Create a workbook with a worksheet.

    1. Create a new Visual Basic or C# project .

    2. Add a Button to the form.

    3. Double-click the Button to open the code-behind for its Click event.

    4. Create a Workbook with one Worksheet:

In Visual Basic:

Dim workbook As New Infragistics.Documents.Excel.Workbook()
Dim worksheet As Infragistics.Documents.Excel.Worksheet = _
  workbook.Worksheets.Add("Sheet1")

In C#:

Infragistics.Documents.Excel.Workbook workbook = new Infragistics.Documents.Excel.Workbook();
Infragistics.Documents.Excel.Worksheet worksheet = workbook.Worksheets.Add( "Sheet1" );

  1. Increase the width of the first column so all text for the cells is visible:

In Visual Basic:

worksheet.Columns.Item(0).Width = 6000

In C#:

worksheet.Columns[0].Width = 6000;

  1. Apply the styles to the cells.

    1. Change the horizontal alignment of a cell so the value is centered in the cell:

In Visual Basic:

worksheet.Rows.Item(0).Cells.Item(0).Value = "Center"
worksheet.Rows.Item(0).Cells.Item(0).CellFormat.Alignment = _
  Infragistics.Documents.Excel.HorizontalCellAlignment.Center

In C#:

worksheet.Rows[0].Cells[0].Value = "Center";
worksheet.Rows[0].Cells[0].CellFormat.Alignment =
  Infragistics.Documents.Excel.HorizontalCellAlignment.Center;

  1. Give the cell different border styles and colors to separate it from other cells:

In Visual Basic:

worksheet.Rows.Item(1).Cells.Item(0).CellFormat.BottomBorderColor = Color.Red
worksheet.Rows.Item(1).Cells.Item(0).CellFormat.BottomBorderStyle = _
  Infragistics.Documents.Excel.CellBorderLineStyle.DashDot
worksheet.Rows.Item(1).Cells.Item(0).CellFormat.LeftBorderColor = Color.Yellow
worksheet.Rows.Item(1).Cells.Item(0).CellFormat.LeftBorderStyle = _
  Infragistics.Documents.Excel.CellBorderLineStyle.Thick
worksheet.Rows.Item(1).Cells.Item(0).CellFormat.RightBorderColor = Color.Orange
worksheet.Rows.Item(1).Cells.Item(0).CellFormat.RightBorderStyle = _
  Infragistics.Documents.Excel.CellBorderLineStyle.Thin
worksheet.Rows.Item(1).Cells.Item(0).CellFormat.TopBorderColor = Color.Blue
worksheet.Rows.Item(1).Cells.Item(0).CellFormat.TopBorderStyle = _
  Infragistics.Documents.Excel.CellBorderLineStyle.Double

In C#:

worksheet.Rows[1].Cells[0].CellFormat.BottomBorderColor = Color.Red;
worksheet.Rows[1].Cells[0].CellFormat.BottomBorderStyle =
  Infragistics.Documents.Excel.CellBorderLineStyle.DashDot;
worksheet.Rows[1].Cells[0].CellFormat.LeftBorderColor = Color.Yellow;
worksheet.Rows[1].Cells[0].CellFormat.LeftBorderStyle =
  Infragistics.Documents.Excel.CellBorderLineStyle.Thick;
worksheet.Rows[1].Cells[0].CellFormat.RightBorderColor = Color.Orange;
worksheet.Rows[1].Cells[0].CellFormat.RightBorderStyle =
  Infragistics.Documents.Excel.CellBorderLineStyle.Thin;
worksheet.Rows[1].Cells[0].CellFormat.TopBorderColor = Color.Blue;
worksheet.Rows[1].Cells[0].CellFormat.TopBorderStyle =
  Infragistics.Documents.Excel.CellBorderLineStyle.Double;

  1. Apply a background style to a cell to it stands out:

In Visual Basic:

worksheet.Rows.Item(2).Cells.Item(0).CellFormat.FillPattern = _
  Infragistics.Documents.Excel.FillPatternStyle.DiagonalCrosshatch
worksheet.Rows.Item(2).Cells.Item(0).CellFormat.FillPatternBackgroundColor = _
  Color.Lime
worksheet.Rows.Item(2).Cells.Item(0).CellFormat.FillPatternForegroundColor = _
  Color.Gray

In C#:

worksheet.Rows[2].Cells[0].CellFormat.FillPattern =
  Infragistics.Documents.Excel.FillPatternStyle.DiagonalCrosshatch;
worksheet.Rows[2].Cells[0].CellFormat.FillPatternBackgroundColor =
  Color.Lime;
worksheet.Rows[2].Cells[0].CellFormat.FillPatternForegroundColor =
  Color.Gray;

  1. Change the font of a cell so its value appears differently:

In Visual Basic:

worksheet.Rows.Item(3).Cells.Item(0).Value = 57
worksheet.Rows.Item(3).Cells.Item(0).CellFormat.Font.Bold = _
  Infragistics.Documents.Excel.ExcelDefaultableBoolean.True
worksheet.Rows.Item(3).Cells.Item(0).CellFormat.Font.UnderlineStyle = _
  Infragistics.Documents.Excel.FontUnderlineStyle.Double

In C#:

worksheet.Rows[3].Cells[0].Value = 57;
worksheet.Rows[3].Cells[0].CellFormat.Font.Bold =
  Infragistics.Documents.Excel.ExcelDefaultableBoolean.True;
worksheet.Rows[3].Cells[0].CellFormat.Font.UnderlineStyle =
  Infragistics.Documents.Excel.FontUnderlineStyle.Double;

  1. Apply a format string to a cell so the type of value displayed is easily recognized (the following cell is used to display currency):

In Visual Basic:

worksheet.Rows.Item(4).Cells.Item(0).Value = -4357.059
worksheet.Rows.Item(4).Cells.Item(0).CellFormat.FormatString = _
  """$""#,##0.00_);[Red](""$""#,##0.00)"

In C#:

worksheet.Rows[4].Cells[0].Value = -4357.059;
worksheet.Rows[4].Cells[0].CellFormat.FormatString =
  "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)";

  1. Indent text in a cell:

In Visual Basic:

worksheet.Rows.Item(5).Cells.Item(0).Value = "Intented"
worksheet.Rows.Item(5).Cells.Item(0).CellFormat.Indent = 2

In C#:

worksheet.Rows[5].Cells[0].Value = "Intented";
worksheet.Rows[5].Cells[0].CellFormat.Indent = 2;

  1. Rotate text in a cell:

In Visual Basic:

worksheet.Rows.Item(6).Cells.Item(0).Value = "Rotated"
worksheet.Rows.Item(6).Cells.Item(0).CellFormat.Rotation = 45

In C#:

worksheet.Rows[6].Cells[0].Value = "Rotated";
worksheet.Rows[6].Cells[0].CellFormat.Rotation = 45;

  1. Shrink text to fit in the cell:

In Visual Basic:

worksheet.Rows.Item(7).Cells.Item(0).Value = _
  "Shrink text so it is all visible in the cell"
worksheet.Rows.Item(7).Cells.Item(0).CellFormat.ShrinkToFit = _
  Infragistics.Documents.Excel.ExcelDefaultableBoolean.True

In C#:

worksheet.Rows[7].Cells[0].Value =
  "Shrink text so it is all visible in the cell";
worksheet.Rows[7].Cells[0].CellFormat.ShrinkToFit =
  Infragistics.Documents.Excel.ExcelDefaultableBoolean.True;

  1. Change the vertical alignment of a cell so the value appears at the top of the cell when it does not have a default height:

In Visual Basic:

worksheet.Rows.Item(8).Height = 500
worksheet.Rows.Item(8).Cells.Item(0).Value = "Top"
worksheet.Rows.Item(8).Cells.Item(0).CellFormat.VerticalAlignment = _
  Infragistics.Documents.Excel.VerticalCellAlignment.Top

In C#:

worksheet.Rows[8].Height = 500;
worksheet.Rows[8].Cells[0].Value = "Top";
worksheet.Rows[8].Cells[0].CellFormat.VerticalAlignment =
  Infragistics.Documents.Excel.VerticalCellAlignment.Top;

  1. Wrap the text in a cell so it does hang over into the next cell or get cut off:

In Visual Basic:

worksheet.Rows.Item(9).Cells.Item(0).Value = _
  "This text has been wrapped."
worksheet.Rows.Item(9).Cells.Item(0).CellFormat.WrapText = _
  Infragistics.Documents.Excel.ExcelDefaultableBoolean.True

In C#:

worksheet.Rows[9].Cells[0].Value =
  "This text has been wrapped.";
worksheet.Rows[9].Cells[0].CellFormat.WrapText =
  Infragistics.Documents.Excel.ExcelDefaultableBoolean.True;

  1. Apply mixed formatting to the text in a cell using a FormattedString object:

In Visual Basic:

Dim formattedString As Infragistics.Documents.Excel.FormattedString = _
  New Infragistics.Documents.Excel.FormattedString("Formatted String")
worksheet.Rows.Item(10).Cells.Item(0).Value = formattedString
Dim font1 As Infragistics.Documents.Excel.FormattedStringFont = _
  formattedString.GetFont(3, 6)
font1.Color = Color.Red
font1.UnderlineStyle = Infragistics.Documents.Excel.FontUnderlineStyle.Single
font1.Height = 300
Dim font2 As Infragistics.Documents.Excel.FormattedStringFont = _
  formattedString.GetFont(10)
font2.Bold = Infragistics.Documents.Excel.ExcelDefaultableBoolean.True
font2.Italic = Infragistics.Documents.Excel.ExcelDefaultableBoolean.True
font2.Strikeout = Infragistics.Documents.Excel.ExcelDefaultableBoolean.True

In C#:

Infragistics.Documents.Excel.FormattedString formattedString =
  new Infragistics.Documents.Excel.FormattedString( "Formatted String" );
worksheet.Rows[10].Cells[0].Value = formattedString;
Infragistics.Documents.Excel.FormattedStringFont font1 = formattedString.GetFont( 3, 6 );
font1.Color = Color.Red;
font1.UnderlineStyle = Infragistics.Documents.Excel.FontUnderlineStyle.Single;
font1.Height = 300;
Infragistics.Documents.Excel.FormattedStringFont font2 = formattedString.GetFont( 10 );
font2.Bold = Infragistics.Documents.Excel.ExcelDefaultableBoolean.True;
font2.Italic = Infragistics.Documents.Excel.ExcelDefaultableBoolean.True;
font2.Strikeout = Infragistics.Documents.Excel.ExcelDefaultableBoolean.True;

  1. Serialize the workbook.

Write the workbook to a file:

In Visual Basic:

workbook.Serialize("C:\Styles.xls")

In C#:

workbook.Serialize( "C:\\Styles.xls" );
Displays the results of using the code listed above.