Problem exporting to excel - thousandseparator and percentages
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!
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
Thanks.
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>