Export to Excel - Boolean challenge

10 posts, 2 answers
  1. jlj30
    jlj30 avatar
    101 posts
    Member since:
    Jan 2011

    Posted 18 May Link to this post

    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

  2. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 23 May Link to this post

    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
    Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. jlj30
    jlj30 avatar
    101 posts
    Member since:
    Jan 2011

    Posted 24 May in reply to Kostadin Link to this post

    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

  5. Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 27 May Link to this post

    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
    Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
  6. jlj30
    jlj30 avatar
    101 posts
    Member since:
    Jan 2011

    Posted 30 May in reply to Kostadin Link to this post

    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

  7. Answer
    Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 02 Jun Link to this post

    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
    Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
  8. jlj30
    jlj30 avatar
    101 posts
    Member since:
    Jan 2011

    Posted 03 Jun in reply to Kostadin Link to this post

    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

  9. jlj30
    jlj30 avatar
    101 posts
    Member since:
    Jan 2011

    Posted 07 Jun Link to this post

    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

  10. Answer
    Kostadin
    Admin
    Kostadin avatar
    1709 posts

    Posted 13 Jun Link to this post

    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
    Do you need help with upgrading your ASP.NET AJAX, WPF or WinForms projects? Check the Telerik API Analyzer and share your thoughts.
  11. jlj30
    jlj30 avatar
    101 posts
    Member since:
    Jan 2011

    Posted 14 Jun in reply to Kostadin Link to this post

    Hi Kostadin,

    This did it!

    Thanks

    Jim

Back to Top
UI for ASP.NET Ajax is Ready for VS 2017