Get "real" column header

Get "real" column header

Posted by: iteam on 10 July 2019, 7:25 pm EST

• 

  iteam

  • Post Options:
  • Link
    Copy

  Posted 10 July 2019, 7:25 pm EST

  Hi!

  I’m trying to get from a cell his real header, to be able to make formulas relative to a cell.

  I need, on workbook initialize, to recognize a cell and create into it a formula that sums everything above it.

  The problem is the following:

       <!-- colonna statica per il nome della riga -->
       <gc-column [headerText]="'Dimensione'"
              [width]="100"
              [dataField]="'rowName'">
       </gc-column>
  
  

  As you can see, column have a cutom header text.
  
  When I do something like this… :

  
  tempSheet.getSheet().getValue(i, 0, GC.Spread.Sheets.SheetArea.colHeader)
  
  

  … the value that I get is “Dimensione”, but I nead the column reference instead header name to make a formula from this cell to every cell above.

  Infact this will not work:

  
  tempSheet.getSheet().setFormula(0, j, 'SUM(Dimensione1+Dimensione2)');
  

  Instead of something like this:

  
  tempSheet.getSheet().setFormula(0, j, 'SUM(A1+A2)');
  
  

  I need to get the “A” that reference the column!

  Thanks in advance

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 11 July 2019, 2:58 pm EST

  Hi,

  You could use the following formula to calculate the sum of all cells above the current cell without the need of getting column letter:

  =SUM(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))
  

  Further, you may use the following formula to get a column’s letter using column index:

  function getColumnLetterFromIndex(sheet, colIndex){
      return GC.Spread.Sheets.CalcEngine.evaluateFormula(sheet, '=SUBSTITUTE(ADDRESS(1,'+colIndex+',4),"1","")', 0, 0);
  }
  getColumnLetterFromIndex(sheet, 2); // returns B
  

  Regards