Through environment building and component integration, we learned to use Vite and SpreadJS to integrate online Excel editing capabilities into Vue 3 projects.

In this chapter, I will continue to extend the prototype of Vue 3 to implement data binding, template file import/update/export, and Pivottables. The implementation idea of this chapter is basically similar to that of the previous article “Component Integration”.

Design ideas

· Create two components of SpreadJS and Designer at the same time to display different component types by switching routes.

· Add “Load” and “Update” buttons to the toolbar of the editor component.

· Click “Load” to load the Excel file obtained from the server, modify the component in the editor, and click “Update” button to transfer the modified file to the server.

· Switch the route to display the SpreadJS component, and add two buttons “load” and “update” to the component, with the same functions as above.

SpreadJS component introduction

SpreadJS is an HTML5-based native JavaScript component that is compatible with more than 450 Excel formulas and provides a high degree of Excel like functionality for developing Web Excel components. Realize multi-user collaborative editing, high-performance template design, data filling and other functional modules, the component architecture conforms to UMD specifications, can be embedded in various applications in a native way, and combined with the backend technology framework.

integration SpreadJS components

First integrate SpreadJS in components/ Spreadsheet.vue, as shown below:

<template> <div> <div ref="ssHost" style="height:700px; width:100%; text-align: left;" ></div> </div> </template> <script> import { onMounted, ref} from "vue"; import ".. /.. /node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css" import GC from "@grapecity/spread-sheets" export default { name: 'SpreadSheets', props: { }, setup(props, {emit}) { const ssHost = ref(null); onMounted(() => { var workbook = new GC.Spread.Sheets.Workbook(ssHost.value); emit("workbookInitialized", workbook) }); return { ssHost }; } } </script>Copy the code

The steps are as follows:

  1. Add a div to the template. This div is the spread container. You can set the width and height of the container through CSS, which is to define the display size and position of the spread.

  2. The dependencies needed to import this component.

  3. Create a new spread in the setup method.

  4. Introduce this component and related dependencies in views/ spreadsheet.vue.

import SpreadSheets from '.. /components/SpreadSheets.vue' import {ref} from "vue" import axios from "axios" import GC from '@grapecity/spread-sheets' import ExcelIO from '@grapecity/spread-excelio'Copy the code
  1. Use the component label in the template
<template> <div> < button@click ="load($event)"> load </button> < button@click ="update($event)"> update </button> <SpreadSheets v-on:workbookInitialized="workbookInitialized"></SpreadSheets> </div> </template>Copy the code
  1. Initialize spread in the setup function.

let workbook = undefined;

let workbookInitialized=(wb)=>{

      workbook = wb

    }

Copy the code

Function expansion: Excel document import/update/export

The import/update/export function of Excel documents can be realized by customizing the “load”, “update” and “export” buttons.

· The Load method performs the loading of Excel files. After receiving the JSON data transferred in the background, the file is loaded using fromJSON method.

let load = (e)=>{ let formData = new FormData(); formData.append("fileName", "path"); axios.post('spread/loadTemplate', formData, { responseType: "Json"}). Then ((response) = > {the if (response) {alert (" load success "); templateJSON = response.data; workbook.fromJSON(templateJSON); }}). Catch ((response) => {alert(" error "); })}Copy the code

· Update Updates the execution file. In the designer to load the file to do some operations, such as modifying the background color, add text, etc., using the toJSON method to save the current spread as JSON data to the background storage.

let update = (e)=>{ let spreadJSON = JSON.stringify(workbook.toJSON()); let formData = new FormData(); formData.append("jsonString", spreadJSON); formData.append("fileName", "fileName"); Axios.post ('spread/updateTemplate', formData). Then ((response) => {if(response) {alert(" update successful "); }}). Catch ((response) => {alert(" error "); })}Copy the code

· The Save method performs the spread JSON export as an Excel file.


  *//export SpreadJS json to excel file*

 excelio.save(json, **function** (blob) {

  *//do whatever you want with blob*

  *//such as you can save it*

 }, **function** (e) {

  *//process error*

  console.log(e);

 });

Copy the code

Extensions: Pivottables

SpreadJS has a built-in PivotTable feature that, by integrating it into a project, makes it possible to use pivottables, which support conditional formats such as sorting and filtering, analyze data by different dimensions, and customize topics.

SpreadJS pivottables support data sources in two formats:

· Table name: PivotTable uses the name of a table to fetch the data source.

· Range formula: PivotTable uses a range formula of the worksheet to get data. This range formula must be the absolute path of the worksheet.

To use pivottables, add the following link to the beginning of the document:

<head> <script src='... /spreadjs/gc.spread.sheets.all.x.x.x.min.js' type='text/javascript'></script> <script src='... /spreadjs/plugins/gc.spread.pivot.x.x.x.min.js' type='text/javascript'></script> </head>Copy the code

Create a PivotTable with the name of a table:

    let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 });
    let sheet1 = spread.getSheet(0);
    let sheet2 = spread.getSheet(1);
    sheet1.setRowCount(250);
    let table = sheet.tables.add('table1', 0, 0, 200, 200);
    sheet1.setArray(0, 0, pivotSales);
    let pivotTable = sheet2.pivotTables.add("PivotTable", "table1", 0, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium2);

Copy the code

A PivotTable can also be created using a range formula for the worksheet:

let range = "=Sheet2! A1:D4"; let pivotTable = sheet2.pivotTables.add("PivotTable", range, 0, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium2);Copy the code

By switching routes, you can see that SpreadJS and the online table editor can switch display normally. Modify the loaded Excel file in the editor, click the “Update” button on the toolbar, then switch to the SpreadJS component and click the “Load” button to see that the modified file has been updated.

The sample code

· Vue3 integrated SpreadJS component Demo download

That’s all it takes to integrate SpreadJS with an online table editor in Vue 3. In addition to Vue, SpreadJS can be embedded in a variety of applications (desktop, APP, Web) in a native way. It is combined with various backend technology frameworks (Java,.NET, JavaScript, Vue, React, etc.).

By integrating SpreadJS, developers can develop high-performance formula calculations, online import and export of Excel documents, pivottables and visual analysis, providing users with a flexible and easy-to-use user experience.

Further reading

· Vue 3 component development practice: Build spreadJs-based table editing system (Environment Building)

· Vue 3 component development practice: Build spreadJs-based table editing system (component integration)

· SpreadJS Vue framework support