Telerik blogs

New export and import capabilities for ASP.NET AJAX suite

by Daniel Peichev
June 27, 2014 Release 0 Comments


One of the most popular questions these days is “How to export the control X to the Docx/Xlsx formats?” We now have an elegant solution with the Document Processing Library (DPL) which gives you the freedom to create, export, and import documents of various formats (Docx, Xslx, Rtf) with ease.

The Example

Now let me show you a brief example as to how you can use the DPL library to export our RadGrid control.
As you can see below the control declaration is very simple in this case:

<telerik:RadGrid ID="RadGrid1" runat="server" OnNeedDataSource="RadGrid1_NeedDataSource" OnItemCommand="RadGrid1_ItemCommand" AllowPaging="true">
    <ExportSettings IgnorePaging="true" />
    <MasterTableView CommandItemDisplay="Top">
        <CommandItemSettings ShowExportToExcelButton="true" ShowExportToWordButton="true" />
    </MasterTableView>
</telerik:RadGrid>

First, I have created a simple static array that contains the supported item types:
static GridItemType[] supportedItemTypes = new GridItemType[]
            {
                GridItemType.Header,
                GridItemType.AlternatingItem,
                GridItemType.Item
            };

Then I handle the ItemCommand event to intercept the built-in export command and replace it with the custom DPL approach:
protected void RadGrid1_ItemCommand(object sender, GridCommandEventArgs e)
{
    if (e.CommandName == RadGrid.ExportToExcelCommandName)
    {
        this.PreRender += (s, a) => { ExportToXlsx(); };
        e.Canceled = true;
    }
}

The above example demonstrates how I handle the Excel export, but the same logic applies for Word also.

Now let's get straight to the point. Traversing the RadGrid structure and creating the workbook:
private void ExportToXlsx()
 {
     string fileName = "RadGrid.xlsx";
     string filePath = Server.MapPath(fileName);
 
     IgnorePaging(); //more info about this method later
 
     Workbook workbook = new Workbook();
     Worksheet worksheet = workbook.Worksheets.Add();
     GridItem item;
 
     var gridColumns = RadGrid1.MasterTableView.RenderColumns;
     var gridItems = RadGrid1.MasterTableView.GetItems(supportedItemTypes);
 
     for (int itemNum = 0; itemNum < gridItems.Length; itemNum++)
     {
         item = gridItems[itemNum];
         for (int cellNum = 0; cellNum < item.Cells.Count; cellNum++)
         {
             worksheet.Cells[itemNum, cellNum].SetValue(GetCellText(item.Cells[cellNum].Text));
         }
     }
 
     GenerateFile(workbook, fileName);
 }

It is very simple and clear, isn't it? We can also add a logic to hide (hide, not delete) the invisible columns.
for (int colNum = 0; colNum < gridColumns.Length; colNum++)
{
    if (!gridColumns[colNum].Visible)
    {
        worksheet.Columns[colNum].SetWidth(new ColumnWidth(0, false));
    }
}

A possible place to insert the above code is just before the GenerateFile method in the ExportToXlsx body.

Now that the most important tasks are already covered let's take a look on two utility methods: IgnorePaging and GetCellText.
private string GetCellText(string text)
{
    return text.Replace(" ", "");
}
 
private void IgnorePaging()
{
    if (RadGrid1.ExportSettings.IgnorePaging)
    {
        RadGrid1.AllowPaging = false;
        RadGrid1.Rebind();
    }
}

I think these two methods are self-describing. The logic for replacing the nbsp in the GetCellText is truly simple and generally it is not necessary to be extracted in a separate method, but I did that for clarity.

And last, but not least is the GenerateFile method. It will instantiate the corresponding provider and then send the file via the response.
private void GenerateFile(object structure, string fileName)
{
    Workbook workbook = structure as Workbook;
 
    byte[] output;
 
    using (MemoryStream ms = new MemoryStream())
    {
        if (workbook != null)
        {
            XlsxFormatProvider provider = new XlsxFormatProvider();
            provider.Export(workbook, ms);
        }
        ms.Position = 0;
        output = ms.ToArray();
    }
 
    Response.ContentType = "application/excel";
    Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
    Response.BinaryWrite(output);
    Response.Flush();
    Response.Close();
}

This method, as all of the methods below are stripped from the Word-specific code for simplicity. You can find the full code in the attached demos.

So, attached to this blog post are the following examples:
All of these examples are based on the DPL and should be a good starting point for you. Furthermore you may find the following resources helpful:
RadPdfProcessing overview
RadWordsProcessing overview
RadSpreadProcessing overview
RadEditor Import and Export to DOCX by using the Telerik Document Processing library
RadGrid DPL integration 

The Future

For Q3 2014 we will finish the full-blown integration of the DPL library with RadGrid, RadEditor, RadTreeList, and RadPivotGrid. You will be able to export any of these controls with a simple button click. Of course, the DPL is here to stay so it will still be available as a standalone component even when we finish the integration process, so if you prefer a fully custom approach you will still have the right tools at hand.

Questions?

As always, I would like to hear your opinion on this matter. Feel free to leave your feedback in the comment section below.

ASP.NET, Excel, Export, Telerik Document Processing Libraries, Web Forms
About the Author

Daniel Peichev

is Software Developer at one of Telerik's ASP.NET AJAX teams, where he primarily works on exporting functionality for RadGrid and RadTreeList. Daniel is interested in ASP.NET, Javascript, CSS and the cutting edge Microsoft technologies and products.

Related Posts

Release Mobile Desktop WPF WinForms WinUI .NET MAUI Xamarin

What’s New in R2 2023 with Telerik Desktop and Mobile Components

Comments

Comments are disabled in preview mode.