Preventing Notes when exporting to Excel sheet

Posted by: ashis on 8 September 2017, 1:11 pm EST

  • Posted 8 September 2017, 1:11 pm EST

    Hi Bob


    We are adding notes for some rows to mark something in the spreadsheet.When we are exporting it to Excelsheet, the notes are also exported.Any way to prevent this ?


    Thanks


    Ashis

  • Replied 8 September 2017, 1:11 pm EST

    If you are using version 4 you can set the ExcelSaveFlag to DataOnly.
  • Replied 8 September 2017, 1:11 pm EST

    You could try using multiple flags.  This is the complete list:






























    NoFlagsSet[0] Saves the entire spreadsheet to the
    Excel-compatible file; no save flags are set
    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
    SaveAsFiltered[8 ] Saves the rows as they are filtered
    SaveBothCustomRowAndColumnHeaders[12] Saves both the custom row headers and the custom
    column headers to the Excel-compatible file
    UseOOXMLFormat[16] Saves using OfficeOpen XML format
    SaveAsViewed[136] Saves the spreadsheet as viewed
    DataOnly[32] Saves only the data
    SaveAlternatingRowStyles[64] Saves the styles of alternating rows

  • Replied 8 September 2017, 1:11 pm EST

    These flags cannot be combined given their values.  To do this they would need to be in the binary 0,1,2,4,8,16 etc sequence.


    I see that values 2 and 4 save the row and column headers, yet to have the two combined i would have expected a flag of value 6, and yours of value 12!  If one is to try to follow the binary sequence the SaveAsViewed flag appears to be using another flag whose value is 128 and whose meaning we do not know.


    The documentation clearly states on the "Cell Settings Exported" help page that cell notes are not exported to Excel format spreadsheets, and yet the program does with no apparent way of stopping it.  Previous versions of Spread did not export the cell notes.


     


     

  • Replied 8 September 2017, 1:11 pm EST

    This code worked for me:

     FpSpread1.Sheets(0).ColumnHeader.Cells(0, 0).Text = "test"
            FpSpread1.Sheets(0).Cells(0, 0).Note = "test"
            FpSpread1.Sheets(0).Cells(0, 1).Value = "abc"

     FpSpread1.SaveExcel("c:\test.xls", FarPoint.Excel.ExcelSaveFlags.DataOnly Or FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders)

    The documentation has been updated.  Thanks.

  • Replied 8 September 2017, 1:11 pm EST

    I suppose the real problem I have is not so much that the cell notes are exported, just that when you open the file in Excel regardless of the show notes selected option from the “Tools/ options” menu item, the notes are displayed “in your face”.  To no longer show the notes, or their indicator, in Excel you have to go into the Tools/ Options menu item and switch the comments off by selecting firstly to one of the comments display options, and hitting OK; and then return to the Options form and select the comments none option and hit OK again.


    I have attached a zip file containing two exported files.  The file names should be self explanatory.  If you open the file WithNotesAndFormat.xls you should see what I mean about being "in your face"


    In our application we store in the cell notes long strings defining the format and content of the cell.  This means that we do not have to rewrite our code to add new items in the reports; we just add a new item to the spreadsheet with the format and content information in the cell notes.  The report generator then goes through each cell, picks up the notes and uses that information to populate and format the sheet.


    I can, sort of, resolve the problem by using a local workbook, going through each sheet and removing the cell notes prior to saving it.  And because the workbook is local it doesn’t matter that the notes have been removed.


    2008/05/ExportedSheets.zip
  • Replied 8 September 2017, 1:11 pm EST

    You created normal notes (FpSpread1.Sheets(0).Cells(0, 0).Note = "test")?  If so, this looks like a bug that will be fixed in the next patch (just see the note color by default).
  • Replied 8 September 2017, 1:11 pm EST

    Yes Bob


    If I am changing ExcelSaveFlag to DataOnly, the column header is not coming in the excel sheet.


    I also need tha tcolumn header.


    Thanks

  • Replied 8 September 2017, 1:11 pm EST

    Try 'OR-ing' the SaveFlags to include the headers.
Need extra support?

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

Learn More

Forum Channels