Creating an Excel Viewer in JavaScript can be a daunting task, but with the SpreadJS JavaScript spreadsheet, the process for creating it is much simpler. In this tutorial blog, we will show you how you can use the power of SpreadJS to create a Viewer that allows you to open and save Excel files in the web browser, as well as protect sheets from being edited and add a password. To follow along with this blog, be sure to download the sample.
Getting Started with SpreadJS
This project will be made with three files: an HTML, a JavaScript file, and a CSS file. We can start by incorporating SpreadJS into our project. You can do this in a few different ways:
Reference Local Files
SpreadJS can be downloaded from our website and imported into an application: GrapeCity, Inc. Once downloaded, we can extract that ZIP file and copy the JS and CSS files to our application, specifically these files:
- gc.spread.sheets.all.xx.x.x.min.js
- gc.spread.sheets.io.xx.x.x.min.js
- gc.spread.sheets.excel2013white.xx.x.x.css
Once we put them in our application’s folder, we can reference them in the code:
<link rel="stylesheet" type="text/css" href="./styles/gc.spread.sheets.excel2013white.css">
<script src="./scripts/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="./scripts/gc.spread.sheets.io.min.js" type="text/javascript"></script>
<script src="./scripts/gc.spread.sheets.charts.min.js" type="text/javascript"></script>
<script src="./scripts/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>Reference NPM
The other way to reference SpreadJS is via the NPM files. These can be added to the application with the following command:
npm install @grapecity/spread-sheets @grapecity/spread-sheets-io @grapecity/spread-sheets-charts @grapecity/spread-sheets-shapes @grapecity/spread-sheets-pivotsThen, we can reference the files in our code:
<link rel="stylesheet" type="text/css" href="./node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="./node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="./node_modules/@grapecity/spread-sheets-io/dist/gc.spread.sheets.io.min.js" type="text/javascript"></script>
<script src="./node_modules/@grapecity/spread-sheets-charts/dist/gc.spread.sheets.charts.min.js" type="text/javascript"></script>
<script src="./node_modules/@grapecity/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>Create HTML content
Once those files are referenced, we can assemble the HTML page and the CSS styles. For the styles, I have created the styles ahead of time:
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-container {
width: calc(100% - 280px);
height: 100%;
float: left;
}
.sample-spreadsheets {
width: 100%;
height: calc(100% - 25px);
overflow: hidden;
}
.options-container {
float: right;
width: 280px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.sample-options {
z-index: 1000;
}
.inputContainer {
width: 100%;
height: auto;
border: 1px solid #eee;
padding: 6px 12px;
margin-bottom: 10px;
box-sizing: border-box;
}
.settingButton {
color: #fff;
background: #82bc00;
outline: 0;
line-height: 1.5715;
position: relative;
display: inline-block;
font-weight: 400;
white-space: nowrap;
text-align: center;
height: 32px;
padding: 4px 15px;
font-size: 14px;
border-radius: 2px;
user-select: none;
cursor: pointer;
border: 1px solid #82bc00;
box-sizing: border-box;
margin-bottom: 10px;
margin-top: 10px;
}
.settingButton:hover {
color: #fff;
border-color: #88b031;
background: #88b031;
}
.settingButton:disabled {
background: #e2dfdf;
border-color: #ffffff;
}
.options-title {
font-weight: bold;
margin: 4px 2px;
}
#selectedFile {
display: none;
}
select, input[type="text"], input[type="number"] {
display: inline-block;
margin-left: auto;
width: 120px;
font-weight: 400;
outline: 0;
line-height: 1.5715;
border-radius: 2px;
border: 1px solid #F4F8EB;
box-sizing: border-box;
}
.passwordIpt {
margin-top: 10px;
height: 25px;
}
.passwordIpt[warning="true"] {
border-color: red;
}
.passwordIpt[warning="true"]::placeholder {
color: red;
opacity: 0.8;
}
@keyframes shake {
0% { transform: translate(1px, 1px) rotate(0deg); }
10% { transform: translate(-1px, -2px) rotate(-1deg); }
20% { transform: translate(-3px, 0px) rotate(1deg); }
30% { transform: translate(3px, 2px) rotate(0deg); }
40% { transform: translate(1px, -1px) rotate(1deg); }
50% { transform: translate(-1px, 2px) rotate(-1deg); }
60% { transform: translate(-3px, 1px) rotate(0deg); }
70% { transform: translate(3px, 1px) rotate(-1deg); }
80% { transform: translate(-1px, -1px) rotate(1deg); }
90% { transform: translate(1px, 2px) rotate(0deg); }
100% { transform: translate(1px, 1px) rotate(0deg); }
}
#warningBox {
color: red;
}We can then add all of the buttons and UI we’ll need for this application, which includes:
- SpreadJS Instance
- Status Bar
- Import Section
- Password Textbox
- File Select Button
- Import Button
- Export Section
- Password Textbox
- Export Button
We can use the appropriate styles for each element when we add them in the HTML body section:
<body>
<div class="sample-tutorial">
<div class="sample-container">
<div id="ss" class="sample-spreadsheets"></div>
<div id="statusBar"></div>
</div>
<div class="options-container">
<div class="option-row">
<div class="inputContainer">
<div class="options-title">Import:</div>
<input class="passwordIpt" id="importPassword" type="password" placeholder="Password" disabled>
<br>
<div id="warningBox"></div>
<input id="selectedFile" type="file" accept=".xlsx" />
<button class="settingButton" id="selectBtn">Select</button>
<button class="settingButton" id="importBtn" disabled>Import</button>
</div>
<div class="inputContainer">
<div class="options-title">Export:</div>
<input class="passwordIpt" id="exportPassword" type="password" placeholder="Password">
<br>
<button class="settingButton" id="exportBtn">Export</button>
</div>
</div>
</div>
</div>
</body>Initialize SpreadJS
Now that we have our files referenced and the HTML content set up, we can initialize the SpreadJS instance and prepare it for adding Excel import code in the app.js file. We can put this inside of the window’s onload function:
window.onload = function () {
let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
}Add Buttons and Functionality
For the purposes of this application, we can also make things easier to write by creating some variables we can utilize for the UI we created before the window.onload function:
const $ = selector => document.querySelector(selector);
const listen = (host, type, handler) => host.addEventListener(type, handler);Now, we can create variables to reference the different HTML elements inside the window.onload function more easily:
const importPassword = $('#importPassword');
const selectBtn = $('#selectBtn');
const fileSelect = $('#selectedFile');
const importBtn = $('#importBtn');
const warningBox = $('#warningBox');
const exportPassword = $('#exportPassword');
const exportBtn = $('#exportBtn');We can now add event listeners and functions for the file select button and password textbox, as well as a handler for an incorrect password message:
listen(selectBtn, "click", () => fileSelect.click());
const fileSelectedHandler = () => {
importPassword.disabled = false;
importBtn.disabled = false;
}
listen(fileSelect, 'change', fileSelectedHandler);
const wrongPasswordHandler = message => {
importPassword.setAttribute('warning', true);
importPassword.style.animation = "shake 0.5s";
setTimeout(() => importPassword.style.animation = "", 500);
warningBox.innerText = message;
importPassword.value = '';
};
listen(importPassword, 'focus', () => {
warningBox.innerText = '';
importPassword.removeAttribute('warning');
});Importing an Excel File into SpreadJS
Now, we can add the code to import an Excel file into our SpreadJS instance. Since we are potentially importing password-protected files, we need to account for that when calling the SpreadJS import function. We can also add the event handler once we write the function:
const importFileHandler = () => {
let file = fileSelect.files[0];
if (!file) return ;
spread.import(file, console.log, error => {
if (error.errorCode === GC.Spread.Sheets.IO.ErrorCode.noPassword || error.errorCode === GC.Spread.Sheets.IO.ErrorCode.invalidPassword) {
wrongPasswordHandler(error.errorMessage);
}
}, {
fileType: GC.Spread.Sheets.FileType.excel,
password: importPassword.value
});
};
listen(importBtn, 'click', importFileHandler);Exporting an Excel File from SpreadJS
Similar to importing, we can support the user entering a password to add to an Excel file when we export, so we just need to pass in the password in the SpreadJS export function. We’ll add an event handler for this as well:
const exportFileHandler = () => {
let password = exportPassword.value;
spread.export(blob => saveAs(blob, (password ? 'encrypted-' : '') + 'export.xlsx'), console.log, {
fileType: GC.Spread.Sheets.FileType.excel,
password: password
});
};
listen(exportBtn, 'click', exportFileHandler);Protecting the Data
We can also protect the data to prevent users from changing it. To accomplish this, we can add a button that will protect the current sheet of the workbook. This can be changed to fit any sort of requirement but will be the active sheet for the purposes of this sample. Similar to the other buttons, we need to add a handler for clicking on it, but with SpreadJS, we can also specify the protection options:
const protectHandler = () => {
var option = {
allowSelectLockedCells:true,
allowSelectUnlockedCells:true,
allowFilter: true,
allowSort: false,
allowResizeRows: true,
allowResizeColumns: false,
allowEditObjects: false,
allowDragInsertRows: false,
allowDragInsertColumns: false,
allowInsertRows: false,
allowInsertColumns: false,
allowDeleteRows: false,
allowDeleteColumns: false,
allowOutlineColumns: false,
allowOutlineRows: false
};
spread.getSheet(0).options.protectionOptions = option;
spread.getSheet(0).options.isProtected = true;
};
listen(protectBtn, 'click', protectHandler);Running the Application
All that is left now is to run the application. Since we have made this with PureJS and HTML, we can simply open the HTML file in a web browser:
We can click on the “Select” button to select an Excel file to load, and then we can click on the “Import” button to import it into SpreadJS:
Now we can add a password by typing it into the Password text box under export and click on the “Export” button:
You have now made your very own Excel Viewer with SpreadJS! With this, you can open, protect, and add a password to Excel files before exporting them, all in a few easy steps.
To try this out for yourself, be sure to download a trial of SpreadJS today: Download JavaScript Spreadsheet | SpreadJS
For more information, check out our demos and documentation.
Tags:
Related Blogs
Try Our JavaScript Spreadsheet Components
SpreadJS Resources
Kevin Ashley