This is a migrated thread and some comments may be shown as answers.

Export to Excel not including customized gridtemplatecolumn.

4 Answers 274 Views

Grid

This is a migrated thread and some comments may be shown as answers.

aks asked on 26 Oct 2011, 07:05 PM

Screenshot.JPG

I have a radgrid which includes a customized gridtemplatecolumn. When I export to Excel, it leaves out the customized column. Can someone show me how to export the column? I am attaching my front and backend code and a screenshot. The column name is "Open Items by Age" and includes three data elements. Also, it there a way to clean up the column so it does not have the weird lines showing in the table. I set table border to zero. Thank in advance!

<telerik:RadGrid ID="grdCurrentandPrior" runat="server" AllowSorting="True" AutoGenerateColumns="False"

GridLines="Horizontal" Skin="Web20" Style="margin-right: 0px" Width="897px" Height="252px"

OnNeedDataSource="grdCurrentandPrior_NeedDataSource" OnExcelMLExportRowCreated="grdCurrentandPrior_ExcelMLExportRowCreated"

OnSortCommand="grdCurrentandPrior_SortCommand">

<exportsettings filename="OpenItemsCurrentInventory" ignorepaging="True" openinnewwindow="True"

exportonlydata="true" excel-format="ExcelML">

</exportsettings>

<mastertableview commanditemdisplay="Bottom" allownaturalsort="false" usealldatafields="true">

<CommandItemSettings ShowExportToExcelButton="true" ShowAddNewRecordButton="false"

ShowRefreshButton="false" />

<Columns>

<telerik:GridTemplateColumn DataField="Assignee" HeaderText="Assignee" UniqueName="Assignee"

SortExpression="Assignee">

<HeaderStyle Font-Bold="True" Width="150px" HorizontalAlign="Center" Font-Names="Arial" />

<ItemTemplate>

<asp:Label ID="lbAssignee" runat="server" Text='<%# Eval("Assignee") %>'></asp:Label>

</ItemTemplate>

<FooterStyle HorizontalAlign="Right" Font-Bold="true" />

</telerik:GridTemplateColumn>

<telerik:GridTemplateColumn DataField="State" HeaderText="State" UniqueName="State"

SortExpression="State" Visible="false">

<HeaderStyle Font-Bold="True" Width="150px" HorizontalAlign="Center" Font-Names="Arial" />

<ItemTemplate>

<asp:Label ID="lbState" runat="server" Text='<%# Eval("State") %>'></asp:Label>

</ItemTemplate>

<FooterStyle HorizontalAlign="Right" Font-Bold="true" />

</telerik:GridTemplateColumn>

<telerik:GridTemplateColumn DataField="JurisdictionCount" HeaderText="Jurisdictions"

UniqueName="JurisdictionCount" SortExpression="JurisdictionCount">

<HeaderStyle Font-Bold="True" Width="80px" HorizontalAlign="Center" Font-Names="Arial" />

<ItemTemplate>

<asp:Label ID="lbJurisdiction" runat="server" Text='<%# Eval("JurisdictionCount", "{0:N0}")%>'></asp:Label>

</ItemTemplate>

<ItemStyle HorizontalAlign="Right" ForeColor="Black" />

<FooterStyle HorizontalAlign="Right" Font-Bold="true" />

</telerik:GridTemplateColumn>

<telerik:GridTemplateColumn UniqueName="OpenItemsByAge" >

<HeaderTemplate>

<table id="Table1" cellspacing="0" cellpadding="0" width="300" border="0">

<tr>

<td colspan="3" align="center">

<b>Open Items by Age</b>

</td>

</tr>

<tr>

<td align="center" style="width: 33%">

<asp:LinkButton CssClass="Button" ID="btnPrioOpen" Text="Prior"

CommandName='Sort' CommandArgument='OpenItemsCountPrior' runat="server" /></td>

<td align="center" style="width: 33%">

<asp:LinkButton CssClass="Button" ID="btnLastMonth" Text="Last Month"

CommandName='Sort' CommandArgument='OpenItemsCountLastMonth' runat="server" /></td>

<td align="center" style="width: 34%">

<asp:LinkButton CssClass="Button" ID="btnCurrentOpen" Text="Current"

CommandName='Sort' CommandArgument='OpenItemsCountCurrent' runat="server" /></td>

</tr>

</table>

</HeaderTemplate>

<ItemTemplate >

<table id="Table2" cellspacing="0" cellpadding="0" width="300" border="0">

<tr>

<td align="right" width="33%">

<%# Eval("OpenItemsCountPrior", "{0:N0}") %>

</td>

<td align="right" width="33%">

<%# Eval("OpenItemsCountLastMonth", "{0:N0}")%>

</td>

<td align="right" width="34%">

<%# Eval("OpenItemsCountCurrent", "{0:N0}") %>

</td>

</tr>

</table>

</ItemTemplate>

</telerik:GridTemplateColumn>

<telerik:GridTemplateColumn Visible="False" DataField="OpenItemsCountPrior" HeaderText="Prior Open Items"

UniqueName="OpenItemsCountPrior" SortExpression="OpenItemsCountPrior">

<HeaderStyle Font-Bold="True" Width="80px" HorizontalAlign="Center" Font-Names="Arial" />

<ItemTemplate>

<asp:Label ID="lbPrior" runat="server" Text='<%# Eval("OpenItemsCountPrior", "{0:N0}") %>'></asp:Label>

</ItemTemplate>

<ItemStyle HorizontalAlign="Right" ForeColor="Black" />

<FooterStyle HorizontalAlign="Right" Font-Bold="true" />

</telerik:GridTemplateColumn>

<telerik:GridTemplateColumn Visible="False" DataField="OpenItemsCountLastMonth" HeaderText="Last Month Items"

UniqueName="OpenItemsCountLastMonth" SortExpression="OpenItemsCountLastMonth">

<HeaderStyle Font-Bold="True" Width="80px" HorizontalAlign="Center" Font-Names="Arial" />

<ItemTemplate>

<asp:Label ID="lbLastMonth" runat="server" Text='<%# Eval("OpenItemsCountLastMonth", "{0:N0}") %>'></asp:Label>

</ItemTemplate>

<ItemStyle HorizontalAlign="Right" ForeColor="Black" />

<FooterStyle HorizontalAlign="Right" Font-Bold="true" />

</telerik:GridTemplateColumn>

<telerik:GridTemplateColumn Visible="False" DataField="OpenItemsCountCurrent" HeaderText="Current Open Items"

UniqueName="OpenItemsCountCurrent" SortExpression="OpenItemsCountCurrent">

<HeaderStyle Font-Bold="True" Width="100px" HorizontalAlign="Center" Font-Names="Arial" />

<ItemTemplate>

<asp:Label ID="lbCurrent" runat="server" Text='<%# Eval("OpenItemsCountCurrent", "{0:N0}") %>'></asp:Label>

</ItemTemplate>

<ItemStyle HorizontalAlign="Right" ForeColor="Black" />

<FooterStyle HorizontalAlign="Right" Font-Bold="true" />

</telerik:GridTemplateColumn>

<telerik:GridTemplateColumn DataField="TotalRemainingItems" HeaderText="Items Left to Pay"

UniqueName="TotalRemainingItems" SortExpression="TotalRemainingItems">

<HeaderStyle Font-Bold="True" Width="95px" HorizontalAlign="Center" Font-Names="Arial" />

<ItemTemplate>

<asp:Label ID="lblTotalRemain" runat="server" Text='<%# Eval("TotalRemainingItems", "{0:N0}") %>'></asp:Label>

</ItemTemplate>

<ItemStyle HorizontalAlign="Right" ForeColor="Black" />

<FooterStyle HorizontalAlign="Right" Font-Bold="true" />

</telerik:GridTemplateColumn>

<telerik:GridTemplateColumn DataField="CompleteCount" HeaderText="Complete"

UniqueName="CompleteCount" SortExpression="CompleteCount">

<HeaderStyle Font-Bold="True" Width="80px" HorizontalAlign="Center" Font-Names="Arial" />

<ItemTemplate>

<asp:Label ID="lblCompleteCount" runat="server" Text='<%# Eval("CompleteCount", "{0:N0}") %>'></asp:Label>

</ItemTemplate>

<ItemStyle HorizontalAlign="Right" ForeColor="Black" />

<FooterStyle HorizontalAlign="Right" Font-Bold="true" />

</telerik:GridTemplateColumn>

<telerik:GridTemplateColumn DataField="PercentComplete" HeaderText="% Complete" UniqueName="clmPctComplete"

SortExpression="PercentComplete">

<HeaderStyle Font-Bold="True" Width="85px" HorizontalAlign="Center" Font-Names="Arial" />

<ItemTemplate>

<asp:Label ID="lblPctComplete" runat="server" Text='<%# Eval("PercentComplete", "{0:N2}%")%>'></asp:Label>

</ItemTemplate>

<ItemStyle HorizontalAlign="Right" ForeColor="Black" />

<FooterStyle HorizontalAlign="Right" Font-Bold="true" />

</telerik:GridTemplateColumn>

<telerik:GridBoundColumn DataField="AssigneeEmployeeKey" ReadOnly="True" UniqueName="AssigneeEmployeeKey"

Visible="False">

</telerik:GridBoundColumn>

</Columns>

</mastertableview>

<headerstyle borderstyle="Solid" />

<clientsettings>

<Scrolling AllowScroll="True" UseStaticHeaders="True" />

</clientsettings>

</telerik:RadGrid>

protected void grdCurrentandPrior_ExcelMLExportRowCreated(object source, GridExportExcelMLRowCreatedArgs e)

{

Int32 JurisdictionCount = 0;

Int32 OpenItemsCountPrior = 0;

Int32 OpenItemsCountCurrent = 0;

Int32 TotalRemainingItems = 0;

Int32 LastMonthItemsCount = 0;

Int32 CompleteCount = 0;

foreach (GridDataItem item in grdCurrentandPrior.MasterTableView.Items)

{

Label jc = (Label)item.FindControl("lbJurisdiction");

JurisdictionCount += Int32.Parse(jc.Text.Replace(",", "").Trim());

Label oipc = (Label)item.FindControl("lbPrior");

OpenItemsCountPrior += Int32.Parse(oipc.Text.Replace(",", "").Trim());

Label oicc = (Label)item.FindControl("lbCurrent");

OpenItemsCountCurrent += Int32.Parse(oicc.Text.Replace(",", "").Trim());

Label tr = (Label)item.FindControl("lblTotalRemain");

TotalRemainingItems += Int32.Parse(tr.Text.Replace(",", "").Trim());

Label lc = (Label)item.FindControl("lbLastMonth");

LastMonthItemsCount += Int32.Parse(lc.Text.Replace(",", "").Trim());

Label cc = (Label)item.FindControl("lblCompleteCount");

CompleteCount += Int32.Parse(cc.Text.Replace(",", "").Trim());

}

if (e.Worksheet.Table.Rows.Count == grdCurrentandPrior.Items.Count + 1)

{

RowElement row = new RowElement();

GridFooterItem footer = (source as RadGrid).MasterTableView.GetItems(GridItemType.Footer)[0] as GridFooterItem;

foreach (GridColumn column in (source as RadGrid).MasterTableView.Columns)

{

column.Visible = true;

}

foreach (GridColumn column in (source as RadGrid).MasterTableView.Columns)

{

CellElement cell = new CellElement();

string cellText = "";

if ((column.UniqueName == "Assignee") && (rdoAssignee.Checked == true))

cellText = "Totals";

else

{

if ((column.UniqueName == "State") && (rdoState.Checked == true))

cellText = "Totals";

}

if (column.UniqueName == "JurisdictionCount")

cellText = JurisdictionCount.ToString();

if (column.UniqueName == "OpenItemsCountPrior")

cellText = OpenItemsCountPrior.ToString();

if (column.UniqueName == "OpenItemsCountCurrent")

cellText = OpenItemsCountCurrent.ToString();

if (column.UniqueName == "TotalRemainingItems")

cellText = TotalRemainingItems.ToString();

if (column.UniqueName == "OpenItemsCountLastMonth")

cellText = LastMonthItemsCount.ToString();

if (cellText != "")

{

cell.Data.DataItem = cellText == " " ? "" : cellText;

row.Cells.Add(cell);

}

e.Worksheet.Table.Rows.Add(row);

}

4 Answers, 1 is accepted

answered on 28 Oct 2011, 07:23 AM

Hello,

You can try the following code snippet to export GridTemplateColumns.
C#:

protected void RadGrid1_ItemCommand(object sender, GridCommandEventArgs e)

{

if (e.CommandName == RadGrid.ExportToExcelCommandName)

{

RadGrid1.MasterTableView.GetColumn("OpenItemsCountPrior").Visible = true;

RadGrid1.MasterTableView.GetColumn("OpenItemsCountLastMonth").Visible = true;

RadGrid1.MasterTableView.GetColumn("TotalRemainingItems").Visible = true;

RadGrid1.MasterTableView.GetColumn("CompleteCount").Visible = true;

RadGrid1.MasterTableView.GetColumn("clmPctComplete").Visible = true;

}

Thanks,
Princy

answered on 30 Jul 2012, 07:48 AM

Hi telrik team,

can i ask for a sample on how to export in csv,xls,xlsx using GridTemplateColumn
this is my code my grid is already working and it gets the selected value and stored in a textbox temporarily, but what i want now
is to export those selected values in different excel format and i search on the forums but i hvnt find example that suites to my project.
Hoping for your reply ..thank you God bless!

aspx code:

<telerik:RadGrid ID="ExportReponse_RadGrid" Width="97%" OnNeedDataSource="ExportReponse_RadGrid_NeedDataSource" runat="server" OnExcelMLExportRowCreated="ExportReponse_RadGrid_ExcelMLExportRowCreated"
     AllowSorting="True" PageSize="10" AllowPaging="True" OnPreRender="ExportReponse_RadGrid_PreRender" OnItemCreated="ExportReponse_RadGrid_ItemCreated" onSelectedIndexChanged="ExportReponse_RadGrid_SelectedIndexChanged" AllowMultiRowSelection="True" Gridlines="None" >

             <MasterTableView Width="100%" Summary="RadGrid table" />
           <PagerStyle Mode="NextPrevAndNumeric"/>

        <SelectedItemStyle CssClass="SelectedItem" />

<MasterTableView AutoGenerateColumns="false">
            <Columns>
            <telerik:GridTemplateColumn UniqueName="CheckBoxTemplateColumn">
                            <HeaderTemplate>
                             <asp:CheckBox id="chkHeaderSelection" OnCheckedChanged="ToggleSelectedState" AutoPostBack="True" runat="server"></asp:CheckBox>
                            </HeaderTemplate>
                            <ItemTemplate>
                                <asp:CheckBox id="chkRowSelection" OnCheckedChanged="ToggleRowSelection" AutoPostBack="True" runat="server"></asp:CheckBox>
                            </ItemTemplate>
             </telerik:GridTemplateColumn>

                <telerik:GridBoundColumn UniqueName="ItemID" DataField="item_id" DataType="System.Int32" HeaderText="Item ID" Display="false" />
                    <telerik:GridBoundColumn UniqueName="item_text" DataField="item_text" DataType="System.String" HeaderText="Question Type"/>
            </Columns>

   </MasterTableView>
    <ClientSettings EnableRowHoverStyle="true" EnablePostBackOnRowClick="true">
            <Selecting AllowRowSelect="true" />
    </ClientSettings>
</telerik:RadGrid>

Code behind

private void ExportReponse_RadGrid_ItemPreRender(object sender, EventArgs e)
        {
            ((sender as GridDataItem)["CheckBoxTemplateColumn"].FindControl("chkRowSelection") as CheckBox).Checked = (sender as GridDataItem).Selected;
        }

        protected void ExportReponse_RadGrid_PreRender(object sender, EventArgs e)
        {
            GridHeaderItem headerItem = ExportReponse_RadGrid.MasterTableView.GetItems(GridItemType.Header)[0] as GridHeaderItem;
            (headerItem.FindControl("chkHeaderSelection") as CheckBox).Checked = ExportReponse_RadGrid.SelectedItems.Count == ExportReponse_RadGrid.Items.Count;
        }

        protected void ExportReponse_RadGrid_ItemCreated(object sender, GridItemEventArgs e)
        {
            if (e.Item is GridDataItem)
            {
                e.Item.PreRender += new EventHandler(ExportReponse_RadGrid_ItemPreRender);
            }
        }

        protected void ToggleRowSelection(object sender, EventArgs e)
        {

            GridItem gItem = (sender as CheckBox).NamingContainer as GridItem;

            gItem.Selected = (sender as CheckBox).Checked;

            GridDataItem dataItem = ExportReponse_RadGrid.MasterTableView.Items[gItem.ItemIndex];

            if (gItem.Selected)
            {
                string selectText = dataItem["ItemID"].Text;
                txtItemsToExport.Text += selectText + ";";

            }
            else
            {
                if (txtItemsToExport.Text.Contains(dataItem["ItemID"].Text))
                {
                    txtItemsToExport.Text = txtItemsToExport.Text.Replace(dataItem["ItemID"].Text + ";", "");
                }
            }

        }

        ///
        ///All Selected Rows
        ///
        protected void ToggleSelectedState(object sender, EventArgs e)
        {
            CheckBox headerCheckBox = (sender as CheckBox);
            foreach (GridDataItem dataItem in ExportReponse_RadGrid.MasterTableView.Items)
            {
                (dataItem.FindControl("chkRowSelection") as CheckBox).Checked = headerCheckBox.Checked;
                dataItem.Selected = headerCheckBox.Checked;

                if (dataItem.Selected)
                {
                    string selectText = dataItem["ItemID"].Text;
                    txtItemsToExport.Text += selectText + ";";

                }
                else
                {
                    if (txtItemsToExport.Text.Contains(dataItem["ItemID"].Text))
                    {
                        txtItemsToExport.Text = txtItemsToExport.Text.Replace(dataItem["ItemID"].Text + ";", "");
                    }
                }

            }
        }

answered on 02 Aug 2012, 09:01 AM

jenny.zip

Hi Jenny,

I made a simple demo that will be a good starting point for you.
Hope this helps.

Regards,
Daniel
the Telerik team

If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.

Product Bundles

DevCraft

Web

Mobile

Document Management

Desktop

Reporting & Mocking

Automated Testing

CMS

UI/UX Tools

Debugging

Free Tools

Support and Learning

Productivity and Design Tools

Export to Excel not including customized gridtemplatecolumn.

4 Answers, 1 is accepted