SpreadJS has always supported the ability to format cells to satisfy different requirements, but when it came to implementing formulas in addition to specialized formatting, multiple cells had to be used.
With SpreadJS v13.1, we have combined this idea into a feature called, format strings.
Here, we'll go over the features of format strings, and how you can leverage them in your application.
These three different parts are:
- Text Templates
- Formulas
- @ Symbol
They can be used separately or combined to fit your specific requirements for formatting cells in your workbook. In this blog, we will import an Excel template and add format strings to a few of the cells to show how they can enhance your workbooks with just a few lines of JavaScript code.
Project Setup
To start working with format strings, we first can create a simple HTML file with SpreadJS references:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<title>SpreadJS Format Strings</title>
<link href="./node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="./node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js"></script>
<script type="text/javascript" src="./node_modules/@grapecity/spread-excelio/dist/gc.spread.excelio.min.js"></script>
</head>
<body>
<div id="spreadSheet" style="width: 1300px; height: 800px; border: 1px solid gray"></div>
</body>
</html>
Since we reference the SpreadJS NPM files, we will want to navigate to the project folder with a command prompt and run the following command: npm install @grapecity/spread-sheets @grapecity/spread-sheets-excelio
For this demo, we can use an Excel template and import it into SpreadJS. For simplicity, I created a JS file from the Excel template using the SpreadJS Designer, and have included it in the demo zip file. To load this file in the HTML page, simply add the following line:
<script type="text/javascript" src="./ExcelTemplate.js"></script>
We can also add script code to initialize the SpreadJS instance and load that template into it:
<script>
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("spreadSheet"), { sheetCount: 1 });
spread.fromJSON(ExcelTemplate);
var sheet = spread.getActiveSheet();
}
</script>
Now we have our HTML file and script ready to add format strings to:
Text Templates
In the first type of format string, we can add our text templates. These essentially allow you to perform string concatenation without having to have separate cells for data and text. In this case, we can add together the totals for both the "Projected Monthly Income" and "Actual Monthly Income" sections. There is not a formula in those sections yet to add them together, so we can both create the formula and define the string to format with.
We can create a separate style for each cell to define four things:
- Formatter
- Background Color
- Font
- Vertical Alignment
The displayed text can be written normally when defining a text template.
However, the formulas that you want to have evaluated will need to start with a "$" followed by brackets, like so:
"Formulas go within the brackets like this: $\{{ FORMULA HERE }}"
In this case, we want to specify that the SUM formula we are adding is the total for each monthly income, so we can define the style as so:
var textTemplateStyle1 = new GC.Spread.Sheets.Style();
textTemplateStyle1.formatter = "Total projected monthly income is $\{{=SUM(C5:C6)}}";
textTemplateStyle1.backColor = "#91e0ff";
textTemplateStyle1.font = "bold 12pt Lucida Sans";
textTemplateStyle1.vAlign = GC.Spread.Sheets.VerticalAlign.center;
Then we can set that style for the cell: sheet.setStyle(6, 1, textTemplateStyle1);
The cells with that style will now show the text combined with the evaluated result of that formula like so:
This can be especially useful for combining a lot of different formulas and values into one cell without having to use extra cells for calculation, like with reports or dashboards.
Formulas and the "@" Symbol
Another way to use format strings is with formulas and data. In some cases, you might have a cell in your workbook that has some data in it, but you want to use that data within some calculations in the same cell. This is where the "@" symbol comes in: you can reference the data in the cell itself when creating a format string for that cell. As an example of this project, we can use it to reference the name of the data set in the cell linked to a cell range reference via named ranges.
When using the name of the range, SpreadJS will return the cell range reference which we can create a sparkline with.
To start off, we can add some custom names for the cell range references:
sheet.addCustomName('Housing', '=$C$15:$C$24', 0, 0);
sheet.addCustomName('Entertainment', '=$H$15:$H$23', 0, 0);
sheet.addCustomName('Loans', '=$H$27:$H$32', 0, 0);
Those cell range references correspond to the "Projected Cost" column for each of the different sections in the Personal Monthly Budget sheet. To make it easier for the user to change the data for the sparkline, we can create a drop-down list for the names of each section, making sure they match the custom names we defined:
var formulaStyle = new GC.Spread.Sheets.Style();
formulaStyle.backColor = '#fff2cc';
formulaStyle.cellButtons = [
{
imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
command: "openList",
useButtonStyle: true
}
]
formulaStyle.dropDowns = [
{
type: GC.Spread.Sheets.DropDownType.list,
option: {
items: [
{
text: 'Housing',
value: 'Housing'
},
{
text: 'Entertainment',
value: 'Entertainment'
},
{
text: 'Loans',
value: 'Loans'
}
]
}
}
];
Now we have everything we need to use the lookup table, formulas, and the "@" symbol to create a dynamic sparkline with a format string. This string will have 3 parts to it:
- @
- Use the current cell value (in this case the data section name) to get the corresponding cell range reference string from the named range
- INDIRECT(<above cell range reference string)
- Get the cell range reference from the cell range reference string of
- COLUMNSPARKLINE(, 0)
- Create a column sparkline from the cell range reference
Combined all together into the formatter, this would be defined as:
formulaStyle.formatter = '=COLUMNSPARKLINE(INDIRECT(@),0)';
Finally we can set that style with the formatter in the cell: sheet.setStyle(9, 4, formulaStyle);
Since the sparkline doesn't show the name of the data, we can set another format string to just show the data section name in the cell below:
sheet.getRange(11, 4, 1, 1).formatter('=E10');
With all of these format strings in use, you can see that you can create an interactive budget sheet with only a few lines of JavaScript code.
Format Strings are just one of the many new features we have added to SpreadJS with v13.1.
Kevin Ashley