EPPlus.Samples.VB/03-Styling/02-Conditional formatting/BlanksErrorsAndDuplicates.vb at master · EPPlusSoftware/EPPlus.Samples.VB · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
Imports OfficeOpenXml
Imports OfficeOpenXml.Style
Imports System.Drawing

Namespace EPPlusSamples.ConditionalFormatting
    Friend Class BlanksErrorsAndDuplicates
        Public Shared Sub Run(ByVal pck As ExcelPackage)
            Dim sheet = pck.Workbook.Worksheets.Add("BlanksAndErrors")

            Dim address = "A1:A20"

            ' -------------------------------------------------------------------
            ' Create a ContainsBlanks rule
            ' -------------------------------------------------------------------
            Dim containsBlanks = sheet.ConditionalFormatting.AddContainsBlanks(address)

            containsBlanks.Style.Border.BorderAround(ExcelBorderStyle.DashDot, Color.Goldenrod)

            ' -------------------------------------------------------------------
            ' Create a NotContainsBlanks rule
            ' -------------------------------------------------------------------
            Dim noBlanks = sheet.ConditionalFormatting.AddNotContainsBlanks(address)

            noBlanks.Style.Border.Top.Style = ExcelBorderStyle.Double
            noBlanks.Style.Border.Top.Color.Color = Color.ForestGreen

            sheet.Cells("A3:A6").Formula = "Row()"

            ' -------------------------------------------------------------------
            ' Create a ContainsErrors rule
            ' -------------------------------------------------------------------
            Dim containsErrors = sheet.ConditionalFormatting.AddContainsErrors(address)

            'Add a few incorrect formulas
            sheet.Cells("A7").Formula = "I an Invalid Formula"
            sheet.Cells("A8").Formula = "SUM(1,""Nonsense"")"
            'Will show up appropriately but prompts excel to update links on opening the file
            'sheet.Cells["A9"].Formula = "SUM(1,nonExistent!J12)";

            containsErrors.Style.Border.BorderAround(ExcelBorderStyle.Thick, Color.Red)

            containsErrors.Priority = 1

            ' -------------------------------------------------------------------
            ' Create a NotContainsErrors rule
            ' -------------------------------------------------------------------
            Dim noErrors = sheet.ConditionalFormatting.AddNotContainsErrors(address)

            noErrors.Style.Border.Right.Style = ExcelBorderStyle.Double
            noErrors.Style.Border.Right.Color.Color = Color.Purple

            ' -------------------------------------------------------------------
            ' Create a DuplicateValues rule
            ' -------------------------------------------------------------------
            Dim duplicateValues = sheet.ConditionalFormatting.AddDuplicateValues(address)

            duplicateValues.Style.Fill.Style = eDxfFillStyle.PatternFill
            duplicateValues.Style.Fill.PatternType = ExcelFillStyle.Solid
            duplicateValues.Style.Fill.BackgroundColor.Color = Color.DarkOrange

        End Sub
    End Class
End Namespace