PerformanceAndProtectionSample.vb

' ***********************************************************************************************
' Required Notice: Copyright (C) EPPlus Software AB. 
' This software is licensed under PolyForm Noncommercial License 1.0.0 
' and may only be used for noncommercial purposes 
' https://polyformproject.org/licenses/noncommercial/1.0.0/
' 
' A commercial license to use this software can be purchased at https://epplussoftware.com
' ************************************************************************************************
' Date               Author                       Change
' ************************************************************************************************
' 01/27/2020         EPPlus Software AB           Initial release EPPlus 5
' ***********************************************************************************************
Imports System
Imports OfficeOpenXml
Imports OfficeOpenXml.Style
Imports System.Drawing

Namespace EPPlusSamples.WorkbookWorksheetAndRanges
    Public Module PerformanceAndProtectionSample
        ''' <summary>
        ''' This sample load a number of rows, style them and insert a row at the top.
        ''' A password is set to protect locked cells. Column 3 & 4 will be editable, the rest will be locked.
        ''' </summary>
        ''' <paramname="rows"></param>
        Public Sub Run(ByVal rows As Integer)
            Console.WriteLine("Running sample 1.7")
            Dim newFile = FileUtil.GetCleanFileInfo("1.07-PerformanceAndProtection.xlsx")
            Using package As ExcelPackage = New ExcelPackage()
                Console.WriteLine("{0:HH.mm.ss}" & vbTab & "Starting...", Date.Now)

                'Load the sheet with one string column, one date column and a few random numbers.
                Dim ws = package.Workbook.Worksheets.Add("Performance Test")

                'Format all cells
                Dim cols = ws.Cells("A:XFD")
                cols.Style.Fill.PatternType = ExcelFillStyle.Solid
                cols.Style.Fill.BackgroundColor.SetColor(Color.LightGray)

                Dim rnd = New Random()
                For row = 1 To rows
                    ws.SetValue(row, 1, row)                               'The SetValue method is a little bit faster than using the Value property
                    ws.SetValue(row, 2, String.Format("Row {0}", row))
                    ws.SetValue(row, 3, Date.Today.AddDays(row))
                    ws.SetValue(row, 4, rnd.NextDouble() * 10000)
                    If row Mod 10000 = 0 Then
                        Console.WriteLine("{0:HH.mm.ss}" & vbTab & "Writing row {1}...", Date.Now, row)
                    End If
                Next
                'Set the formula using the R1C1 format
                ws.Cells(1, 5, rows, 5).FormulaR1C1 = "RC[-4]+RC[-1]"

                'Add a sum at the end
                ws.Cells(rows + 1, 5).Formula = String.Format("Sum({0})", New ExcelAddress(1, 5, rows, 5).Address)
                ws.Cells(rows + 1, 5).Style.Font.Bold = True
                ws.Cells(rows + 1, 5).Style.Numberformat.Format = "#,##0.00"

                Console.WriteLine("{0:HH.mm.ss}" & vbTab & "Writing row {1}...", Date.Now, rows)
                Console.WriteLine("{0:HH.mm.ss}" & vbTab & "Formatting...", Date.Now)
                'Format the date and numeric columns
                ws.Cells(1, 1, rows, 1).Style.Numberformat.Format = "#,##0"
                ws.Cells(1, 3, rows, 3).Style.Numberformat.Format = "YYYY-MM-DD"
                ws.Cells(1, 4, rows, 5).Style.Numberformat.Format = "#,##0.00"

                Console.WriteLine("{0:HH.mm.ss}" & vbTab & "Insert a row at the top...", Date.Now)
                'Insert a row at the top. Note that the formula-addresses are shifted down
                ws.InsertRow(1, 1)

                'Write the headers and style them
                ws.Cells("A1").Value = "Index"
                ws.Cells("B1").Value = "Text"
                ws.Cells("C1").Value = "Date"
                ws.Cells("D1").Value = "Number"
                ws.Cells("E1").Value = "Formula"
                ws.View.FreezePanes(2, 1)

                Using rng = ws.Cells("A1:E1")
                    rng.Style.Font.Bold = True
                    rng.Style.Font.Color.SetColor(Color.White)
                    rng.Style.WrapText = True
                    rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center
                    rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid
                    rng.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue)
                End Using

                Console.WriteLine("{0:HH.mm.ss}" & vbTab & "Autofit columns and lock and format cells...", Date.Now)
                ws.Cells(rows - 100, 1, rows, 5).AutoFitColumns(5)     'Auto fit using the last 100 rows with minimum width 5
                ws.Columns(5).Width = 15                                'We need to set the width for column F manually since the end sum formula is the widest cell in the column (EPPlus don't calculate any forumlas, so no output text is avalible). 

                'Now we set the sheet protection and a password.
                ws.Cells(2, 3, rows + 1, 4).Style.Locked = False
                ws.Cells(2, 3, rows + 1, 4).Style.Fill.PatternType = ExcelFillStyle.Solid
                ws.Cells(2, 3, rows + 1, 4).Style.Fill.BackgroundColor.SetColor(Color.White)
                ws.Cells(1, 5, rows + 2, 5).Style.Hidden = True    'Hide the formula

                ws.Protection.SetPassword("EPPlus")

                ws.Select("C2")
                Console.WriteLine("{0:HH.mm.ss}" & vbTab & "Saving...", Date.Now)
                package.Compression = CompressionLevel.BestSpeed
                package.SaveAs(newFile)
            End Using
            Console.WriteLine("{0:HH.mm.ss}" & vbTab & "Done!!", Date.Now)
            Console.WriteLine("Sample 1.7 created:", newFile.FullName)
            Console.WriteLine()
        End Sub
    End Module
End Namespace