Excel Export - Adding text and functions

[Mario]

 

Good afternoon, I tried to export a radgrid to Excel with success using 

Telerik.Web.UI.GridExcelExportFormat.ExcelML with ExportOnlyData/IgnorePaging/OpenInNewWindow all set to true. I have search for a couple of days a way to add content to the Excel sheet before adding the radgrid. I mean, I need to add some text to the Excel file and execute some macro on the Excel sheet. How can I do this? The radgrid is using Ajax.

 

Any help would be most appreciated. Shoud a use an Excel template and feed the datagrid into it? By the way, I am using Excel 2007 but hope everything work's also in Excel 2003.

I use the function ExcelMLExportRowCreated and ExcelMLExportStylesCreated to format the loook and feel of the columns with success.

 

 

Thank's for your precious time :)

 

Mario

[Mario]

Any response yet?

Please let me know if I can help or redirect furter toward a solution...

Tks :)

[Daniel]

Hello Mario,

You can add custom text above RadGrid this way:

protected void RadGrid1_ExcelMLExportRowCreated(object source, GridExportExcelMLRowCreatedArgs e)
{
    if (e.RowType == GridExportExcelMLRowType.HeaderRow)
    {
        RowElement newRow = new RowElement();
        CellElement newCell = new CellElement();
        int colsCount = RadGrid1.MasterTableView.AutoGeneratedColumns.Length;
        int itemsCount = RadGrid1.MasterTableView.Items.Count;
        newCell.Data.DataItem = "Test Test";
        newRow.Cells.Add(newCell);
        e.Worksheet.Table.Rows.Insert(0, newRow);
        e.Worksheet.AutoFilter.Range = "R2C1:R" + itemsCount + "C" + colsCount;
    }
}

I attached simple project to this post. Let me know if you need more information.

Best regards,
Daniel
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Vasssek]

Hi,

could you please provide code, how ot add sum formula when exporting to xlsx format. I suppose it should be done in InfrastructureExporting event.

Thank you.

[Attila Antal]

Hi Vasssek,

The ExportInfraStructure will allow you to get access to the output the Grid created but before passed to the Document Processing libraries to actually build the Excel file, see Export Infrastructure. "The Export Infrastructure is designed to be a middle tier structure that sits between an export library and server control."

The Telerik Document Processing (TDP) libraries are the ones that you can use to interact with documents even creating formulas, see General Information - Formulas. For implementing formulas, you'll have to get the XLSX structure out of the Grid and process it further with the Telerik Document Processing (TDP) libraries. 

We have RadGrid online demo where the XLSX output is extracted, then imported into the TDP libraries for further processing, see Grid - Export Multiple Grids. In this particular example, you will see that the output is extracted from each grid, and the TDP libraries are used to construct a document with as many Sheets in the same WorkBook as many Grids are extracted. In your case, however, you can get the output, implement the formula instructed in the TDP documentation and return it in the Response for downloading.

 

See examples using the TDP libraries in different scenarios:

• XLSX and DOCX Export - RadGrid
• Understanding Telerik AJAX and Document Processing Library Spreadsheet Workbook and Worksheets
• Customize content when Exporting RadGrid to PDF
• Export large amount of data to PDF, XLSX and CSV using the Telerik Document Processing libraries
• Export HTML content into Docx
• Add Document Header and Footer to Exported Excel XLSX File

 

Kind regards,
Attila Antal
Progress Telerik