Reading and writing encrypted files (VB.Net / netframework)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\vb\VS2022\netframework\10.API\25.Encrypted Files and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​NET-​demos/​tree/​master/​vb/​VS2022/​netframework/​Modules/​10.​API/​25.​Encrypted Files

Overview

How to read and write Encrypted Excel files using FlexCel.

Concepts

• Concepts on this demo are similar to the ones shown in Encryption And Subtotals example, but are repeated here so you can see them even if you are not using reports.

• There are 4 ways to protect data in Excel:

  1. A password to open. This is the only way that will actually encrypt the file. All other methods just will add a record telling Excel not to modify the file. On Excel, this option is on ->Tools->Options... Security tab.

  2. A password to modify. Also on ->Tools->Options->Security tab, this password will allow you to open the file, but not to save it. The file will not be encrypted, and you can always use Save As to save the file.

  3. Protecting the workbook. This will cause a pseudo-encryption of the file, but the password used to encrypt will be always the same, no matter what password you enter. On Excel, this option is at ->Tools->Protection->Protect Workbook.

  4. Protecting the sheet. This option will protect the cells, objects, etc on a particular sheet. ->Tools->Protection->Protect Sheet.

• When the file is encrypted (using a password to open) FlexCel supports four different encryption modes: Excel 95, Standard Excel97/2000 encryption, Excel 2007 xlsx encryption and Excel 2010 "agile" xlsx encryption.

• To open an encrypted file, you can set the OpenPassword property or the OnPassword event. Use the OnPassword event when you want to interactively ask for a password if the file is encrypted.

Files

AssemblyInfo.vb

Imports System.Reflection
Imports System.Runtime.CompilerServices

'
' General Information about an assembly is controlled through the following 
' set of attributes. Change these attribute values to modify the information
' associated with an assembly.
'
<Assembly: AssemblyTitle("")>
<Assembly: AssemblyDescription("")>
<Assembly: AssemblyConfiguration("")>
<Assembly: AssemblyCompany("")>
<Assembly: AssemblyProduct("")>
<Assembly: AssemblyCopyright("(c) 2002 - 2014 TMS Software")>
<Assembly: AssemblyTrademark("")>
<Assembly: AssemblyCulture("")>

'
' Version information for an assembly consists of the following four values:
'
'      Major Version
'      Minor Version 
'      Build Number
'      Revision
'
' You can specify all the values or you can default the Revision and Build Numbers 
' by using the '*' as shown below:

<Assembly: AssemblyVersion("6.2.1.0")>

'
' In order to sign your assembly you must specify a key to use. Refer to the 
' Microsoft .NET Framework documentation for more information on assembly signing.
'
' Use the attributes below to control which key is used for signing. 
'
' Notes: 
'   (*) If no key is specified, the assembly is not signed.
'   (*) KeyName refers to a key that has been installed in the Crypto Service
'       Provider (CSP) on your machine. KeyFile refers to a file which contains
'       a key.
'   (*) If the KeyFile and the KeyName values are both specified, the 
'       following processing occurs:
'       (1) If the KeyName can be found in the CSP, that key is used.
'       (2) If the KeyName does not exist and the KeyFile does exist, the key 
'           in the KeyFile is installed into the CSP and used.
'   (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
'       When specifying the KeyFile, the location of the KeyFile should be
'       relative to the project output directory which is
'       %Project Directory%\obj\<configuration>. For example, if your KeyFile is
'       located in the project directory, you would specify the AssemblyKeyFile 
'       attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
'   (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
'       documentation for more information on this.
'
<Assembly: AssemblyDelaySign(False)>
<Assembly: AssemblyKeyFile("")>
<Assembly: AssemblyKeyName("")>

Form1.Designer.vb

Imports System.Collections
Imports System.ComponentModel
Imports System.IO
Imports System.Reflection
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Namespace EncryptedFiles
    Partial Public Class mainForm
        Inherits System.Windows.Forms.Form

        Private panel2 As System.Windows.Forms.Panel
        Private WithEvents btnExit As System.Windows.Forms.Button
        Private WithEvents btnGo As System.Windows.Forms.Button
        Private saveFileDialog1 As System.Windows.Forms.SaveFileDialog
        Private components As System.ComponentModel.IContainer = Nothing

        ''' <summary>
        ''' Clean up any resources being used.
        ''' </summary>
        Protected Overrides Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                If components IsNot Nothing Then
                    components.Dispose()
                End If
            End If
            MyBase.Dispose(disposing)
        End Sub

        #Region "Windows Form Designer generated code"
        ''' <summary>
        ''' Required method for Designer support - do not modify
        ''' the contents of this method with the code editor.
        ''' </summary>
        Private Sub InitializeComponent()
            Me.panel2 = New System.Windows.Forms.Panel()
            Me.btnExit = New System.Windows.Forms.Button()
            Me.saveFileDialog1 = New System.Windows.Forms.SaveFileDialog()
            Me.btnGo = New System.Windows.Forms.Button()
            Me.panel2.SuspendLayout()
            Me.SuspendLayout()
            ' 
            ' panel2
            ' 
            Me.panel2.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D
            Me.panel2.Controls.Add(Me.btnExit)
            Me.panel2.Dock = System.Windows.Forms.DockStyle.Top
            Me.panel2.Location = New System.Drawing.Point(0, 0)
            Me.panel2.Name = "panel2"
            Me.panel2.Size = New System.Drawing.Size(336, 35)
            Me.panel2.TabIndex = 4
            ' 
            ' btnExit
            ' 
            Me.btnExit.Anchor = (CType((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
            Me.btnExit.BackColor = System.Drawing.SystemColors.Control
            Me.btnExit.Image = My.Resources._4close
            Me.btnExit.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft
            Me.btnExit.Location = New System.Drawing.Point(272, 2)
            Me.btnExit.Name = "btnExit"
            Me.btnExit.Size = New System.Drawing.Size(56, 26)
            Me.btnExit.TabIndex = 2
            Me.btnExit.Text = "Exit"
            Me.btnExit.TextAlign = System.Drawing.ContentAlignment.MiddleRight
            Me.btnExit.UseVisualStyleBackColor = False
'           Me.btnExit.Click += New System.EventHandler(Me.btnExit_Click)
            ' 
            ' saveFileDialog1
            ' 
            Me.saveFileDialog1.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm|Excel 97/2003|*.xls|Excel 2007|*.xlsx;*.xlsm|All " & "files|*.*"
            Me.saveFileDialog1.RestoreDirectory = True
            ' 
            ' btnGo
            ' 
            Me.btnGo.BackColor = System.Drawing.SystemColors.Control
            Me.btnGo.Image = My.Resources._4gears
            Me.btnGo.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft
            Me.btnGo.Location = New System.Drawing.Point(96, 72)
            Me.btnGo.Name = "btnGo"
            Me.btnGo.Size = New System.Drawing.Size(152, 30)
            Me.btnGo.TabIndex = 5
            Me.btnGo.Text = "Create Encrypted File"
            Me.btnGo.TextAlign = System.Drawing.ContentAlignment.MiddleRight
            Me.btnGo.UseVisualStyleBackColor = False
'           Me.btnGo.Click += New System.EventHandler(Me.btnGo_Click)
            ' 
            ' mainForm
            ' 
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.ClientSize = New System.Drawing.Size(336, 122)
            Me.Controls.Add(Me.btnGo)
            Me.Controls.Add(Me.panel2)
            Me.Name = "mainForm"
            Me.Text = "Encrypted Excel Files"
            Me.panel2.ResumeLayout(False)
            Me.ResumeLayout(False)

        End Sub
        #End Region
    End Class
End Namespace

Form1.vb

Imports System.Collections
Imports System.ComponentModel
Imports System.IO
Imports System.Reflection

Imports FlexCel.Core
Imports FlexCel.XlsAdapter

Namespace EncryptedFiles
    ''' <summary>
    ''' Shows how to deal with Encrypted files.
    ''' </summary>
    Partial Public Class mainForm
        Inherits System.Windows.Forms.Form

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub btnExit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExit.Click
            Close()
        End Sub

        'The event that will actually provide the password to open the empty form.
        Private Sub GetPassword(ByVal e As OnPasswordEventArgs)
            Dim Pwd As New PasswordDialog()
            e.Password = String.Empty
            If Pwd.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then
                Return
            End If
            e.Password = Pwd.Password
        End Sub

        Private Sub btnGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGo.Click
            ' On this demo we will fill data on an existing file with the api, starting with an encrypted file holding the starting formats.

            ' Declare some data for the chart.
            Dim Names() As String = { "Dog", "Cat", "Cow", "Horse", "Fish" }
            Dim Quantities() As Integer = { 123, 200, 150, 0, 180 }

            ' Use two folders up to where the exe is to store the data. (Exe is stored at bin\debug)
            Dim DataPath As String = Path.Combine(Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), ".."), "..") & Path.DirectorySeparatorChar

            Dim xls As New XlsFile(True)

            ' We will use the OnPassword event here to show how to 
            ' open a file if you don't know a priory if it is encrypted or not.
            ' If you already knew the file was encrypted, (as in this case)you could use:
            ' xls.Protection.OpenPassword = "42";

            AddHandler xls.Protection.OnPassword, AddressOf GetPassword
            xls.Open(Path.Combine(DataPath, "EmptyForm.xls"))

            ' Insert rows so the chart range grows. On this case we assume the data is at least 2 rows long. If not, we should handle 
            ' the case and do a xls.DeleteRange.
            xls.InsertAndCopyRange(New TXlsCellRange(1, 1, 1, 2), 5, 1, Names.Length - 2, TFlxInsertMode.ShiftRangeDown, TRangeCopyMode.None)

            ' Fill the data.
            For i As Integer = 0 To Names.Length - 1
                xls.SetCellValue(4 + i, 1, Names(i))
                xls.SetCellValue(4 + i, 2, Quantities(i))
            Next i

            ' Set a new password for opening.
            xls.Protection.OpenPassword = "43"
            xls.Protection.SetModifyPassword("43", False, "Ford Prefect")

            If saveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                xls.Save(saveFileDialog1.FileName)

                If MessageBox.Show("Do you want to open the generated file? (Remember password is 43)", "Confirm", MessageBoxButtons.YesNo) = System.Windows.Forms.DialogResult.Yes Then
                    Process.Start(saveFileDialog1.FileName)
                End If
            End If
        End Sub

    End Class
End Namespace

PasswordDialog.Designer.vb

Imports System.Collections
Imports System.ComponentModel
Namespace EncryptedFiles
    Partial Public Class PasswordDialog
        Inherits System.Windows.Forms.Form

        Private label1 As System.Windows.Forms.Label
        Private label2 As System.Windows.Forms.Label
        Private PasswordEdit As System.Windows.Forms.TextBox
        Private label3 As System.Windows.Forms.Label
        Private btnOk As System.Windows.Forms.Button
        Public btnCancel As System.Windows.Forms.Button
        ''' <summary>
        ''' Required designer variable.
        ''' </summary>
        Private components As System.ComponentModel.Container = Nothing

        ''' <summary>
        ''' Clean up any resources being used.
        ''' </summary>
        Protected Overrides Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                If components IsNot Nothing Then
                    components.Dispose()
                End If
            End If
            MyBase.Dispose(disposing)
        End Sub

        #Region "Windows Form Designer generated code"
        ''' <summary>
        ''' Required method for Designer support - do not modify
        ''' the contents of this method with the code editor.
        ''' </summary>
        Private Sub InitializeComponent()
            Me.label1 = New System.Windows.Forms.Label()
            Me.label2 = New System.Windows.Forms.Label()
            Me.PasswordEdit = New System.Windows.Forms.TextBox()
            Me.label3 = New System.Windows.Forms.Label()
            Me.btnOk = New System.Windows.Forms.Button()
            Me.btnCancel = New System.Windows.Forms.Button()
            Me.SuspendLayout()
            ' 
            ' label1
            ' 
            Me.label1.Location = New System.Drawing.Point(16, 8)
            Me.label1.Name = "label1"
            Me.label1.Size = New System.Drawing.Size(280, 23)
            Me.label1.TabIndex = 0
            Me.label1.Text = "Please enter the password to open the template."
            ' 
            ' label2
            ' 
            Me.label2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, (CByte(0)))
            Me.label2.Location = New System.Drawing.Point(16, 32)
            Me.label2.Name = "label2"
            Me.label2.Size = New System.Drawing.Size(176, 23)
            Me.label2.TabIndex = 1
            Me.label2.Text = "HINT: The password is 42"
            ' 
            ' PasswordEdit
            ' 
            Me.PasswordEdit.Anchor = (CType(((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
            Me.PasswordEdit.Location = New System.Drawing.Point(128, 64)
            Me.PasswordEdit.Name = "PasswordEdit"
            Me.PasswordEdit.PasswordChar = "*"c
            Me.PasswordEdit.Size = New System.Drawing.Size(360, 20)
            Me.PasswordEdit.TabIndex = 0
            Me.PasswordEdit.Text = ""
            ' 
            ' label3
            ' 
            Me.label3.Location = New System.Drawing.Point(56, 64)
            Me.label3.Name = "label3"
            Me.label3.Size = New System.Drawing.Size(64, 23)
            Me.label3.TabIndex = 3
            Me.label3.Text = "Password:"
            ' 
            ' btnOk
            ' 
            Me.btnOk.DialogResult = System.Windows.Forms.DialogResult.OK
            Me.btnOk.Location = New System.Drawing.Point(128, 112)
            Me.btnOk.Name = "btnOk"
            Me.btnOk.TabIndex = 1
            Me.btnOk.Text = "Ok"
            ' 
            ' btnCancel
            ' 
            Me.btnCancel.DialogResult = System.Windows.Forms.DialogResult.Cancel
            Me.btnCancel.Location = New System.Drawing.Point(216, 112)
            Me.btnCancel.Name = "btnCancel"
            Me.btnCancel.TabIndex = 2
            Me.btnCancel.Text = "Cancel"
            ' 
            ' PasswordDialog
            ' 
            Me.AcceptButton = Me.btnOk
            Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
            Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
            Me.ClientSize = New System.Drawing.Size(520, 154)
            Me.Controls.Add(Me.btnCancel)
            Me.Controls.Add(Me.btnOk)
            Me.Controls.Add(Me.label3)
            Me.Controls.Add(Me.PasswordEdit)
            Me.Controls.Add(Me.label2)
            Me.Controls.Add(Me.label1)
            Me.Name = "PasswordDialog"
            Me.ShowInTaskbar = False
            Me.Text = "Information"
            Me.ResumeLayout(False)

        End Sub
        #End Region
    End Class
End Namespace

PasswordDialog.vb

Imports System.Collections
Imports System.ComponentModel

Namespace EncryptedFiles
    ''' <summary>
    ''' Summary description for PasswordDialog.
    ''' </summary>
    Partial Public Class PasswordDialog
        Inherits System.Windows.Forms.Form

        Public Sub New()
            '
            ' Required for Windows Form Designer support
            '
            InitializeComponent()

            '
            ' TODO: Add any constructor code after InitializeComponent call
            '
        End Sub

        Public ReadOnly Property Password() As String
            Get
                Return PasswordEdit.Text
            End Get
        End Property
    End Class
End Namespace

Program.vb

Namespace EncryptedFiles
    Friend NotInheritable Class Program

        Private Sub New()
        End Sub

        ''' <summary>
        ''' The main entry point for the application.
        ''' </summary>
       <STAThread> _
        Shared Sub Main()
            Application.EnableVisualStyles()
            Application.SetCompatibleTextRenderingDefault(False)
            Application.Run(New mainForm())
        End Sub
    End Class
End Namespace