Except the back color of solid fill, now SpreadJS support gradient cell fills and pattern cell fills.
The following sample shows set with gradient top to bottom with red-white-blue.
The following sample shows set with gradient inner to outer with red-white-blue.
The following sample shows the lightHorizontal red pattern fill on the white background.
import { Component, NgModule, enableProdMode } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
import { platformBrowserDynamic } from '@angular/platform-browser-dynamic';
import { SpreadSheetsModule } from '@mescius/spread-sheets-angular';
import GC from '@mescius/spread-sheets';
import './styles.css';
@Component({
selector: 'app-component',
templateUrl: 'src/app.component.html'
})
export class AppComponent {
autoGenerateColumns = false;
dataSource: any[];
spread: GC.Spread.Sheets.Workbook;
hostStyle = {
width: 'calc(100% - 280px)',
height: '100%',
overflow: 'hidden',
float: 'left'
};
constructor() {
}
initSpread($event: any) {
this.spread = $event.spread;
let spread = this.spread
spread.suspendPaint();
let sheet = spread.getActiveSheet();
this.initSheet(sheet);
// Set range with pattern fill
sheet.getRange(3, 2, 3, 2).backColor({type: GC.Spread.Sheets.PatternType.darkDown, backgroundColor: '#D3D3D3', patternColor: "white"});
sheet.getRange(6, 1, 12, 1).backColor({type: GC.Spread.Sheets.PatternType.gray125, backgroundColor: '#B3B3B3', patternColor: "white"});
// Set range with gradient path fill
sheet.getRange(1, 3, 1, 1).backColor({type:"path", left: 0.1, top: 0.3, right: 0.9, bottom: 0.6, stops: [{position:0, color:"#C3C3C3"},{position:1, color:"white"}]});
// Set conditional format with gradient path fill
let cfs = sheet.conditionalFormats;
let style = new GC.Spread.Sheets.Style();
style.backColor = {degree: 90, stops: [{position:0.3, color:"white"},{position:1, color:"#C3C3C3"},]};
cfs.addCellValueRule(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.greaterThan, 250000, 0, style, [new GC.Spread.Sheets.Range(6, 2, 12, 1)]);
// Set table style with gradient fill
let border = new GC.Spread.Sheets.LineBorder();
let tableStyle = new GC.Spread.Sheets.Tables.TableTheme();
tableStyle.name('tableStyle1');
tableStyle.secondRowStripStyle(new GC.Spread.Sheets.Tables.TableStyle({degree: 90, stops: [{position:0, color:"accent 1"},{position:0.1, color:"white"},{position:0.7, color:"white"},{position:1, color:"accent 6"},]}, 'black', '10px arial', border, border, border, border, border, border))
tableStyle.firstRowStripStyle(new GC.Spread.Sheets.Tables.TableStyle({degree: 90, stops: [{position:0, color:"accent 6"},{position:0.1, color:"white"},{position:0.7, color:"white"},{position:1, color:"accent 1"},]}, 'black', '10px arial', border, border, border, border, border, border))
sheet.tables.findByName('cT').style(tableStyle);
spread.resumePaint();
}
initSheet(sheet: any) {
let gcns = GC.Spread.Sheets;
let data = [
[, "FY 2019"],
[, "Sales"],
[, "Monthly", "Cumulative"],
["Apr", 188897, 188897],
["May", 208146, 397043],
["Jun", 226196, 623239],
["Jul", 277318, 900557],
["Aug", 263273, 1163830],
["Sep", 259845, 1423675],
["Oct", 241047, 1664722],
["Nov", 256306, 1921028],
["Dec", 195845, 2116873],
["Jan", 204934, 2321808],
["Feb", 257852, 2579660],
["Mar", 227779, 2807439]
];
sheet.setArray(3, 1, data);
sheet.setColumnWidth(2, 110);
sheet.setColumnWidth(3, 110);
sheet.setRowCount(20);
sheet.setColumnCount(9);
sheet.options.gridline.showHorizontalGridline = false;
sheet.options.gridline.showVerticalGridline = false;
sheet.getRange(3, 1, 15, 3).setBorder(
new gcns.LineBorder("black", gcns.LineStyle.medium),
{ all: true });
sheet.addSpan(3, 2, 1, 2);
sheet.addSpan(4, 2, 1, 2);
sheet.getRange(3, 2, 3, 2).hAlign(gcns.HorizontalAlign.center);
let cMapSource = [
{ "Currency": "USD", "Value": 1, "Symbol": "$" },
{ "Currency": "CNY", "Value": 7.02, "Symbol": "¥" },
{ "Currency": "JPY", "Value": 108.8, "Symbol": "¥" },
{ "Currency": "EURO", "Value": 0.91, "Symbol": "€" },
];
sheet.tables.addFromDataSource('cT', 3, 5, cMapSource);
[5, 6, 7].forEach((col) => { sheet.setColumnWidth(col, 80); });
sheet.getCell(1, 2).value("Unit:").hAlign(gcns.HorizontalAlign.right);
let dv1 = gcns.DataValidation.createFormulaListValidator('=cT[[#Data], [Currency]]');
sheet.setDataValidator(1, 3, dv1);
sheet.getCell(1, 3).hAlign(gcns.HorizontalAlign.center).value("USD");
sheet.getRange(6, 2, 12, 2)
.hAlign(gcns.HorizontalAlign.center)
.formatter('=VLOOKUP($D$2,cT[#Data],3,FALSE)&" "&TEXT(@*VLOOKUP($D$2,cT[#Data],2,FALSE),"###,###")');
}
setGradientButtonClicked($event: any) {
let backColor: any = {};
backColor.degree = parseInt(document.getElementById('gradientDegree').value, 10);
backColor.stops = JSON.parse(document.getElementById('gradientStops').value);
let sheet = this.spread.getActiveSheet();
let selection = sheet.getSelections()[0];
if (selection) {
sheet.getRange(selection.row, selection.col, selection.rowCount, selection.colCount).backColor(backColor);
}
}
setPathButtonClicked($event: any) {
let backColor: any = {type: "path"};
backColor.left = parseFloat(document.getElementById('pathLeft').value);
backColor.right = parseFloat(document.getElementById('pathRight').value);
backColor.top = parseFloat(document.getElementById('pathTop').value);
backColor.bottom = parseFloat(document.getElementById('pathBottom').value);
backColor.stops = JSON.parse(document.getElementById('pathStops').value);
let sheet = this.spread.getActiveSheet();
let selection = sheet.getSelections()[0];
if (selection) {
sheet.getRange(selection.row, selection.col, selection.rowCount, selection.colCount).backColor(backColor);
}
}
setPatternButtonClicked($event: any) {
let backColor: any = {};
backColor.type = GC.Spread.Sheets.PatternType[document.getElementById('patternType').value];
backColor.backgroundColor = document.getElementById('bgColor').value;
backColor.patternColor = document.getElementById('patternColor').value;
let sheet = this.spread.getActiveSheet();
let selection = sheet.getSelections()[0];
if (selection) {
sheet.getRange(selection.row, selection.col, selection.rowCount, selection.colCount).backColor(backColor);
}
}
}
@NgModule({
imports: [BrowserModule, SpreadSheetsModule],
declarations: [AppComponent],
exports: [AppComponent],
bootstrap: [AppComponent]
})
export class AppModule {}
enableProdMode();
// Bootstrap application with hash style navigation and global services.
platformBrowserDynamic().bootstrapModule(AppModule);
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- Polyfills -->
<script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script>
<script src="$DEMOROOT$/en/angular/node_modules/zone.js/fesm2015/zone.min.js"></script>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script>
<script src="systemjs.config.js"></script>
<script>
// workaround to load 'rxjs/operators' from the rxjs bundle
System.import('rxjs').then(function (m) {
System.import('@angular/compiler');
System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators));
System.import('$DEMOROOT$/en/lib/angular/license.ts');
System.import('./src/app.component');
});
</script>
</head>
<body>
<app-component></app-component>
</body>
</html>
<div class="sample-tutorial">
<gc-spread-sheets [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)">
<gc-worksheet [autoGenerateColumns]='autoGenerateColumns'>
</gc-worksheet>
</gc-spread-sheets>
<div class="options-container">
<strong class="sp-demo-HeaderTitle">Settings:</strong>
<P style="padding:2px 10px">Set the fill effect for the select range:</P>
<fieldset>
<div class="settingsDiv">
<div class="option-row">
<label for="gradientDegree">Degree:</label>
<input type="text" id="gradientDegree" value="45">
</div>
<div class="option-row">
<label for="gradientStops">Stops:</label>
<textarea id="gradientStops"
rows="4">[{"position":0,"color":"#838383"},{"position":0.5,"color":"white"},{"position":1,"color":"#82bc00"}]</textarea>
</div>
<div class="option-row">
<button id="setGradientButton" (click)="setGradientButtonClicked($event)">Set Gradient Fill</button>
</div>
</div>
</fieldset>
<fieldset>
<div class="settingsDiv">
<div class="option-row">
<label for="pathLeft">Left:</label>
<input type="text" id="pathLeft" value="0.5">
</div>
<div class="option-row">
<label for="pathRight">Right:</label>
<input type="text" id="pathRight" value="0.9">
</div>
<div class="option-row">
<label for="pathTop">Top:</label>
<input type="text" id="pathTop" value="0.5">
</div>
<div class="option-row">
<label for="pathBottom">Bottom:</label>
<input type="text" id="pathBottom" value="0.7">
</div>
<div class="option-row">
<label for="pathStops">Stops:</label>
<textarea id="pathStops"
rows="4">[{"position":0,"color":"#82bc00"},{"position":0.5,"color":"white"},{"position":1,"color":"#838383"}]</textarea>
</div>
<div class="option-row">
<button id="setPathButton" (click)="setPathButtonClicked($event)">Set Gradient Path Fill</button>
</div>
</div>
</fieldset>
<fieldset>
<div class="settingsDiv">
<div class="option-row">
<label for="patternType">Pattern Type:</label>
<select id="patternType">
<option value="solid">solid</option>
<option value="darkGray">darkGray</option>
<option value="mediumGray">mediumGray</option>
<option value="lightGray">lightGray</option>
<option value="gray125">gray125</option>
<option value="gray0615">gray0615</option>
<option value="darkHorizontal">darkHorizontal</option>
<option value="darkVertical">darkVertical</option>
<option value="darkDown">darkDown</option>
<option value="darkUp" selected="selected">darkUp</option>
<option value="darkGrid">darkGrid</option>
<option value="darkTrellis">darkTrellis</option>
<option value="lightHorizontal">lightHorizontal</option>
<option value="lightVertical">lightVertical</option>
<option value="lightDown">lightDown</option>
<option value="lightUp">lightUp</option>
<option value="lightGrid">lightGrid</option>
<option value="lightTrellis">lightTrellis</option>
</select>
</div>
<div class="option-row">
<label for="bgColor">Background color:</label>
<input type="text" id="bgColor" value="white">
</div>
<div class="option-row">
<label for="patternColor">Pattern color:</label>
<input type="text" id="patternColor" value="#C3C3C3">
</div>
<div class="option-row">
<button id="setPatternButton" (click)="setPatternButtonClicked($event)">Set Pattern Fill</button>
</div>
</div>
</fieldset>
</div>
</div>
.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: 1px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.options-container legend {
text-align: center;
}
fieldset {
margin-bottom: 5px;
padding: 2px;
}
.option-row {
font-size: 14px;
padding: 2px;
}
input, textarea, select {
display:block;
width: 100%;
margin: 4px 0;
box-sizing: border-box;
}
label, input, textarea, select {
padding: 1px 6px;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
button {
font-weight: bold;
background-color: #ecf3ff;
border-color: #0b93d5;
width: 100%;
height: 24px;
border-radius: 4px;
border-width: thin;
}
(function (global) {
System.config({
transpiler: 'ts',
typescriptOptions: {
tsconfig: true
},
meta: {
'typescript': {
"exports": "ts"
},
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'core-js': 'npm:core-js/client/shim.min.js',
'zone': 'npm:zone.js/fesm2015/zone.min.js',
'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js',
'@angular/core': 'npm:@angular/core/fesm2022',
'@angular/common': 'npm:@angular/common/fesm2022/common.mjs',
'@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs',
'@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs',
'@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs',
'@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs',
'@angular/router': 'npm:@angular/router/fesm2022/router.mjs',
'@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs',
'jszip': 'npm:jszip/dist/jszip.min.js',
'typescript': 'npm:typescript/lib/typescript.js',
'ts': './plugin.js',
'tslib':'npm:tslib/tslib.js',
'css': 'npm:systemjs-plugin-css/css.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-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'ts'
},
rxjs: {
defaultExtension: 'js'
},
"node_modules": {
defaultExtension: 'js'
},
"node_modules/@angular": {
defaultExtension: 'mjs'
},
"@mescius/spread-sheets-angular": {
defaultExtension: 'mjs'
},
'@angular/core': {
defaultExtension: 'mjs',
main: 'core.mjs'
}
}
});
})(this);
