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,

    Luigggye


    2011/05/Sample.JPG
  • Replied 8 September 2017, 2:38 pm EST

    Luigggye,


    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.























































    MemberDescription
    NoFlagsSet[0] Saves the spreadsheet to the Excel-compatible file with no special options.
    NoFormulas[1] Saves the displayed data but not the formulas to the Excel-compatible file.
    SaveCustomRowHeaders[2] Saves the custom row headers to the Excel-compatible file.
    SaveCustomColumnHeaders[4] Saves the custom column headers to the Excel-compatible file.
    SaveAsFilteredMusic Saves the results of rows after filtering them to the Excel-compatible file.
    SaveBothCustomRowAndColumnHeaders[12] Saves both the custom row headers and the custom column headers to the Excel-compatible file.
    UseOOXMLFormat[16] Saves the spreadsheet to an Excel 2007 (OfficeOpen XML) format.
    SaveAsViewed[136] Saves the spreadsheet as viewed to the Excel-compatible file.
    DataOnly[32] Saves only the data to the Excel-compatible file.
    SaveAlternatingRowStyles[64] Saves the appearance settings of alternating rows to the Excel-compatible file.
    UseCustomPaletteForColorApproximations[256] Use a custom color palette, and use it for color approximations if there are more than 56 colors set into Spread.
    UseDefaultColorPalette[512] Use the default color palette.
    NoNotes[1024] Saves the displayed data and formatting, but not the notes.
    DocumentCaching[2048] Keeps Excel data that was previously loaded from the files (e.g. VBA, Macro).
    AutoRowHeight[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.
    ComboDataOnly[8192] Save the displayed data in the combobox celltype, do not save the items in the combo box.

  • 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 ??? Farpoint.win.?????????

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


    Thanks in advance,

    Luigggye

  • Replied 8 September 2017, 2:38 pm EST

    ExcelSaveFlags can be OR'd together like FarPoint.Excel.ExcelSaveFlags.NoFormulas|FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders


    That is by the way the complete namespace path to the SaveExcelFlags FarPoint.Excel.ExcelSaveFlags

  • 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.

    Luigggye

     

     

Need extra support?

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

Learn More

Forum Channels