Spreadsheet Document Protection

This demo shows how to protect the workbook structure and worksheet data using the Spreadsheet Document API. Specify protection options on the Options pane and select an output format. Click Download to save the document to your computer.

Options
Password (optional)
Password to unprotect worksheet
Permissions
File Format
@model AspNetCoreDemos.OfficeFileAPI.SpreadsheetDocumentProtectionModel
@using DevExtreme.AspNet.Mvc

@{ Html.BeginForm("SpreadsheetDocumentProtectionExportTo", "DocumentProtection", FormMethod.Post); }

<div class="demo-view-container">
    @await Html.PartialAsync("SpreadsheetPreviewPartial", Model.PreviewModel)
</div>
<div class="options">
    <div class="caption">Options</div>
    <div class="option">
        @(Html.DevExtreme().CheckBoxFor(m => m.ProtectWorkbook)
                           .ID("ProtectWorkbook")
                           .OnValueChanged("WorkbookProtection")
                           .Text("Protect Workbook"))
    </div>
    <div class="option">
        @(Html.DevExtreme().TagBoxFor(m => m.WorkbookProtectionTypes)
                           .ID("WorkbookProtection")
                           .DataSource(Html.GetEnumSelectList<AspNetCoreDemos.OfficeFileAPI.WorkbookProtectionTypes>()
                           .Select(i => new { Value = int.Parse(i.Value), Text = i.Text }))
                           .ValueExpr("Value")
                           .DisplayExpr("Text")
                           .ShowSelectionControls(true))
    </div>
    <div class="option">
        <div class="label">Password (optional)</div>
        @(Html.DevExtreme().TextBoxFor(m => m.WorkbookPassword)
                           .ID("WorkbookPassword")
                           .Mode(TextBoxMode.Password))
    </div>
    <div class="option">
        @(Html.DevExtreme().CheckBoxFor(m => m.ProtectWorksheet)
                           .ID("ProtectWorksheet")
                           .OnValueChanged("WorksheetProtection")
                           .Text("Protect Worksheet"))
    </div>
    <div class="option">
        <div class="label">Password to unprotect worksheet</div>
        @(Html.DevExtreme().TextBoxFor(m => m.WorksheetPassword)
                           .ID("WorksheetPassword")
                           .Mode(TextBoxMode.Password))
    </div>
    <div class="option">
        <div class="label">Permissions</div>
        @(Html.DevExtreme().TagBoxFor(m => m.WorksheetPermissions)
                           .ID("WorksheetPermissions")
                           .DataSource(Html.GetEnumSelectList<AspNetCoreDemos.OfficeFileAPI.WorksheetProtectionPermissionsType>()
                           .Select(i => new { Value = int.Parse(i.Value), Text = i.Text }))
                           .ValueExpr("Value")
                           .DisplayExpr("Text")
                           .MaxDisplayedTags(8)
                           .ShowSelectionControls(true))
    </div>
    <div class="option">
        <div class="label">File Format</div>
            @(Html.DevExtreme().SelectBoxFor(m => m.FileFormat)
                               .DataSource(Html.GetEnumSelectList<AspNetCoreDemos.OfficeFileAPI.SpreadsheetProtectionFileFormat>()
                               .Select(i => new { Value = int.Parse(i.Value), Text = i.Text }))
                               .ValueExpr("Value")
                               .DisplayExpr("Text"))
     </div>
     <div class="option-buttons">
            @(Html.DevExtreme().Button()
                               .ID("ProtectAndDownload")
                               .Text("Download")
                               .Type(ButtonType.Default)
                               .StylingMode(ButtonStylingMode.Contained)
                               .UseSubmitBehavior(true))
    </div>
</div>

<script>
    function WorkbookProtection() {
        let checked = $("#ProtectWorkbook").dxCheckBox("instance").option("value");
        $("#WorkbookProtection").dxTagBox("instance").option("disabled", !checked);
        $("#WorkbookPassword").dxTextBox("instance").option("disabled", !checked);
    }
    function WorksheetProtection() {
        let checked = $("#ProtectWorksheet").dxCheckBox("instance").option("value");
        $("#WorksheetPassword").dxTextBox("instance").option("disabled", !checked);
        $("#WorksheetPermissions").dxTagBox("instance").option("disabled", !checked);
    }
</script>

@{ Html.EndForm(); }
@model AspNetCoreDemos.OfficeFileAPI.SpreadsheetPreviewModel

<iframe id="previewFrame" src="@Url.Action(Model.PreviewDocumentAction, Model.ControllerName)" class="demo-preview-border" style="width: 100%; height: @Model.HeightInPixels;box-sizing:border-box"></iframe>

<script type="text/javascript">
    SpreadsheetPreview = {
        basePath: '@Url.Action(Model.PreviewDocumentAction, Model.ControllerName)',
        Update: function (param) {
            var iframeElementName = "previewFrame";
            var iframeElement = document.getElementById(iframeElementName);
            if (!iframeElement)
                return;
            var additionalParams = "&" + new Date().valueOf();
            if (param)
                additionalParams = param;
            iframeElement.src = this.basePath + "?" + additionalParams;
        }
    };
</script>

using Microsoft.AspNetCore.Hosting;

namespace AspNetCoreDemos.OfficeFileAPI {
    public partial class DocumentProtectionController : OfficeDemoController {
        public DocumentProtectionController(IWebHostEnvironment hostingEnvironment)
            : base(hostingEnvironment) {
        }
    }
}
using System.IO;
using DevExpress.Spreadsheet;
using Microsoft.AspNetCore.Mvc;

namespace AspNetCoreDemos.OfficeFileAPI {
    public partial class DocumentProtectionController {
        const string spreadsheetDocumentProtectionDefaultFile = "/Documents/SimpleMonthlyBudget.xltx";

        public IActionResult SpreadsheetDocumentProtection() {
            SpreadsheetDocumentProtectionModel model = new SpreadsheetDocumentProtectionModel();
            model.PreviewModel.Workbook = CreateProtectedWorkbook(model);
            return View(model);
        }

        public IActionResult SpreadsheetDocumentProtectionExportTo(SpreadsheetDocumentProtectionModel model) {
            IWorkbook workbook = CreateProtectedWorkbook(model);
            SpreadsheetFileFormat fileFormat = model.FileFormat;
            DocumentFormat format = SpreadsheetUtils.ConvertToFormat(fileFormat);
            MemoryStream stream = new MemoryStream();
            workbook.SaveDocument(stream, format);
            stream.Seek(0, SeekOrigin.Begin);
            string contentType = SpreadsheetUtils.ConvertToContentType(fileFormat);
            string fileExtension = SpreadsheetUtils.ConvertToFileExtension(fileFormat);
            return CreateFileStreamResult(stream, contentType, fileExtension);
        }

        public IActionResult SpreadsheetDocumentProtectionPreview(SpreadsheetDocumentProtectionModel model) {
            SpreadsheetPreviewModel previewModel = model.PreviewModel;
            previewModel.Workbook = CreateProtectedWorkbook(model);
            return GenerateHtmlPreview(previewModel);
        }

        IWorkbook CreateProtectedWorkbook(SpreadsheetDocumentProtectionModel model) {
            IWorkbook workbook = new Workbook();
            string filePath = HostingEnvironment.ContentRootPath + spreadsheetDocumentProtectionDefaultFile;
            workbook.LoadDocument(filePath);
            if(model.ProtectWorkbook)
                workbook.Protect(model.WorkbookPassword ?? string.Empty, model.GetLockStructure(), model.GetLockWindows());
            if(model.ProtectWorksheet) {
                WorksheetProtectionPermissions permissions = model.GetPermissions();
                workbook.Worksheets[0].Protect(model.WorksheetPassword ?? string.Empty, permissions);
            }
            return workbook;
        }
    }
}
using System;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using DevExpress.Spreadsheet;

namespace AspNetCoreDemos.OfficeFileAPI {
    public enum WorkbookProtectionTypes {
        Structure = 1,
        Windows = 2
    }

    public enum WorksheetProtectionPermissionsType {
        [Display(Name = "Select locked cells")]
        SelectLockedCells = 0,
        [Display(Name = "Select unlocked cells")]
        SelectUnlockedCells = 1,
        [Display(Name = "Format cells")]
        FormatCells = 2,
        [Display(Name = "Format columns")]
        FormatColumns = 3,
        [Display(Name = "Format rows")]
        FormatRows = 4,
        [Display(Name = "Insert columns")]
        InsertColumns = 5,
        [Display(Name = "Insert rows")]
        InsertRows = 6,
        [Display(Name = "Insert hyperlinks")]
        InsertHyperlinks = 7,
        [Display(Name = "Delete columns")]
        DeleteColumns = 8,
        [Display(Name = "Delete rows")]
        DeleteRows = 9,
        [Display(Name = "Sort")]
        Sort = 10,
        [Display(Name = "Use AutoFilter")]
        AutoFilters = 11,
        [Display(Name = "Use PivotTable reports")]
        PivotTables = 12,
        [Display(Name = "Edit objects")]
        Objects = 13,
        [Display(Name = "Edit scenarios")]
        Scenarios = 14
    }

    public class SpreadsheetDocumentProtectionModel : SpreadsheetModelBase {
        public SpreadsheetDocumentProtectionModel() {
            PreviewModel.PreviewDocumentAction = "SpreadsheetDocumentProtectionPreview";
            PreviewModel.HeightInPixels = "620px";
            ProtectWorkbook = true;
            WorkbookProtectionTypes = new int[] { 1 };
            ProtectWorksheet = true;
            WorksheetPermissions = new int[] { 0, 1 };
            FileFormat = SpreadsheetFileFormat.Xlsx;
        }

        public bool ProtectWorkbook { get; set; }
        public int[] WorkbookProtectionTypes { get; set; }
        public string WorkbookPassword { get; set; }
        public bool ProtectWorksheet { get; set; }
        public string WorksheetPassword { get; set; }
        public int[] WorksheetPermissions { get; set; }

        public WorksheetProtectionPermissions GetPermissions() {
            WorksheetProtectionPermissions permissions = 0;
            if (WorksheetPermissions != null) {
                foreach (int value in WorksheetPermissions)
                    permissions |= (WorksheetProtectionPermissions)(0x01 << value);
            }
            return permissions;
        }

        public bool GetLockStructure() {
            if (WorkbookProtectionTypes == null)
                return false;
            return WorkbookProtectionTypes.Contains(1);
        }

        public bool GetLockWindows() {
            if (WorkbookProtectionTypes == null)
                return false;
            return WorkbookProtectionTypes.Contains(2);
        }
    }
}
namespace AspNetCoreDemos.OfficeFileAPI {
    public class SpreadsheetModelBase {
        public SpreadsheetModelBase() {
            PreviewModel = new SpreadsheetPreviewModel();
            PreviewModel.OwnerPropertyName = "PreviewModel";
            FileFormat = SpreadsheetFileFormat.Xlsx;
        }

        public SpreadsheetFileFormat FileFormat { get; set; }
        public SpreadsheetPreviewModel PreviewModel { get; internal set; }
    }
}