Class: ReportSheet

Spread.Report.ReportSheet

Table of contents

Constructors

• constructor

Properties

• options

Methods

• addRecordAt
• currentPage
• deleteRecordAt
• generatePageSheets
• getActualStyle
• getCells
• getCollapseState
• getColumnWidth
• getPagesCount
• getRange
• getRowHeight
• getSpan
• getStyle
• getTemplate
• getTemplateCell
• getValue
• isDirty
• loadTemplate
• name
• parameter
• printInfo
• printPageIndexes
• refresh
• regenerateReport
• renderMode
• repaint
• resetCellValue
• setParametersUI
• submit
• toWorksheet
• toggleCollapseState
• updateCellValue

Constructors

constructor

• new ReportSheet(name)

Represents a ReportSheet.

example

const spread = new GC.Spread.Sheets.Workbook('spread-host', { sheetCount: 0 });
const reportSheet = spread.addSheetTab(0, 'orders-report', GC.Spread.Sheets.SheetType.reportSheet);
const templateSheet = reportSheet.getTemplate();

const ordersTable = spread.dataManager().addTable('Orders', {
    remote: {
        read: {
            url: 'https://demodata.mescius.io/northwind/api/v1/orders'
        }
    }
});

// load the data from remote.
ordersTable.fetch().then(() => {
    // set style for the template.
    const headerStyle = new GC.Spread.Sheets.Style();
    headerStyle.backColor = '#80CBC4';
    headerStyle.foreColor = '#424242';
    headerStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.right;
    headerStyle.font = '12px Maine';
    const dataStyle = new GC.Spread.Sheets.Style();
    dataStyle.foreColor = '#424242';
    dataStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.right;
    dataStyle.font = '12px Maine';
    const border = new GC.Spread.Sheets.LineBorder('#E0E0E0', 1);
    dataStyle.borderBottom = border;
    dataStyle.borderTop = border;
    dataStyle.borderLeft = border;
    dataStyle.borderRight = border;
    const colWidthArray = [90, 90, 90, 80, 220, 150, 110];
    colWidthArray.forEach((width, i) => {
        templateSheet.setColumnWidth(i, width);
    });
    templateSheet.getRange('A1:G1').setStyle(headerStyle);
    templateSheet.getRange('A2:G2').setStyle(dataStyle);
    templateSheet.setFormatter(1, 2, 'yyyy-MM-dd');

    // set value and binding for the template.
    const columns = ['orderId', 'customerId', 'orderDate', 'freight', 'shipName', 'shipCity', 'shipCountry'];
    columns.forEach((columnName, i) => {
        templateSheet.setValue(0, i, `${columnName[0].toUpperCase()}${columnName.substring(1)}`);
        templateSheet.setTemplateCell(1, i, {
            type: 'List',
            binding: `Orders[${columnName}]`,
        });
    });

    reportSheet.refresh();
});

Parameters

Name	Type	Description
name	string	The name of the ReportSheet.

Properties

options

• options: IReportOptions

Indicates the options of the ReportSheet.

property [dirtyStyle] The style will be used to paint the modified cells in the preview mode.

property [printAllPages] Indicates whether print all pages.

Methods

addRecordAt

▸ addRecordAt(row, col): void

Add record based on the specified cell.

example

// add record at A2 cell.
reportSheet.addRecordAt(1, 0);

Parameters

Name	Type	Description
row	number	The row index.
col	number	The column index.

Returns

void

---

currentPage

▸ currentPage(page?): number

Get or set the current page index.

example

// get the current page index.
const page = reportSheet.currentPage();

// go to the next page.
reportSheet.currentPage(page + 1);

Parameters

Name	Type	Description
page?	number	The page index (0 base).

Returns

number

Return the current page index.

---

deleteRecordAt

▸ deleteRecordAt(row, col): void

Delete record based on the specified cell.

example

// delete record at A2 cell.
reportSheet.deleteRecordAt(1, 0);

Parameters

Name	Type	Description
row	number	The row index.
col	number	The column index.

Returns

void

---

generatePageSheets

▸ generatePageSheets(addToSpread, sheetNameGenerator?): Worksheet[]

Generate the every page of the current report as a worksheet, and return them in an array.

example

// generate the pages and add them to the spread.
const pageSheets = reportSheet.generatePageSheets(true);

// generate the pages and add them to the spread, and customize the sheet name.
const pageSheets = reportSheet.generatePageSheets(true, (i) => `report-page-${i + 1}`);

Parameters

Name	Type	Description
addToSpread	boolean	Control whether add the page sheets to the current spread.
sheetNameGenerator?	SheetNameGenerator	The optional sheet name generator.

Returns

Worksheet[]

Return the generated worksheets.

---

getActualStyle

▸ getActualStyle(row, col): Style

Get the actual style of the cell in current render mode.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
templateSheet.getCell(0, 0).backColor("red");
templateSheet.setTemplateCell(1, 0, {
   binding: "Orders[orderId]",
   type: "Group",
});
templateSheet.getCell(1, 0).backColor("green");
const designActualStyle1 = report.getActualStyle(0, 0); // backColor: red
const designActualStyle2 = report.getActualStyle(1, 0); // backColor: green
report.renderMode("Preview");
const previewActualStyle1 = report.getActualStyle(0, 0); // backColor: red
const previewActualStyle2 = report.getActualStyle(1, 0); // backColor: red
report.renderMode("PaginatedPreview");
const paginatedPreviewActualStyle1 = report.getActualStyle(0, 0); // backColor: red
const paginatedPreviewActualStyle2 = report.getActualStyle(1, 0); // backColor: red

Parameters

Name	Type	Description
row	number	The row index.
col	number	The column index.

Returns

Style

Return actual style of the cell.

---

getCells

▸ getCells(templateRow, templateCol, currentRow?, currentCol?): IDataCell[]

Get expanded cells of the template cell base on the current cell in preview.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
templateSheet.setTemplateCell(1, 0, {
   binding: "Orders[orderId]",
   type: "Group",
});
report.regenerateReport();
const allCustomerIdCells = report.getCells(0, 0); // all 89 cells
const fistCustomerIdCell = report.getCells(0, 0, 0, 0); // value: VINET
const allOrderIdCells = report.getCells(1, 0); // all 830 cells
const firstOrderIdCell = report.getCells(1, 0, 89, 0); // value: 10248

Parameters

Name	Type	Description
templateRow	number	The row index in template.
templateCol	number	The column index in template.
currentRow?	number	The row index of current cell in preview.
currentCol?	number	The column index of current cell in preview.

Returns

IDataCell[]

Return expanded cells of the template cell base on the current cell.

---

getCollapseState

▸ getCollapseState(row, col): boolean

Return the collapse state of the specified cell.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
   showCollapseButton: true
});
templateSheet.setTemplateCell(0, 1, {
   binding: "Orders[orderId]",
   type: "Group",
});
templateSheet.addSpan(0, 0, 2, 1);
report.renderMode("Preview");
report.getCollapseState(0, 0); // false
report.toggleCollapseState(0, 0);
report.getCollapseState(0, 0); // true

Parameters

Name	Type	Description
row	number	The row index.
col	number	The column index.

Returns

boolean

---

getColumnWidth

▸ getColumnWidth(col): number

Get the column width by col index.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
   spillDirection: "Horizontal"
});
templateSheet.setColumnWidth(0, 100);
const designColumnWidth1 = report.getColumnWidth(0); // ColumnWidth: 100
const designColumnWidth2 = report.getColumnWidth(1); // ColumnWidth: 62
report.renderMode("Preview");
const previewColumnWidth1 = report.getColumnWidth(0); // ColumnWidth: 100
const previewColumnWidth2 = report.getColumnWidth(1); // ColumnWidth: 100
report.renderMode("PaginatedPreview");
const paginatedPreviewColumnWidth1 = report.getColumnWidth(0); // ColumnWidth: 100
const paginatedPreviewColumnWidth2 = report.getColumnWidth(1); // ColumnWidth: 100

Parameters

Name	Type	Description
col	number	The column index.

Returns

number

Return column width.

---

getPagesCount

▸ getPagesCount(): number

Get the pages count of the report.

example

// get the pages count.
const pagesCount = reportSheet.getPagesCount();

Returns

number

Return the pages count of the report.

---

getRange

▸ getRange(): Range

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(199, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
const designRange = report.getRange(); // row: 0, col: 0, rowCount: 200, colCount: 20.
report.renderMode("Preview");
const previewRange = report.getRange(); // row: 0, col: 0, rowCount: 288, colCount: 1.
report.renderMode("PaginatedPreview");
const paginatedPreviewRange = report.getRange(); // row: 0, col: 0, rowCount: 45, colCount: 1.

Returns

Range

Return the report sheet range. If the render mode is Design, return the template sheet range. If the render mode is Preview, return the whole report range. If the render mode is PaginatedPreview, return the current page's range.

---

getRowHeight

▸ getRowHeight(row): number

Get the row height by row index.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
templateSheet.setRowHeight(0, 30);
const designRowHeight1 = report.getRowHeight(0); // RowHeight: 30
const designRowHeight2 = report.getRowHeight(1); // RowHeight: 20
report.renderMode("Preview");
const previewRowHeight1 = report.getRowHeight(0); // RowHeight: 30
const previewRowHeight2 = report.getRowHeight(1); // RowHeight: 30
report.renderMode("PaginatedPreview");
const paginatedPreviewRowHeight1 = report.getRowHeight(0); // RowHeight: 30
const paginatedPreviewRowHeight2 = report.getRowHeight(1); // RowHeight: 30

Parameters

Name	Type	Description
row	number	The row index.

Returns

number

Return row height.

---

getSpan

▸ getSpan(row, col): Range

Get the range of the cell in current render mode.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
templateSheet.addSpan(0, 0, 2, 1);
const designSpan1 = report.getSpan(0, 0); // span: row: 0, col: 0, rowCount: 2, colCount: 1
const designSpan2 = report.getSpan(2, 0); // span: null
report.renderMode("Preview");
const previewSpan1 = report.getSpan(0, 0); // span: row: 0, col: 0, rowCount: 2, colCount: 1
const previewSpan2 = report.getSpan(2, 0); // span: row: 2, col: 0, rowCount: 2, colCount: 1
report.renderMode("PaginatedPreview");
const paginatedPreviewSpan1 = report.getSpan(0, 0); // span: row: 0, col: 0, rowCount: 2, colCount: 1
const paginatedPreviewSpan2 = report.getSpan(2, 0); // span: row: 2, col: 0, rowCount: 2, colCount: 1

Parameters

Name	Type	Description
row	number	The row index.
col	number	The column index.

Returns

Range

Return range of the cell.

---

getStyle

▸ getStyle(row, col): Style

Get the style of the cell in current render mode.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
templateSheet.getCell(0, 0).backColor("red");
templateSheet.setTemplateCell(1, 0, {
   binding: "Orders[orderId]",
   type: "Group",
});
templateSheet.getCell(1, 0).backColor("green");
const designStyle1 = report.getStyle(0, 0); // backColor: red
const designStyle2 = report.getStyle(1, 0); // backColor: green
report.renderMode("Preview");
const previewStyle1 = report.getStyle(0, 0); // backColor: red
const previewStyle2 = report.getStyle(1, 0); // backColor: red
report.renderMode("PaginatedPreview");
const paginatedPreviewStyle1 = report.getStyle(0, 0); // backColor: red
const paginatedPreviewStyle2 = report.getStyle(1, 0); // backColor: red

Parameters

Name	Type	Description
row	number	The row index.
col	number	The column index.

Returns

Style

Return style of the cell.

---

getTemplate

▸ getTemplate(): TemplateSheet

get the template sheet of the current report sheet.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate(); // get the reportSheet templateSheet
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
report.renderMode("Preview");

Returns

TemplateSheet

Return the template sheet of the current report sheet.

---

getTemplateCell

▸ getTemplateCell(row, col): TemplateCell

Get template cell of specified cell.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
templateSheet.setTemplateCell(1, 0, {
   binding: "Orders[orderId]",
   type: "Group",
});
const designTemplateCell1 = report.getTemplateCell(0, 0); // binding: Orders[customerId]
const designTemplateCell2 = report.getTemplateCell(1, 0); // binding: Orders[orderId]
report.renderMode("Preview");
const previewTemplateCell1 = report.getTemplateCell(0, 0); // binding: Orders[customerId]
const previewTemplateCell2 = report.getTemplateCell(1, 0); // binding: Orders[customerId]
const previewTemplateCell3 = report.getTemplateCell(89, 0); // binding: Orders[orderId]
report.renderMode("PaginatedPreview");
const paginatedPreviewTemplateCell1 = report.getTemplateCell(0, 0); // binding: Orders[customerId]
const paginatedPreviewTemplateCell2 = report.getTemplateCell(1, 0); // binding: Orders[customerId]

Parameters

Name	Type	Description
row	number	The row index.
col	number	The column index.

Returns

TemplateCell

Return the template cell of specified cell. If the render mode is Design, return the template cell of the template sheet in the specified row col. If the render mode is Preview, return the template cell of the specified cell. If the render mode is PaginatedPreview, return the template cell of the specified cell.

---

getValue

▸ getValue(row, col): any

Get the value of the cell in current render mode.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
templateSheet.setValue(1, 0, "test");
const designValue = report.getValue(1, 0); // test
report.renderMode("Preview");
const previewValue = report.getValue(1, 0); // TOMSP
report.renderMode("PaginatedPreview");
const paginatedPreviewValue = report.getValue(1, 0); // TOMSP

Parameters

Name	Type	Description
row	number	The row index.
col	number	The column index.

Returns

any

Return value of the cell.

---

isDirty

▸ isDirty(): boolean

Return a boolean value indicate wether the report has un-submit changes.

example

// if the current reportSheet has un-submit changes, isDirty will be true.
const isDirty = reportSheet.isDirty();

Returns

boolean

Return true if the report has un-submit changes, otherwise return false.

---

loadTemplate

▸ loadTemplate(templateJson): void

Update the report sheet template, and also the report will be regenerated based on the new template and parameter values.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
const templateSheet = new GC.Spread.Report.TemplateSheet("Template");
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
report.loadTemplate(templateSheet.toJSON());

Parameters

Name	Type	Description
templateJson	Object	The template json.

Returns

void

---

name

▸ name(value?): any

Gets or sets the name of the ReportSheet.

example

// get the report sheet name
const name = reportSheet.name();

// set the report sheet name
reportSheet.name('new-name');

Parameters

Name	Type
value?	string

Returns

any

returns the ReportSheet name.

---

parameter

▸ parameter(parameter?): IParameter

Get or set the parameter in reportsheet.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
report.getTemplate().setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
   filter: {
       condition: {
           column: "customerId",
           operator: "Equal",
           parameter: "customerId"
       }
   }
});
report.renderMode("Preview");

let parameter = report.parameter(); // get the reportsheet parameter
parameter.customerId = "VINET";
report.parameter(parameter); // set the parameter.
report.regenerateReport(); // regenerate reportsheet according to the new parameter.

Parameters

Name	Type
parameter?	IParameter

Returns

IParameter

Returns the reportsheet current working parameter.

---

printInfo

▸ printInfo(value?): ReportSheet | PrintInfo

Gets or sets the print information for the report sheet.

example

// set the paper size.
const printInfo = reportSheet.printInfo();
printInfo.paperSize(new GC.Spread.Sheets.Print.PaperSize(GC.Spread.Sheets.Print.PaperKind.a3));

Parameters

Name	Type
value?	PrintInfo

Returns

ReportSheet | PrintInfo

If no value is set, returns the print information for the report sheet; otherwise, returns the report sheet.

---

printPageIndexes

▸ printPageIndexes(pageIndexes?): number[]

Get or set the page index array that will be print. empty array indicates print all the pages.

example

// print only the first and fifth pages of the current report.
reportSheet.printPageIndexes([0, 4]);
reportSheet.options.printAllPages = true;
spread.print();

// clear the print page indexes setting.
reportSheet.printPageIndexes([]);

Parameters

Name	Type	Description
pageIndexes?	number[]	The print page index array (0 base).

Returns

number[]

Return the page index array that will be print.

---

refresh

▸ refresh(): void

Refresh the current report sheet, all the render modes can support this function. Design: Refresh the cache of the template sheet and repaint. Preview: Regenerate the report base on the current template sheet and repaint. PaginatedPreview: Regenerate the report base on the current template sheet and repaint.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
report.refresh();

Returns

void

---

regenerateReport

▸ regenerateReport(): void

Regenerate the report base on the current template.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
report.regenerateReport();

Returns

void

---

renderMode

▸ renderMode(renderMode?): RenderMode

Get or set the render mode.

example

// switch to design mode.
reportSheet.renderMode('Design');

// switch to preview mode.
reportSheet.renderMode('Preview');

// switch to paginated preview mode.
reportSheet.renderMode('PaginatedPreview');

Parameters

Name	Type	Description
renderMode?	RenderMode	The render mode.

Returns

RenderMode

Return the render mode.

---

repaint

▸ repaint(): void

Repaint the current report sheet.

Returns

void

---

resetCellValue

▸ resetCellValue(row, col): void

Reset specified cell's value.

example

// If the A2 cell's value is modified, resetCellValue can reset the A2 cell to its original value.
reportSheet.resetCellValue(1, 0);

Parameters

Name	Type	Description
row	number	The row index.
col	number	The column index.

Returns

void

---

setParametersUI

▸ setParametersUI(host, initParametersUI, onChange): void

Set the reportSheet parametersUI.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
report.parameter({ customerId: "VINET" });
report.getTemplate().setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
   filter: {
       condition: {
           column: "customerId",
           operator: "Equal",
           parameter: "customerId"
       }
   }
});
report.renderMode("Preview");

const host = document.getElementById("parameterUIHost");
report.setParametersUI(host, initParametersUI, onChanged);
function initParametersUI (sheet) {
    sheet.getCell(3, 3).value("CustomerId:"); // add static label cell
    sheet.getCell(3, 4).bindingPath("customerId").tag("customerId"); // add parameter binding path and tag to this cell
    const submitButton = new GC.Spread.Sheets.CellTypes.Button(); // add submit button
    submitButton.text("Submit");
    sheet.getCell(3, 5).cellType(submitButton).tag("submitButton"); // set button cell type and tag to this cell
}
function onChanged (reportSheet, changedArgs) {
    if (changedArgs.tag === "submitButton") { // submit button clicked.
        reportSheet.regenerateReport();
    }
    if (changedArgs.tag === "customerId") {
        changedArgs.newValue = changedArgs.newValue.toUpperCase(); // update newValue here.
    }
}

Parameters

Name	Type	Description
host	string | HTMLElement	The parameterUI host element or host id.
initParametersUI	InitParametersUIFunctionType	The callback to modify the parameterUI sheet.
onChange	OnChangeFunctionType	The callback when the parameterUI value changed or button clicked.

Returns

void

---

submit

▸ submit(): void

Submit the changes to the remote database based on your data entry setting through the DataManger.

example

// submit the changes.
reportSheet.submit();

Returns

void

---

toWorksheet

▸ toWorksheet(): Worksheet

Generated a static worksheet(no formula, no cell binding) base on the current report sheet.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
});
report.renderMode("Preview");
const reportWorksheet = report.toWorksheet();

Returns

Worksheet

Return the generated worksheet.

---

toggleCollapseState

▸ toggleCollapseState(row, col): void

Toggle the collapse state of the specified cell.

example

const report = spread.addSheetTab(0, "Report", GC.Spread.Sheets.SheetType.reportSheet);
report.renderMode("Design");
const templateSheet = report.getTemplate();
templateSheet.setTemplateCell(0, 0, {
   binding: "Orders[customerId]",
   type: "Group",
   showCollapseButton: true
});
templateSheet.setTemplateCell(0, 1, {
   binding: "Orders[orderId]",
   type: "Group",
});
templateSheet.addSpan(0, 0, 2, 1);
report.renderMode("Preview");
report.toggleCollapseState(0, 0);

Parameters

Name	Type	Description
row	number	The row index.
col	number	The column index.

Returns

void

---

updateCellValue

▸ updateCellValue(row, col, value): void

Update the specified cell's value.

example

// update the A2 cell's value.
reportSheet.updateCellValue(1, 0, 'test');

Parameters

Name	Type	Description
row	number	The row index.
col	number	The column index.
value	any	The new value of the cell.

Returns

void