Report Sheet Data Charts

SpreadJS supports DataCharts in a ReportSheet, which provide a more intuitive way to present data through visualization. DataCharts simplify data comparison and analysis, enhancing data readability. They also aid in making more informed decisions.

DataCharts also provide more powerful and flexible data binding, allowing you to bind the data inside the data table of the data manager directly and provide more powerful style settings.

DataCharts in ReportSheets support two types:

Float Chart

Float Chart allows you to add a DataChart anywhere ina ReportSheet. The position of the DataChart is based on the cell's position where the upper-left corner of the cell is located after generating the Report. Furthermore, the position of the Float Chart is calculated according to the Report’s location.

You can also set Pagination mode for a Float Chart and place it in the Report Title or Report End area; it will expand with the Title or End area.

The Float Chart supports both cell binding and table binding. In cell binding, the Float Chart uses the default data provided by SpreadJS because the report data is not generated in the Design mode. Whereas, in table binding, the Float Chart will get the latest data to draw as long as the data exists in the table; hence you can still use the real data to draw in the design mode.

You can refer to the following example code to add Float Chart in ReportSheet:

// Add ReportSheet to workbook.
reportSheet = spread.addSheetTab(0, 'Float Chart', GC.Spread.Sheets.SheetType.reportSheet);

// Get template sheet of current ReportSheet.
const templateSheet = reportSheet.getTemplate();

// Add Float Chart to ReportSheet.             
const chart = templateSheet.dataCharts.add("reportChart1", 230, 20, 300, 200, GC.Spread.Sheets.DataCharts.DataChartType.column);
chart.setChartConfig({
    tableName: 'Sales',
    plots: [
        {
            type: GC.Spread.Sheets.DataCharts.DataChartType.column,
            encodings: {
                values: [{ field: 'Sales', aggregate: GC.Spread.Sheets.DataCharts.Aggregate.sum }],
                category: { field: 'Product' },
                color: { field: 'Product' },
            }
        }
    ]
});

float-chart

Template Cell Chart

Template Cell Chart allows you to add a DataChart to the template cell in ReportSheet. This chart will repeat with the cell, and also the current cell is the context when the DataChart is getting the data of the cells.

You can refer to the following example code to add Template Cell Chart in ReportSheet by setting the template cell type as "Chart" and setting "dataChartName" of the template cell:

// Add ReportSheet to workbook.
reportSheet = spread.addSheetTab(0, 'Cell Chart', GC.Spread.Sheets.SheetType.reportSheet);

// Get template sheet of current ReportSheet.
const templateSheet = reportSheet.getTemplate();

// Set row height and column width.
templateSheet.defaults.colWidth = 120;
templateSheet.setRowHeight(2, 60);
templateSheet.setColumnWidth(5, 300);

// Add styles for template cells.
const headerStyle = new GC.Spread.Sheets.Style();
headerStyle.backColor = "#6e8ec2";
headerStyle.foreColor = "#ffffff";
headerStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
headerStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
templateSheet.getRange("A2:F2").setStyle(headerStyle);

const cellStyle = new GC.Spread.Sheets.Style();
cellStyle.backColor = "#f6f9ff";
cellStyle.foreColor = "#323232";
cellStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
cellStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
cellStyle.borderBottom = new GC.Spread.Sheets.LineBorder("rgb(216, 216, 216)", GC.Spread.Sheets.LineStyle.medium);
templateSheet.getRange("A3:E3").setStyle(cellStyle);

// Add template cells.
['Region', "ProductCategory", "Product", "Sales", "Return"].forEach((x, r) => {
    templateSheet.setValue(1, r, x);
    if (r <= 2) {
        templateSheet.setTemplateCell(2, r, {
            type: "Group",
            binding: `Sales[${x}]`,
            spillDirection: "Vertical",
            context: {
                horizontal: "Default",
                vertical: "Default"
            }
        })
    }
    if (r > 2) {
        templateSheet.setTemplateCell(2, r, {
            type: "Summary",
            binding: `Sales[${x}]`,
            spillDirection: "Vertical",
            aggregate: "Sum",
            context: {
                horizontal: "Default",
                vertical: "Default"
            }
        })
    }
})

// Add Template Cell Chart to ReportSheet.
const chart = templateSheet.dataCharts.add("reportChart1", 20, 20, 400, 300, GC.Spread.Sheets.DataCharts.DataChartType.column);

// Set Data Chart configuration.
chart.setChartConfig({
    tableName: 'Sales',
    plots: [
        {
            type: GC.Spread.Sheets.DataCharts.DataChartType.line,
            encodings: {
                values: [{ field: 'Sales', displayName: "Sales" }, { field: 'Return', displayName: "Return" },],
                category: { field: 'Product', displayName: "Product" },
            }
        }
    ]
});

// Set template cell "type" and "dataChartName".
templateSheet.setTemplateCell(2, 5, {
    type: "Chart",
    context: {
        horizontal: "Default",
        
        // The vertical context is the A3 cell.
        // The Template Cell Chart will repeat with A3.
        vertical: "A3"
    },
    dataChartName: "reportChart1"
})

template-cell-chart