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

RadGrid: Export to .xlsx Cell styling

1 Answer 305 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Nabil
Top achievements
Rank 1
Nabil asked on 29 Jan 2020, 11:32 AM

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 

 

1 Answer, 1 is accepted

Sort by
0
Attila Antal
Telerik team
answered on 03 Feb 2020, 09:59 AM

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.

 

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

Get quickly onboarded and successful with UI for ASP.NET AJAX with the Virtual Classroom technical trainings, available to all active customers. Learn More.
Tags
Grid
Asked by
Nabil
Top achievements
Rank 1
Answers by
Attila Antal
Telerik team
Share this question
or