Export to excel without formulas

Posted by: jfulkers on 8 September 2017, 4:08 am EST

    • Post Options:
    • Link

    Posted 8 September 2017, 4:08 am 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.

     

     

  • Posted 8 September 2017, 4:08 am EST

    What SaveExcel overload are you using?  If you set the second parameter to ExcelSaveFlags.NoFormulas then this should work.

  • Posted 8 September 2017, 4:08 am 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.

  • Posted 8 September 2017, 4:08 am 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

     

  • Posted 6 July 2020, 2:53 am EST

    Hello,

    I Had the spread in COM version and this worked. When exporting Custom Function with native the excel exports the custom funcions with 1000+B1+B2.

    In .NET version appear #VALUE.

    Is there any solution?

  • Posted 7 July 2020, 2:50 am EST

    Hello,

    I am sorry but I could not understand your problem. Would it be possible for you to please elaborate? Could you share the code snippet of the same, that should help me better understand your concern.

    Regards,

Need extra support?

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

Learn More

Forum Channels