Export to Excel not including customized gridtemplatecolumn.

5 posts, 0 answers
  1. aks
    aks avatar
    22 posts
    Member since:
    Apr 2008

    Posted 26 Oct 2011 Link to this post

    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);
              }
          }

  2. Princy
    Princy avatar
    17421 posts
    Member since:
    Mar 2007

    Posted 28 Oct 2011 Link to this post

    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

  3. DevCraft Release
  4. jenny
    jenny avatar
    5 posts
    Member since:
    Jun 2011

    Posted 30 Jul 2012 Link to this post

    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 + ";", "");
                        }
                    }

                }
            }

  5. Daniel
    Admin
    Daniel avatar
    4780 posts
    Member since:
    Sep 2012

    Posted 02 Aug 2012 Link to this post

    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.

  6. jenny
    jenny avatar
    5 posts
    Member since:
    Jun 2011

    Posted 02 Aug 2012 Link to this post

    Thank You Daniel! it works! God bless :)

Back to Top
DevCraft Release