how to calculate how many rows and columns are occupied in a spreadsheet

Question

Hi,I want to calculate how many rows and columns are occupied in a spreadsheet so, that I can delete the rest ones and keep only those rows and columns which are occupied I want to pass rowCount and colCount in the below function after calculating those.  adjustVisibleRowsAndColumns(    rowCount: number,    colCount: number,    sheetObjectReference: any  ) {    if (sheetObjectReference) {      let sheet: SheetModel = sheetObjectReference.getActiveSheet();      sheet.rowCount = rowCount;      sheet.colCount = colCount;    }  }

Babu Periyasamy · Answer

Hi Vaishali Jain,We have checked your reported query of limiting the number
of columns and rows rendered in the Spreadsheet based on your shared code snippet.
And it can be achieved by setting the isFinite to true in the scrollSettings
property of our Spreadsheet component and it will render the rows and columns
based on the rowCount and colCount property of the Sheet model.And also, if you want to render the rows and
columns based on the data occupied, you can set the rowCount and colCount
based on the usedRange of the Sheet. Note: To get the number of rows and columns occupied, we can
utilize the usedRange property of Sheet. For your convenience, we have prepared the
sample, in which we have set the rowCount and colCount based on the usedRange
in the created
event and attached below along with the code snippet for your reference,Code snippet:

created() {
         
  this.adjustVisibleRowsAndColumns();
      }
     
  adjustVisibleRowsAndColumns() {
         
  let sheets: SheetModel[] = this.spreadsheetObj.sheets;
         
  for (var i = 0; i < sheets.length; i++) {
             
  //Set the rowCount and colCount based on used
  range.
             
  sheets[i].rowCount =
  sheets[i].usedRange.rowIndex + 1;
             
  sheets[i].colCount = sheets[i].usedRange.colIndex + 1;
         
  }
         
  //To set the modified sheet property to the
  Spreadsheet.
         
  this.spreadsheetObj.setProperties({
  sheets: sheets }, true);
         
  //To refresh the changes in UI. 
          this.spreadsheetObj.resize();
      }

Sample link: https://stackblitz.com/edit/angular-gaekkq-ijfd88?file=src%2Fapp.component.ts For more information, please refer the below
documentation,https://ej2.syncfusion.com/angular/documentation/spreadsheet/scrolling#finite-scrolling-with-defined-rows-and-columnsKindly, check the above details and get back
to us for further validation.

Vaishali Jain · Answer

Hi,Thanks for replying but I am still not able to calculate rowCount because I am getting data from API and there inside usedRange colIndex is coming fine but rowIndex is not coming fine. it is taking all the rows in it. So how can I properly get the rowIndex and remove unoccuiedAttaching my code and api response both.Thanks,Vaishali JainAttachment: report_2c4cf20f.zip

Babu Periyasamy · Answer

Hi Vaishali Jain,We have checked your reported query of row Index is not
proper in the usedRange based on your shared JSON. And we would like to let you
know that the rowIndex and colIndex of usedRange
property of the Sheet includes the empty cells with styles property alone in
it. And in your shared JSON, there are greater number of empty cells with
styles property until 1000 rows and so the rowIndex in the usedRange
property is returning index of the rows including the styles alone in it. This
is not an issue, and it is the default behaviour of our Spreadsheet like the
Microsoft Excel.And you can resolve the issue by removing the unwanted
styles in the empty cells of the Excel file that you are opening. However,
based on your shared query we suspect that you want to adjust the row and
column count based on the cells with data in it.For your convenience, we have prepared the sample in which
we have implemented two functions in the sample named `getUsedRowDataIndex`
function to get the row index based on the value present and the `getUsedColDataIndex`
function to get the column index based on the value present and exclude empty
cells with the unwanted styles it. Below attached the prepared sample and code
snippet along with the video demonstration for your reference,Code snippet:

created() {
         
  //Added the cell styles for the empty cells also.
          this.spreadsheetObj.cellFormat({
  fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:Z1');
      }
      adjustVisibleRowsAndColumns() {
          let sheets:
  SheetModel[] = this.spreadsheetObj.sheets;
          for (var i = 0;
  i < sheets.length; i++) {
             
  //Set the rowCount and colCount based on used range.
             
  sheets[i].rowCount =
  this.getUsedRowDataIndex(sheets[i].rows) + 1;
             
  sheets[i].colCount = this.getUsedColDataIndex(sheets[i].rows) + 1;
          }
         //To set the
  modified sheet property to the Spreadsheet.
         
  this.spreadsheetObj.setProperties({ sheets: sheets }, true);
          //To refresh
  the changes in UI. 
          this.spreadsheetObj.resize();
      }
      //Function to get the used row index
  with value in it.
      getUsedRowDataIndex(rows) {
          // Iterate backwards through the rows array.
          for (let i =
  rows.length - 1; i >= 0; i--) {
             
  // Check if the current row exists and has at
  least one cell with a value.
             
  if
  (rows[i]?.cells?.some(cell => cell?.value !== undefined &&
  cell.value !== null && cell.value !== '')) {
                 
  // Return the index of the last row with a value.
                 
  return i;
             
  }
          }
          // Return 0 if
  no rows contain a value
          return 0;
      }
      //Function to get the used column index
  with value in it.
      getUsedColDataIndex(rows) {
          return
  rows.reduce((highestIndex, row) => {
             
  // Check if the current row exists and has cells.
             
  if (row?.cells) {
                 
  // Map over the cells to find indices of cells
  that have values.
                 
  const valueCells =
  row.cells
                     
  .map((cell, cIndex) => cell?.value !== undefined && cell.value !==
  null && cell.value !== '' ? cIndex : -1)
                     
  .filter(index => index !== -1); // Filter out indices that are -1 (no
  value).
      
                  // Calculate the maximum cell index among the found
  value cells and the current highest index
                 
  const maxCellIndex = Math.max(...valueCells, highestIndex);
                 
  // Return the new highest cell index if it's
  greater than the current highest index
                 
  return maxCellIndex > highestIndex ? maxCellIndex : highestIndex;
             
  }
             
  // Return the current highest index if the row
  doesn't have cells
             
  return highestIndex;
          }, 0); // Start with 0 as the initial highest index
      }

Sample link: https://stackblitz.com/edit/angular-gaekkq-sgctar?file=src%2Fapp.component.ts
In the above sample, we have applied some styles properties
to the cells without the data in the created
event and then on a button click, we have adjusted the row and column count
based on the row and column index calculated from the implemented function
which excludes the index without data in it.Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/UsedRange_in_Spreadsheet-483469381Kindly, check the above details and get back to us for
further clarification.

Vaishali Jain · Answer

HI,Thanks for answering.Is there any way to remove empty rows and columns from JSON itself by applying any logic as it is unnecessary creating too much rows in the JSON responseThanks,Vaishali

Vaishali Jain · Answer

Hi,I have also tried this but its not working as expected. Some sheets showing correct rows and columns and some having extra white spaces on the bottom of the sheet and keep scrolling.  Attaching my codeThanks,VaishaliAttachment: trial_815ce0e.zip

Babu Periyasamy · Answer

Hi Vaishali Jain,Regarding skipping unnecessary cells:We have checked your reported query of skipping the
unnecessary style properties in the empty cell from the JSON returned from the
server. And currently, we don’t have support for skipping the style
properties while importing the excel file in our Spreadsheet. However, we
have already confirmed this as a feature and logged it as a feature request. It
will be available in any of our upcoming releases. You can communicate
and track the status of the feature using the below link from our feedback
portal.Feedback link for tracking purposes: https://www.syncfusion.com/feedback/52103/provide-options-to-skip-style-properties-of-the-sheet-while-importing-an-excel
At the planning stage for every release cycle, we review all
the open features once again and finalize features for implementation based on
specific parameters including product vision, technological feasibility, and
customer interest. Once we have anything definite to share about these features
implementation, we will move the feedback to scheduled status with the
tentative release timeline. We appreciate your patience until then.Regarding some sheet having extra white spaces on the
bottom:We have checked your reported query based on your shared
code snippets. And we have modified the previously shared sample by setting the
row and column count in the created event. But the row and column counts are
properly updated, and we are not able replicate your reported issue of getting
white spaces at the bottom.For your convenience, we have prepared the video
demonstration and attached below along with the checked sample,Sample link: https://stackblitz.com/edit/angular-gaekkq-rdnern?file=src%2Fapp.component.ts
Video link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/Set_row_and_col_count180530569
Kindly, check the above information and please share the
below details,
 If
     possible, share the particular excel file that you are facing the issue
     with dummy data.
 Share
     the details of the features used in the excel file like number of rows and
     columns, number of sheets and other features like formatting and styles.
     Also, please share the conditional formatting rules that you have applied
     in your excel. Based on that, we will check by creating the dummy excel
     file.
 Share
     the Spreadsheet package version that you are using.
 If
     possible, please share the detailed description of the issue that you are
     facing along with the screenshot or video demonstration from your end.
Please share the above requested information. Based on that,
we will check and provide you the better solution quickly.