Convert cells with a formula to a value

Convert cells with a formula to a value

Posted by: niels on 21 May 2023, 10:54 pm EST

• 

  niels

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 21 May 2023, 10:54 pm EST

  Version 16.1
  
  The workbook contans some Custom Function
  
  This can be done
  
  FpSpread.SaveExcel(fileName, ExcelSaveFlags.UseOOXMLFormat | ExcelSaveFlags.NoFormulas);

  But Is it possible to do this only it a formula contains a specific Custom Function?

• 

  niels

  • Post Options:
  • Link
    Copy

  Posted 21 May 2023, 10:59 pm EST

  This seems to work
  
  cell.Copy(cell, ManipulationOptions.All ^ ManipulationOptions.Formulas);
  
  But not for dynamic arrays

• 

  niels

  • Post Options:
  • Link
    Copy

  Posted 21 May 2023, 11:03 pm EST - Updated 21 May 2023, 11:08 pm EST

  O no this seems not to work for dynamic arrays
  
  cell.Copy(cell, ManipulationOptions.All ^ ManipulationOptions.Formulas);

• 

  niels

  • Post Options:
  • Link
    Copy

  Posted 22 May 2023, 12:18 am EST

  This works
  
  var cell = worksheet.Cells[r, c];
  
  var formula = cell.Formula;

                          if (formula != null && formula.Contains("MH."))
                          {
                              var ctx = FpSpread.AsWorkbook().WorkbookSet.CalculationEngine.EvaluationContext;
                              var value = FpSpread.AsWorkbook().WorkbookSet.CalculationEngine.Evaluate(formula, worksheet,
                                  new CellReference(cell.Row, cell.Column));
                              var columnCount = value.GetColumnCount(ctx);
                              if (columnCount < 0) continue;
                              var rowCount = value.GetRowCount(ctx);
                              if (rowCount < 0) continue;
                              var range = worksheet.Cells[cell.Row, cell.Column, cell.Row + rowCount - 1, cell.Column + columnCount - 1];
                              range.Copy(range, ManipulationOptions.Values | ManipulationOptions.Comments | ManipulationOptions.ConditionalFormatting | ManipulationOptions.Formats | ManipulationOptions.Formatting | ManipulationOptions.NumberFormats);
  
                          }
  

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 23 May 2023, 4:34 pm EST - Updated 23 May 2023, 5:33 pm EST

  Hi Niels,

  Unfortunately, we could not replicate the issue on end by using the given information. We have attached a sample application for your reference. See FormulaToValueCells.zip

  We kindly request you update the attached application so that we can reproduce the issue on our end. Otherwise, please provide a stripped-down version of the sample application that replicates the issue on your end.

  Thanks & Regards,
  
  Aastha