Hierarchical radgrid export to excel with formatting

3 posts, 1 answers
  1. Lokesh
    Lokesh avatar
    89 posts
    Member since:
    Jul 2012

    Posted 21 Oct 2011 Link to this post

    Hi Team,
    I have a hierarchical radgrid and I want to export it to Excel
    I have 2 problems exporting it .

    1. I am exporting it on button_Click (button outside the grid). 
        My page has RadAjaxPanel . So when I click the button, it doesn't export it to excel.

    2. I want to format it .
        I want to add some header with values from dropdown on my page.

    Attaching image of the grid and below is my page and MasterPage

    MainMaster.master :::
    <%@ Master Language="C#" AutoEventWireup="true" CodeBehind="MainMasterPage.master.cs" Inherits="AceFinance.MainMasterPage" %>
    <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="act" %>
    <%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>
       
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
      
    <head id="Head1" runat="server">
        <title>Ace Finance</title>
    </head>
    <body class="bodymaster">
        <form id="form1" runat="server">
             <telerik:RadScriptManager ID="ScriptManager" runat="server" AsyncPostBackTimeout="1500" />
        <telerik:RadAjaxManager ID="RadAjaxManagerMaster" runat="server" EnableAJAX="true">
               <AjaxSettings>
                     <telerik:AjaxSetting AjaxControlID="rmFinanceMenu">
                       <UpdatedControls>
                               <telerik:AjaxUpdatedControl ControlID="rmFinanceMenu" LoadingPanelID="AjaxLoadingPanel1" />
                       </UpdatedControls>
                   </telerik:AjaxSetting>
               </AjaxSettings>
        </telerik:RadAjaxManager>
      
            <telerik:RadAjaxLoadingPanel runat="server" ID="RadAjaxLoadingPanel1" Skin="Default" />
            <asp:Panel runat="server" ID="Panel1" CssClass="menu-container">
            //  my code... menus, etc.
          </asp:Panel>
     </form>
        </body>
    </html>

    page.aspx :::
    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ManageGiroPayments.aspx.cs" Inherits="AceFinance.ManageGiroPayments" MasterPageFile="~/MainMasterPage.Master" Title="Giro Payments" %>
    <%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>
    <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>
      
    <asp:Content ID="Content1" ContentPlaceHolderID="MainContentPlaceHolder" runat="server">
        <telerik:RadAjaxPanel ID="rjxPanel" runat="server" LoadingPanelID="RadAjaxLoadingPanel1">
            <fieldset>
                <asp:Label ID="lblGiroPayment" runat="server" Text="Giro Payments" Font-Bold="true"  CssClass="ClsLabelHead" />
                <br /> <br />
                <table width="100%">
                    <tr>
                        <td>
                            <asp:Label ID="lblOrganization" runat="server" Text="Organization" Font-Bold="true" />
                            <telerik:RadComboBox ID="rcbOrganization" runat="server" OnSelectedIndexChanged="rcbOrganization_SelectedIndexChanged" AutoPostBack="true" Height="140px" />
                        </td>
                        <td>
                            <asp:Label ID="lblAccountNo" runat="server" Text="Account #" Font-Bold="true" />
                            <telerik:RadComboBox ID="rcbBankAccount" runat="server" />
                        </td>
                        <td>
                            <asp:Label ID="lblCostCenter" runat="server" Text="Cost Center" Font-Bold="true"  />
                            <telerik:RadComboBox ID="rcbCostCenter" runat="server" Height="140px" />
                        </td>
                    </tr>
                    <tr>
                        <td style="width:100%" colspan="3">
                            <telerik:RadGrid ID="rgGiroPayments" runat="server" OnNeedDataSource="rgGiroPayments_NeedDataSource" AllowMultiRowSelection="true">
                                <MasterTableView>
                                    <GroupByExpressions>
                                        <telerik:GridGroupByExpression>
                                            <SelectFields>
                                                <telerik:GridGroupByField FieldName="EntityKeyName" FieldAlias="Vendor" />
                                            </SelectFields>
                                            <GroupByFields>
                                                <telerik:GridGroupByField FieldName="EntityKeyName" FieldAlias="Vednor" />
                                            </GroupByFields>
                                        </telerik:GridGroupByExpression>
                                    </GroupByExpressions>
                                    <Columns>
                                        <telerik:GridBoundColumn DataField="AP_InvoiceID" HeaderText="AP_InvoiceID" Display="false" UniqueName="AP_InvoiceID">
                                        </telerik:GridBoundColumn>
                                        <telerik:GridBoundColumn DataField="InvoiceNo" HeaderText="Invoice #" DataType="system.string"
                                            UniqueName="InvoiceNo" FilterControlWidth="75%" SortExpression="InvoiceNo"
                                            AutoPostBackOnFilter="true" CurrentFilterFunction="Contains">
                                        </telerik:GridBoundColumn>
                                        <telerik:GridBoundColumn DataField="InvoiceDate" HeaderText="Date" FilterControlWidth="75%"
                                            UniqueName="InvoiceDate" DataFormatString="{0:d}" AutoPostBackOnFilter="true"
                                            CurrentFilterFunction="Contains">
                                        </telerik:GridBoundColumn>
                                        <telerik:GridBoundColumn DataField="EntityID" HeaderText="EntityID" Display="false" UniqueName="EntityID">
                                        </telerik:GridBoundColumn>
                                        <telerik:GridBoundColumn DataField="EntityKeyID" HeaderText="EntityKeyID" Display="false" UniqueName="EntityKeyID">
                                        </telerik:GridBoundColumn>
                                        <telerik:GridBoundColumn DataField="EntityKeyName" FilterControlWidth="75%" HeaderText="EntityKeyName"
                                            UniqueName="EntityKeyName" AutoPostBackOnFilter="true" CurrentFilterFunction="Contains" Display="false">
                                        </telerik:GridBoundColumn>
                                        <telerik:GridBoundColumn DataField="InvoiceCurrencyName" HeaderText="Currency"
                                            UniqueName="InvoiceCurrencyName" FilterControlWidth="50%" AutoPostBackOnFilter="true"
                                            CurrentFilterFunction="Contains" Display="false">
                                        </telerik:GridBoundColumn>
                                        <telerik:GridBoundColumn DataField="CurrencySymbol" HeaderText="Currency"
                                            UniqueName="CurrencySymbol" FilterControlWidth="50%" AutoPostBackOnFilter="true"
                                            CurrentFilterFunction="Contains">
                                        </telerik:GridBoundColumn>
                                        <telerik:GridBoundColumn DataField="InvoiceAmount" HeaderText="Amount" UniqueName="InvoiceAmount"
                                            FilterControlWidth="50%" AutoPostBackOnFilter="true" CurrentFilterFunction="Contains">
                                        </telerik:GridBoundColumn>
                                        <telerik:GridTemplateColumn HeaderText="Paid Amount" UniqueName="PaidAmount">
                                            <ItemTemplate>
                                                <asp:TextBox ID="txtPaidAmount" runat="server" />
                                            </ItemTemplate>
                                        </telerik:GridTemplateColumn>
                                        <telerik:GridBoundColumn DataField="BalanceAmount" HeaderText="Due Amount" UniqueName="BalanceAmount"
                                            AutoPostBackOnFilter="true" CurrentFilterFunction="Contains">
                                        </telerik:GridBoundColumn>
                                        <telerik:GridClientSelectColumn UniqueName="Select">
                                        </telerik:GridClientSelectColumn>
                                    </Columns>
                                </MasterTableView>
                                <ClientSettings Selecting-AllowRowSelect="true"></ClientSettings>
                            </telerik:RadGrid>
                        </td>
                    </tr>
                    <tr>
                        <td align="center" colspan="3">
                            <asp:Button ID="btnPayment" runat="server" Text="Make Payment" OnClick="btnPayment_Click" Width="100px" />
                        </td>
                    </tr>
                </table>
            </fieldset>
        </telerik:RadAjaxPanel>
    </asp:Content>


    This is  Button_Click
    protected void btnPayment_Click(object sender, EventArgs e)
         {
             try
             {
                 string InvoiceIDList = GlobalConstants.BLANK;
                 DataTable dt = new DataTable();
                 dt.Columns.Add("VendorID");
                 dt.Columns.Add("AP_InvoiceID");
                 dt.Columns.Add("InvoiceAmount");
                 dt.Columns.Add("PaidAmount");
                 APInvoices AllAPInvoices = APInvoices.GetAllInvoices(int.Parse(rcbOrganization.SelectedValue), UIHelper.GetCurrentUsersLanguageID());
                 foreach (GridDataItem item in rgGiroPayments.SelectedItems)
                 {
                     TextBox txtPaidAmount = item.FindControl("txtPaidAmount") as TextBox;
                     if (txtPaidAmount.Text == GlobalConstants.BLANK)
                         throw new Exception("Please enter Paid Amount for Invoice " + item["InvoiceNo"].Text);
                     if (double.Parse(txtPaidAmount.Text) > double.Parse(item["BalanceAmount"].Text))
                         throw new Exception("Paid Amount cannot be greater than Due Amount for Invoice " + item["InvoiceNo"].Text);
                     dt.Rows.Add(int.Parse(item["EntityKeyID"].Text), item["AP_InvoiceID"].Text, item["InvoiceAmount"].Text, txtPaidAmount.Text);
                     APInvoice AnAPInvoice = AllAPInvoices.GetItem(int.Parse(item["AP_InvoiceID"].Text));
                     AnAPInvoice.PaidAmount = double.Parse(txtPaidAmount.Text);
                 }
                 //AllAPInvoices.Save(); 
                 DataRow[] dr = dt.Select("", "VendorID");
                 for (int i = 0; i < dt.Rows.Count; i++)
                 {
                     InvoiceIDList = InvoiceIDList + dt.Rows[i]["AP_InvoiceID"].ToString() + ",";
                 }
                 // Export to Excel code here 
                 rgGiroPayments.AllowSorting = false;
                 rgGiroPayments.AllowFilteringByColumn = false;
                 rgGiroPayments.ExportSettings.IgnorePaging = true;
                 //rgGiroPayments.ExportSettings.ExportOnlyData = true;
                 rgGiroPayments.MasterTableView.HierarchyDefaultExpanded = true;
                 rgGiroPayments.ExportSettings.OpenInNewWindow = true;   
                 rgGiroPayments.MasterTableView.ExportToExcel();
                 Session["APInvoiceInfoList"] = null;
                 rgGiroPayments.Rebind();
             }
             catch (Exception ex)
             {
                 XITingExceptionProcessor.ProcessException(this, ex);
             }
         }


    Waiting for your positive reply,

    Thanks,
    Lok..

    Please refere excel_format_001.png file..
    its an updated image.

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

    Posted 21 Oct 2011 Link to this post

    Hello Lokesh,

    The exporting feature of the control work with regular postbacks only. For exporting you need to temporarily disable the Ajax and can enable it after exporting. The online help article describes more about this.
    Export from Ajaxified Grid

    You can go through  the following documentation which explains the formating of RadGrid on exporting.
    Word/Excel export (HTML-based)

    Thanks,
    Princy.
  3. UI for ASP.NET Ajax is Ready for VS 2017
  4. Lokesh
    Lokesh avatar
    89 posts
    Member since:
    Jul 2012

    Posted 21 Oct 2011 Link to this post

    Hello Princy,
    Thanks a ton for your support.
    Link you provided were very helpfull and it worked for me..

    Meanwhile I also tried the other way to export to excel which I think, will be helpfull to others..
    I got all the columns and rows required in excel in Datatable and used it to export.

    Here is the code :
    protected void btnPayment_Click(object sender, EventArgs e)
           {
               try
               {
                   string InvoiceIDList = GlobalConstants.BLANK;
                   DataTable dt = new DataTable();
                   dt.Columns.Add("VendorID");
                   dt.Columns.Add("AP_InvoiceID");
                   dt.Columns.Add("InvoiceAmount");
                   dt.Columns.Add("PaidAmount");
                   APInvoices AllAPInvoices = APInvoices.GetAllInvoices(int.Parse(rcbOrganization.SelectedValue), UIHelper.GetCurrentUsersLanguageID());
                   foreach (GridDataItem item in rgGiroPayments.SelectedItems)
                   {
                       TextBox txtPaidAmount = item.FindControl("txtPaidAmount") as TextBox;
                       if (txtPaidAmount.Text == GlobalConstants.BLANK)
                           throw new Exception("Please enter Paid Amount for Invoice " + item["InvoiceNo"].Text);
                       if (double.Parse(txtPaidAmount.Text) > double.Parse(item["BalanceAmount"].Text))
                           throw new Exception("Paid Amount cannot be greater than Due Amount for Invoice " + item["InvoiceNo"].Text);
                       dt.Rows.Add(int.Parse(item["EntityKeyID"].Text), item["AP_InvoiceID"].Text, item["InvoiceAmount"].Text, txtPaidAmount.Text);
                       APInvoice AnAPInvoice = AllAPInvoices.GetItem(int.Parse(item["AP_InvoiceID"].Text));
                       AnAPInvoice.PaidAmount += double.Parse(txtPaidAmount.Text);
                   }
                   AllAPInvoices.Save(); 
                   DataRow[] dr = dt.Select("", "VendorID");
                   for (int i = 0; i < dt.Rows.Count; i++)
                   {
                       InvoiceIDList = InvoiceIDList + dt.Rows[i]["AP_InvoiceID"].ToString() + ",";
                   }
                   int lstCommIndx = InvoiceIDList.LastIndexOf(',');
                   InvoiceIDList = InvoiceIDList.Remove(lstCommIndx);
                   AccPayments.GenerateGiroPayment(InvoiceIDList, int.Parse(rcbBankAccount.SelectedValue), Csla.ApplicationContext.User.Identity.Name, "Generate Giro Payment");
                   // Code for Export to Excel ....
                   DataTable dtOriginal = new DataTable();
                   DataTable dtTemp = new DataTable();
                   dtTemp.Columns.Add("Organization");
                   dtTemp.Columns.Add("Vendor");
                   dtTemp.Columns.Add("Invoice No");
                   dtTemp.Columns.Add("Invoice Amount");
                   dtTemp.Columns.Add("Paid Amount");
                   foreach (GridDataItem item in rgGiroPayments.SelectedItems)
                   {
                       TextBox txtPaidAmount = item.FindControl("txtPaidAmount") as TextBox;
                       dtTemp.Rows.Add(rcbOrganization.Text, item["EntityKeyName"].Text, item["InvoiceNo"].Text, item["InvoiceAmount"].Text, txtPaidAmount.Text);
                   }
                   DataRow[] drAddItem = dtTemp.Select("", "Vendor");
                   ExportToSpreadsheet(this,dtTemp, "MyFile");
                   Session["APInvoiceInfoList"] = null;
                   rgGiroPayments.Rebind();
               }
               catch (Exception ex)
               {
                   XITingExceptionProcessor.ProcessException(this, ex);
               }
           }
           public static void ExportToSpreadsheet(System.Web.UI.Page CurrentPage, DataTable table, string name)
           {            
               CurrentPage.Response.Charset = "";
               // set MIME type to be Excel file. 
               CurrentPage.Response.ContentType = "application/vnd.ms-excel";
               // add a header to response to force download (specifying filename) 
               StringBuilder temp = new StringBuilder();
               temp.Append("attachment; filename=\"")
                   .Append(name)
                   .Append(".xls\"");
               CurrentPage.Response.AddHeader("Content-Disposition", temp.ToString());
               CurrentPage.Response.Write("<HTML>");
               CurrentPage.Response.Write("<BODY>");
               CurrentPage.Response.Write("<TABLE>");
               StringBuilder ColumnTitlesBuilder = new StringBuilder();
               ColumnTitlesBuilder.Append("<TR style=\"border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid;\">");
               for (int i = 0; i < table.Columns.Count; i++)
               {
                   ColumnTitlesBuilder.Append("<TD>").Append(table.Columns[i].ColumnName).Append("</TD>");
               }
               ColumnTitlesBuilder.Append("</TR>").Append(Environment.NewLine);
               CurrentPage.Response.Write(ColumnTitlesBuilder.ToString());
               foreach (DataRow row in table.Rows)
               {
                   StringBuilder DataRowBuilder = new StringBuilder();
                   DataRowBuilder.Append("<TR style=\"font-size: 12px; color: black; font-family: 'Century Gothic'; border-right: black thin solid; border-top: black thin solid; border-left: black thin solid; border-bottom: black thin solid;\">");
                   for (int i = 0; i < table.Columns.Count; i++)
                   {
                       DataRowBuilder.Append("<TD>").Append(row[i]).Append("</TD>");
                   }
                   DataRowBuilder.Append("</TR>").Append(Environment.NewLine);
                   CurrentPage.Response.Write(DataRowBuilder.ToString());
               }
               CurrentPage.Response.Write("</TABLE>");
               CurrentPage.Response.Write("</BODY>");
               CurrentPage.Response.Write("</HTML>");
               CurrentPage.Response.Flush();
           


    This also worked for me.
    But problem with this solution is that , I cannot rebind my grid after I call ExportToSpreadsheet() method....
    Do you know why ???

    Any help appreciated.

    Regards,
    Lok.. 
Back to Top