WorkbookSecurity.aspx.vb
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports ExpertXls.ExcelLib
''' <summary>
''' Workbook Security: the workbook code is in App_Code\WorkbookSecurityDemo.vb.
''' </summary>
Partial Class WorkbookSecurityPage
Inherits Page
' AutoEventWireup="true" in the page directive connects Page_Load to the Load event
Protected Sub Page_Load(sender As Object, e As EventArgs)
End Sub
Protected Sub buttonCreate_Click(sender As Object, e As EventArgs)
Dim format As ExcelWorkbookFormat = If(radioXlsx.Checked, ExcelWorkbookFormat.Xlsx_2007, ExcelWorkbookFormat.Xls_2003)
Dim workbookBytes As Byte()
Try
' create the workbook in memory; the input files are in the folders of the web site
Using stream As New MemoryStream()
WorkbookSecurityDemo.CreateWorkbook(format, Server.MapPath("~"), stream, textBoxPassword.Text)
workbookBytes = stream.ToArray()
End Using
Catch ex As Exception
labelError.Text = "The workbook could not be created: " & Server.HtmlEncode(ex.Message)
Return
End Try
WorkbookDownload.Send(Response, workbookBytes, WorkbookSecurityDemo.OutputName & DemoLayout.GetExtension(format), format)
End Sub
End Class
App_Code/WorkbookSecurityDemo.vb
Imports System.Drawing
Imports System.IO
Imports ExpertXls.ExcelLib
''' <summary>
''' Protects a workbook: a protected worksheet where only the unlocked input cells can be edited, a hidden formula,
''' a protected workbook structure and, for the Excel 97-2003 format, a password to open the file.
''' </summary>
Public Module WorkbookSecurityDemo
''' <summary>The name of the generated file, without extension.</summary>
Public Const OutputName As String = "WorkbookSecurity"
''' <summary>The password that removes the worksheet and workbook protection in Excel.</summary>
Public Const ProtectionPassword As String = "expertxls"
''' <summary>
''' Creates the workbook in the given format and saves it to the output stream.
''' </summary>
''' <param name="format">Excel 97-2003 (.xls) or Excel 2007+ (.xlsx).</param>
''' <param name="inputFolder">The folder that contains the Images folder of the sample.</param>
''' <param name="output">The stream that receives the workbook.</param>
''' <param name="openPassword">The password to open the workbook, or Nothing. Used only for the .xls format.</param>
Public Sub CreateWorkbook(format As ExcelWorkbookFormat, inputFolder As String, output As Stream, openPassword As String)
Dim workbook As New ExcelWorkbook(format)
' workbook.LicenseKey = "your license key"
workbook.DocumentProperties.Subject = "Workbook and worksheet protection"
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET"
Dim logo As Image = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"))
Try
Dim titleStyle As ExcelCellStyle = DemoLayout.AddTitleStyle(workbook)
Dim sectionStyle As ExcelCellStyle = DemoLayout.AddSectionStyle(workbook)
Dim worksheet As ExcelWorksheet = workbook.Worksheets(0)
worksheet.Name = "Order Form"
DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Portrait)
DemoLayout.WriteTitle(worksheet, "A2:E3", "A Protected Order Form", titleStyle)
worksheet("A1").ColumnWidthInChars = 28
worksheet("B1:E1").ColumnWidthInChars = 14
DemoLayout.WriteSection(worksheet, "A5:E5", "Only the yellow cells can be edited", sectionStyle)
Dim products As String() = {"HTML to PDF Converter", "PDF Merge", "PDF Security", "Excel Library for .NET"}
Dim prices As Double() = {550, 150, 150, 350}
worksheet("A7").Text = "Product"
worksheet("B7").Text = "Unit price"
worksheet("C7").Text = "Quantity"
worksheet("D7").Text = "Price"
worksheet("A7:D7").Style.Font.Bold = True
For i As Integer = 0 To products.Length - 1
Dim row As Integer = 8 + i
worksheet(row, 1).Text = products(i)
worksheet(row, 2).Value = prices(i)
worksheet(row, 3).Value = 0
worksheet(row, 4).Formula = String.Format("=B{0}*C{0}", row)
Next
worksheet("A12").Text = "Total"
worksheet("A12").Style.Font.Bold = True
worksheet("D12").Formula = "=SUM(D8:D11)"
worksheet("B8:B11").Style.Number.NumberFormatString = "#,##0.00"
worksheet("D8:D12").Style.Number.NumberFormatString = "#,##0.00"
' All the cells are locked by default, but the lock works only when the worksheet is protected.
' Unlock the input cells and hide the formula of the total from the formula bar.
Dim inputCells As ExcelRange = worksheet("C8:C11")
inputCells.Style.Protection.Locked = False
inputCells.Style.Fill.FillType = ExcelCellFillType.SolidFill
inputCells.Style.Fill.SolidFillOptions.BackColor = Color.FromArgb(255, 255, 153)
inputCells.Style.Borders.LineStyle = ExcelCellLineStyle.Thin
worksheet("D12").Style.Protection.HiddenFormula = True
' protect the worksheet with a password; the flags say what the user can still do
worksheet.WorksheetSecurity.ProtectWorksheet(ProtectionPassword,
ExcelProtectionAllowFlags.AllowSelectUnlockedCells Or ExcelProtectionAllowFlags.AllowSelectLockedCells Or
ExcelProtectionAllowFlags.AllowFormatColumns)
' a second worksheet that is not protected
Dim notesWorksheet As ExcelWorksheet = workbook.Worksheets.AddWorksheet("Notes")
notesWorksheet("A1").Text = "This worksheet is not protected."
notesWorksheet("A2").Text = "The workbook structure is protected: worksheets cannot be added, deleted, renamed or moved."
notesWorksheet("A3").Text = "The protection password is: " & ProtectionPassword
' protect the structure of the workbook (the worksheets), not the window
workbook.DocumentSecurity.ProtectWorkbook(False, True, ProtectionPassword)
' a password to open the file is supported for the Excel 97-2003 format
If Not String.IsNullOrEmpty(openPassword) AndAlso format = ExcelWorkbookFormat.Xls_2003 Then
workbook.DocumentSecurity.OpenWorkbookPassword = openPassword
End If
workbook.Save(output)
Finally
workbook.Close()
logo.Dispose()
End Try
End Sub
End Module