SpreadJS is a pure front-end table control from Vineyard combining more than 40 years of control expertise and experience in spreadsheet applications. As an Excel-like control, how does SpreadJS achieve the current popular table collaboration? This article will give a brief introduction.
First of all, from the framework construction, this example adopts the popular development method of separating the front and back ends, and the front end uses npm as scaffolding to build the Svelte framework. The backend uses Java's SpringBoot as the backend framework. The front-end uses SpreadJS V15.2.5 and the SpreadJS online form editor Designer for the front-end platform, and the back-end uses GCExcel as the terminal processing of documents, providing backup and recovery at any time.
First, let's introduce the SpreadJS online form editor built under the front-end Svelte framework.
1. Introduce relevant SpreadJS resources in the pageage.json file
"@grapecity/spread-excelio": "15.2.5",
"@grapecity/spread-sheets": "15.2.5",
"@grapecity/spread-sheets-barcode": "15.2.5",
"@grapecity/spread-sheets-charts": "15.2.5",
"@grapecity/spread-sheets-designer": "15.2.5",
"@grapecity/spread-sheets-designer-resources-cn": "15.2.5",
"@grapecity/spread-sheets-languagepackages": "15.2.5",
"@grapecity/spread-sheets-pdf": "15.2.5",
"@grapecity/spread-sheets-pivot-addon": "15.2.5",
"@grapecity/spread-sheets-pivots": "^14.0.0",
"@grapecity/spread-sheets-print": "15.2.5",
"@grapecity/spread-sheets-resources-zh": "15.2.5",
"@grapecity/spread-sheets-shapes": "15.2.5",
"@grapecity/spread-sheets-tablesheet": "15.2.5", 2. Then, integrate the online form editor Svelte component version. In the previous article, we introduced how to implement an online table editor in the Svelte framework.
With this in mind, we create a new SpreadSheet.svelte file and write it to the basic online table editor.
<script>
import {onMount} from 'svelte';
import '@grapecity/spread-sheets-print';
import "@grapecity/spread-sheets-charts";
import '@grapecity/spread-sheets-shapes';
import '@grapecity/spread-sheets-pivot-addon';
import '@grapecity/spread-sheets-tablesheet';
import '@grapecity/spread-sheets-designer-resources-cn';
import '@grapecity/spread-sheets-designer';
import * as GC from '@grapecity/spread-sheets';
import * as GCDesigner from '@grapecity/spread-sheets-designer';
let designer = null;
onMount(async () => {
designer = new GCDesigner.Spread.Sheets.Designer.Designer(document.getElementById("designerHost"));
let spread = designer.getWorkbook();
});
</script>
<div id="designerHost" class="designer-host"></div>
<style scoped>
@import "@grapecity/spread-sheets-designer/styles/gc.spread.sheets.designer.min.css";
@import '@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css';
.designer-host {
width: 100%;
height: 100vh;
}
</style>
3. There may be more than one collaborative document, we need to create a document list on the page to allow users to choose which document to edit, so we need to create a document list page OnlineSheets.svelte. In this page, we want to implement route jumping, and load document data.
Here we use svelte-spa-router for route jumping and isomorphic-fetch for front-end and back-end data transfer.
<script>
import {onMount} from 'svelte';
import { link } from "svelte-spa-router";
import {Utility} from "../utility.js";
let docList = [];
onMount(async () => {
Utility.getDocList().then(result => {
docList = result.map((item,index)=>{
return {
path:'/Spreadsheet/' + item.substring(0, item.lastIndexOf('.')),
index,
fileName:item
}
})
});
});
</script>
<main class="main">
<table className='table' aria-labelledby="tabelLabel">
<thead>
<tr>
<th>Document</th>
<th></th>
</tr>
</thead>
<tbody>
{#each docList as docItem}
<tr>
<td>{docItem.index}</td>
<td>{docItem.fileName}</td>
<td className='row'>
<a use:link={docItem.path}> Open</a>
</td>
</tr>
{/each}
</tbody>
</table>
</main> The above code implements document list viewing and document jumping, using <a use:link={docItem.path}> Open</a> will jump to the online table editor designed earlier.
At this point, the relevant content of the front-end is ready, and the back-end work is set up.
To prepare the backend, first install Gradle as the package manager. Of course, other tools can also be used instead, such as maven, or the way Origin introduces jar packages that need to be used. After that, create a springboot project to build gradle, reference GCExcel, and the websockets needed for collaboration later.
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<version>2.4.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-websocket</artifactId>
<version>2.4.3</version>
</dependency>
<dependency>
<groupId>com.grapecity.documents</groupId>
<artifactId>gcexcel</artifactId>
<version>4.0.3</version>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.8.6</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>org.apache.tomcat.embed</groupId>
<artifactId>tomcat-embed-jasper</artifactId>
<version>10.0.2</version>
</dependency>
<dependency>
<groupId>com.jayway.jsonpath</groupId>
<artifactId>json-path</artifactId>
<version>2.5.0</version>
</dependency>
</dependencies> In this way, we have built the basic environment of the framework, and then we will introduce how to build webSockets.
In the SpreadSheet.svelte file, write the following code to create the webSocket link:
function connectDocument(docName) {
if (webSocket != null) {
return;
}
var ws = new WebSocket(Utility.webSocketUrl); //'ws://localhost:8090/spreadjs'
ws.onopen = function () {
var data = {
cmd: "connect",
docID: docName
}
ws.send(JSON.stringify(data));
}
ws.onmessage = onmessage;
webSocket = ws;
} Next, we visit the document list page, jump from the document list page to the document, and edit it.
Next we need to listen for operations issued by the frontend. Because the online form editor itself encapsulates all the possible operations of all users, a lot of effort is saved.
onMount(async () => {
//初始化Designer
designer = new GCDesigner.Spread.Sheets.Designer.Designer(document.getElementById("designerHost"));
let spread = designer.getWorkbook();
//fromJSON
openDocument(docName);
//建立webSocket
connectDocument(docName);
var cm = spread.commandManager();
cm.addListener('myListener', onCommandExecute)
}); According to cmd, judge and do some simple encapsulation of the command, and then send the encapsulated command to the server, and then send synchronization instructions through websocket:
function onCommandExecute(args) {
console.log(args.command);
var command = args.command;
var ServerCommand = null;
switch (command.cmd) {
case Utility.ServerCommands.EditCell:
ServerCommand = {
sheetName: command.sheetName,
row: command.row,
column: command.col,
newValue: command.newValue
}
break;
case Utility.ServerCommands.ResizeRow:
ServerCommand = {
sheetName: command.sheetName,
rows: command.rows,
size: command.size
};
break;
case Utility.ServerCommands.ResizeColumn:
ServerCommand = {
sheetName: command.sheetName,
columns: command.columns,
size: command.size
};
break;
case 'Designer.' + Utility.ServerCommands.SetFontFamily:
case 'Designer.' + Utility.ServerCommands.SetFontSize:
case 'Designer.' + Utility.ServerCommands.SetBackColor:
case 'Designer.' + Utility.ServerCommands.SetForeColor:
case 'Designer.' + Utility.ServerCommands.SetFontWeight:
case 'Designer.' + Utility.ServerCommands.SetFontStyle:
case 'Designer.' + Utility.ServerCommands.SetUnderline:
case 'Designer.' + Utility.ServerCommands.SetDoubleUnderline:
if (command.value && command.value.indexOf('undefined') === -1) {
ServerCommand = {
sheetName: command.sheetName,
selections: command.selections,
value: command.value
}
}
break;
case Utility.ServerCommands.MoveFloatingObjects:
ServerCommand = {
sheetName: command.sheetName,
floatingObjects: command.floatingObjects,
offsetX: command.offsetX,
offsetY: command.offsetY
};
break;
case Utility.ServerCommands.ResizeFloatingObjects:
ServerCommand = {
sheetName: command.sheetName,
floatingObjects: command.floatingObjects,
offsetX: command.offsetX,
offsetY: command.offsetY,
offsetWidth: command.offsetWidth,
offsetHeight: command.offsetHeight
};
break;
case Utility.ServerCommands.InsertColumns:
case Utility.ServerCommands.InsertRows:
ServerCommand = {
sheetName: command.sheetName,
selections: command.selections
};
break;
default:
}
if (ServerCommand != null) {
var cmd = command.cmd;
var dotIndex = cmd.lastIndexOf('.');
if (dotIndex !== -1) {
cmd = cmd.substring(dotIndex + 1);
}
ServerCommand.cmd = cmd;
ServerCommand.docID = params.fileName;
Utility.ExecuteCommandAtServer(ServerCommand);
command.docID = ServerCommand.docID;
webSocket.send(JSON.stringify(command))
}
} When the collaborator receives a request through the websocket, use the onmessage method to make a synchronous command. Here, before executing the command on the cooperating side, you need to cancel the previous listener to avoid sending websockets again and causing an infinite loop. After execution, add the listener again.
function onmessage(message) {
var command = JSON.parse(message.data);
command._styles = null;
let spread = designer.getWorkbook()
var cm = spread.commandManager();
cm.removeListener('myListener');
spread.commandManager().execute(command);
cm.addListener('myListener', onCommandExecute);
} At this point, the collaboration base content is completed, let's take a look at what happens after editing the cell content.
As below screenshot shown, modify the E4 cell contents while opening the console network tab.
Change the E4 cell value 2500 to 2000, at which point the EditCell event is triggered and the interactive command is issued:
At this time, a new window is created, the link is copied, and the content of the document has changed to 2000.
As shown in the following GIF:
The above is the Svelte framework combined with SpreadJS to achieve table collaboration documents, if you want to experience more features or download and try SpreadJS for free, welcome to search and visit the official website of Grapevine City.