ConditionalFormatting.aspx.vb
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports ExpertXls.ExcelLib
''' <summary>
''' Conditional Formatting: the workbook code is in App_Code\ConditionalFormattingDemo.vb.
''' </summary>
Partial Class ConditionalFormattingPage
Inherits Page
' AutoEventWireup="true" in the page directive connects Page_Load to the Load event
Protected Sub Page_Load(sender As Object, e As EventArgs)
End Sub
Protected Sub buttonCreate_Click(sender As Object, e As EventArgs)
Dim format As ExcelWorkbookFormat = If(radioXlsx.Checked, ExcelWorkbookFormat.Xlsx_2007, ExcelWorkbookFormat.Xls_2003)
Dim workbookBytes As Byte()
Try
' create the workbook in memory; the input files are in the folders of the web site
Using stream As New MemoryStream()
ConditionalFormattingDemo.CreateWorkbook(format, Server.MapPath("~"), stream)
workbookBytes = stream.ToArray()
End Using
Catch ex As Exception
labelError.Text = "The workbook could not be created: " & Server.HtmlEncode(ex.Message)
Return
End Try
WorkbookDownload.Send(Response, workbookBytes, ConditionalFormattingDemo.OutputName & DemoLayout.GetExtension(format), format)
End Sub
End Class
App_Code/ConditionalFormattingDemo.vb
Imports System.Drawing
Imports System.IO
Imports ExpertXls.ExcelLib
''' <summary>
''' Adds conditional formatting to a sales table: cell value rules and a formula rule that change the font and
''' fill of the cells that meet a condition, a color scale, data bars and an icon set. Excel evaluates the rules
''' when the workbook is displayed, so the formatting follows the values when they are changed. In the Excel
''' 2007+ format the demo also adds sparklines, small charts drawn in cells.
''' </summary>
Public Module ConditionalFormattingDemo
''' <summary>The name of the generated file, without extension.</summary>
Public Const OutputName As String = "ConditionalFormatting"
''' <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 = "Conditional formatting"
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)
' the first worksheet: rules that format the cells meeting a condition
Dim rules As ExcelWorksheet = workbook.Worksheets(0)
rules.Name = "Rules"
DemoLayout.SetupPage(rules, logo, ExcelPageOrientation.Landscape)
DemoLayout.WriteTitle(rules, "A2:G3", "Conditional Formatting Rules", titleStyle)
DemoLayout.WriteSection(rules, "A5:G5", "Green: quarters over 1,000. Red: quarters under 300. Bold blue: a total over 4,000.", sectionStyle)
DemoLayout.WriteSalesTable(rules, 6, 1)
' the quarter values greater than 1000 and less than 300
Dim quarters As ExcelRange = rules("C7:F18")
Dim high As ExcelConditionalFormat = quarters.ConditionalFormats.AddCellValueRule(ExcelConditionalFormatOperator.GreaterThan, "1000")
high.BackColor = Color.FromArgb(198, 239, 206)
high.FontColor = Color.FromArgb(0, 97, 0)
high.Bold = True
Dim low As ExcelConditionalFormat = quarters.ConditionalFormats.AddCellValueRule(ExcelConditionalFormatOperator.LessThan, "300")
low.BackColor = Color.FromArgb(255, 199, 206)
low.FontColor = Color.FromArgb(156, 0, 6)
' a formula rule: the product name is highlighted when the total of its row is over 4000;
' the relative reference $G7 is evaluated for each row of the range
Dim topProducts As ExcelConditionalFormat = rules("A7:A18").ConditionalFormats.AddFormulaRule("$G7>4000")
topProducts.FontColor = Color.FromArgb(0, 51, 153)
topProducts.Bold = True
' the totals between two values
Dim middle As ExcelConditionalFormat = rules("G7:G18").ConditionalFormats.AddCellValueRule(
ExcelConditionalFormatOperator.Between, "2000", "3000")
middle.BackColor = Color.FromArgb(255, 235, 156)
middle.Italic = True
' the second worksheet: color scales, data bars and icon sets visualize the values
Dim visuals As ExcelWorksheet = workbook.Worksheets.AddWorksheet("Visualizations")
DemoLayout.SetupPage(visuals, logo, ExcelPageOrientation.Landscape)
DemoLayout.WriteTitle(visuals, "A2:I3", "Color Scales, Data Bars and Icon Sets", titleStyle)
DemoLayout.WriteSection(visuals, "A5:I5", "Color scale on the quarters, data bars on the totals, icons on the growth from Q1 to Q4.", sectionStyle)
DemoLayout.WriteSalesTable(visuals, 6, 1)
' the growth from Q1 to Q4 of each product, displayed as a percent
visuals("H6").Text = "Growth"
visuals("H6").Style.Font.Bold = True
For row As Integer = 7 To 18
visuals(row, 8).Formula = "=(F" & row & "-C" & row & ")/C" & row
Next
visuals("H7:H18").Style.Number.NumberFormatString = "0.0%"
' a three color scale: red for the lowest values, yellow in the middle, green for the highest
visuals("C7:F18").ConditionalFormats.AddColorScale(Color.FromArgb(248, 105, 107), Color.FromArgb(255, 235, 132), Color.FromArgb(99, 190, 123))
' data bars proportional to the totals
visuals("G7:G18").ConditionalFormats.AddDataBar(Color.FromArgb(99, 142, 198))
' three arrows: up for the highest growth, sideways, down for the lowest
visuals("H7:H18").ConditionalFormats.AddIconSet(ExcelConditionalFormatIconSet.ThreeArrows)
visuals("I6").Text = "Trend"
visuals("I6").Style.Font.Bold = True
visuals("I6").ColumnWidthInChars = 16
If format = ExcelWorkbookFormat.Xlsx_2007 Then
' a line sparkline in each cell of column I, drawn from the four quarters of the row
Dim trend As ExcelSparklineGroup = visuals.SparklineGroups.Add(ExcelSparklineType.Line, visuals("C7:F18"), visuals("I7:I18"))
trend.ShowMarkers = True
trend.ShowHighPoint = True
trend.HighPointColor = Color.FromArgb(0, 176, 80)
' a column sparkline under each quarter, drawn from the 12 products
visuals("B19").Text = "By product"
visuals.SparklineGroups.Add(ExcelSparklineType.Column, visuals("C7:F18"), visuals("C19:F19"))
visuals("A19").RowHeightInPoints = 30
Else
visuals("I7").Text = "Sparklines need the Excel 2007+ (.xlsx) format"
visuals("I7").Style.Font.Italic = True
End If
workbook.Save(output)
Finally
workbook.Close()
logo.Dispose()
End Try
End Sub
End Module