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

Hierarchical radgrid export to excel with formatting

2 Answers 311 Views

Grid

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

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">

<html xmlns="http://www.w3.org/1999/xhtml">

<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

Accepted

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..