biff excel export currency in excel file

5 posts, 0 answers
  1. Prathibarani
    Prathibarani avatar
    55 posts
    Member since:
    Mar 2013

    Posted 26 Sep 2013 Link to this post

    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
  2.
  3. Prathibarani
    Prathibarani avatar
    55 posts
    Member since:
    Mar 2013

    Posted 26 Sep 2013 Link to this post

    Al
    Also, I need to keep $ sign and commas. Only get rid of green corners in excel.
  5. Konstantin Dikov
    Admin
    Konstantin Dikov avatar
    2466 posts

    Posted 01 Oct 2013 Link to this post

    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.
  7. MBEN
    MBEN avatar
    203 posts
    Member since:
    Nov 2011

    Posted 08 Feb 2017 Link to this post

    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.

  8.
  9. Konstantin Dikov
    Admin
    Konstantin Dikov avatar
    2466 posts

    Posted 13 Feb 2017 Link to this post

    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.
Back to Top