Implementing Password Security on Excel Workbooks Using Visual Studio .NET

• Download source files - 0.9 Kb
• Download demo project - 301 Kb

Introduction

Developing secure products are as important as the product itself. Most security measures involve data encryption and passwords. Excel sheets are mostly used for reporting purposes and reports mostly based on confidential data, so security is very important for Excel based reports.

Here, I have tried to develop a small utility for a security implementation. The same code can be used in both Visual Studio 2003 and 2005, and the code can be implemented in C# with proper syntax changes.

Code

Imports System.IO
Module Module1
    Public Filename As String
    Public oexcel As Excel.Application
    Public obook As Excel.Workbook
    Public osheet As Excel.Worksheet
    Sub Main()
        Try
            'File name and path, here i used abc file to be 
            'stored in Bin directory in the sloution directory
            Filename = AppDomain.CurrentDomain.BaseDirectory & _
                       "abc.xls"
            'check if file already exists then delete 
            'it to create a new file
            If File.Exists(Filename) Then
                File.Delete(Filename)
            End If
            If Not File.Exists(Filename) Then
                'create new excel application
                oexcel = CreateObject("Excel.Application")
                oexcel.Application.DisplayAlerts = False
                'add a new workbook
                obook = oexcel.Workbooks.Add
                oexcel.Visible = True

                Console.WriteLine("Generating Auto Report")
                osheet = oexcel.Worksheets(1)
                osheet.Name = "Test Sheet"
                osheet.Range("A1:G1").Merge()
                osheet.Range("A1").Value = "Implementing " & _ 
                       "Password Security on Excel " & _ 
                       "Workbook Using Studio.Net"
                osheet.Range("A1").Font.Color = RGB(255, 255, 255)
                osheet.Range("A1").Interior.ColorIndex = 5
                osheet.Range("A1").Font.Bold = True


                'password variable and value
                Dim password As String = "abc"
                'if you are using excel 11 or above 
                'then use the following code
                obook.Password = password
                obook.SaveAs(Filename)
                'otherwise use the folowing one
                obook.SaveAs(Filename, password:=password)
                'end application object and session
                osheet = Nothing
                obook.Close()
                obook = Nothing
                oexcel.Quit()
                oexcel = Nothing
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
        End Try
    End Sub
End Module

Working With the Demo

Download the demo project and unzip it, and then run "Excel_Automation.exe". It will generate an Excel sheet in the same directory with the name "abc.xls". When you try to open the Excel sheet, it will ask to enter the password, enter "abc", then the book will be visible to you.

Working With the Code

Here, I have attached the VB.NET code file, download it and then create a new console application project, and add the file to that project, and try with your desired changes. You can run the same code in both versions of Visual Studio .NET.

Summary

This is a small effort to generate a secure report for your critical data. Any feedback/comments are welcomed.