EPPlus.Samples.VB/03-Styling/02-Conditional formatting/DatabarsExample.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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
Imports OfficeOpenXml
Imports System.Drawing
Imports OfficeOpenXml.Drawing
Imports OfficeOpenXml.Style

Namespace EPPlusSamples.ConditionalFormatting
    Friend Class DatabarsExample
        Public Shared Sub Run(ByVal pck As ExcelPackage)
            Dim ws = pck.Workbook.Worksheets.Add("Databars")

            ws.Cells("A2:H21").Formula = "Row() - 11"

            'Adding gradient databar
            ws.Cells("A2:A21").ConditionalFormatting.AddDatabar(Color.OrangeRed)
            ws.Cells("A1").Value = "Gradient"

            'Solid Color Databar
            Dim databar = ws.Cells("B2:B21").ConditionalFormatting.AddDatabar(Color.BlueViolet)
            databar.Gradient = False

            ws.Cells("B1").Value = "Solid"

            'Below only accesible epplus7 and beyond

            'Themecolor bar note that input color does not matter if fill is changed
            Dim databarTheme = ws.Cells("C2:C21").ConditionalFormatting.AddDatabar(Color.BlueViolet)

            ws.Cells("C1").Value = "ThemeColor"

            databarTheme.FillColor.SetColor(eThemeSchemeColor.Accent2)
            'You can also set the border color
            databarTheme.BorderColor.SetColor(Color.Green)

            'Auto color
            Dim databarAuto = ws.Cells("D2:D21").ConditionalFormatting.AddDatabar(Color.Red)
            ws.Cells("D1").Value = "AutoColor"

            'Note: Auto color is white
            databarAuto.FillColor.SetAuto()
            'Making the white visible by filling a background color
            ws.Cells("D10:D21").Style.Fill.PatternType = ExcelFillStyle.Solid
            ws.Cells("D10:D21").Style.Fill.BackgroundColor.SetColor(Color.Cornsilk)


            'Indexed color (excel legacy)
            Dim databarIndexed = ws.Cells("E2:E21").ConditionalFormatting.AddDatabar(Color.Red)
            ws.Cells("E1").Value = "IndexAndNegativeColors"

            databarIndexed.FillColor.SetColor(ExcelIndexedColor.Indexed12)

            'similarily you can also apply all these settings to negative bar colors and borders
            databarIndexed.NegativeFillColor.SetColor(eThemeSchemeColor.Accent4)
            databarIndexed.NegativeBorderColor.SetColor(ExcelIndexedColor.Indexed45)
            'And the axis between negative and positive numbers
            databarIndexed.AxisColor.SetColor(Color.Purple)

            'Alternatively positive and negative colors can just be the same
            Dim boolsEx = ws.Cells("F2:F21").ConditionalFormatting.AddDatabar(Color.Green)
            ws.Cells("F1").Value = "SameAsPositive"

            boolsEx.BorderColor.SetColor(Color.Black)

            boolsEx.NegativeBarBorderColorSameAsPositive = True
            boolsEx.NegativeBarColorSameAsPositive = True

            '--------------------------------------------------------
            'Databars also contain other settings such as these

            Dim dataBarWithSettings = ws.Cells("G2:G21").ConditionalFormatting.AddDatabar(Color.Blue)
            ws.Cells("G1").Value = "MultipleSettings"

            dataBarWithSettings.AxisColor.SetColor(Color.Purple)

            dataBarWithSettings.AxisPosition = OfficeOpenXml.ConditionalFormatting.eExcelDatabarAxisPosition.Automatic
            'Direction of the databar (Default is left to right)
            dataBarWithSettings.Direction = OfficeOpenXml.ConditionalFormatting.eDatabarDirection.RightToLeft

            'Define when the databars length reaches its maximum and minimum value
            dataBarWithSettings.HighValue.Type = OfficeOpenXml.ConditionalFormatting.eExcelConditionalFormattingValueObjectType.Num
            dataBarWithSettings.LowValue.Type = OfficeOpenXml.ConditionalFormatting.eExcelConditionalFormattingValueObjectType.Num

            dataBarWithSettings.HighValue.Value = 5
            dataBarWithSettings.LowValue.Value = -5

            Dim dbSameDirection = ws.ConditionalFormatting.AddDatabar("H2:H21", Color.Yellow)
            ws.Cells("H1").Value = "SameDirection"

            'Show negative and positive bars in same direction
            dbSameDirection.AxisPosition = OfficeOpenXml.ConditionalFormatting.eExcelDatabarAxisPosition.None
            ws.Cells.AutoFitColumns()
        End Sub
    End Class
End Namespace