WorkbookSecurity.aspx.cs
using System;
using System.Data;
using System.IO;
using System.Web.UI;
using ExpertXls.ExcelLib;
using ExcelLibraryDemos;
/// <summary>
/// Workbook Security: the workbook code is in App_Code\WorkbookSecurityDemo.cs.
/// </summary>
public partial class WorkbookSecurityPage : Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void buttonCreate_Click(object sender, EventArgs e)
{
ExcelWorkbookFormat format = radioXlsx.Checked ? ExcelWorkbookFormat.Xlsx_2007 : ExcelWorkbookFormat.Xls_2003;
byte[] workbookBytes;
try
{
// create the workbook in memory; the input files are in the folders of the web site
using (MemoryStream stream = new MemoryStream())
{
WorkbookSecurityDemo.CreateWorkbook(format, Server.MapPath("~"), stream, textBoxPassword.Text);
workbookBytes = stream.ToArray();
}
}
catch (Exception ex)
{
labelError.Text = "The workbook could not be created: " + Server.HtmlEncode(ex.Message);
return;
}
WorkbookDownload.Send(Response, workbookBytes, WorkbookSecurityDemo.OutputName + DemoLayout.GetExtension(format), format);
}
}
App_Code/WorkbookSecurityDemo.cs
using System;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;
namespace ExcelLibraryDemos
{
/// <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 static class WorkbookSecurityDemo
{
/// <summary>The name of the generated file, without extension.</summary>
public const string OutputName = "WorkbookSecurity";
/// <summary>The password that removes the worksheet and workbook protection in Excel.</summary>
public const string ProtectionPassword = "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 null. Used only for the .xls format.</param>
public static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output, string openPassword)
{
ExcelWorkbook workbook = 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";
Image logo = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"));
try
{
ExcelCellStyle titleStyle = DemoLayout.AddTitleStyle(workbook);
ExcelCellStyle sectionStyle = DemoLayout.AddSectionStyle(workbook);
ExcelWorksheet worksheet = 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);
string[] products = { "HTML to PDF Converter", "PDF Merge", "PDF Security", "Excel Library for .NET" };
double[] prices = { 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 (int i = 0; i < products.Length; i++)
{
int row = 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);
}
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.
ExcelRange inputCells = 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 | ExcelProtectionAllowFlags.AllowSelectLockedCells |
ExcelProtectionAllowFlags.AllowFormatColumns);
// a second worksheet that is not protected
ExcelWorksheet notesWorksheet = 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 (!String.IsNullOrEmpty(openPassword) && format == ExcelWorkbookFormat.Xls_2003)
workbook.DocumentSecurity.OpenWorkbookPassword = openPassword;
workbook.Save(output);
}
finally
{
workbook.Close();
logo.Dispose();
}
}
}
}