Problem exporting to Excel (custom formulas)

Posted by: luigggye-fp on 8 September 2017, 2:38 pm EST

  • Posted 8 September 2017, 2:38 pm EST

    Hi, I am sucesfully using a "custom function" in order to set the columns totals in a sheet.

    But when I want to export the sheet to an Excel file using: FP.SaveExcel(MacroFilePath_EXCEL, FarPoint.Win.Spread.Model.IncludeHeaders.ColumnHeadersCustomOnly), the columns total is misunderstood, as you can see in the attached sample of the exported file.

    I use a custom function because I want to include in the sum of the columns "just some cells"  not  "all cells" according my logic.

    Two questions:

    1) There is some way to export the sheet with their values (without formulas?), in other word, export the sheet just the way it look ?
    2) There a way to export just "part of the sheet" ?, for example: to export the rows X to Y ?

    Thanks in advance,


  • Replied 8 September 2017, 2:38 pm EST


    There are other options for ExcelSaveFlags have you tried NoFormulas, SaveAsViewed, or DataOnly? As far as saving only part of your sheet to an excel file, I don't see a ready made way to do that, you could always instantiate an invisible spread you can copy the date you want to its sheet then save it to excel from there.

    <TABLE class=FilteredItemListTable>

    <TD class=MemberNameCell>NoFlagsSet</TD>
    <TD class=DescriptionCell>[0] Saves the spreadsheet to the Excel-compatible file with no special options. </TD></TR>
    <TD class=MemberNameCell>NoFormulas</TD>
    <TD class=DescriptionCell>[1] Saves the displayed data but not the formulas to the Excel-compatible file. </TD></TR>
    <TD class=MemberNameCell>SaveCustomRowHeaders</TD>
    <TD class=DescriptionCell>[2] Saves the custom row headers to the Excel-compatible file. </TD></TR>
    <TD class=MemberNameCell>SaveCustomColumnHeaders</TD>
    <TD class=DescriptionCell>[4] Saves the custom column headers to the Excel-compatible file. </TD></TR>
    <TD class=MemberNameCell>SaveAsFiltered</TD>
    <TD class=DescriptionCell><img src="/emoticons/emotion-29.gif" alt="Music" /> Saves the results of rows after filtering them to the Excel-compatible file. </TD></TR>
    <TD class=MemberNameCell>SaveBothCustomRowAndColumnHeaders</TD>
    <TD class=DescriptionCell>[12] Saves both the custom row headers and the custom column headers to the Excel-compatible file. </TD></TR>
    <TD class=MemberNameCell>UseOOXMLFormat</TD>
    <TD class=DescriptionCell>[16] Saves the spreadsheet to an Excel 2007 (OfficeOpen XML) format. </TD></TR>
    <TD class=MemberNameCell>SaveAsViewed</TD>
    <TD class=DescriptionCell>[136] Saves the spreadsheet as viewed to the Excel-compatible file. </TD></TR>
    <TD class=MemberNameCell>DataOnly</TD>
    <TD class=DescriptionCell>[32] Saves only the data to the Excel-compatible file. </TD></TR>
    <TD class=MemberNameCell>SaveAlternatingRowStyles</TD>
    <TD class=DescriptionCell>[64] Saves the appearance settings of alternating rows to the Excel-compatible file. </TD></TR>
    <TD class=MemberNameCell>UseCustomPaletteForColorApproximations</TD>
    <TD class=DescriptionCell>[256] Use a custom color palette, and use it for color approximations if there are more than 56 colors set into Spread. </TD></TR>
    <TD class=MemberNameCell>UseDefaultColorPalette</TD>
    <TD class=DescriptionCell>[512] Use the default color palette. </TD></TR>
    <TD class=MemberNameCell>NoNotes</TD>
    <TD class=DescriptionCell>[1024] Saves the displayed data and formatting, but not the notes. </TD></TR>
    <TD class=MemberNameCell>DocumentCaching</TD>
    <TD class=DescriptionCell>[2048] Keeps Excel data that was previously loaded from the files (e.g. VBA, Macro). </TD></TR>
    <TD class=MemberNameCell>AutoRowHeight</TD>
    <TD class=DescriptionCell>[4096] The default row height is not saved. Excel will automatically determine row heights based on the largest font that is set in each row. </TD></TR>
    <TD class=MemberNameCell>ComboDataOnly</TD>
    <TD class=DescriptionCell>[8192] Save the displayed data in the combobox celltype, do not save the items in the combo box. </TD></TR></TABLE>

  • Replied 8 September 2017, 2:38 pm EST

    Thanks for your response.

    I believe the right choice for me is to use the flags: NoFormulas and SaveCustomColumnHeaders.

    But I don`t know how to use both flags together.

    Currently I am using:

    FP.SaveExcel(EXCEL_File_Path, FarPoint.Win.Spread.Model.IncludeHeaders.ColumnHeadersCustomOnly)

    I tried to use:

    Dim excelSaveFlags As ExcelSaveFlags    ' this line fails with the error: ExcelSaveFlags not defined...!!  

    What is the complete path for ExcelSaveFlags ???

    FP.SaveExcel(EXCEL_File_Path, FarPoint.Win.Spread.Model.IncludeHeaders.ColumnHeadersCustomOnly + NoFormulas ??????)   ' How to write this?

    Thanks in advance,


  • Replied 8 September 2017, 2:38 pm EST

    ExcelSaveFlags can be OR'd together like <FONT face="Courier New">FarPoint.Excel.ExcelSaveFlags.NoFormulas|FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders</FONT>

    <FONT face="Courier New">That is by the way the complete namespace path to the SaveExcelFlags FarPoint.Excel.ExcelSaveFlags</FONT>

  • Replied 8 September 2017, 2:38 pm EST

    Work fine.!!!

    FP.SaveExcel(MacroFilePath_EXCEL, FarPoint.Excel.ExcelSaveFlags.NoFormulas Or FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders)

    Thanks so much.




Need extra support?

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

Learn More

Forum Channels