SpreadJS As a pure front-end spreadsheet control based on HTML5, it is compatible with more than 450 Excel formulas. It can bring users a friendly experience of using Excel, and can meet business scenarios such as Web Excel component development, data filling, Excel class report design, table and document collaborative editing in enterprise IT departments.

Offline filling-in, as one of the typical application scenarios for data filling, allows business people to complete filling-in work anytime, anywhere, in a production environment, without restriction to the network.

The general implementation process for offline filling is:

1. Output HTML offline Report

2. Data Filling in a Non-Network Environment

3. Submit data after networking

Password protection is particularly important at this time because of the asynchronous operation process that requires permission control for offline filers.

As an online Excel editing control, SpreadJS currently only supports workbook password protection, not worksheet password protection.However, SpreadJS was originally designed to maintain maximum compatibility with Excel. When an Excel worksheet is imported, the password-related parts are also saved in the ssjson of SpreadJS. After serialization by spread.toJSON(), we can also use password protection normally. The serialized Json file is shown below:

The red box in the figure above shows the password protection of the worksheet and the set password string.

As long as we keep this section, we can add worksheet protection to the Excel template we designed.

Note that because Excel encrypts the password once, we cannot change the corresponding key value in json directly to the plaintext password we want, so we need to import the Excel with the corresponding password in advance to extract the information about the password.

You can follow the demonstration below:

1. First import an empty Excel and set the corresponding password protection for the corresponding worksheet

1. Import this Excel into SpreadJS, then get the entire json through spread.toJSON(), and find the corresponding password protection settings through the code:

var json = spread.toJSON()

var protectOptions = json.sheets.Sheet1.protectionOptions

Because password protection is set on Sheet1 by default in Excel, you need to use json.sheets.Sheet1.protectionOptions to get the corresponding protectOptions settings and store them temporarily in a variable for later use.

1. Next, we are working on a permanent design fill-in template.

Fill-in templates are designed in the same way, but they are designed differently, especially in desktop designers and online table editors.

Fill in the template design principle: temporarily saved protectOptions merge into the final generated ssjson.(ExcelIO of SpreadJS exports Excel using ssjson generated by spread serialized toJSON, so through the js operation, we can enter the previously temporary saved protectOptions merge.)

Because the actions checked during the design of form protection also change the protectionOptions, simply replacing will result in the loss of form protection options set at the time of the design template, for example, we checked to resize rows and columns when designing the template, as shown below:

At this point, it is recorded in protectOptions

If we directly replace the previously temporary saved protectOptions, these settings will be lost.

So here's what you need to do:

First, temporarily save the current protectOptions:

var tempProtectOptions = json.sheets.Sheet1.protectionOptions

Then, replace it with protectOptions with the previous password:

json.sheets.Sheet1.protectionOptions = protectOptions

After that, merge the contents of tempProtectOptions:

<div>json.sheets.Sheet1.protectionOptions.allowResizeRows = true;</div>

<div>json.sheets.Sheet1.protectionOptions.allowResizeColumns = true;</div>

Finally, the adjusted ssjson is given to ExcelIO for export:

<div>

 <span style="color: rgb(51, 51, 51); font-family: monospace, monospace; font-size: 16px; white-space: pre; background-color: rgb(248, 248, 248);"> excelio.save(json, </span><span class="hljs-function" style="box-sizing: inherit; color: rgb(51, 51, 51); font-family: monospace, monospace; font-size: 16px; white-space: pre;"><span class="hljs-keyword" style="box-sizing: inherit; font-weight: 700;">function</span> (<span class="hljs-params" style="box-sizing: inherit;">blob</span>) </span><span style="color: rgb(51, 51, 51); font-family: monospace, monospace; font-size: 16px; white-space: pre; background-color: rgb(248, 248, 248);">{

 </span><span style="color: rgb(51, 51, 51); font-family: monospace, monospace; font-size: 16px; white-space: pre; background-color: rgb(248, 248, 248);">

 },

 </span><span class="hljs-function" style="box-sizing: inherit; color: rgb(51, 51, 51); font-family: monospace, monospace; font-size: 16px; white-space: pre;"><span class="hljs-keyword" style="box-sizing: inherit; font-weight: 700;">function</span> (<span class="hljs-params" style="box-sizing: inherit;">e</span>) </span><span style="color: rgb(51, 51, 51); font-family: monospace, monospace; font-size: 16px; white-space: pre; background-color: rgb(248, 248, 248);">{</span><span style="color: rgb(51, 51, 51); font-family: monospace, monospace; font-size: 16px; white-space: pre; background-color: rgb(248, 248, 248);">

 </span><span class="hljs-built_in" style="box-sizing: inherit; color: rgb(0, 134, 179); font-family: monospace, monospace; font-size: 16px; white-space: pre;">console</span><span style="color: rgb(51, 51, 51); font-family: monospace, monospace; font-size: 16px; white-space: pre; background-color: rgb(248, 248, 248);">.log(e);

 });

 </span>

</div>

At this point, the exported Excel will have a password, which is the password set in Excel before importing, so that when filing offline, you can control the operation rights of filers, filers can not modify files with password protection.

That's how SpreadJS achieves password permission control in offline filing scenarios. You can do this in Online Table Editor for SpreadJS Complete the password setup and import Excel to see the effect.