App_Code/CellStylesDemo.vb
Imports System.Drawing
Imports System.IO
Imports ExpertXls.ExcelLib
''' <summary>
''' Shows the parts of a cell style: solid, pattern and gradient fills, text alignment and rotation, fonts and
''' borders. A style can be a named workbook style shared by many cells or the style of a single range.
''' </summary>
Public Module CellStylesDemo
''' <summary>The name of the generated file, without extension.</summary>
Public Const OutputName As String = "CellStyles"
''' <summary>
''' Creates the workbook in the given format and saves it to the output stream.
''' </summary>
''' <param name="format">Excel 97-2003 (.xls) or Excel 2007+ (.xlsx).</param>
''' <param name="inputFolder">The folder that contains the Images folder of the sample.</param>
''' <param name="output">The stream that receives the workbook.</param>
Public Sub CreateWorkbook(format As ExcelWorkbookFormat, inputFolder As String, output As Stream)
Dim workbook As New ExcelWorkbook(format)
' workbook.LicenseKey = "your license key"
workbook.DocumentProperties.Subject = "Cell styles"
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET"
Dim logo As Image = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"))
Try
Dim titleStyle As ExcelCellStyle = DemoLayout.AddTitleStyle(workbook)
Dim sectionStyle As ExcelCellStyle = DemoLayout.AddSectionStyle(workbook)
Dim xlsx As Boolean = format = ExcelWorkbookFormat.Xlsx_2007
Dim worksheet As ExcelWorksheet = workbook.Worksheets(0)
worksheet.Name = "Cell Styles"
DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Landscape)
DemoLayout.WriteTitle(worksheet, "A2:G3", "Cell Styles Demo", titleStyle)
worksheet("A1:G1").ColumnWidthInChars = 15
' A NAMED STYLE: every cell that uses it changes when the style changes
DemoLayout.WriteSection(worksheet, "A5:C5", "A named workbook style", sectionStyle)
Dim customStyle As ExcelCellStyle = workbook.Styles.AddStyle("CustomCellStyle")
customStyle.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center
customStyle.Font.Size = 14
customStyle.Font.Bold = True
' each border of a cell can have its own color and line style
customStyle.Borders(ExcelCellBorderIndex.Top).LineStyle = ExcelCellLineStyle.Medium
customStyle.Borders(ExcelCellBorderIndex.Top).Color = Color.Green
customStyle.Borders(ExcelCellBorderIndex.Bottom).LineStyle = ExcelCellLineStyle.Medium
customStyle.Borders(ExcelCellBorderIndex.Bottom).Color = Color.Blue
customStyle.Borders(ExcelCellBorderIndex.Left).LineStyle = ExcelCellLineStyle.Medium
customStyle.Borders(ExcelCellBorderIndex.Left).Color = Color.Red
customStyle.Borders(ExcelCellBorderIndex.Right).LineStyle = ExcelCellLineStyle.Medium
customStyle.Borders(ExcelCellBorderIndex.Right).Color = Color.Orange
customStyle.Borders(ExcelCellBorderIndex.DiagonalDown).LineStyle = ExcelCellLineStyle.Thin
customStyle.Borders(ExcelCellBorderIndex.DiagonalDown).Color = Color.Orange
If xlsx Then
customStyle.Fill.FillType = ExcelCellFillType.GradientFill
customStyle.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 255, 204)
customStyle.Fill.GradientFillOptions.Color2 = Color.Orange
Else
customStyle.Fill.FillType = ExcelCellFillType.SolidFill
customStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204)
End If
worksheet("A7:G7").Style = customStyle
worksheet("A7:G7").Value = "Styled"
' FILLS
DemoLayout.WriteSection(worksheet, "A9:C9", "Solid fill", sectionStyle)
worksheet("A11:G11").Style.Fill.FillType = ExcelCellFillType.SolidFill
worksheet("A11:G11").Style.Fill.SolidFillOptions.BackColor = Color.Orange
DemoLayout.WriteSection(worksheet, "A13:C13", "Pattern fills", sectionStyle)
Dim patterns As ExcelCellFillPatternType() = {ExcelCellFillPatternType.Gray_12_5_Percent,
ExcelCellFillPatternType.DiagonalCrosshatch, ExcelCellFillPatternType.HorizontalStripe}
For i As Integer = 0 To patterns.Length - 1
Dim patternRow As ExcelRange = worksheet(15 + i, 1, 15 + i, 7)
patternRow.Style.Fill.FillType = ExcelCellFillType.PatternFill
patternRow.Style.Fill.PatternFillOptions.PatternType = patterns(i)
patternRow.Style.Fill.PatternFillOptions.BackColor = Color.FromArgb(255, 255, 204)
patternRow.Style.Fill.PatternFillOptions.PatternColor = Color.Orange
worksheet(15 + i, 1).Value = patterns(i).ToString()
Next
' ALIGNMENT AND ROTATION
DemoLayout.WriteSection(worksheet, "A19:C19", "Text alignment and rotation", sectionStyle)
Dim alignmentRow As ExcelRange = worksheet("A21:G21")
alignmentRow.RowHeightInPoints = 75
alignmentRow.Style.Borders.LineStyle = ExcelCellLineStyle.Medium
alignmentRow.Style.Borders.Color = Color.DarkBlue
alignmentRow.Style.Font.Bold = True
If xlsx Then
alignmentRow.Style.Fill.FillType = ExcelCellFillType.GradientFill
alignmentRow.Style.Fill.GradientFillOptions.Direction = ExcelGradientDirection.DiagonlDown
alignmentRow.Style.Fill.GradientFillOptions.Color1 = Color.FromArgb(255, 255, 204)
alignmentRow.Style.Fill.GradientFillOptions.Color2 = Color.Orange
Else
alignmentRow.Style.Fill.FillType = ExcelCellFillType.SolidFill
alignmentRow.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 204)
End If
SetAlignedText(worksheet("A21"), "Top Left", ExcelCellHorizontalAlignmentType.Left, ExcelCellVerticalAlignmentType.Top, 0, Color.Red)
SetAlignedText(worksheet("B21"), "Bottom Right", ExcelCellHorizontalAlignmentType.Right, ExcelCellVerticalAlignmentType.Bottom, 0, Color.Green)
SetAlignedText(worksheet("C21"), "Center", ExcelCellHorizontalAlignmentType.Center, ExcelCellVerticalAlignmentType.Center, 0, Color.Blue)
' the orientation is the text rotation in degrees, from -90 to 90
SetAlignedText(worksheet("D21"), "Up 90", ExcelCellHorizontalAlignmentType.Center, ExcelCellVerticalAlignmentType.Center, 90, Color.Orange)
SetAlignedText(worksheet("E21"), "Down 90", ExcelCellHorizontalAlignmentType.Center, ExcelCellVerticalAlignmentType.Top, -90, Color.Red)
SetAlignedText(worksheet("F21"), "Up 45", ExcelCellHorizontalAlignmentType.Center, ExcelCellVerticalAlignmentType.Center, 45, Color.Green)
worksheet("G21").Style.Alignment.WrapText = True
SetAlignedText(worksheet("G21"), "A longer text wrapped in the cell", ExcelCellHorizontalAlignmentType.Left, ExcelCellVerticalAlignmentType.Top, 0, Color.Blue)
' FONTS
DemoLayout.WriteSection(worksheet, "A23:C23", "Fonts", sectionStyle)
worksheet("A25:G25").RowHeightInPoints = 30
worksheet("A25:G25").Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center
worksheet("A25:G25").Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center
worksheet("A25:G25").Value = "Hello"
worksheet("A25").Style.Font.Size = 14
worksheet("A25").Style.Font.Color = Color.Green
worksheet("B25").Style.Font.Name = "Verdana"
worksheet("B25").Style.Font.Size = 16
worksheet("B25").Style.Font.Color = Color.Blue
worksheet("C25").Style.Font.Name = "Times New Roman"
worksheet("C25").Style.Font.Italic = True
worksheet("C25").Style.Font.Size = 16
worksheet("C25").Style.Font.Color = Color.Red
worksheet("D25").Style.Font.Bold = True
worksheet("D25").Style.Font.UnderlineType = ExcelCellUnderlineType.Double
worksheet("E25").Style.Font.UnderlineType = ExcelCellUnderlineType.DoubleAccounting
worksheet("F25").Style.Font.IsStrikethrough = True
worksheet("F25").Style.Font.Color = Color.Orange
worksheet("G25").Value = "x2"
worksheet("G25").Style.Font.IsSuperscript = True
' BORDERS: one line style for all the borders, or a line style for each border
DemoLayout.WriteSection(worksheet, "A27:C27", "Borders", sectionStyle)
worksheet("A29:G29").RowHeightInPoints = 40
Dim lineStyles As ExcelCellLineStyle() = {ExcelCellLineStyle.Thin, ExcelCellLineStyle.Medium,
ExcelCellLineStyle.Thick, ExcelCellLineStyle.Double, ExcelCellLineStyle.Dot,
ExcelCellLineStyle.DashDot, ExcelCellLineStyle.MediumDashDot}
Dim lineColors As Color() = {Color.Green, Color.DarkBlue, Color.Orange, Color.Green, Color.Blue, Color.Red, Color.Red}
For i As Integer = 0 To lineStyles.Length - 1
Dim cell As ExcelRange = worksheet(29, 1 + i)
cell.Style.Borders.LineStyle = lineStyles(i)
cell.Style.Borders.Color = lineColors(i)
cell.Value = lineStyles(i).ToString()
cell.Style.Alignment.HorizontalAlignment = ExcelCellHorizontalAlignmentType.Center
cell.Style.Alignment.VerticalAlignment = ExcelCellVerticalAlignmentType.Center
Next
worksheet("E29").Style.Borders(ExcelCellBorderIndex.DiagonalUp).LineStyle = ExcelCellLineStyle.Dot
worksheet("E29").Style.Borders(ExcelCellBorderIndex.DiagonalUp).Color = Color.Blue
workbook.Save(output)
Finally
workbook.Close()
logo.Dispose()
End Try
End Sub
Private Sub SetAlignedText(cell As ExcelRange, text As String, horizontal As ExcelCellHorizontalAlignmentType,
vertical As ExcelCellVerticalAlignmentType, orientation As Integer, color As Color)
cell.Value = text
cell.Style.Alignment.HorizontalAlignment = horizontal
cell.Style.Alignment.VerticalAlignment = vertical
cell.Style.Alignment.Orientation = orientation
cell.Style.Font.Color = color
End Sub
End Module