How to improve performance with hide zero rows functionality

How to improve performance with hide zero rows functionality

Posted by: mayank.singh on 10 June 2021, 5:47 pm EST

• 

  mayank.singh

  • Post Options:
  • Link
    Copy

  Posted 10 June 2021, 5:47 pm EST

  Hi Team,
  
  In my desktop client case there is 25k rows and 16 + column. I just need to hide zero values rows and I’m iterating a loop and checking the zero value for each column for every iteration. and its almost taking 6 min so please suggest the suitable solution for this.

  form.SuspendSpreadLayout();
  
  ReportArea area = (ReportArea)areaOperations.Area.UserInterfaceData;
  
  if (area.CustomData)
  
  return;

              int startRow = area.DataBeginRow;
              int endRow = area.AreaEndRow;
              int sheetNo = area.SheetNo;
              Sheet reportSheet = dataDefinitionParser.GetSheet(sheetNo);
  
              IList<int> udidCols = areaOperations.GetDefinitionColumnUdidPositions();
       
              form.SheetAutoCalculation(sheetNo, false);
              for (int i = startRow; i <= endRow; i++)
              {
                  row = i;
                  if (isHeaderRow(sheetNo, row, udidCols))
                      continue;
  
                  if (IsZeroRow(areaOperations, sheetNo, row))
                  {
                      if (area.HiddenRowsDone == false && !form.RowVisible(sheetNo, row))
                      {
                          //Put row to hidden rows collection
                          if (!reportSheet.HiddenRowsTable.Contains(row))
                              reportSheet.HiddenRowsTable.Add(row);
                      }
                      if (!reportSheet.HiddenRowsTable.Contains(row))
                          //Don't do anything to originally hidden row
                          if (state)
                          {
                              //Hide row
                              form.HideRow(sheetNo, row);
                              //Add zero row to zerorowstable
                              if (!reportSheet.ZeroRowsTable.Contains(row))
                                  reportSheet.ZeroRowsTable.Add(row);
                          }
                          else
                          {
                              //Show row, must check rowset
                              if (reportSheet.DefaultRowSet || reportSheet.SelectedRowSet.MarkedRows.Contains(row))
                                  form.ShowRow(sheetNo, row);
                          }
                  }
                  else if (!reportSheet.HiddenRowsTable.Contains(row) && (reportSheet.DefaultRowSet || reportSheet.SelectedRowSet.MarkedRows.Contains(row)))
                      form.ShowRow(sheetNo, row);
              }
  
  
              if (state)
                  //Show active row, hidezerorows may cause unvisible rows
                  if (form.GCSpread == null)
                      form.ShowRow(0, form.ActiveRow, VerticalPosition.Center);
  
              area.HiddenRowsDone = true;
  
              form.ResumeSpreadLayout();
              form.SheetAutoCalculation(sheetNo, true);
  

  and IsZeroRow is:

  int col = areaOperations.Area.DefinitionColumns.Count;
  
  Sheet sheet = dataDefinitionParser.GetSheet(sheetNo);
  
  form.SuspendSpreadLayout();
  
  foreach (DATADEF.DataColumn column in areaOperations.Area.DataColumns.GetDataColumns())
  
  {
  
  if (column.DataField == null)
  
  continue;

              if (sheet.SubTotalRowPositions != null && sheet.SubTotalRowPositions.Count > 0 && sheet.SubTotalRowPositions.Contains(row))
              {
                  zeroRow = false;
                  break;
              }
  
              if (column.DataField.Type == DataFieldType.Numeric
                  || column.DataField.Type == DataFieldType.Formula)
              //Check if other than zero
              {
                  //alternative to get number                    
                  object o = formHelper.GetObjectFromSheet(sheetNo, row, col);
                  if (!double.TryParse(o.ToString(), out n))
                      n = 0;
                  if (n != 0)
                  {
                      zeroRow = false;
                      break;
                  }
              }
              else if (column.DataField.Type == DataFieldType.Comment)
              {
                  //Comment, ignore
                  col++;
                  continue;
              }
              else
              {
                  //Extra data
                  //Check length
                  string value = formHelper.GetStringFromSheet(sheetNo, row, col);
                  if (value.Length != 0)
                  {
                      zeroRow = false;
                      break;
                  }
              }
              col++;
          }
          form.ResumeSpreadLayout();
  

• 

  jitender.yadav

  

  • Post Options:
  • Link
    Copy

  Posted 14 June 2021, 11:39 pm EST

  Hi,

  Spread itself should not be causing this performance issue.
  
  I created a sample application with 25k rows and 30 columns. To hide empty rows (~70% of total rows), it took about 5 seconds.

  I would suggest you to profile your application and see which method calls are taking most of the time.

  If you can attach a sample demonstrating the issue, then we might be able to suggest a better solution.

  Regards,
  
  Jitender