Trim/Cut/Limit Columns and Rows to spreadsheetObj data/source

[Myles Renaud]

Good Afternoon Syncfusion Community

I have been struggling with finding a way to limit the columns and rows to the dataSource in my Spreadsheet.

I have tried this for reference
https://www.syncfusion.com/forums/169247/limit-columns-in-spreadsheet

I have also tried to use the hideColumn command which works initially but when you go to horizontal scroll the extra columns reappear. 

const sheet = this.spreadsheetObj.getActiveSheet();
const colCount = sheet?.colCount ?? 0;
this.spreadsheetObj.hideColumn(columnCount, colCount - 1, true);

This may be due to the fact that my dataSource and columns are dynamic and selected via a secondary API. The amount of rows may fluctuate per dataSource but is generally limited to 100 maximum but frequently will only have a dozen or so records. As well as the columns ranging anywhere from 6-20 potentially.

I do not have anything more than the ejs-spreadsheet tags including sheets or sheet defined on the html

    <ejs-spreadsheet #spreadsheet

                            [showRibbon]="true"

                            [allowFindAndReplace]="true"

                            [allowHyperlink]="false"

                            [showSheetTabs]="true"

                            [allowWrap]="true"

                            [enableContextMenu]="true"

                            [showAggregate]="false"

                            (dialogBeforeOpen) = "dialogBeforeOpen($event)"

                            (created)="created()"

                            (actionComplete)="actionComplete($event)"

                            (actionBegin)="actionBegin($event)"

                            (cellEdit)="onCellEdit($event)"

                            (beforeCellUpdate)="onBeforeCellUpdate($event)"

                            (cellSave)="cellSave($event)"

                            (complete)="onSpreadsheetComplete($event)"

                            (select)="onSelect($event)">                   

    </ejs-spreadsheet>

      this.spreadsheetObj.insertSheet([

        {

          index: 0,

          name: UtilityService.newGuid(),

          rows: this.getDataInSpreadSheetRowFormat(modelConfiguration, modelData),

          protectSettings: { selectCells: true },

          frozenRows: 1

        },

      ]);

As an example

[Myles Renaud]

I have managed to figure out columns by adding the colCount attribute when inserting the sheet based on the dataSource as well as the custom row.height I had previously mentioned in another discussion after adding the customHeight tag.

I have tried to set the columnWidth to Auto / Fill but no success just yet. Ideally I would like to fill based on the spreadsheet component without a horizontal scroll.

And limit rows beyond the dataSource.

Thanks!

let colCounts = entityConfiguration.fields.length;

      //Insert data sheet

      this.spreadsheetObj.insertSheet([

        {

          index: 0,

          name: UtilityService.newGuid(),

          rows: this.getDataInSpreadSheetRowFormat(entityConfiguration, entityData),

          protectSettings: { selectCells: true },

          frozenRows: 1,

          colCount: colCounts

        },

      ]);

      //setTimeout(() => {

        let sheet = this.spreadsheetObj.sheets[0];

        let desiredHeight = 25;

        sheet.rows?.forEach((row) => {

          row.customHeight = true;

          row.height = desiredHeight;        

        });

        this.spreadsheetObj.refresh();

      //}, 200);

[Myles Renaud]

For some reason the range is not working? The first column is being set but not the rest?

[Janakiraman Sakthivel]

Hi Myles Renaud,

Query1: I have been struggling with finding a way to limit the columns and rows to the dataSource in my Spreadsheet.

We suspect that you expect the spreadsheet to render with defined rows and columns from your data source, without creating new rows or columns when scrolling. You can achieve this by setting the 'isFinite' property in 'scrollSettings' to true, and by assigning the row count and column count from the data source. For further details regarding this, please refer to the documentation shared below.

https://ej2.syncfusion.com/angular/documentation/spreadsheet/scrolling

For your reference, we have shared the code snippet and sample for this below.

Sample: Vhjnnl (forked) - StackBlitz

CODE SNIPPET:

app.component.html:

<ejs-spreadsheet #default [scrollSettings]="{ isFinite: true }" (created)="created()">

    </ejs-spreadsheet>


app.component.ts:

created() {

        let totalRowsCount: number = this.data.length;

        let totalColumnsCount: number = Object.keys(this.data[0]).length;

        this.spreadsheetObj.insertSheet([

            {

                index: 0,

                name: 'First Sheet',

                ranges: [{ dataSource: this.data }],

                protectSettings: { selectCells: true },

                frozenRows: 1,

                rowCount: totalRowsCount,

                colCount: totalColumnsCount,

            },

        ]);

    }

Please find the API references below.

https://helpej2.syncfusion.com/angular/documentation/api/spreadsheet/#sheets

https://helpej2.syncfusion.com/angular/documentation/api/spreadsheet/sheetModel/#colcount

https://helpej2.syncfusion.com/angular/documentation/api/spreadsheet/sheetModel/#rowcount

Query2: I have tried to set the columnWidth to Auto / Fill but no success just yet. The first column is being set but not the rest?

We have checked the issue you reported, and we were able to replicate it on our end. During our validation process, we discovered that the setColumnsWidth() method began assigning column widths before the data was rendered in the spreadsheet from the 'insertSheet()' method. This may have caused the problem you reported. To resolve this, we have modified the process to assign column widths using the setColumnsWidth() method within a 'setTimeout' function. As a result, the assigned column widths are now properly applied to all columns. Therefore, we suggest you follow this process to resolve your reported problem.

For your reference, we have shared the code snippet and sample for this below.

Sample: Vhjnnl (forked) - StackBlitz

CODE SNIPPET:

created() {

        let totalRowsCount: number = this.data.length;

        let totalColumnsCount: number = Object.keys(this.data[0]).length;

        let rowRange: string = '1:' + totalRowsCount;

        this.spreadsheetObj.insertSheet([

            {

                index: 0,

                name: 'First Sheet',

                ranges: [{ dataSource: this.data }],

                protectSettings: { selectCells: true },

                frozenRows: 1,

                rowCount: totalRowsCount,

                colCount: totalColumnsCount,

            },

        ]);

        setTimeout(() => {

            this.spreadsheetObj.setColumnsWidth(125, ['A:I']);

            this.spreadsheetObj.setRowsHeight(25, [rowRange]);

        });

    }

We suggest using the 'setRowsHeight' method to set the height for the required rows, as demonstrated in the code snippet above.

If we have misunderstood your queries, kindly explain in more detail regarding your queries for our understanding. Based on that, we will check and provide you with a better solution quickly.

[Myles Renaud]

Thanks I appreciate the suggested changes and they are working as intended.

Just to follow up on the width, is there a possibility to limit columns / autofit to my spreadsheet component eliminating my horizontal scroll bar?

The data and columns are dynamic so I will have anywhere from 6-20 columns depending on the table as an example below.

Is this a setting for the spreadsheet and columns to autofit, are there any SCSS changes I need to make the the spreadsheet first to disable the horizontal scroll bar on load until I manually make columns wider? I intend to have a couple of specific column data types manually set in my configuration and would be nice if the rest just autofit in relation to the space that is left.

Thanks!

[Babu Periyasamy]

Hi Myles Renaud,

We have checked your reported query based on your shared details and we suspect that you want to remove the empty horizontal scrollbar present.

To achieve this, we have prepared a sample where we initially render the Spreadsheet with a small dataSource and set the colCount property of the Sheet based on the usedRange in the created event.

Then, we check if an empty horizontal scrollbar is present by comparing the scrollWidth and clientWidth of the scroll element. If the scrollbar is empty, we add a custom class to the element. This custom class includes a “display: none” style property in the CSS file to hide the scrollbar.

Additionally, if we update the dataSource with a larger dataset that extends beyond the viewport width, then we again check the scroll element in the dataSourceChanged event and if it is not empty, we remove the custom class that hides the empty scrollbar and shows the scrollbar to scroll the content beyond the viewport.

For your convenience, we have prepared the video demonstration and attached below along with the prepared sample and code snippet for your reference,

Code snippet:

//app.component.ts

 

created() {

        this.setColCountAndScroller();

    }

    setColCountAndScroller() {

        let sheets: SheetModel[] = this.spreadsheetObj.sheets;

        for (let i = 0; i < sheets.length; i++) {

            //Set the colCount based on the usedRange.

            sheets[i].colCount = sheets[i].usedRange.colIndex + 1;

        }

        this.spreadsheetObj.sheets = sheets;

        this.spreadsheetObj.dataBind();

        setTimeout(() => {

            this.hideShowScroller();

        },10)

    }

    hideShowScroller() {

        let scroller: Element = this.spreadsheetObj.element.querySelector('.e-scroller');

        //To check whether there is empty scroller or not.

        if (scroller.scrollWidth === scroller.clientWidth) {

            //Added the custom classname to hide the empty scroller in the CSS level.

            scroller.classList.add('e-scroller-hide');

        } else {

            scroller.classList.remove('e-scroller-hide');

        }

    }

    changeDataSource() {

        //Update the new dataSource.

        this.data = newData();

        this.spreadsheetObj.dataBind();

    }

 

// Triggers after the dataSource is changed in the spreadsheet.

dataSourceChanged() {

        //Update the column count and scroller based on the updated dataSource.

        this.setColCountAndScroller();

}

 

//app.component.css

 

/* Hide the empty scroller */

.e-scroller-hide {

    display: none;

}

 

Sample link: https://stackblitz.com/edit/angular-mwhu3h-mh1anf?file=src%2Fapp.component.html

Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Remove_Empty_Scrollbar-1078029100

Kindly, check the details in your end. And if we misunderstood your requirement, please share the detailed description of your requirement along with the screenshot or video demonstration. Based on that, we will check and provide you the better solution quickly.