Telerik Forums / UI for ASP.NET AJAX
This is a migrated thread and some comments may be shown as answers.

Hierarchical radgrid export to excel with formatting

2 Answers 178 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Lokesh
Top achievements
Rank 1
Lokesh asked on 21 Oct 2011, 09:18 AM
export-to-excel.png
excel-format.png
excel-format-001.png
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 Answers, 1 is accepted

Sort by
0
Accepted
Princy
Top achievements
Rank 2
answered on 21 Oct 2011, 11:40 AM
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.
0
Lokesh
Top achievements
Rank 1
answered on 21 Oct 2011, 12:33 PM
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.. 
Tags
Grid
Asked by
Lokesh
Top achievements
Rank 1
Answers by
Princy
Top achievements
Rank 2
Lokesh
Top achievements
Rank 1
Share this question
or