Export to excel without formulas.

Posted by: jfulkers on 8 September 2017, 1:08 pm EST

  • Posted 8 September 2017, 1:08 pm EST

    Is there a way to Save to excel with just the data values and not the formulas BUT to also keep all the rest of the formatting.  If I choose to save to excel with data only, I lose ALL formatting including the formulas.


     


     

  • Replied 8 September 2017, 1:08 pm EST

    What SaveExcel overload are you using?  If you set the second parameter to ExcelSaveFlags.NoFormulas then this should work.
  • Replied 8 September 2017, 1:08 pm EST

    Thanks BobM, that works great except for one thing.  When we have a custom formula set up on a cell, and use the :"FarPoint.Excel.ExcelSaveFlags.NoFormulas", it does not evaluate the result of the custom formula to the exported cell.. It is showing as blank.


    Our custom formula is basically set up to act as an Excel SumProduct formula by adding up one column based upon the values in two other corresponding columns.  The only thing I can think of is to loop through the grid and switch out all the custom formulas and replace with "SumProduct" during the export process..  That just seems very inefficient, especially when dealing with a 15K+ row grid.


    example of sumproduct that works in excel for us:


    =SumProduct((P9:P21="22")*(AH9:AH21="3")*R9:R21)


    I know the version of SumProduct including in spread is a watered down version of Excel's SumProduct and does not support the formula above, which is why we went with the custom formula in the first place.

  • Replied 8 September 2017, 1:08 pm EST

    Hello,


    I am not able to reproduce this behavior. My formulas (built in functions and custom functions) all exported the calculated results to Excel. Could you post a small zipped project reproducing this behavior for us to debug?

     
      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    FpSpread1.ActiveSheet.AddCustomFunction(New CubeFunctionInfo())
    FpSpread1.ActiveSheet.SetValue(0, 0, 1.0)
    FpSpread1.ActiveSheet.SetValue(0, 1, 2.0)
    FpSpread1.ActiveSheet.SetFormula(0, 2, "SUM(A1:B1)")
    FpSpread1.ActiveSheet.SetFormula(0, 3, "CUBE(C1)")
    End Sub

    Private Sub
    Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    FpSpread1.SaveExcel("c:\dummy\test.xls", FarPoint.Excel.ExcelSaveFlags.NoFormulas)
    End Sub
    End Class

    Public Class
    CubeFunctionInfo
    Inherits FarPoint.CalcEngine.FunctionInfo
    Public Overrides ReadOnly Property Name() As String
    Get
    Return
    "CUBE"
    End Get
    End Property
    Public Overrides ReadOnly Property
    MinArgs() As Integer
    Get
    Return
    1
    End Get
    End Property
    Public Overrides ReadOnly Property
    MaxArgs() As Integer
    Get
    Return
    1
    End Get
    End Property
    Public Overrides Function
    Evaluate(ByVal args() As Object) As Object
    Dim
    num As Double = FarPoint.CalcEngine.CalcConvert.ToDouble(args(0))
    Return num * num * num
    End Function

    End Class

     
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels