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 ?



  • Replied 8 September 2017, 1:11 pm EST

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

    <table class="FilteredItemListTable"><tr>
    <td class="MemberNameCell">NoFlagsSet</td>
    <td class="DescriptionCell">[0] Saves the entire spreadsheet to the
    Excel-compatible file; no save flags are set </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">[8 ] Saves the rows as they are filtered </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 using OfficeOpen XML format </td></tr>
    <td class="MemberNameCell">SaveAsViewed</td>
    <td class="DescriptionCell">[136] Saves the spreadsheet as viewed </td></tr>
    <td class="MemberNameCell">DataOnly</td>
    <td class="DescriptionCell">[32] Saves only the data </td></tr>
    <td class="MemberNameCell">SaveAlternatingRowStyles</td>
    <td class="DescriptionCell">[64] Saves the styles of alternating rows </td></tr></table>
  • 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

    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.


  • Replied 8 September 2017, 1:11 pm EST

    Try 'OR-ing' the SaveFlags to include the headers.
  • 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 "<SPAN id=pagetitle>Cell Settings Exported" help page </SPAN>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

    <FONT size=2><FONT color=#000080><FONT face=Arial>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.</FONT></FONT></FONT>

    <FONT size=2><FONT color=#000080><FONT face=Arial>

    <FONT face=Arial color=navy size=2><SPAN>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"<o:p></o:p></SPAN></FONT>

    </FONT></FONT></FONT><FONT size=2><FONT color=#000080><FONT face=Arial><o:p>

    <FONT face=Arial color=navy size=2><SPAN>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.<o:p></o:p></SPAN></FONT>

    <FONT face=Arial color=navy size=2><SPAN>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.<o:p></o:p></SPAN></FONT>

  • 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).
Need extra support?

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

Learn More

Forum Channels