テーブルのヘッダまたはフッタを表示するかどうか、交互行または交互列スタイルを表示するかどうか、先頭列または最終列を強調表示するかどうかを定義できます。次に、例を示します。
SpreadJSでは、組み込みの数種類のテーブルスタイルを使用することも、これらのスタイルをカスタマイズすることもできます。次に、例を示します。
SpreadJSは、テーブルのヘッダ、データ、フッタ、また、列のヘッダ、データ、フッタのスタイルのカスタマイズをサポートします。次に、例を示します。
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
initSpread(spread);
};
function setStyles (sheet) {
let table = sheet.tables.all()[0];
sheet.suspendPaint();
let tableHeaderStyle = new GC.Spread.Sheets.Style();
tableHeaderStyle.backColor = "Accent 1 80";
let tableDataStyle = new GC.Spread.Sheets.Style();
tableDataStyle.backColor = "Accent 4 80";
let tableFooterStyle = new GC.Spread.Sheets.Style();
tableFooterStyle.backColor = "Accent 6 80";
table.layoutStyle({
header: tableHeaderStyle,
data: tableDataStyle,
footer: tableFooterStyle
});
let tableColumnHeaderStyle = new GC.Spread.Sheets.Style();
tableColumnHeaderStyle.backColor = "Accent 1 40";
let tableColumnDataStyle = new GC.Spread.Sheets.Style();
tableColumnDataStyle.backColor = "Accent 4 40";
let tableColumnFooterStyle = new GC.Spread.Sheets.Style();
tableColumnFooterStyle.backColor = "Accent 6 40";
table.columnLayoutStyle(1, {
header: tableColumnHeaderStyle,
data: tableColumnDataStyle,
footer: tableColumnFooterStyle
});
let tableColumn3DataStyle = new GC.Spread.Sheets.Style();
tableColumn3DataStyle.formatter = "0%";
table.columnLayoutStyle(3, {
data: tableColumn3DataStyle
});
sheet.resumePaint();
}
function getActiveSheetTable (sheet) {
let table = sheet.tables.find(sheet.getActiveRowIndex(), sheet.getActiveColumnIndex()) || sheet.tables.all()[0];
return table;
}
function initCustomThemes(spread) {
const theme1 = new GC.Spread.Sheets.Tables.TableTheme();
theme1.name('custom1');
theme1.wholeTableStyle(new GC.Spread.Sheets.Tables.TableStyle('#e0f2f1'));
theme1.headerRowStyle(new GC.Spread.Sheets.Tables.TableStyle('#26a69a', '#fff'));
theme1.firstRowStripStyle(new GC.Spread.Sheets.Tables.TableStyle('#b2dfdb'));
theme1.firstColumnStripStyle(new GC.Spread.Sheets.Tables.TableStyle('#b2dfdb'));
theme1.footerRowStyle(new GC.Spread.Sheets.Tables.TableStyle('#26a69a', '#fff'));
theme1.highlightFirstColumnStyle(new GC.Spread.Sheets.Tables.TableStyle('#26a69a', '#fff'));
theme1.highlightLastColumnStyle(new GC.Spread.Sheets.Tables.TableStyle('#26a69a', '#fff'));
const theme2 = new GC.Spread.Sheets.Tables.TableTheme();
theme2.name('custom2');
theme2.wholeTableStyle(new GC.Spread.Sheets.Tables.TableStyle('#ede7f6'));
theme2.headerRowStyle(new GC.Spread.Sheets.Tables.TableStyle('#ede7f6', '#000'));
theme2.firstRowStripStyle(new GC.Spread.Sheets.Tables.TableStyle('#e1bee7'));
theme2.firstColumnStripStyle(new GC.Spread.Sheets.Tables.TableStyle('#e1bee7'));
theme2.footerRowStyle(new GC.Spread.Sheets.Tables.TableStyle('#ede7f6', '#000'));
spread.customTableThemes.add(theme1);
spread.customTableThemes.add(theme2);
let options = _getElementById("tableStyles").innerHTML;
const customThemes = spread.customTableThemes.all();
if (customThemes && customThemes.length > 0) {
customThemes.forEach((theme) => {
options += `<option>${theme.name()}</option>`;
});
}
_getElementById("tableStyles").innerHTML = options;
}
function initSpread(spread) {
var spreadNS = GC.Spread.Sheets;
spread.fromJSON(jsonData);
initCustomThemes(spread);
var sheet = spread.getActiveSheet();
setStyles(sheet);
_getElementById("showHeader").addEventListener('change', function() {
var sheet = spread.getActiveSheet();
var table = getActiveSheetTable(sheet);
if (table) {
table.showHeader(_getElementById("showHeader").checked);
sheet.invalidateLayout();
sheet.repaint();
}
});
_getElementById("showFooter").addEventListener('change', function() {
var sheet = spread.getActiveSheet();
var table = getActiveSheetTable(sheet);
if (table) {
table.showFooter(_getElementById("showFooter").checked);
sheet.invalidateLayout();
sheet.repaint();
}
});
_getElementById("highlightFirstColumn").addEventListener('change', function() {
var sheet = spread.getActiveSheet();
var table = getActiveSheetTable(sheet);
if (table) {
table.highlightFirstColumn(_getElementById("highlightFirstColumn").checked);
sheet.invalidateLayout();
sheet.repaint();
}
});
_getElementById("highlightLastColumn").addEventListener('change', function() {
var sheet = spread.getActiveSheet();
var table = getActiveSheetTable(sheet);
if (table) {
table.highlightLastColumn(_getElementById("highlightLastColumn").checked);
sheet.invalidateLayout();
sheet.repaint();
}
});
_getElementById("bandRows").addEventListener('change', function() {
var sheet = spread.getActiveSheet();
var table = getActiveSheetTable(sheet);
if (table) {
table.bandRows(_getElementById("bandRows").checked);
sheet.invalidateLayout();
sheet.repaint();
}
});
_getElementById("bandColumns").addEventListener('change', function() {
var sheet = spread.getActiveSheet();
var table = getActiveSheetTable(sheet);
if (table) {
table.bandColumns(_getElementById("bandColumns").checked);
sheet.invalidateLayout();
sheet.repaint();
}
});
//change table style
_getElementById("tableStyles").addEventListener('change', function() {
var sheet = spread.getActiveSheet();
var table = getActiveSheetTable(sheet);
if (table) {
var styleName = _getElementById("tableStyles").value;
// if (style) {
table.style(styleName);
sheet.repaint();
// }
}
});
function updateTableStyle(themeName) {
var obj = _getElementById("tableStyles");
if (themeName) {
obj.value = themeName;
}
}
spread.bind(GC.Spread.Sheets.Events.EnterCell, (eventType, data) => {
if (data) {
let activeSheet = spread.getActiveSheet();
let activeTable = activeSheet.tables.find(activeSheet.getActiveRowIndex(), activeSheet.getActiveColumnIndex());
if (activeTable) {
let style = activeTable.style(), name = 'None';
if (style) {
name = style.name();
}
updateTableStyle(name);
}
}
});
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="ja-jp" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="data.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<p>シートのテーブル領域を選択後に以下のスタイルを変更してください。</p>
<div class="option-group">
<input type="checkbox" id="showHeader" checked/>
<label for="showHeader">ヘッダ行</label>
</div>
<div class="option-group">
<input type="checkbox" id="highlightFirstColumn" />
<label for="highlightFirstColumn">最初の列</label>
</div>
<div class="option-group">
<input type="checkbox" id="showFooter" />
<label for="showFooter">合計行</label>
</div>
<div class="option-group">
<input type="checkbox" id="highlightLastColumn" />
<label for="highlightLastColumn">最後の列</label>
</div>
<div class="option-group">
<input type="checkbox" id="bandColumns" />
<label for="bandColumns">1列おきに網掛け</label>
</div>
<div class="option-group">
<input type="checkbox" id="bandRows" />
<label for="bandRows">1行おきに網掛け</label>
</div>
<div class="option-group">
<label for="tableStyles">組み込みスタイル：</label>
<select id="tableStyles">
<option value="Light1">Light1</option>
<option value="Light2">Light2</option>
<option value="Light3">Light3</option>
<option value="Light4">Light4</option>
<option value="Light5">Light5</option>
<option value="Light6">Light6</option>
<option value="Light7">Light7</option>
<option value="Light8">Light8</option>
<option value="Light9">Light9</option>
<option value="Light10">Light10</option>
<option value="Light11">Light11</option>
<option value="Light12">Light12</option>
<option value="Light13">Light13</option>
<option value="Light14">Light14</option>
<option value="Light15">Light15</option>
<option value="Light16">Light16</option>
<option value="Light17">Light17</option>
<option value="Light18">Light18</option>
<option value="Light19">Light19</option>
<option value="Light20">Light20</option>
<option value="Light21">Light21</option>
<option value="Medium1">Medium1</option>
<option value="Medium2">Medium2</option>
<option value="Medium3">Medium3</option>
<option value="Medium4">Medium4</option>
<option value="Medium5">Medium5</option>
<option value="Medium6">Medium6</option>
<option value="Medium7">Medium7</option>
<option value="Medium8">Medium8</option>
<option value="Medium9">Medium9</option>
<option value="Medium10">Medium10</option>
<option value="Medium11">Medium11</option>
<option value="Medium12">Medium12</option>
<option value="Medium13">Medium13</option>
<option value="Medium14">Medium14</option>
<option value="Medium15">Medium15</option>
<option value="Medium16">Medium16</option>
<option value="Medium17">Medium17</option>
<option value="Medium18">Medium18</option>
<option value="Medium19">Medium19</option>
<option value="Medium20">Medium20</option>
<option value="Medium21">Medium21</option>
<option value="Medium22">Medium22</option>
<option value="Medium23">Medium23</option>
<option value="Medium24">Medium24</option>
<option value="Medium25">Medium25</option>
<option value="Medium26">Medium26</option>
<option value="Medium27">Medium27</option>
<option value="Medium28">Medium28</option>
<option value="Dark1">Dark1</option>
<option value="Dark2">Dark2</option>
<option value="Dark3">Dark3</option>
<option value="Dark4">Dark4</option>
<option value="Dark5">Dark5</option>
<option value="Dark6">Dark6</option>
<option value="Dark7">Dark7</option>
<option value="Dark8">Dark8</option>
<option value="Dark9">Dark9</option>
<option value="Dark10">Dark10</option>
<option value="Dark11">Dark11</option>
<option value="None" selected>None</option>
</select>
</div>
</div>
</div>
</body>
</html>
var jsonData = {
version: "16.0.0",
name: "",
sheetCount: 2,
customList: [],
activeSheetIndex: 1,
sheets: {
Table: {
name: "Table",
isSelected: false,
activeRow: 38,
activeCol: 2,
visible: 1,
theme: {
name: "Office",
themeColor: {
name: "Office",
background1: { a: 255, r: 255, g: 255, b: 255 },
background2: { a: 255, r: 231, g: 230, b: 230 },
text1: { a: 255, r: 0, g: 0, b: 0 },
text2: { a: 255, r: 68, g: 84, b: 106 },
accent1: { a: 255, r: 91, g: 155, b: 213 },
accent2: { a: 255, r: 237, g: 125, b: 49 },
accent3: { a: 255, r: 165, g: 165, b: 165 },
accent4: { a: 255, r: 255, g: 192, b: 0 },
accent5: { a: 255, r: 68, g: 114, b: 196 },
accent6: { a: 255, r: 112, g: 173, b: 71 },
hyperlink: { a: 255, r: 5, g: 99, b: 193 },
followedHyperlink: { a: 255, r: 149, g: 79, b: 114 },
},
headingFont: "'Calibri Light'",
bodyFont: "Calibri",
font: { headerFont: "'Calibri Light'", bodyFont: "Calibri" },
},
data: {
dataTable: {
0: {
0: { value: "SalesPers" },
1: { value: "Region" },
2: { value: "SaleAmt" },
3: { value: "ComPct" },
4: { value: "ComAmt" },
},
1: {
0: { value: "Joe" },
1: { value: "North" },
2: { value: 260 },
3: { value: 0.1 },
4: { value: 26, formula: { si: 0 } },
},
2: {
0: { value: "Robert" },
1: { value: "South" },
2: { value: 660 },
3: { value: 0.15 },
4: { value: 99, formula: { si: 0 } },
},
3: {
0: { value: "Michelle" },
1: { value: "East" },
2: { value: 940 },
3: { value: 0.15 },
4: { value: 141, formula: { si: 0 } },
},
4: {
0: { value: "Erich" },
1: { value: "West" },
2: { value: 410 },
3: { value: 0.12 },
4: { value: 49.2, formula: { si: 0 } },
},
5: {
0: { value: "Dafna" },
1: { value: "North" },
2: { value: 800 },
3: { value: 0.15 },
4: { value: 120, formula: { si: 0 } },
},
6: {
0: { value: "Robert" },
1: { value: "South" },
2: { value: 900 },
3: { value: 0.15 },
4: { value: 135, formula: { si: 0 } },
}
},
defaultDataNode: {
style: {
backColor: null,
foreColor: "Text 1 0",
vAlign: 2,
font: "normal normal 14.6667px Calibri",
themeFont: "Body",
borderLeft: null,
borderTop: null,
borderRight: null,
borderBottom: null,
locked: true,
textIndent: 0,
wordWrap: false,
shrinkToFit: false,
textDecoration: 0,
isVerticalText: false,
textOrientation: 0,
fontStyle: "normal",
fontWeight: "normal",
fontSize: "14.6667px",
fontFamily: "Calibri",
},
},
},
rowHeaderData: { defaultDataNode: { style: { themeFont: "Body" } } },
colHeaderData: { defaultDataNode: { style: { themeFont: "Body" } } },
columns: [
{ size: 81 },
{ size: 65 },
{ size: 74 },
{ size: 69 },
{ size: 76 },
],
defaultData: {},
leftCellIndex: 0,
topCellIndex: 0,
selections: {
0: { row: 38, col: 2, rowCount: 1, colCount: 1 },
length: 1,
},
defaults: {
colHeaderRowHeight: 20,
colWidth: 64,
rowHeaderColWidth: 40,
rowHeight: 20,
_isExcelDefaultColumnWidth: true,
},
rowOutlines: { items: [] },
columnOutlines: { items: [] },
cellStates: {},
states: {},
outlineColumnOptions: {},
autoMergeRangeInfos: [],
tables: [
{
name: "Table1",
row: 0,
col: 0,
rowCount: 7,
colCount: 5,
rowFilter: {
range: { row: 1, col: 0, rowCount: 6, colCount: 5 },
typeName: "HideRowFilter",
dialogVisibleInfo: {},
filterButtonVisibleInfo: {
0: true,
1: true,
2: true,
3: true,
4: true,
},
showFilterButton: true,
filteredOutRows: [],
tableName: "Table1",
},
columns: [
{ id: 1, name: "SalesPers", footerValue: "Total" },
{ id: 2, name: "Region" },
{ id: 3, name: "SaleAmt" },
{ id: 4, name: "ComPct" },
{
id: 5,
name: "ComAmt",
footerFormula: "SUBTOTAL(109,Table1[ComAmt])",
dataAreaFormula: "C2*D2",
},
],
},
],
shapeCollectionOption: { snapMode: 0 },
sharedFormulas: {
0: {
formula: "C2*D2",
baseRow: 1,
baseColumn: 4,
lastRow: 6,
lastColumn: 4,
},
nextIndex: 1,
},
printInfo: {
showColumnHeader: 1,
showRowHeader: 1,
paperSize: { width: 850, height: 1100, kind: 1 },
},
index: 0,
order: 0,
},
TableWithLayoutStyle: {
name: "TableWithLayoutStyle",
isSelected: true,
activeRow: 5,
activeCol: 7,
visible: 1,
theme: {
name: "Office",
themeColor: {
name: "Office",
background1: { a: 255, r: 255, g: 255, b: 255 },
background2: { a: 255, r: 231, g: 230, b: 230 },
text1: { a: 255, r: 0, g: 0, b: 0 },
text2: { a: 255, r: 68, g: 84, b: 106 },
accent1: { a: 255, r: 91, g: 155, b: 213 },
accent2: { a: 255, r: 237, g: 125, b: 49 },
accent3: { a: 255, r: 165, g: 165, b: 165 },
accent4: { a: 255, r: 255, g: 192, b: 0 },
accent5: { a: 255, r: 68, g: 114, b: 196 },
accent6: { a: 255, r: 112, g: 173, b: 71 },
hyperlink: { a: 255, r: 5, g: 99, b: 193 },
followedHyperlink: { a: 255, r: 149, g: 79, b: 114 },
},
headingFont: "'Calibri Light'",
bodyFont: "Calibri",
font: { headerFont: "'Calibri Light'", bodyFont: "Calibri" },
},
data: {
dataTable: {
0: {
0: { value: "SalesPers" },
1: { value: "Region" },
2: { value: "SaleAmt" },
3: { value: "ComPct" },
4: { value: "ComAmt" },
8: {
value:
"Add record to the table, see the auto-expanded table layout style",
},
},
1: {
0: { value: "Joe" },
1: { value: "North" },
2: { value: 260 },
3: { value: 0.1 },
4: { value: 26, formula: { si: 0 } },
8: {
value: "The table layout style‘s priority is higher than the table theme",
},
},
2: {
0: { value: "Robert" },
1: { value: "South" },
2: { value: 660 },
3: { value: 0.15 },
4: { value: 99, formula: { si: 0 } },
8: {
value: "Usually 'Banded Row' and 'Banded Column' only have backColor setting, so check these two options may take no effect",
},
},
3: {
0: { value: "Michelle" },
1: { value: "East" },
2: { value: 940 },
3: { value: 0.15 },
4: { value: 141, formula: { si: 0 } },
},
4: {
0: { value: "Erich" },
1: { value: "West" },
2: { value: 410 },
3: { value: 0.12 },
4: { value: 49.2, formula: { si: 0 } },
},
5: {
0: { value: "Dafna" },
1: { value: "North" },
2: { value: 800 },
3: { value: 0.15 },
4: { value: 120, formula: { si: 0 } },
},
6: {
0: { value: "Robert" },
1: { value: "South" },
2: { value: 900 },
3: { value: 0.15 },
4: { value: 135, formula: { si: 0 } },
},
},
defaultDataNode: {
style: {
backColor: null,
foreColor: "Text 1 0",
vAlign: 2,
font: "normal normal 14.6667px Calibri",
themeFont: "Body",
borderLeft: null,
borderTop: null,
borderRight: null,
borderBottom: null,
locked: true,
textIndent: 0,
wordWrap: false,
shrinkToFit: false,
textDecoration: 0,
isVerticalText: false,
textOrientation: 0,
fontStyle: "normal",
fontWeight: "normal",
fontSize: "14.6667px",
fontFamily: "Calibri",
},
},
},
rowHeaderData: { defaultDataNode: { style: { themeFont: "Body" } } },
colHeaderData: { defaultDataNode: { style: { themeFont: "Body" } } },
columns: [
{ size: 81 },
{ size: 65 },
{ size: 74 },
{ size: 69 },
{ size: 76 },
],
defaultData: {},
leftCellIndex: 0,
topCellIndex: 0,
selections: {
0: { row: 5, col: 7, rowCount: 1, colCount: 1 },
length: 1,
},
defaults: {
colHeaderRowHeight: 20,
colWidth: 64,
rowHeaderColWidth: 40,
rowHeight: 20,
_isExcelDefaultColumnWidth: true,
},
rowOutlines: { items: [] },
columnOutlines: { items: [] },
cellStates: {},
states: {},
outlineColumnOptions: {},
autoMergeRangeInfos: [],
tables: [
{
name: "Table2",
row: 0,
col: 0,
rowCount: 7,
colCount: 5,
rowFilter: {
range: { row: 1, col: 0, rowCount: 6, colCount: 5 },
typeName: "HideRowFilter",
dialogVisibleInfo: {},
filterButtonVisibleInfo: {
0: true,
1: true,
2: true,
3: true,
4: true,
},
showFilterButton: true,
filteredOutRows: [],
tableName: "Table2",
},
columns: [
{ id: 1, name: "SalesPers", footerValue: "Total" },
{ id: 2, name: "Region" },
{ id: 3, name: "SaleAmt" },
{ id: 4, name: "ComPct" },
{
id: 5,
name: "ComAmt",
footerFormula: "SUBTOTAL(109,Table2[ComAmt])",
dataAreaFormula: "C2*D2",
},
],
},
],
shapeCollectionOption: { snapMode: 0 },
sharedFormulas: {
0: {
formula: "C2*D2",
baseRow: 1,
baseColumn: 4,
lastRow: 6,
lastColumn: 4,
},
nextIndex: 1,
},
printInfo: {
showColumnHeader: 1,
showRowHeader: 1,
paperSize: { width: 850, height: 1100, kind: 1 },
},
index: 1,
order: 1,
},
},
sheetTabCount: 0,
namedPatterns: {},
pivotCaches: {},
};
.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;
}
.option-row {
font-size: 14px;
padding: 5px;
margin-top: 10px;
}
.option-group {
margin-bottom: 6px;
}
label {
display: inline-block;
min-width: 90px;
margin-bottom: 6px;
}
select {
padding: 4px 6px;
}
p {
padding: 0 0 12px;
margin: 0;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
