Negative Currency format in Export to excel format issue.

[Phillip]

I am trying to get .xlsx export to not give this warning shown below. 

I am using a RadGrid that AutoGenerates columns. The RadGrid's culture is en-US. Need Data source gets a DataTable with one column and one row, with the value of -4.50. On ColumnCreated it makes the GridNumericColumn Currency type. The Grid displays the format properly. It also puts the value into excel as though it looks correct.

**The problem I currently see is that it is putting a line break after the value in excel. Not sure why this is happening with the export?

Example of code:

(.aspx file)

<div class="form-row mt-4">
            <div class="col-md">
                <div class="form-inline float-md-right">
                    <telerik:radajaxpanel runat="server" postbackcontrols="btnExportTest">
                        <asp:Button ID="btnExportTest" OnClick="btnExportTest_Click" runat="server" Text="Export" />
                    </telerik:radajaxpanel>
                </div>
            </div>
        </div>
        <div class="form-row mt-4">
            <div class="col-md">
                <telerik:RadGrid ID="Grid1" RenderMode="Lightweight" Skin="Bootstrap" runat="server" OnNeedDataSource="Grid1_NeedDataSource"
                    AllowFilteringByColumn="false" AllowPaging="false" AutoGenerateColumns="true" OnColumnCreated="Grid1_ColumnCreated">
                </telerik:RadGrid>
            </div>
        </div>

(.cs)

protected void Grid1_NeedDataSource(object sender, GridNeedDataSourceEventArgs e)
        {
            DataTable table = new DataTable();
            table.Columns.Add("currency", typeof(double));
            table.Rows.Add("-4.50");
            Grid1.DataSource = table;
        }

        protected void btnExportTest_Click(object sender, EventArgs e)
        {
            Grid1.ExportSettings.IgnorePaging = true;
            Grid1.ExportSettings.FileName = "test";
            Grid1.ExportSettings.ExportOnlyData = true;
            Grid1.ExportSettings.OpenInNewWindow = true;
            Grid1.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx;
            Grid1.MasterTableView.ExportToExcel();
        }

        protected void Grid1_ColumnCreated(object sender, GridColumnCreatedEventArgs e)
        {
            if (e.Column is GridNumericColumn)
            {
                ((GridNumericColumn)e.Column).NumericType = NumericType.Currency;
            }
        }

I verified my windows settings are set up to show negative Currency with ($0.00).

This seems like it is just a problem with exporting from Telerik? Is there something that can make negative currencies export?

Currently using Q2 2021 Telerik version

[Attila Antal]

Hi Philip,

The reason behind the issue is that the Grid formats the int/double/decimal value as displayed in the Web Page, and when exporting this Formatted String value is sent to the Exported document. The built-in export functionality does not handle all sorts of formatting, but we have created events and APIs that you can use to format the output as needed.

When calling the ExportToExcel() function of the Grid and the Excel-Format is set to either Biff or Xlsx, the Grid will raise the OnInfrastructureExporting event. In that event, you will have access to the structure and you can apply additional formatting. Check out the Export Infrastructure article for more information.

Here is one example:

protected void Grid1_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{
    var grid = (RadGrid)sender;
    // Get the Table from the generated Structure
    var structureTable = e.ExportStructure.Tables[0];
    // Access the Currency column in the Grid
    var currencyColumn = grid.MasterTableView.GetColumn("Currency") as GridNumericColumn;
    // Get the Column's order index
    var colIndex = currencyColumn.OrderIndex;
    // Substract the GridExpandColumn and GridRowIndicatorColumn from this index
    colIndex -= 2;
    // Increase the column index by 1 as the Structure Table Cell indexing starts with 1 instead of 0
    colIndex += 1; 
    // Create a loop for every Row in the Grid, indexing should start with 1 because of the Structure Table Cell
    for (int rowIndex = 1; rowIndex <= grid.MasterTableView.Items.Count; rowIndex++)
    {
        // Increase the index by 1 because the first row in the Structure Table is the Header Row
        rowIndex += 1; 
        // Access the Structure Table Cell by row and column index
        var currencyCell = e.ExportStructure.Tables[0].Cells[colIndex, rowIndex];
        // Get the "string" value from the Cell and parse it to "double"
        var cellValue = double.Parse(currencyCell.Value.ToString(), System.Globalization.NumberStyles.Currency);
        // reset the Cell's value using the "double" type
        currencyCell.Value = cellValue;
        // Set the Cell's Format
        currencyCell.Format = "\"$\"#,##0.00_);[Red](\"$\"#,##0.00)"; // This format tells the Excel Document to treat the Value as Currency for both Negative and Positive numbers
    }
}

 

Result

 

Regards,
Attila Antal
Progress Telerik