概述
最近项目有用要使用到报表相关业务开发，综合考虑，最后选择了speadjs，这个库提供的功能很强大，拥有wps的绝大部功能，能满足项目需求，如果需要商用，需要自行找对应的客服进行授权，这里记录一期插件的基本使用。
基本报表功能（不带表格设计器）
效果
能做到编辑、样式修改、导入表格，导出表格等
代码
结合提供的和vue相关的组件使用，具体其他框架的使用方法基本一致，可到官网自行查看
<template>
<div class="sheet-wrap">
<!-- 操作区 -->
<div class="operate-area">
导入： <input type="file" @change="handleImportData" />
<el-button type="primary" @click="handleExportData"> 导出</el-button>
<el-button type="primary" @click="saveToDB"> 保存</el-button>
<el-button type="primary" @click="getExcelData"> 获取表格数据</el-button>
</div>
<div class="sample-tutorial">
<!-- 设计器 -->
<div id="designerHost"></div>
<div id="NameBox"></div>
<gc-spread-sheets
class="sample-spreadsheets"
@workbookInitialized="initSpread"
@editChange="rangeChanged"
>
<gc-worksheet></gc-worksheet>
</gc-spread-sheets>
<!-- 底部状态栏 -->
<div id="statusBar"></div>
</div>
</div>
</template>
<script>
import "@grapecity/spread-sheets-vue"; //vue表格组件
import GC from "@grapecity/spread-sheets"; //表格
import "@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013lightGray.css"; //样式
import "@grapecity/spread-sheets-resources-zh";
// import spreadExcel from "@grapecity/spread-excelio"; //ExcelIO（内部是commonjs的规范导出的，因此只能使用commonjs的规范导入）
// 获取IO类的实例
const spreadExcel = require("@grapecity/spread-excelio");
import saveAs from "file-saver"; // 导入文件保存模块
GC.Spread.Common.CultureManager.culture("zh-cn"); //中文化
//设置开发授权key(本地测试随便写或者不设置都能用)
// let SpreadJSKey = "xxx";
// GC.Spread.Sheets.LicenseKey = SpreadJSKey;
import { getData } from "./data";
export default {
data() {
return {
autoGenerateColumns: false,
spread: null,
};
},
methods: {
// 初始化
initSpread(spread) {
this.$nextTick(() => {
this.spread = spread;
// 自定义数据
let sheet = spread.getSheet(0);
// 设置数据
let table = sheet.tables.addFromDataSource(
"Table1",
0,
0,
getData(),
GC.Spread.Sheets.Tables.TableThemes.medium2
);
// 高亮第一列
table.highlightFirstColumn(true);
// 显示标题
table.showHeader(true);
// 列宽
sheet.setColumnWidth(0, 130);
sheet.setColumnWidth(1, 130);
sheet.setColumnWidth(2, 70);
sheet.setColumnWidth(3, 70);
sheet.setColumnWidth(4, 100);
sheet.setColumnWidth(5, 260);
// 设置表格标签颜色
spread.setSheetCount(3);
spread.startSheetIndex(0);
spread.getSheet(0).options.sheetTabColor = "red";
spread.getSheet(1).options.sheetTabColor = "#FFFF00";
spread.getSheet(2).options.sheetTabColor = "Accent 4";
// sheet1.setValue(0, 0, "Hello World!");
// 根据后端返回的json数据设置(其中json数据格式为this.spread.toJson函数返回的数据格式)
// const json = `{version:"xxx",docProps:{},namedStyles:{}。。。。}`
// this.spread.fromJSON(JSON.stringify(json));
// 初始化底部状态栏
this.initBottomStatusBar();
// 初始化名称对话框
this.intNameBox();
});
},
// 初始化名称对话框
intNameBox() {
let sheet = this.spread.getActiveSheet();
let nameBox = new GC.Spread.Sheets.NameBox.NameBox(
document.getElementById("NameBox"),
this.spread
);
console.log(nameBox);
},
// 初始化底部状态栏
initBottomStatusBar() {
let statusBar = new GC.Spread.Sheets.StatusBar.StatusBar(
document.getElementById("statusBar")
);
statusBar.bind(this.spread);
},
// 导入数据
handleImportData(e) {
// excel文件
const file = e.target.files[0];
let excelIO = new spreadExcel.IO();
excelIO.open(
file,
(json) => {
console.log("json", json);
this.spread.fromJSON(json);
},
(e) => {
console.log(e);
}
);
},
//导出数据
handleExportData() {
// 获取IO类的实例
let excelIO = new spreadExcel.IO();
let fileName = "test.xlsx";
let json = JSON.stringify(this.spread.toJSON());
excelIO.save(
json,
(blob) => {
saveAs(blob, fileName);
},
(e) => {
console.log(e);
}
);
},
// 保存到数据库
saveToDB() {
let sheet = this.spread.getActiveSheet();
// 将脏单元格信息存储在dirtyCells变量中
// 或者变更后的单元格相关数据（包含新旧值）
let dirtyCells = sheet.getDirtyCells();
// console.log(dirtyCells);
// 将变更后的数据转换成json数据（可提交给后端更新）
console.log(JSON.stringify(dirtyCells, null, 4));
// 注意: 向数据库发布变更
this.$message.success("保存成功");
},
// 单元格内容变更（https://demo.grapecity.com.cn/spreadjs/help/api/classes/GC.Spread.Sheets.Events#rangechanged）
rangeChanged(data, args) {
console.log(data, args);
this.$message.success("自动保存成功");
},
// 获取表格数据
getExcelData() {
// 这里整块给后端，包含表格样式数据，公式等等
console.log("excel-data", this.spread.toJSON());
},
},
};
</script>
<style>
.sheet-wrap {
width: 100%;
height: 700px;
}
.spread-host {
width: 100%;
height: 700px;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 500px;
}
#statusBar {
height: 30px;
}
</style>
依赖
{
"name": "vue2-project",
"version": "0.1.0",
"private": true,
"scripts": {
"serve": "vue-cli-service serve",
"build": "vue-cli-service build"
},
"dependencies": {
"@grapecity/spread-excelio": "^17.0.0",
"@grapecity/spread-sheets": "^17.0.0",
"@grapecity/spread-sheets-io": "^17.0.0",
"@grapecity/spread-sheets-languagepackages": "^17.0.0",
"@grapecity/spread-sheets-pdf": "^17.0.0",
"@grapecity/spread-sheets-resources-zh": "^17.0.0",
"@grapecity/spread-sheets-vue": "^17.0.0",
"chroma-js": "^2.4.2",
"core-js": "^3.8.3",
"element-ui": "^2.15.14",
"file-saver": "^2.0.5",
"register-service-worker": "^1.7.2",
"relation-graph": "^2.1.24",
"vue": "^2.6.14",
"vue-router": "^3.5.1",
"vuex": "^3.6.2"
},
"devDependencies": {
"@vue/cli-plugin-babel": "~5.0.0",
"@vue/cli-plugin-pwa": "~5.0.0",
"@vue/cli-plugin-router": "~5.0.0",
"@vue/cli-plugin-vuex": "~5.0.0",
"@vue/cli-service": "~5.0.0",
"less": "^4.0.0",
"less-loader": "^8.0.0",
"vue-template-compiler": "^2.6.14"
}
}
完善报表功能（带表格设计器）
效果
代码
<template>
<div class="wrap">
<div class="operate-area">
导入： <input type="file" @change="handleImportData" />
<el-button type="primary" @click="handleExportData"> 导出</el-button>
<el-button type="primary" @click="saveToDB"> 保存</el-button>
<el-button type="primary" @click="getExcelData"> 获取表格数据</el-button>
</div>
<!-- 带设计器的完整表格控件 -->
<div id="designerHost">
<gc-spread-sheets-designer
:styleInfo="styleInfo"
:config="config"
@designerInitialized="designerInitialized"
ref="desinger"
>
</gc-spread-sheets-designer>
</div>
</div>
</template>
<script>
import GC from "@grapecity/spread-sheets"; //表格
// // 获取IO类的实例
const spreadExcel = require("@grapecity/spread-excelio");
import saveAs from "file-saver"; // 导入文件保存模块
import "@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css";
import "@grapecity/spread-sheets-designer/styles/gc.spread.sheets.designer.min.css";
import "@grapecity/spread-sheets-designer-resources-cn";
import "@grapecity/spread-sheets-designer-vue";
import "@grapecity/spread-sheets-resources-zh";
GC.Spread.Common.CultureManager.culture("zh-cn"); //中文化
import { getData } from "./data";
// 设置开发授权key(本地测试随便写或者不设置都能用)
// let SpreadJSKey = "xxx";
// GC.Spread.Sheets.LicenseKey = SpreadJSKey;
export default {
data() {
//所有设计器tab配置
let config = GC.Spread.Sheets.Designer.DefaultConfig;
// 去掉开始菜单
// config.ribbon = config.ribbon.filter((item) => item.id !== "file");
// 隐藏文件菜单
config.fileMenu = "";
console.log(config);
// 注册自定义的tab
config.commandMap = {
Welcome: {
title: "自定义菜单",
text: "自定义菜单",
iconClass: "ribbon-button-welcome",
bigButton: "true",
commandName: "Welcome",
execute: async (context, propertyName, fontItalicChecked) => {
// 点击触发事件
alert("Welcome to new designer.");
},
},
};
// 追加到现存的菜单位置
config.ribbon[0].buttonGroups.unshift({
label: "自定义菜单项",
thumbnailClass: "welcome",
commandGroup: {
children: [
{
direction: "vertical",
commands: ["Welcome"],
},
// This is custom button ----------------end-------------
],
},
});
return {
autoGenerateColumns: false,
spread: null,
styleInfo: { height: "100%", width: "100%" },
config: config,
spreadOptions: {
sheetCount: 2,
},
designer: null,
};
},
methods: {
designerInitialized(value) {
this.designer = value;
this.spread = this.designer.Spread;
this.bindCellDataChange();
},
bindCellDataChange() {
console.log(this.spread.toJSON());
let sheet = this.spread.getActiveSheet();
// 绑定表格数据变更事件
this.spread.bind(
GC.Spread.Sheets.Events.EditChange,
function (sender, args) {
console.log(
"Cell (" + args.row + ", " + args.col + ") data has been changed."
);
}
);
},
// 导入数据
handleImportData(e) {
// excel文件
const file = e.target.files[0];
let excelIO = new spreadExcel.IO();
excelIO.open(
file,
(json) => {
console.log("json", json);
this.spread.fromJSON(json);
},
(e) => {
console.log(e);
}
);
},
//导出数据
handleExportData() {
// 获取IO类的实例
let excelIO = new spreadExcel.IO();
let fileName = "test.xlsx";
let json = JSON.stringify(this.spread.toJSON());
excelIO.save(
json,
(blob) => {
saveAs(blob, fileName);
},
(e) => {
console.log(e);
}
);
},
// 保存到数据库
saveToDB() {
let sheet = this.spread.getActiveSheet();
console.dir(this.spread);
// 将脏单元格信息存储在dirtyCells变量中
// 或者变更后的单元格相关数据（包含新旧值）
let dirtyCells = sheet.getDirtyCells();
// 将变更后的数据转换成json数据（可提交给后端更新）
console.log(JSON.stringify(dirtyCells, null, 4));
// 注意: 向数据库发布变更
this.$message.success("保存成功");
},
// 单元格内容变更（https://demo.grapecity.com.cn/spreadjs/help/api/classes/GC.Spread.Sheets.Events#rangechanged）
rangeChanged(data, args) {
console.log(data, args);
this.$message.success("自动保存成功");
},
// 获取表格数据
getExcelData() {
// 这里整块给后端，包含表格样式数据，公式等等
console.log("excel-data", this.spread.toJSON());
},
},
};
</script>
<style>
.wrap {
height: 100vw;
}
#designerHost {
height: 800px;
}
</style>
依赖
{
"name": "vue2-project",
"version": "0.1.0",
"private": true,
"scripts": {
"serve": "vue-cli-service serve",
"build": "vue-cli-service build"
},
"dependencies": {
"@grapecity/spread-excelio": "^17.0.0",
"@grapecity/spread-sheets": "^17.0.0",
"@grapecity/spread-sheets-barcode": "^17.0.0",
"@grapecity/spread-sheets-charts": "^17.0.0",
"@grapecity/spread-sheets-designer": "^17.0.0",
"@grapecity/spread-sheets-designer-resources-cn": "^17.0.0",
"@grapecity/spread-sheets-designer-resources-en": "^17.0.0",
"@grapecity/spread-sheets-designer-vue": "^17.0.0",
"@grapecity/spread-sheets-formula-panel": "^17.0.0",
"@grapecity/spread-sheets-ganttsheet": "^17.0.0",
"@grapecity/spread-sheets-io": "^17.0.0",
"@grapecity/spread-sheets-languagepackages": "^17.0.0",
"@grapecity/spread-sheets-pdf": "^17.0.0",
"@grapecity/spread-sheets-pivot-addon": "^17.0.0",
"@grapecity/spread-sheets-print": "^17.0.0",
"@grapecity/spread-sheets-resources-zh": "^17.0.0",
"@grapecity/spread-sheets-shapes": "^17.0.0",
"@grapecity/spread-sheets-slicers": "^17.0.0",
"@grapecity/spread-sheets-tablesheet": "^17.0.0",
"@grapecity/spread-sheets-vue": "^17.0.0",
"chroma-js": "^2.4.2",
"core-js": "^3.8.3",
"element-ui": "^2.15.14",
"file-saver": "^2.0.5",
"register-service-worker": "^1.7.2",
"relation-graph": "^2.1.24",
"vue": "^2.6.14",
"vue-router": "^3.5.1",
"vuex": "^3.6.2"
},
"devDependencies": {
"@vue/cli-plugin-babel": "~5.0.0",
"@vue/cli-plugin-pwa": "~5.0.0",
"@vue/cli-plugin-router": "~5.0.0",
"@vue/cli-plugin-vuex": "~5.0.0",
"@vue/cli-service": "~5.0.0",
"less": "^4.0.0",
"less-loader": "^8.0.0",
"vue-template-compiler": "^2.6.14"
}
}
总结
对应项目运行不起来请查看版本是否对应，或者参照官网文档示例。
原文链接：https://juejin.cn/post/7316772279522033714 作者：gnip
