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?

     <PRE class=coloredcode> <SPAN class=kwd>Private Sub</SPAN> Form1_Load(<SPAN class=kwd>ByVal</SPAN> sender <SPAN class=kwd>As</SPAN> System.<SPAN class=kwd>Object</SPAN>, <SPAN class=kwd>ByVal</SPAN> e <SPAN class=kwd>As</SPAN> System.EventArgs) <SPAN class=kwd>Handles MyBase</SPAN>.Load
    FpSpread1.ActiveSheet.AddCustomFunction(<SPAN class=kwd>New</SPAN> CubeFunctionInfo())
    FpSpread1.ActiveSheet.SetValue(0, 0, 1.0)
    FpSpread1.ActiveSheet.SetValue(0, 1, 2.0)
    FpSpread1.ActiveSheet.SetFormula(0, 2, <SPAN class=st>"SUM(A1:B1)"</SPAN>)
    FpSpread1.ActiveSheet.SetFormula(0, 3, <SPAN class=st>"CUBE(C1)"</SPAN>)
    <SPAN class=kwd>End Sub

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

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

    End Class</SPAN>
    </PRE> 
Need extra support?

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

Learn More

Forum Channels