Export to Excel - Boolean challenge

[jlj30]

Hi,

I need to place an "X" in all cells that have a true value in the export.  I've seen similar posts, but I am using Format="Xlsx" and trying to accomplish this task using the OnInfrastructureExporting event handler as follows:

protected void grdUserAdmin_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{
    var colCount = e.ExportStructure.Tables[0].Columns.Count;
    var rowCount = e.ExportStructure.Tables[0].Rows.Count;
    ExportStyle headerStyle = new ExportStyle();
    headerStyle.ForeColor = Color.White;
    headerStyle.BackColor = Color.Blue;
    headerStyle.Font.Bold = true;

    // Set the header style for all columns
    for (var i = 1; i <= colCount; i++)
    {
        e.ExportStructure.Tables[0].Cells[i, 1].Style = headerStyle;
        e.ExportStructure.Tables[0].Columns[i].Width = 200; // Would like auto-width !!
    }

    // Place an "X" in cells assigned to a boolean data field if value is true
    var headerText = "";
    for (var c = 1; c <= colCount; c++)
    {
        for (var r = 1; r <= rowCount; r++)
        {
            if (r == 1)
            {
                headerText = e.ExportStructure.Tables[0].Cells[c, r].Value.ToString();
                continue;
            }
            if (headerText == "Active")
            {
                var x = e.ExportStructure.Tables[0].Cells[c, r].Value.ToString();
                System.Diagnostics.Debug.WriteLine("Cell Value: " + x);
                if (Convert.ToBoolean(e.ExportStructure.Tables[0].Cells[c, r].Value.ToString()))
                    e.ExportStructure.Tables[0].Cells[c, r].Value = "X";
            }
        }
    }

}

The value for the variable x above is always "" or null (hard to tell in the Output section of VS).
I know that my data for column Active should have all true values.
The conversion to boolean fails!
I think I'm close.
What am I doing wrong?
Thanks in advance for any advice.
Jim

[Kostadin]

Hello Jim,

I examined the provided code snippet and as far as I can see it looks correct. Nevertheless, could you please let me know the type of the column that showing the boolean values. Additionally you can try getting the Text of the cell instead of its Value.

e.ExportStructure.Tables[0].Cells[c, r].Text

Regards,
Kostadin
Telerik

[jlj30]

Hi Kostadin,

It seems that if my column is a GridBoundColumn then I do get "True" or "False" in the Value property - perfect.

However, the columns in question are of type GridTemplateColumn.

Here's an example (I have numerous such columns):

<telerik:GridTemplateColumn HeaderText="Common" AllowFiltering="true" SortExpression="Common_Flag"
    UniqueName="Common_Flag_Column" FilterControlWidth="85px" DataField="Common_Flag">
    <ItemTemplate>
        <asp:Image ID="imgCommon" runat="server" ImageAlign="Middle" ImageUrl="~/Images/Fast Icon Images/bt_apply_16x16.png"
            Visible='<%# Eval("Common_Flag") %>' />
    </ItemTemplate>
    <HeaderStyle HorizontalAlign="Center" Width="10%"></HeaderStyle>
    <ItemStyle HorizontalAlign="Center"></ItemStyle>
</telerik:GridTemplateColumn>

I have seen other threads that suggest adding a label or textbox to the template and hiding the image and making the label/textbox visible during export.

I would like to have a generic method that exports ANY grid, and handles columns such as the example above - so that an X appears in the export cells where the bound value is true. All of our boolean columns on all grids withiin our application are displayed using GridTemplateColumn's with an image.

Any suggestions on how to accomplish this using BIFF and the InfrastructureExporting event?

Code snippets always appreciated :)

Thanks

Jim

[Kostadin]

Hello Jim,

Please try binding the AlternateText of the Image control to the boolean filed and check whether the value will be exported. Additionally you can try setting ExportOnlyData to false if you have enabled it in the export options.

Regards,
Kostadin
Telerik

[jlj30]

Hi Kostadin,

I tried: AlternateText='<%# Eval("Common_Flag") %>' on my Image declaration, but with no luck.  Yes, I tried Bind as well as Eval.

Also, it seems that I'm getting an unexpected first row at the top of by exported file.  I had to change my code to apply the header formatting to the 2nd row.

Here's my current OnInfrastructureExporting event handler:

protected void grdControls_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{
    try
    {
        var colCount = e.ExportStructure.Tables[0].Columns.Count;
        // Set the header style for all columns
        var headerStyle = new ExportStyle();
        headerStyle.ForeColor = Color.White;
        headerStyle.BackColor = Color.Blue;
        headerStyle.Font.Bold = true;
        var booleanHeaderStyle = new ExportStyle();
        booleanHeaderStyle.ForeColor = Color.White;
        booleanHeaderStyle.BackColor = Color.Blue;
        booleanHeaderStyle.Font.Bold = true;
        booleanHeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        var booleanColumnStyle = new ExportStyle();
        booleanColumnStyle.HorizontalAlign = HorizontalAlign.Center;

        for (var i = 1; i <= colCount; i++)
        {
            e.ExportStructure.Tables[0].Cells[i, 2].Style = headerStyle;
            e.ExportStructure.Tables[0].Columns[i].Width = 300; // Would like auto-width !!
        }

        // Place an "X" in cells assigned to a boolean data field if value is true
        var rowCount = e.ExportStructure.Tables[0].Rows.Count;
        for (var c = 1; c <= colCount; c++)
        {
            var booleanColumn = false;
            for (var r = 2; r <= rowCount; r++)
            {
                var cellValue = e.ExportStructure.Tables[0].Cells[c, r].Value.ToString();
                var cellText = e.ExportStructure.Tables[0].Cells[c, r].Text.ToString();
                System.Diagnostics.Debug.WriteLine("Cell Value: " + cellValue + "  cellText: " + cellText);
                if (cellValue == "True" || cellValue == "False")
                {
                    booleanColumn = true;
                    e.ExportStructure.Tables[0].Cells[c, r].Value = (cellValue == "True") ? "X" : "";
                    e.ExportStructure.Tables[0].Cells[c, r].Style = booleanColumnStyle;
                }
            }
            if (booleanColumn)
            {
                e.ExportStructure.Tables[0].Cells[c, 1].Style = booleanHeaderStyle; // Also center the header
                e.ExportStructure.Tables[0].Columns[c].Width = 100;
            }
        }
    }
    catch
        (Exception ex)
    {
        HandleError(ex);
    }
}

 

I've also attached a screenshot of an exported file.

As you can see the image appears (the checkmark).

What am I doing wrong?

Also, what happened to your documentation.  It seems that it's taken a turn for the worse.  For example how do I easily find the OnInfrastructureExporting event doc?

Thanks for any suggestions as to how to proceed.

Jim

[Kostadin]

Hi Jim,

I prepared a small sample where you can see that the alternate text of the Image control is exported by default to Xlsx export format. Could you please check it out and let me know how it differs from your real setup? I would appreciate if you can replicate the issue either in my sample or in a small runnable one and send it to us?
Regards OnInfrastructureExporting you can find more information in the API section.

Regards,
Kostadin
Telerik

[jlj30]

Hi Kostadin,

Thanks for the simple sample.

It was just what I needed to get me going.

Switching from Biff to Xlsx and using the AlternateText property of the image did the trick.

Thanks again.

Jim

[jlj30]

Hi Kostadin,

I have a similar issue with a LinkButton in a GridTemplateColumn.

Here's the declaration of the column:

<telerik:GridTemplateColumn HeaderText="Name" UniqueName="Name" DataField="Name" CurrentFilterFunction="Contains" AutoPostBackOnFilter="true"
    AllowFiltering="true" SortExpression="Name">
    <ItemTemplate>
        <asp:LinkButton ID="lnkName" runat="server" CommandArgument='<%# Eval("URL")%>' CommandName="Navigate"
            Text='<%# Bind("Name") %>'>
        </asp:LinkButton>
    </ItemTemplate>
    <HeaderStyle Width="15%" />
</telerik:GridTemplateColumn>

If I set ExportOnlyData="False" the exported column does contain the text as expected, but there is an extra row inserted at the top of the exported file (i.e., the header row is the second row).

How do I avoid this extra row?  I don't see a Delete or Remove row capability in the OnInfrastructureExporting event handler.

Any advice on handling LinkButton's in GridTemplateColumn's would be most appreciated.

Thanks in advance.

Jim

[Kostadin]

Hello Jim,

I assume the extra row is the command item if you enable it in your configuration. A possible solution is to hide the command item when export command is fired. Please check out the following code snippet.

protected void RadGrid1_ItemCommand(object sender, Telerik.Web.UI.GridCommandEventArgs e)
{
    if(e.CommandName == RadGrid.ExportToExcelCommandName)
    {
        RadGrid1.MasterTableView.CommandItemDisplay = GridCommandItemDisplay.None;
    }
}

Regards,
Kostadin
Telerik

[jlj30]

Hi Kostadin,

This did it!

Thanks

Jim