This is a migrated thread and some comments may be shown as answers.

biff excel export currency in excel file

4 Answers 49 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Prathibarani
Top achievements
Rank 1
Prathibarani asked on 27 Sep 2013, 03:23 AM
Hi
Hi,
We use Radgrid and use Bill Excel Export. One of the column data is currency. At design time,
we are declaring it like this:
  
<telerik:GridBoundColumn DataField="Amount" HeaderText="Amount" UniqueName="Amount" DataFormatString="{0:C2}" HeaderTooltip="Amount">
     <HeaderStyle Width="12%"></HeaderStyle>
     <ItemStyle HorizontalAlign="Right" />
</telerik:GridBoundColumn>
  
When I export, in excel this column shows up with green triangle in the corner, that means currency is
treated as string. I know that this is because of use of DataFormatString. The amount is showing fine
with $ sign and commas. I need this amount column to be treated as currency and not string. This make it
get rid of green corner in excel. Please let me know how to do it. I tried mso-number-format,
exportinfrastructure.cell.format etc. Nothing worked out.
  
Thanks,
Prathiba

4 Answers, 1 is accepted

Sort by
0
Prathibarani
Top achievements
Rank 1
answered on 27 Sep 2013, 04:01 AM
Al
Also, I need to keep $ sign and commas. Only get rid of green corners in excel.
0
Konstantin Dikov
Telerik team
answered on 01 Oct 2013, 04:22 PM
Hi Prathibarani,

Could you please try to replace your GridBoundColumn with the followin and see if it works for you:
<telerik:GridBoundColumn DataField="Amount" DataType="System.String" HeaderText="Amount" UniqueName="Amount" DataFormatString="{0:C2}" HeaderTooltip="Amount">
    <HeaderStyle Width="12%"></HeaderStyle>
    <ItemStyle HorizontalAlign="Right" />
</telerik:GridBoundColumn>

I have tested this on my side and the cells are shown as "Currency' in excel.

Hope that helps.

 

Regards,
Konstantin Dikov
Telerik
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to the blog feed now.
0
MBEN
Top achievements
Rank 1
Veteran
answered on 08 Feb 2017, 07:45 PM

I am having same issue and this solution doesn't seem to work for me.

I see C2 as a result when i try to sum the column. I am using the latest version of ASP.NET AJAX controls.

0
Konstantin Dikov
Telerik team
answered on 13 Feb 2017, 09:44 AM
Hi,

I have tested the following configuration and the currency symbols are exported correctly on my end:
<telerik:RadGrid RenderMode="Lightweight" ShowGroupPanel="true" AutoGenerateColumns="false" ID="RadGrid1"
        DataSourceID="SqlDataSource1" AllowFilteringByColumn="True" AllowSorting="True"
        ShowFooter="True" runat="server" GridLines="None" AllowPaging="true" EnableLinqExpressions="false">
        <PagerStyle Mode="NextPrevAndNumeric" EnableAllOptionInPagerComboBox="true"></PagerStyle>
           <ExportSettings HideStructureColumns="true">
              <Excel Format="Biff" />
           </ExportSettings>
        <MasterTableView ShowGroupFooter="true" AllowMultiColumnSorting="true" CommandItemSettings-ShowExportToExcelButton="true" CommandItemSettings-ShowExportToPdfButton="true" CommandItemDisplay="Top">
            <Columns>
                <telerik:GridBoundColumn Aggregate="Count" DataField="ProductID" HeaderText="Product ID"
                    FooterText="Total products: ">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn DataField="ProductName" HeaderText="Product name" SortExpression="ProductName"
                    UniqueName="ProductName">
                </telerik:GridBoundColumn>
                <telerik:GridBoundColumn Aggregate="Sum" DataField="UnitPrice" HeaderText="Unit price" DataType="System.String"  DataFormatString="{0:C2}"
                    FooterText="Total price: ">
                </telerik:GridBoundColumn>
                <telerik:GridTemplateColumn DataField="UnitsInStock" HeaderText="Units in stock"
                    FooterText="All units in stock: " UniqueName="UnitsInStock" Aggregate="Sum"
                    GroupByExpression="UnitsInStock Group By UnitsInStock">
                    <ItemTemplate>
                        <asp:Label ID="lblUnitsInStock" runat="server" Text='<%#Eval("UnitsInStock") %>'>
                        </asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <telerik:RadNumericTextBox RenderMode="Lightweight" ID="txtUnitsInStock" runat="server" text='<%#Bind("UnitsInStock") %>'>
                        </telerik:RadNumericTextBox>
                    </EditItemTemplate>
                </telerik:GridTemplateColumn>
                <telerik:GridCalculatedColumn HeaderText="Total Price" UniqueName="TotalPrice" DataType="System.Double"
                    DataFields="UnitPrice, UnitsInStock" Expression="{0}*{1}" FooterText="Total : "
                    Aggregate="Sum">
                </telerik:GridCalculatedColumn>
            </Columns>
        </MasterTableView>
        <ClientSettings AllowDragToGroup="true">
        </ClientSettings>
         
    </telerik:RadGrid>
<asp:SqlDataSource ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
    SelectCommand="SELECT * FROM Products Where UnitPrice > 9.50" runat="server"></asp:SqlDataSource>

Can you please elaborate if there are any differences in regards of the export configuration in your project?


Regards,
Konstantin Dikov
Telerik by Progress
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Tags
Grid
Asked by
Prathibarani
Top achievements
Rank 1
Answers by
Prathibarani
Top achievements
Rank 1
Konstantin Dikov
Telerik team
MBEN
Top achievements
Rank 1
Veteran
Share this question
or