Use code similar to the following to set a formula for a cell or a range of cells.
You can set a range of cells with formula relative reference auto offset.
Use code similar to the following to set array formula for a range.
You can check if there is a formula in the specified cell by using the following code:
If a formula is lengthy or used frequently, you can add a custom name for it to make using it more convenient.
A custom name can be removed from the custom name collection like this:
You can remove all custom names by using the following method:
You can use the sheet property ShowFormulas to show the formula content instead of the values. With ShowFormulas, you can copy the formulas string to another applications, or print the formulas.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
<gc-worksheet>
</gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<div class="options-row" style="font-size:16px;padding:1px 0px">
Setting a formula via code
</div>
<div class="options-row" style="padding:2px 10px">
Specify the row and column index for the formula cell. Set the row count and column count to specify
the number of rows and columns to add the formula to. Add the formula to the Formula box and then
click SetFormula to apply the formula.
</div>
<div class="options-row">
<label for="rowIndex">RowIndex</label>
<input type="text" id="rowIndex" v-model="rowIndex">
</div>
<div class="options-row">
<label for="columnIndex">ColumnIndex</label>
<input type="text" id="columnIndex" v-model="columnIndex">
</div>
<div class="options-row">
<label for="rowCount">RowCount</label>
<input type="text" id="rowCount" v-model="rowCount">
</div>
<div class="options-row">
<label for="columnCount">ColumnCount</label>
<input type="text" id="columnCount" v-model="columnCount">
</div>
<div class="options-row">
<label for="formula">Formula:</label>
<input type="text" id="formula" v-model="formula">
<input type="button" value="SetFormula" @click="setFormula()">
</div>
</div>
</div>
</template>
<script setup>
import { ref, watch } from "vue";
import "@mescius/spread-sheets-vue";
import GC from '@mescius/spread-sheets';
const spreadRef = ref(null);
const rowIndex = ref("");
const columnIndex = ref("");
const rowCount = ref("");
const columnCount = ref("");
const formula = ref("=SUM(B2,B6)");
let initSpread = function (spread) {
spreadRef.value = spread;
let sheet = spread.getSheet(0);
sheet.suspendPaint();
sheet.setArray(0, 0, [
["Product", "Item Price", "Quantity", "Sales"],
['Kraft Real Mayo', 5.71, 1],
['Smartfood Popcorn', 2.5, 4],
['Teddy Grahams Crackers', 35, 5],
['Parmesan Cheese', 14.89, 9],
['Planter Deluxe Whole Cashew', 8.52, 3],
['Total']
]);
sheet.setColumnWidth(0, 190);
sheet.setColumnWidth(1, 80);
sheet.setColumnWidth(2, 80);
sheet.getRange(1, 3, 5, 1).formula("B2*C2", true);
sheet.addCustomName('customName1', '=$B$2:$B$6', 0, 0);
sheet.addCustomName('customName2', '=$C$2:$C$6', 0, 0);
sheet.setFormula(6, 1, "=SUM(customName1)");
sheet.setFormula(6, 2, "=SUM(customName2)");
sheet.getRange(6, 0, 1, 4).foreColor('red');
sheet.setFormula(6, 3, "B7*C7");
let style = sheet.getStyle(4, 7) || new GC.Spread.Sheets.Style();
style.cellButtons = [{
useButtonStyle: true,
caption: "Show Formulas",
width: 120,
command: function () {
sheet.options.showFormulas = !sheet.options.showFormulas;
if (sheet.options.showFormulas) {
style.cellButtons[0].imageType = GC.Spread.Sheets.ButtonImageType.ok;
} else {
style.cellButtons[0].imageType = GC.Spread.Sheets.ButtonImageType.none;
}
sheet.setStyle(4, 7, style);
},
}];
sheet.setStyle(4, 7, style);
sheet.setColumnWidth(7, 122);
sheet.resumePaint();
}
function setFormula() {
let spread = spreadRef.value;
let sheet = spread.getActiveSheet();
if (formula.value) {
let formulaValue = formula.value;
let rowIndexValue = parseInt(rowIndex.value, 10);
let columnIndexValue = parseInt(columnIndex.value, 10);
if (!isNaN(rowIndexValue) && !isNaN(columnIndexValue)) {
let rowCountValue = Math.max(parseInt(rowCount.value, 10));
let columnCountValue = Math.max(parseInt(columnCount.value, 10));
if (!isNaN(rowCountValue) && !isNaN(columnCountValue)) {
sheet.setArrayFormula(rowIndexValue, columnIndexValue, rowCountValue, columnCountValue, formulaValue);
} else {
sheet.setFormula(rowIndexValue, columnIndexValue, formulaValue);
}
}
}
}
</script>
<style scoped>
#app {
height: 100%;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.options-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
input {
display: inline-block;
}
input[type="text"] {
width: 160px;
}
label {
display: inline-block;
margin-bottom: 6px;
width: 200px;
}
select {
width: 120px;
height: 35px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script src="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-resources-en': 'npm:@mescius/spread-sheets-resources-en/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
