Dynamic Column Size

SpreadJS provides support for dynamic column sizing (also called star sizing and proportional sizing).

It is used to configure the width of the columns and the height of the rows in such a way that the viewport is filled exactly as per the weighted proportion (represented by the * symbol) specified by the user.

This type of sizing can be used in conjunction with numbers to define a weighted proportion, where:

• The weighting factor is expressed by using the * (star character) or n* character (n can be any integer value (like 2*, 3* etc.) or a non-integer value (like 0.5* or 1.5* etc.)).

• Available Size = Viewport Size - Visible Fixed Size.

• The available size is distributed to the visible row or column as per the weighted factor defined by the user.

• Total column width = Viewport width.

Usage Scenario

Dynamic Column sizing is beneficial especially when users need to widen multiple columns proportionally in order to analyze reports or manipulate large spreadsheets.

For example - The following image depicts a spreadsheet with dynamic column sizing applied on column A, B and D and E in the proportion of 1.5*, 1.5*, and respectively.

In the above example, columns A and B with a star size "1.5*" are filling 1.5 times that of a standard "*" sized column in the viewport.

Supported User Actions

Dynamic column sizing (or proportional sizing) ensures the columns and rows in a worksheet fill the viewport exactly whenever the viewport size is changed or the user adds, deletes, or resizes any row or column.

Users can configure dynamic column size in the worksheet by using the setColumnWidth and the setRowHeight methods. After applying the dynamic column size, the columns and rows (on which the star sizing is applied) will automatically resize to fill up the viewport when users perform the following actions on the worskheet:

• Users change the size of the viewport.

• Users add, delete or resizes any columns or rows in the worksheet.

• Users change the row count or column count.

• Users hide or unhide the rows or columns.

• Users group or ungroup the rows and columns.

• Users apply filters to the rows and columns.

• Users expand or collapse the outline columns in the worksheet.

Dynamic Column Sizing is also supported while importing and exporting JSON files.

Dynamic Column Sizing Rules

The following rules must be kept in mind while working with dynamic column size feature in the worksheet:

• The rows and columns can both be set to star size in one sheet.

• When users try to resize the star sized row or column, or call the autoFitColumn/autoFitRow methods, then the operated row or column size will overwrite to a fixed number with the actual size.

  In case you're working on the split resize mode (only nextTo mode), then it will overwrite both the operated row or column along with the next row or column size to a fixed number.

• If the smallest star sizing is <1 (less than 1) or the available size is also <1 (less than 1), then the star size will keep the old size because it will stop calculating the dynamic size.

• If the users set the star sized row or column to "hidden" or "0*", then the methods getColumnWidth and getRowHeight will return 0.

• If the users set the star-sized row or column to an invalid string like "abc", it will be treated as the default value.

• After users zoom the worksheet, the fixed row or column will zoom and the star sizing will stretch to fill the viewport.

• When the star-sized row or column changes, then any binding objects like shapes, charts, comments, and floating objects in the worksheet will also follow the change.

Note: The following limitations must be kept in mind while working with the dynamic column size feature in the worksheet:

• The row headers and the column headers don't support dynamic sizing.

• Because MS Excel doesn't provide support for dynamic column sizing, the dynamic sizing will not be applicable while importing and exporting excel files.

• While printing to PDF or exporting to excel, it will display the size at view size(old size).

Using Code

Refer to the following example code to configure dynamic column sizing in a worksheet.

$(document).ready(function () {
    // Initializing Spread
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });

    // Get the activesheet
    var activeSheet = spread.getSheet(0);
    activeSheet.suspendPaint();
    var colHeader = GC.Spread.Sheets.SheetArea.colHeader;
    activeSheet.setRowCount(12);
    activeSheet.setColumnCount(5);

    // Setting dynamic column sizes
    activeSheet.setColumnWidth(0, "1.5*");
    activeSheet.setColumnWidth(1, "1.5*");
    activeSheet.setColumnWidth(3, "*");
    activeSheet.setColumnWidth(4, "*");
    // Set the row height
    activeSheet.setRowHeight(0, 50, colHeader);
    for (var row = 0; row < 12; row++) {
        activeSheet.setRowHeight(row, "*");
        for (col = 0; col < 5; col++) {
            activeSheet.getCell(row, col)
                .cellPadding('0 0 0 10')
                .vAlign(GC.Spread.Sheets.VerticalAlign.center);
        }
    }
    activeSheet.setValue(0, 0, 'Company Name', colHeader);
    activeSheet.setValue(0, 1, 'City', colHeader);
    activeSheet.setValue(0, 2, 'State', colHeader);
    activeSheet.setValue(0, 3, 'Phone', colHeader);
    activeSheet.setValue(0, 4, 'Fax', colHeader);

    activeSheet.setArray(0, 0, [
        ["Super Mart of the West", "Bentonville", "Arkansas", "(800) 555-2797", "(800) 555-2171"],
        ["Electronics Depot", "Atlanta", "Georgia", "(800) 595-3232", "(800) 595-3231"],
        ["K&S Music", "Minneapolis", "Minnesota", "(612) 304-6073", "(612) 304-6074"],
        ["Tom's Club", "Issaquah", "Washington", "(800) 955-2292", "(800) 955-2293"],
        ["E-Mart", "Hoffman Estates", "Illinois", "(847) 286-2500", "(847) 286-2501"],
        ["Walters", "Deerfield", "Illinois", "(847) 940-2500", "(847) 940-2501"],
        ["StereoShack", "Fort Worth", "Texas", "(817) 820-0741", "(817) 820-0742"],
        ["Circuit Town", "Oak Brook", "Illinois", "(800) 955-2929", "(800) 955-9392"],
        ["Premier Buy", "Richfield", "Minnesota", "(612) 291-1000", "(612) 291-2001"],
        ["ElectrixMax", "Naperville", "Illinois", "(630) 438-7800", "(630) 438-7801"],
        ["Video Emporium", "Dallas", "Texas", "(214) 854-3000", "(214) 854-3001"],
        ["Screen Shop", "Mooresville", "North Carolina", "(800) 445-6937", "(800) 445-6938"]
    ]);

    spread.resumePaint();
});