biff excel export currency in excel file

[Prathibarani]

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

[Prathibarani]

Al

Also, I need to keep $ sign and commas. Only get rid of green corners in excel.

[Konstantin Dikov]

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

[MBEN]

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.

[Konstantin Dikov]

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