Fill Blank Data

The Fill Blank Data feature ensures that datasets inside list or group cells are padded with blank rows or columns until they meet a specified multiple when necessary.

Usage Scenarios

When generating reports, developers often face misaligned layouts. For example, the last page of a paginated report may show fewer rows than expected, or grouped sections may contain blocks of different lengths, leading to uneven Excel/PDF exports.

With Fixed Fill Count, these issues are automatically resolved:

Consistent pagination and grouping: Each page or data block (such as groups or lists) is filled to a fixed multiple of rows or columns, ensuring uniform structure throughout the report.
Non-intrusive blank filling: Empty rows or columns are automatically inserted without affecting filtering, sorting, or the dataset itself.
Cleaner output: Exported or printed reports maintain a neat, professional layout across all blocks.

Related API

This feature is configured through the GC.Spread.Report.TemplateSheet.setTemplateCell method.

When applying the setting on a ListCell or GroupCell, use the fixedFillCount property.

Note:
The value must be a positive integer.
The default is 1, which means no extra padding is applied.

export type ListCell = DataCellBase & {
    type: 'List';
    fixedFillCount?: number;
    // see API reference for other properties
};

export type GroupCell = DataCellBase & {
    type: 'Group';
    fixedFillCount?: number;
    //see API reference for other properties
};

Usage Examples

Step 1: Create a Report Sheet

let spread = new GC.Spread.Sheets.Workbook("spreadjs-host");

// Create Data Source
var sheet = spread.getActiveSheet();
    sheet.tables.add('table1', 0, 0, 8, 5, GC.Spread.Sheets.Tables.TableThemes.light1);
    sheet.setArray(0, 0, [
            ['orderId', 'name', 'employeeId', 'unitPrice', 'quantity'],
            ['A001', 'Pencil', '1', 1.5, 2],
            ['A002', 'Notebook', '1', 5, 1],
            ['A003', 'Eraser', '2', 1, 2],
            ['A004', 'Eraser', '1', 1, 1],
            ['A005', 'Pencil', '2', 1.5, 1],
            ['A006', 'Pencil', '2', 1.5, 2],
            ['A007', 'Notebook', '2', 5, 1]
    ]);
    // convert table1 to a data manager table
    sheet.tables.convertToDataTable('table1');

// Create a Template Sheet
const reportSheet = spread.addSheetTab(1, 'report', GC.Spread.Sheets.SheetType.reportSheet);
reportSheet.renderMode('Design');
const templateSheet = reportSheet.getTemplate();

// set value and binding for the template
const columns = ['name','employeeId', 'orderId', 'unitPrice', 'quantity'];
columns.forEach((columnName, i) => {
    templateSheet.setValue(0, i, `${columnName[0].toUpperCase()}${columnName.substring(1)}`);
    templateSheet.setTemplateCell(1, i, {
      type: 'List',
      binding: `table1[${columnName}]`,
    });
});

// Set Table End
templateSheet.setValue(2,0,'Current Page:');
templateSheet.setFormula(2,1,'=R.CURRENTPAGE()');
templateSheet.setValue(2,2,'Total Pages:');
templateSheet.setFormula(2,3,'=R.PAGESCOUNT()');

// set border style for the template
const style = new GC.Spread.Sheets.Style();
style.borderTop = new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin);
style.borderBottom = new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin);
templateSheet.getRange('A:E').setStyle(style);

// Set Pagination
templateSheet.setPaginationSetting({    
   rowPagination: {       
        paginationDataCell: 'C2',        
        rowCountPerPage: 4,
   },    
   titleRow: {
        start: 0,
        end: 0,
   },
   endRow: {
        start: 2,
        end: 2,
   },
});

// refresh the report
reportSheet.refresh();
reportSheet.renderMode('Preview');

reportSheet.renderMode('PaginatedPreview');

The report contains a total of 7 records.

With rowCountPerPage set to 4, the data is divided across 2 pages:

The first page displays 4 rows.
The remaining 3 rows appear on the second page.

Step 2: Set Fixed Fill Count

Notes:
Works for both vertical (rows) and horizontal (columns) expansions.
Filling is always handled relative to the parent node.

Scenario 1: Apply fixedFillCount to List Column

Setting: Define column Name with fixedFillCount = 2.
Effect:
- The Name column expands from 7 lists → 8 lists.
- The table expands from 7 rows → 8 rows.

reportSheet.renderMode('Design');

templateSheet.setTemplateCell(1, 0, {
    type: 'List',
    binding: 'table1[name]',
    fixedFillCount: 2
});

reportSheet.refresh();
reportSheet.renderMode('Preview');

reportSheet.renderMode('PaginatedPreview');

Scenario 2: Apply fixedFillCount to Group Column

Setting: Define column Name as group, with fixedFillCount = 2.
Effect:
- The Name column expands from 3 groups → 4 groups.
- The table expands from 7 rows → 8 rows.

reportSheet.renderMode('Design');

templateSheet.setTemplateCell(1, 0, {
    type: 'Group',
    binding: 'table1[name]',
    fixedFillCount: 2
});

reportSheet.refresh();
reportSheet.renderMode('Preview');

reportSheet.renderMode('PaginatedPreview');

Using Designer

You can also set the fixedFillCount property of the template cell using the SpreadJS designer's Fixed Fill Count option available in the Report Cell properties panel.

The value defaults to 1. You can change it either by using the up/down arrows or by typing a number directly into the field.