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.<br><br>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. <br><br>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.<br><br>Two questions:<br><br>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 ?<br>2) There a way to export just "part of the sheet" ?, for example: to export the rows X to Y ?<br><br>Thanks in advance,<br><br>Luigggye<br><br>
    2011/05/Sample.JPG
  • Replied 8 September 2017, 2:38 pm EST

    <P>Luigggye,</P>
    <P>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.</P>
    <P>
    <TABLE class=FilteredItemListTable>

    <TR>
    <TH>Member</TH>
    <TH>Description</TH></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>NoFlagsSet</STRONG></TD>
    <TD class=DescriptionCell>[0] Saves the spreadsheet to the Excel-compatible file with no special options. </TD></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>NoFormulas</STRONG></TD>
    <TD class=DescriptionCell>[1] Saves the displayed data but not the formulas to the Excel-compatible file. </TD></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>SaveCustomRowHeaders</STRONG></TD>
    <TD class=DescriptionCell>[2] Saves the custom row headers to the Excel-compatible file. </TD></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>SaveCustomColumnHeaders</STRONG></TD>
    <TD class=DescriptionCell>[4] Saves the custom column headers to the Excel-compatible file. </TD></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>SaveAsFiltered</STRONG></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>
    <TR>
    <TD class=MemberNameCell><STRONG>SaveBothCustomRowAndColumnHeaders</STRONG></TD>
    <TD class=DescriptionCell>[12] Saves both the custom row headers and the custom column headers to the Excel-compatible file. </TD></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>UseOOXMLFormat</STRONG></TD>
    <TD class=DescriptionCell>[16] Saves the spreadsheet to an Excel 2007 (OfficeOpen XML) format. </TD></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>SaveAsViewed</STRONG></TD>
    <TD class=DescriptionCell>[136] Saves the spreadsheet as viewed to the Excel-compatible file. </TD></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>DataOnly</STRONG></TD>
    <TD class=DescriptionCell>[32] Saves only the data to the Excel-compatible file. </TD></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>SaveAlternatingRowStyles</STRONG></TD>
    <TD class=DescriptionCell>[64] Saves the appearance settings of alternating rows to the Excel-compatible file. </TD></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>UseCustomPaletteForColorApproximations</STRONG></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>
    <TR>
    <TD class=MemberNameCell><STRONG>UseDefaultColorPalette</STRONG></TD>
    <TD class=DescriptionCell>[512] Use the default color palette. </TD></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>NoNotes</STRONG></TD>
    <TD class=DescriptionCell>[1024] Saves the displayed data and formatting, but not the notes. </TD></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>DocumentCaching</STRONG></TD>
    <TD class=DescriptionCell>[2048] Keeps Excel data that was previously loaded from the files (e.g. VBA, Macro). </TD></TR>
    <TR>
    <TD class=MemberNameCell><STRONG>AutoRowHeight</STRONG></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>
    <TR>
    <TD class=MemberNameCell><STRONG>ComboDataOnly</STRONG></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></P>
  • Replied 8 September 2017, 2:38 pm EST

    Thanks for your response.<br><br>I believe the right choice for me is to use the flags: NoFormulas and SaveCustomColumnHeaders.<br><br>But I don`t know how to use both flags together.<br><br>Currently I am using:<br><br>FP.SaveExcel(EXCEL_File_Path, FarPoint.Win.Spread.Model.IncludeHeaders.ColumnHeadersCustomOnly)<br><br>I tried to use:<br><br>Dim excelSaveFlags As ExcelSaveFlags    ' this line fails with the error: ExcelSaveFlags not defined...!!  <br><br>What is the complete path for ExcelSaveFlags ??? Farpoint.win.?????????<br><br>FP.SaveExcel(EXCEL_File_Path, FarPoint.Win.Spread.Model.IncludeHeaders.ColumnHeadersCustomOnly + NoFormulas ??????)   ' How to write this?<br><br><br>Thanks in advance,<br><br><p>Luigggye <br></p>
  • Replied 8 September 2017, 2:38 pm EST

    <P>ExcelSaveFlags can be OR'd together like <FONT face="Courier New">FarPoint.Excel.ExcelSaveFlags.NoFormulas|FarPoint.Excel.ExcelSaveFlags.<STRONG>SaveCustomColumnHeaders</STRONG></FONT></P>
    <P><FONT face="Courier New"><STRONG>That is by the way the complete namespace path to the SaveExcelFlags </STRONG>FarPoint.Excel.ExcelSaveFlags</FONT></P>
  • Replied 8 September 2017, 2:38 pm EST

    <p>Work fine.!!!</p><p>FP.SaveExcel(MacroFilePath_EXCEL, FarPoint.Excel.ExcelSaveFlags.NoFormulas Or FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders)<br></p><p>Thanks so much.</p><p>Luigggye <br></p><p> </p><p> </p>
Need extra support?

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

Learn More

Forum Channels