Problem exporting to excel - thousandseparator and percentages

AndvariAndvari
edited 12:53AM in FastReport 4.0
Hi,

I am trying to export a report to excel, but whenever I do the export, excel does not use the formatting I have in my report

1. Thousand separator
My numbers are formated with %2.2n and I am using the char ' . ' as the thousand separator. On my printed report, everything works fine.
But when I export to excel, the thousands separator is ignored

2. Percentages with 1 decimal
I have a few percentages that I want to show only 1 decimal point. I am using the format #.#% and in my printed report,everything works fine.
But when I export to excel, I always get 2 decimal points.

Is there anything else I need to do to force excel to use the formating that I am using with fast reports and not it's own default formatting?
What is the best way to garantee that the data appears correctly in excel?

I have been banging my head against the wall and can't find the solution for this.
Any help would be apreciated.
Thanks!

Comments

  • AndvariAndvari
    edited 12:53AM
    So... any ideas? I'm still stuck on this one :|

    Thanks.
  • DraedenDraeden
    edited 12:53AM
    Hello,

    Could you attach an example fp3 report in order I can check the issue and fix it?
  • AndvariAndvari
    edited April 2011
    Draeden wrote: »
    Hello,

    Could you attach an example fp3 report in order I can check the issue and fix it?


    Hi, I am sending the fp3 file.
    In this specific case, you can find the errors I reported:

    1. The report shows the thousand separator correctly, but when exported to excel it does not show the separator
    2. The percentages have 1 decimal value, but when exported excel shows 2 decimal points

    For some reason I can't seem to upload the file, so I'm pasting the content of the fp3 file here.

    Thank you for the help.

    <?xml version="1.0" encoding="utf-8" standalone="no"?><preparedreport><previewpages><page0><b2 t="0"><m3 u="percentage"/><m8 u="Page 1 van 1"/><m10 u="ATC"/><m11 u="description"/><m12 u="period 1 ??????¬"/><m13 u="period 2 ??????¬"/><m18 u=""/></b2><TfrxNullBand Height="1046,92981" Left="0" Top="0" Width="718,1107" l="0" t="0"/><b3 t="110"><m19 Frame.Typ="3" u="-100% " DisplayFormat.FormatStr="#0.#%"/><m20 Frame.Typ="3" u="ANTIBACTERIELE MIDDELEN VOOR SYSTEMISCH GEBRUIK"/><m21 u="J01"/><m22 Frame.Typ="3" u="504,24"/><m23 Frame.Typ="3" u="0,00"/></b3><b3 t="126"><m19 Frame.Typ="3" u="-100% " DisplayFormat.FormatStr="#0.#%"/><m20 Frame.Typ="3" u="EMOLLIENTIA EN PROTECTIVA"/><m21 u="D02"/><m22 Frame.Typ="3" u="41,82"/><m23 Frame.Typ="3" u="0,00"/></b3><b3 t="142"><m19 Frame.Typ="3" u="-100% " DisplayFormat.FormatStr="#0.#%"/><m20 Frame.Typ="3" u="ANTI-INFLAMMATOIRE EN ANTIREUMATISCHE MIDDELEN"/><m21 u="M01"/><m22 Frame.Typ="3" u="10,70"/><m23 Frame.Typ="3" u="0,00"/></b3><b3 t="158"><m19 Frame.Typ="3" u="166,7% " DisplayFormat.FormatStr="#0.#%"/><m20 Frame.Typ="3" u="HYPOFYSE- EN HYPOTHALAMUSHORMONEN EN VERWANTE VERBINDINGEN"/><m21 u="H01"/><m22 Frame.Typ="3" u="8,11"/><m23 Frame.Typ="3" u="21,63"/></b3><b3 t="174"><m19 Frame.Typ="3" u="0% " DisplayFormat.FormatStr="#0.#%"/><m20 Frame.Typ="3" u="ANTIHISTAMINICA VOOR SYSTEMISCH GEBRUIK"/><m21 u="R06"/><m22 Frame.Typ="3" u="-3,44"/><m23 Frame.Typ="3" u="0,00"/></b3><b3 t="190"><m19 Frame.Typ="3" u="0% " DisplayFormat.FormatStr="#0.#%"/><m20 Frame.Typ="3" u="PSYCHOLEPTICA"/><m21 u="N05"/><m22 Frame.Typ="3" u="-32,19"/><m23 Frame.Typ="3" u="0,00"/></b3><b3 t="206"><m19 Frame.Typ="3" u="0% " DisplayFormat.FormatStr="#0.#%"/><m20 Frame.Typ="3" u="ANALGETICA"/><m21 u="N02"/><m22 Frame.Typ="3" u="-3.429,58"/><m23 Frame.Typ="3" u="177,62"/></b3><b3 t="222"><m19 Frame.Typ="3" u=" " DisplayFormat.FormatStr="#"/><m20 Frame.Typ="3" u=" "/><m21 u=" "/><m22 Frame.Typ="3" u="0,00" DisplayFormat.FormatStr="#0.00"/><m23 Frame.Typ="3" u="0,00" DisplayFormat.FormatStr="#0.00"/></b3><b3 t="238"><m19 Frame.Typ="11" Highlight.Active="1" u="0% " DisplayFormat.FormatStr="#0.#%"/><m20 Frame.Typ="11" Highlight.Active="1" u="Totaal"/><m21 Frame.Typ="11" u=" "/><m22 Frame.Typ="11" Highlight.Active="1" u="-2.900,34"/><m23 Frame.Typ="11" Highlight.Active="1" u="199,25"/></b3><b1 t="1010,92981"><m1 u="01-04-2011"/><m2 u=""/></b1></page0></previewpages><logicalpagenumbers><page n="1" t="1"/></logicalpagenumbers><outline/><anchors/><report><TfrxReport DotMatrixReport="0" PreviewOptions.OutlineVisible="0" PreviewOptions.OutlineWidth="120" ReportOptions.Name=""/></report><sourcepages><TfrxReportPage PaperWidth="210" PaperHeight="297" PaperSize="9" LeftMargin="10" RightMargin="10" TopMargin="10" BottomMargin="10" ColumnWidth="0" ColumnPositions.Text="" HGuides.Text="" VGuides.Text=""><TfrxPageFooter Name="PageFooter1" Height="36" Left="0" Top="264,5671" Width="718,1107"><TfrxMemoView Name="Date" Left="0" Top="18" Width="718" Height="18" ShowHint="False" DisplayFormat.FormatStr="dd-mm-yyyy" DisplayFormat.Kind="fkDateTime" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="4" HAlign="haRight" ParentFont="False" Text="[Date]"/><TfrxMemoView Name="Memo9" Left="0" Top="0" Width="718" Height="18" ShowHint="False" DisplayFormat.FormatStr="dd-mm-yyyy" DisplayFormat.Kind="fkDateTime" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" HAlign="haRight" ParentFont="False" Text=""/></TfrxPageFooter><TfrxPageHeader Name="PageHeader1" Height="110" Left="0" Top="18,89765" Width="718,1107"><TfrxMemoView Name="Memo7" Left="632" Top="92" Width="86" Height="18" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="14" GapY="2" HAlign="haRight" ParentFont="False" Text="percentage"/><TfrxMemoView Name="p_report_title" Left="0" Top="0" Width="718" Height="28" Visible="False" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-16" Font.Name="Arial" Font.Style="1" GapX="1" HAlign="haCenter" ParentFont="False" VAlign="vaCenter" Text="[p_report_title]"/><TfrxMemoView Name="p_filter1" Left="0" Top="28" Width="542" Height="16" Visible="False" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" ParentFont="False" Text="[p_location]"/><TfrxMemoView Name="p_filter2" Left="0" Top="44" Width="542" Height="16" Visible="False" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" ParentFont="False" Text="[p_period_label] [p_period]"/><TfrxMemoView Name="p_filter3" Left="0" Top="60" Width="542" Height="16" Visible="False" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" ParentFont="False" Text="[p_period_label2] [p_period2]"/><TfrxMemoView Name="Memo1" Left="542" Top="28" Width="176" Height="16" ShowHint="False" ExpressionDelimiters="<,>" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" HAlign="haRight" ParentFont="False" Text="<p_page_label> <Page> <p_page_of_label> <TotalPages>"/><TfrxMemoView Name="Memo3" Left="542" Top="44" Width="176" Height="16" Visible="False" ShowHint="False" ExpressionDelimiters="<,>" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" HAlign="haRight" ParentFont="False" Text="<p_username>"/><TfrxMemoView Name="Memo2" Left="0" Top="92" Width="40" Height="18" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="15" GapY="2" ParentFont="False" Text="ATC"/><TfrxMemoView Name="Memo4" Left="40" Top="92" Width="412" Height="18" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="14" GapY="2" ParentFont="False" Text="description"/><TfrxMemoView Name="Memo5" Left="452" Top="92" Width="90" Height="18" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="14" GapY="2" HAlign="haRight" ParentFont="False" Text="period 1 ??????¬"/><TfrxMemoView Name="Memo6" Left="542" Top="92" Width="90" Height="18" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="14" GapY="2" HAlign="haRight" ParentFont="False" Text="period 2 ??????¬"/><TfrxMemoView Name="dummy_frame_left_right_bottom" Left="544" Top="0" Width="172" Height="18" Visible="False" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="11" GapY="2" ParentFont="False" Text="frame dummy"/><TfrxMemoView Name="dummy_frame_left_right" Left="544" Top="0" Width="172" Height="18" Visible="False" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="3" GapY="2" ParentFont="False" Text="frame dummy - left + right"/><TfrxMemoView Name="dummy_frame_right" Left="544" Top="0" Width="172" Height="18" Visible="False" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="3" GapY="2" ParentFont="False" Text="frame dummy - right"/><TfrxMemoView Name="dummy_frame_right_bottom" Left="544" Top="0" Width="172" Height="18" Visible="False" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="11" GapY="2" ParentFont="False" Text="frame dummy - right"/><TfrxMemoView Name="Memo8" Left="542" Top="60" Width="176" Height="16" ShowHint="False" ExpressionDelimiters="<,>" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" HAlign="haRight" ParentFont="False" Text=""/></TfrxPageHeader><TfrxMasterData Name="MasterData1" Height="16" Left="0" Top="188,9765" Width="718,1107" ColumnWidth="0" ColumnGap="0" DataSetName="frxDBATCInkoop" KeepFooter="True" KeepHeader="True" KeepTogether="True" RowCount="0" Stretched="True"><TfrxMemoView Name="frxDBATCInkoopa_perc" Left="632" Top="0" Width="86" Height="16" OnBeforePrint="frxDBATCInkoopa_percOnBeforePrint" ShowHint="False" DisplayFormat.DecimalSeparator="," DisplayFormat.ThousandSeparator="." DisplayFormat.FormatStr="#,#" DisplayFormat.Kind="fkNumeric" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="2" HAlign="haRight" Highlight.Font.Charset="1" Highlight.Font.Color="-1" Highlight.Font.Height="-11" Highlight.Font.Name="Arial" Highlight.Font.Style="1" Highlight.Condition="<frxDBATCInkoop."a_rec_type"> > 2" ParentFont="False" Text="[frxDBATCInkoop."a_perc"] "/><TfrxMemoView Name="frxDBATCInkoopa_cat_descr" Left="40" Top="0" Width="412" Height="16" OnBeforePrint="frxDBATCInkoopa_cat_descrOnBeforePrint" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="2" Highlight.Font.Charset="1" Highlight.Font.Color="0" Highlight.Font.Height="-11" Highlight.Font.Name="Arial" Highlight.Font.Style="1" Highlight.Condition="<frxDBATCInkoop."a_rec_type"> > 2" ParentFont="False" Text="[frxDBATCInkoop."a_cat_descr"]"/><TfrxMemoView Name="frxDBATCInkoopa_cat_pk" Left="0" Top="0" Width="40" Height="16" OnBeforePrint="frxDBATCInkoopa_cat_pkOnBeforePrint" ShowHint="False" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="3" ParentFont="False" Text="[frxDBATCInkoop."a_cat_pk"]"/><TfrxMemoView Name="frxDBATCInkoopcost1" Left="452" Top="0" Width="90" Height="16" OnBeforePrint="frxDBATCInkoopcost1OnBeforePrint" ShowHint="False" DisplayFormat.DecimalSeparator="," DisplayFormat.ThousandSeparator="." DisplayFormat.FormatStr="%2.2n" DisplayFormat.Kind="fkNumeric" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="2" HAlign="haRight" Highlight.Font.Charset="1" Highlight.Font.Color="0" Highlight.Font.Height="-11" Highlight.Font.Name="Arial" Highlight.Font.Style="1" Highlight.Condition="<frxDBATCInkoop."a_rec_type"> > 2" ParentFont="False" Text="[frxDBATCInkoop."cost1"]"/><TfrxMemoView Name="frxDBATCInkoopcost2" Left="542" Top="0" Width="90" Height="16" OnBeforePrint="frxDBATCInkoopcost2OnBeforePrint" ShowHint="False" DisplayFormat.DecimalSeparator="," DisplayFormat.ThousandSeparator="." DisplayFormat.FormatStr="%2.2n" DisplayFormat.Kind="fkNumeric" Font.Charset="1" Font.Color="0" Font.Height="-11" Font.Name="Arial" Font.Style="0" Frame.Typ="2" HAlign="haRight" Highlight.Font.Charset="1" Highlight.Font.Color="0" Highlight.Font.Height="-11" Highlight.Font.Name="Arial" Highlight.Font.Style="1" Highlight.Condition="<frxDBATCInkoop."a_rec_type"> > 2" ParentFont="False" Text="[frxDBATCInkoop."cost2"]"/></TfrxMasterData></TfrxReportPage></sourcepages><dictionary><b1 name="Page0.PageFooter1"/><b2 name="Page0.PageHeader1"/><b3 name="Page0.MasterData1"/><m1 name="Page0.Date"/><m10 name="Page0.Memo2"/><m11 name="Page0.Memo4"/><m12 name="Page0.Memo5"/><m13 name="Page0.Memo6"/><m14 name="Page0.dummy_frame_left_right_bottom"/><m15 name="Page0.dummy_frame_left_right"/><m16 name="Page0.dummy_frame_right"/><m17 name="Page0.dummy_frame_right_bottom"/><m18 name="Page0.Memo8"/><m19 name="Page0.frxDBATCInkoopa_perc"/><m2 name="Page0.Memo9"/><m20 name="Page0.frxDBATCInkoopa_cat_descr"/><m21 name="Page0.frxDBATCInkoopa_cat_pk"/><m22 name="Page0.frxDBATCInkoopcost1"/><m23 name="Page0.frxDBATCInkoopcost2"/><m3 name="Page0.Memo7"/><m4 name="Page0.p_report_title"/><m5 name="Page0.p_filter1"/><m6 name="Page0.p_filter2"/><m7 name="Page0.p_filter3"/><m8 name="Page0.Memo1"/><m9 name="Page0.Memo3"/></dictionary><picturecache/></preparedreport>
  • DraedenDraeden
    edited 12:53AM
    Sorry, but I cannot open this file. Please send this bugreport to support[at]fast-report[dot]com (with the fp3 file) and after it's redirected to me, I'll be able to consider it.

Leave a Comment

Rich Text Editor. To edit a paragraph's style, hit tab to get to the paragraph menu. From there you will be able to pick one style. Nothing defaults to paragraph. An inline formatting menu will show up when you select text. Hit tab to get into that menu. Some elements, such as rich link embeds, images, loading indicators, and error messages may get inserted into the editor. You may navigate to these using the arrow keys inside of the editor and delete them with the delete or backspace key.

Home • FastReport 4.0Comment As ...

© Vanilla Keystone Theme 2024