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

[Solved] sum columns in exported excel

4 Answers 541 Views

Grid

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

4 Answers, 1 is accepted

answered on 06 Jul 2010, 03:27 PM

Hello Ajith,

Straight onto your questions:

1) I need to automatically sum some columns in the footer row of the exported grid.
Can you please let us know which Excel format is used in your scenario? ExcelML or HTML?

2) Need to add some text to the exported report in the form of notes which are passed in.(Excel.pdf,word)
You could insert custom content in the GridExporting/PdfExporting event handler:

protected void RadGrid1_GridExporting(object source, GridExportingArgs e)

{

if (e.ExportType == ExportType.Excel)

{

string myText = "Test";

e.ExportOutput = e.ExportOutput.Replace("<body>", "<body>" + myText);

}

Also, in my radgrid pdf export is not working. The exported pdf contains no data.All the other exporting is perfectly working.
Please disable the scrolling/static headers before exporting.
PDF export help topic

Best regards,
Daniel
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items

answered on 27 Jul 2010, 08:22 PM

I, too, need to sum columns in my exported worksheet. I am using ExcelML export from the RadGrid (AJAX).

My data has two sections, after each of which I need to sum several of the exported columns, and put a formula into some others, like below:
One year    $100,000       -$5000           -5%         $95,000
Two year    $750,000    $100,000       13.3%       $850,000
                  (sum col b)  (sum col c)  (col c/col b) (sum col e)

Prog A        $660,000    -$100000       -15%        $560,000
Prog B          $40,000      $20,000         50%         $60,000
                  (sum col b)  (sum col c)  (col c/col b) (sum col e)

I do not need to sum the two separate sections, only the individual sections. I have searched rather extensively through the examples and the forum and can't seem to find an answer for this.

Code:

<telerik:RadGrid ID="grdYTDAdvAct" runat="server" AllowPaging="false" ShowFooter="false"

AutoGenerateColumns="false" AllowFilteringByColumn="True" AllowSorting="true"

OnNeedDataSource="grdResults_NeedDataSource"

OnExcelMLExportRowCreated="grdYTDAdvAct_ExcelMLExportRowCreated"

OnExcelMLExportStylesCreated="grdYTDAdvAct_ExcelMLExportStylesCreated"

OnGridExporting="grdYTDAdvAct_GridExporting">

<ExportSettings ExportOnlyData="true" IgnorePaging="true" OpenInNewWindow="true">

<Excel Format="ExcelML" />

</ExportSettings>

bool isConfigured = false;

protected void grdYTDAdvAct_ExcelMLExportRowCreated(object source, GridExportExcelMLRowCreatedArgs e)

{

if (e.RowType == GridExportExcelMLRowType.DataRow)

{

//Add custom styles to the desired cells

CellElement cell = e.Row.Cells.GetCellByName("curYE2Balance");

cell.StyleValue = cell.StyleValue == "itemStyle" ? "currencyStyle" : "alternatingCurrencyItemStyl";

cell = e.Row.Cells.GetCellByName("curNetChange1");

cell.StyleValue = cell.StyleValue == "itemStyle" ? "currencyStyle" : "alternatingCurrencyItemStyl";

cell = e.Row.Cells.GetCellByName("nPctChange1");

cell.StyleValue = cell.StyleValue == "itemStyle" ? "percentItemStyle" : "alternatingCurrencyItemStyl";

cell = e.Row.Cells.GetCellByName("curYE1Balance");

cell.StyleValue = cell.StyleValue == "itemStyle" ? "principalItemStyle" : "alternatingCurrencyItemStyl";

if (!isConfigured)

{

//Set Worksheet name

e.Worksheet.Name = "YTD Advance Activity";

//Set Column widths

foreach (ColumnElement column in e.Worksheet.Table.Columns)

{

if (e.Worksheet.Table.Columns.IndexOf(column) == 0)

column.Attributes["ss:Width"] = "20"; //set width 180 to Class Name column

else if (e.Worksheet.Table.Columns.IndexOf(column) == 1)

column.Attributes["ss:Width"] = "180"; //set width 180 to Class Name column

else

column.Attributes["ss:Width"] = "80"; //set width 80 to the rest of the columns

}

isConfigured = true;

}

protected void grdYTDAdvAct_ExcelMLExportStylesCreated(object source, GridExportExcelMLStyleCreatedArgs e)

{

//Add currency and percent styles

StyleElement currencyStyle = new StyleElement("currencyItemStyle");

principalStyle.NumberFormat.FormatType = NumberFormatType.Currency;

e.Styles.Add(currencyStyle);

StyleElement alternatingCurrencyStyle = new StyleElement("alternatingCurrencyItemStyle");

alternatingPrincipalStyle.NumberFormat.FormatType = NumberFormatType.Currency;

e.Styles.Add(alternatingCurrencyStyle);

StyleElement percentStyle = new StyleElement("percentItemStyle");

percentStyle.NumberFormat.FormatType = NumberFormatType.Percent;

e.Styles.Add(percentStyle);

StyleElement alternatingPercentStyle = new StyleElement("alternatingPercentItemStyle");

alternatingPercentStyle.NumberFormat.FormatType = NumberFormatType.Percent;

e.Styles.Add(alternatingPercentStyle);

}

protected void grdYTDAdvAct_GridExporting(object Source, GridExportingArgs e)

{

string myText = "Total:";

e.ExportOutput = e.ExportOutput.Replace("<body>", "<body>" + myText);

}

The grdYTDAdvAct_GridExporting function did not seem to do anything to my output.

Thanks in advance,
DMcDuck

answered on 29 Jul 2010, 04:41 PM

sample.zip

Hello DMcDuck,

I have already addressed the support ticket you have opened on the same topic. Here I am pasting my post in order to make it available for the community:

"I have attached a small sample that demonstrates how to insert subtotal values in the worksheet of the exported grid. Note that for calculated columns you will need to populate your data manually from the grid, because calculated column values are not automatically exported."

I hope this helps.

Regards,
Martin
the Telerik team

answered on 19 May 2013, 04:32 AM

report.png

Hi,
I have almost the same problem
In attached file my report with sum row and here my code to export this report to pdf:

protected void Unnamed1_Click(object sender, EventArgs e) {
// export to pdf
if (TreeViewFilter.SelectedNodes.Count > 0) {
int reportId = Convert.ToInt32(TreeViewFilter.SelectedValue);
string fileName = string.Format("Report{0}.pdf", reportId);
string fileNameGuid = string.Format("Report{0}{1}.pdf", reportId, Guid.NewGuid());
string name = string.Format("{0}\\{1}", ConfigurationManager.AppSettings["ExportFolderPath"], fileName);
if (File.Exists(name)) {
//Set the appropriate ContentType.
Response.ClearContent();
Response.ContentType = "Application/pdf";
Response.AddHeader("content-disposition", string.Format("attachment;filename={0}", fileNameGuid));
//Write the file directly to the HTTP content output stream.
Response.WriteFile(name);
Response.End();
}
}
}

The problem is that sum row does not appear in the exported file. Where the problem? Thanks.