Bids Tracker Report

Open the demo in new window can open the designer

You can use the 'List' cell type to generate the bids tracker report in the following steps.

• Define the Bids table in the DataManager.
• Set the style for the template.
• Bind the data column to the template and use the 'List' cell type.

const spread = new GC.Spread.Sheets.Workbook('demo-host', { sheetCount: 1 });
const records = [
    ['BID 01', 'Bid number 1', '2023/6/27', 2000, 0.5, '2023/7/27'],
    ['BID 02', 'Bid number 2', '2023/6/17', 3500, 0.25, '2023/7/17'],
    ['BID 03', 'Bid number 3', '2023/6/17', 6500, 0.3, '2023/7/17'],
    ['BID 04', 'Bid number 4', '2023/6/27', 4000, 0.2, '2023/7/27'],
    ['BID 05', 'Bid number 5', '2023/6/9', 3800, 0.75, '2023/7/9'],
    ['BID 06', 'Bid number 6', '2023/6/20', 1500, 0.45, '2023/7/20'],
    ['BID 07', 'Bid number 7', '2023/6/22', 5000, 0.65, '2023/7/22'],
];
const columns = ['BID', 'Description', 'DataReceived', 'Amount', 'Progress', 'Deadline'];
const bidsTable = spread.dataManager().addTable('Bids', {
    data: records.map((x) => {
        const record = {};
        columns.forEach((c, i) => record[c] = x[i]);
        return record;
    })
});
bidsTable.fetch().then(() => {
    const reportSheet = spread.addSheetTab(0, 'report1', GC.Spread.Sheets.SheetType.reportSheet);
    const templateSheet = reportSheet.getTemplate();

    // set style for the template
    templateSheet.options.gridline.showHorizontalGridline = false;
    templateSheet.options.gridline.showVerticalGridline = false;
    templateSheet.defaults.colWidth = 120;
    templateSheet.defaults.rowHeight = 30;
    [10, 110].forEach((h, i) => templateSheet.setRowHeight(i, h));
    [10, 80, 160, 120, 120, 260].forEach((w, i) => templateSheet.setColumnWidth(i, w));
    const headerStyle = new GC.Spread.Sheets.Style();
    headerStyle.backColor = '#90CAF9';
    headerStyle.foreColor = '#424242';
    headerStyle.font = 'bold 12px "Open Sans"';
    headerStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    headerStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    const titleStyle = headerStyle.clone();
    titleStyle.backColor = '#00587a';
    titleStyle.foreColor = 'white';
    titleStyle.font = '40px "Open Sans"';
    const dataStyle = headerStyle.clone();
    dataStyle.backColor = 'white';
    dataStyle.font = '12px "Open Sans"';
    const border = new GC.Spread.Sheets.LineBorder('#B3E5FC', 1);
    dataStyle.borderRight = border;
    templateSheet.addSpan(1, 1, 1, 6);
    templateSheet.setValue(1, 1, 'BIDS TRACKER');
    templateSheet.setStyle(1, 1, titleStyle);
    templateSheet.getRange(2, 1, 1, 6).setStyle(headerStyle);
    templateSheet.getRange(3, 1, 1, 6).setStyle(dataStyle);
    templateSheet.getStyle(3, 4).formatter = '"$"#,##0.00';
    templateSheet.getStyle(3, 5).formatter = '0%';
    templateSheet.getStyle(3, 6).borderRight = undefined;
    const cfs = templateSheet.conditionalFormats;
    const dataStyle3 = dataStyle.clone();
    dataStyle3.backColor = '#E3F2FD';
    dataStyle3.borderRight = undefined;
    cfs.addFormulaRule('Mod(R.Index(B4), 2) = 0', dataStyle3, [new GC.Spread.Sheets.Range(3, 1, 1, 6)]);
    const ScaleValueType = GC.Spread.Sheets.ConditionalFormatting.ScaleValueType;
    const dataBarRule = cfs.addDataBarRule(ScaleValueType.number, 0, ScaleValueType.number, 1, '#FFCDD2', [new GC.Spread.Sheets.Range(3, 5, 1, 1)]);
    dataBarRule.gradient(false);

    // set binding for the template
    const columns = ['BID', 'Description', 'Data Received', 'Amount', 'Progress', 'Deadline'];
    columns.forEach((columnName, i) => {
        templateSheet.setValue(2, i + 1, columnName);
        templateSheet.setTemplateCell(3, i + 1, {
            type: 'List',
            binding: `Bids[${columnName.replace(' ', '')}]`,
        });
    });

    reportSheet.refresh();
});