issues with excel export in numeric display

[MBEN]

I have an excel export on my grid in which i export some balances.

Some of the balances are returned in big negative numbers from the database. I need to round them up so they don't show up as big negative numbers in excel.

They show fine in the grid. My column declarations are like:

<telerik:GridBoundColumn Aggregate="Sum" DataField="TransferDollars" HeaderText="Transfers" DataFormatString="{0:c2}"
                            UniqueName="Transfers" SortExpression="TransferDollars" AllowFiltering="false" HeaderStyle-Width="9%"
                            FooterStyle-HorizontalAlign="Right">

 </telerik:GridBoundColumn>

 

I change them to a currency format in my InfrastructureReporting event, how can i round them up before export.

protected virtual void grid_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
        {
            foreach (xls.Row row in e.ExportStructure.Tables[0].Rows)
            {
                row.Cells[2, row.Index].Format = "$#,##0.00;($#,##0.00)";
                row.Cells[3, row.Index].Format = "$#,##0.00;($#,##0.00)";
                row.Cells[4, row.Index].Format = "$#,##0.00;($#,##0.00)";
                row.Cells[5, row.Index].Format = "$#,##0.00;($#,##0.00)";
                row.Cells[6, row.Index].Format = "$#,##0.00;($#,##0.00)";
                row.Cells[7, row.Index].Format = "$#,##0.00;($#,##0.00)";
                row.Cells[8, row.Index].Format = "$#,##0.00;($#,##0.00)";
                row.Cells[9, row.Index].Format = "$#,##0.00;($#,##0.00)";
            }
        }

 

How can I do this?

[Marin Bratanov]

Hello,

You can access the original data item and change the text of the cell in the PreRender event of the grid, if you add the data source field in the DataKeyNames collection. For example (I have highlighted my additions):

<telerik:RadGrid runat="server" ID="rg1" OnNeedDataSource="rg1_NeedDataSource" OnInfrastructureExporting="grid_InfrastructureExporting" OnPreRender="rg1_PreRender" AutoGenerateColumns="false">
    <ExportSettings Excel-Format="Biff"></ExportSettings>
    <MasterTableView CommandItemDisplay="Top" DataKeyNames="TransferDollars">
    <CommandItemSettings ShowExportToExcelButton="true" />
        <Columns>
            <telerik:GridBoundColumn Aggregate="Sum" DataField="TransferDollars" HeaderText="Transfers" DataFormatString="{0:c2}"
                                     UniqueName="Transfers" SortExpression="TransferDollars" AllowFiltering="false" HeaderStyle-Width="9%"
                                     FooterStyle-HorizontalAlign="Right">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn Aggregate="Sum" DataField="TransferDollars" HeaderText="Transfers" DataFormatString="{0:c2}"
                                     UniqueName="Transfers1" SortExpression="TransferDollars" AllowFiltering="false" HeaderStyle-Width="9%"
                                     FooterStyle-HorizontalAlign="Right">
            </telerik:GridBoundColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

protected virtual void grid_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{
    foreach (var row in e.ExportStructure.Tables[0].Rows)
    {
        row.Cells[1, row.Index].Format = "$#,##0.00;($#,##0.00)";
        row.Cells[2, row.Index].Format = "$#,##0.00;($#,##0.00)";
        //row.Cells[3, row.Index].Format = "$#,##0.00;($#,##0.00)";
        //row.Cells[4, row.Index].Format = "$#,##0.00;($#,##0.00)";
        //row.Cells[5, row.Index].Format = "$#,##0.00;($#,##0.00)";
        //row.Cells[6, row.Index].Format = "$#,##0.00;($#,##0.00)";
        //row.Cells[7, row.Index].Format = "$#,##0.00;($#,##0.00)";
        //row.Cells[8, row.Index].Format = "$#,##0.00;($#,##0.00)";
        //row.Cells[9, row.Index].Format = "$#,##0.00;($#,##0.00)";
    }
}

protected void rg1_PreRender(object sender, EventArgs e)
{
    RadGrid grid = sender as RadGrid;
    if (grid.IsExporting)
    {
        foreach (GridDataItem item in grid.Items)//loop the rows
        {
            foreach (GridBoundColumn column in grid.MasterTableView.RenderColumns.OfType<GridBoundColumn>())//loop bound columns
            {
                if (column.DataFormatString != string.Empty && grid.MasterTableView.DataKeyNames.Contains(column.DataField))//check if there is special format string needed and whether the field is available so we can obtain its original data in the body of the loop
                {
                    item[column.UniqueName].Text = Math.Round(decimal.Parse(item.GetDataKeyValue("TransferDollars").ToString())).ToString(); //set the desired text according to your business logic, note the item.GetDataKeyValue("TransferDollars").ToString() approach to get the original data
                }
            }
        }
    }
}

protected DataTable GetGridData()
{
    DataTable tbl = new DataTable();
    tbl.Columns.Add(new DataColumn("TransferDollars", typeof(decimal)));
    tbl.Columns.Add(new DataColumn("Affiliate_Name", typeof(string)));
    tbl.Columns.Add(new DataColumn("TR_Affiliate_Id", typeof(int)));
    tbl.Columns.Add(new DataColumn("someColumn", typeof(string)));
    tbl.Rows.Add(new object[] { 123456789.123456789, "one", 2, "5" });
    tbl.Rows.Add(new object[] { 123456789.567891234, "two", 3, null/*SIMULATE EMPTY VALUE*/ });
    tbl.Rows.Add(new object[] { -123456789.123456789, "three", 4, "5" });
    tbl.Rows.Add(new object[] { -123456789.567891234, "four", 5, "5" });

    return tbl;
}

protected void rg1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
{
    rg1.DataSource = GetGridData();
}

Regards,

Marin Bratanov
Telerik by Progress

[MBEN]

Is IsExporting a grid property? My code does not recognize 'if (grid.IsExporting)'

 

[Marin Bratanov]

Hi,

The IsExporting property is available since Q3 2015. If it is not recognized, then the version you have is older and you would need to upgrade to a newer (at the moment, I advise you use the R3 2016 SP2 release, until R1 2017 SP2 and R2 2017 become available).

Regards,

Marin Bratanov
Telerik by Progress

[MBEN]

Thanks, upgrading the control worked.

I am exporting the footers as well, how can i round the footer total?

[Marin Bratanov]

Hi,

You can use the GetItems method of the MasterTableView in the PreRender event:

protected void rg1_PreRender(object sender, EventArgs e)
{
    RadGrid grid = sender as RadGrid;
    if (grid.IsExporting)
    {
        foreach (GridFooterItem item in grid.MasterTableView.GetItems(GridItemType.Footer))
        {
            item["Transfers1"].Text= "my custom text";
        }
    }
}

Regards,

Marin Bratanov
Telerik by Progress