RadGrid: Export to .xlsx Cell styling

[Nabil]

Hi Team,
We are experiencing problem with applying partial cell styling  after exporting .xlsx. (making part of a string bold)

Example if Table.Cells[1,1] = "Filter By Physicians : Andrew Small (1000316817),Andrew Wise (1000330560),Anthony Alston (1000411264),Anthony Holden (1000182861),Anthony Kane (1000521565),Brandon Logan"

we want to make "Filter By Physicians" bold as shown in above string.

 

we are using below method for formatting cells.

protected void RadGridQMList_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{

table.Cells[c, r].Value = "test";

}

Method to export xlsx

RadGridQMList.ExportSettings.Excel.Format = GridExcelExportFormat.Xlsx;

Kindly let us know how to solve this issue. We will wait for your reply.

Thanks and Regards,
Jaswant 

 

[Attila Antal]

Hi Jaswant ,

I have also replied to your support ticket addressing this question. 

Here are a few articles that will help you get a better understanding on Styling and Export to XLSX.

• Styling Columns / Rows / Cell - Export XLSX
• Understanding Telerik AJAX and Document Processing Library Spreadsheet Workbook and Worksheets
• Telerik Document Processing
• Using XlsxFormatProvider
• Grid - Export Multiple Grids - online demo

 

The code below will help you get started and following the instructions from the above-linked articles, you will be able to implement the required styling and more.

 

protected void RadGrid1_InfrastructureExporting(object sender, GridInfrastructureExportingEventArgs e)
{
    /* 
    * Add the following using statements
    * 
    * using DPL = Telerik.Windows.Documents.Spreadsheet.Model;
    * using AJAXStructure = Telerik.Web.UI.ExportInfrastructure;
    * using DPLProvider = Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;
    * 
    */

    // Get reference to ExportStructure
    AJAXStructure.ExportStructure exportStructure = e.ExportStructure;
    // Structure table
    AJAXStructure.Table table = exportStructure.Tables[0];

    // Reference the Rows collection
    AJAXStructure.RowCollection rows = table.Rows;

    // loop through the rows. Indexing in exporting starts with 1. Row[1] = header, cell[1, rowIndex] accesses the first column, etc..
    for (int i = 1; i <= rows.Count; i++)
    {
        if (i == 1) continue; // skip header row

        AJAXStructure.Row row = rows[i];
        AJAXStructure.CellCollection cells = row.Cells;

        foreach (AJAXStructure.Cell cell in cells)
        {
            // strip the content
            cell.Value = "Some text goes here";
            cell.Style.Font.Bold = true;
        }
    }

    // access cells by index

    int columnIndex = 0;
    int rowIndex = 0;

    AJAXStructure.Cell cellByIndex = table.Cells[columnIndex, rowIndex];

    cellByIndex.Value = "This is a cell accessed by index";


    // Access cells by Excel column names
    table.Cells["A2"].Value = "BRANCHID";
    table.Cells["B2"].Value = "PCODE";
    table.Cells["C2"].Value = "LOCALITY";
    table.Cells["D2"].Value = "STATE";
    table.Cells["E2"].Value = "DELIVERY_ENABLED";


    // Convert Telerik.Web.UI.ExportStructure object to Telerik DPL Document object

    // Render bytes from ExprotStructure
    AJAXStructure.XlsxRenderer xlsxRenderer = new AJAXStructure.XlsxRenderer(exportStructure);

    // Create a new DPL XLS Provider object
    DPLProvider.XlsxFormatProvider xlsxProvider = new DPLProvider.XlsxFormatProvider();

    // Use the Provider to import bytes to DPL WorkBook
    DPL.Workbook dplWorkBook = xlsxProvider.Import(xlsxRenderer.Render());

    // Use the Telerik Document Processing instructions to format the Workbook and its content


    // Prepare the data to be exported
    byte[] data = null;
    using (MemoryStream ms = new MemoryStream())
    {
        xlsxProvider.Export(dplWorkBook, ms);
        data = ms.ToArray();
    }

    // send the data in the response for download
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.Headers.Remove("Content-Disposition");
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + RadGrid1.ExportSettings.FileName + ".xlsx");
    Response.BinaryWrite(data);
    Response.End();
}

 

Kind Regards,
Attila Antal
Progress Telerik