Kendo Grid - export to excel with grouping

[Kirubakaran]

This is my code

1. I want to export master & its related child record to excel and its working fine if i didnt use grouping. but whereas if i add grouping, then its not working. 

2. Also how to remove the header in child records while exporting to excel.

Expecting your help.

[Silviya Stoyanova]

Hello  Kirubakaran,

Thank you for the provided code.

As you could see from the following Demo Excel Export with grouping is available out of the box. From your code, I saw you are also using custom logic for exporting Grid with detailed templates, which as you know is not supported and it is a known limitation - https://docs.telerik.com/kendo-ui/controls/data-management/grid/export/excel-export#known-limitations.

The problem with the above combination is the data structure. It changes depending on if it has group items or not. With group:

 

masterData[rowIndex].CommentsDescription   //gets "undefined"

 

This means that you will need to modify the custom implementation to get the CommentsDescription content. For instance, something like that:

for (var rowIndex = 0; rowIndex < masterData.length; rowIndex++) {
                    var detail = masterData[rowIndex].CommentsDescription ? masterData[rowIndex].CommentsDescription : masterData[rowIndex].items.map(x=>x.CommentsDescription).join(' ');
                  exportChildData(detail, rowIndex);
}

I hope that would help you to move forward with the customization.

Kind Regards,
Silviya Stoyanova
Progress Telerik

[Code]

Can we do the grouped exporting thing on server side? I am unable to find some reference.

[Nikolay]

Hi Code,

Please take a look at the following KB article that utilizes the Document Processing Library to generate an Excel file on the server:

https://docs.telerik.com/kendo-ui/knowledge-base/grid-excel-export-server 

This is the same approach used in the MVC server export demo.

Regards,

Nikolay

[Kirubakaran]

Here is my updated code.

As per your guidance i did few changes and just one issue i am facing.

As you can see in my coding, i applied two for-loops and processed. Here i am not able to skip the group header in my for-loop, due to that the child rows are placing a row upwards (which is incorrect).

How can i find the group header row and skip it to achieve my output?

Looking forward your help on this.

[Kirubakaran]

Silviya Stoyanova,

I found the answer and updated the code to skip the group header in for-loop  :)

Expecting just one solution now.I want to show my Child records below "Comments" column.

Looking forward your help on this.

 

[Silviya Stoyanova]

Hello  Kirubakaran,

I am glad to read you gain progress.

Every row has its type field: 

• "header"
• "footer"
• "group-header"
• "group-footer"
• "data"

So you could find the group-header row like that:

var sheet = e.workbook.sheets[0];
var row = sheet.rows[rowIndex];
           
    if(row.type =="group-header"){
                  //your code

 

To place the description under the Comment column you need to prepend enough cells:

// Prepend empty cells to each row.
for (var ci = 0; ci < sheet.rows.length; ci++) {
                      if (sheet.rows[ci].cells[0].value) {
                        sheet.rows[ci].cells.unshift({},{},{},{},{});
                      }
 }                

 

Kind Regards,
Silviya Stoyanova
Progress Telerik

[Code]

I am doing server side export, but in the "columnsdata" I am not able to get the Fields and other info  of the grouped columns.

[HttpPost]
public FileStreamResult ExportServer(string model, string data)
{
    var columnsData = JsonConvert.DeserializeObject<IList<ExportColumnSettings>>(HttpUtility.UrlDecode(model));
    dynamic options = JsonConvert.DeserializeObject(HttpUtility.UrlDecode(data));
    SpreadDocumentFormat exportFormat = options.format.ToString() == "csv" ? exportFormat = SpreadDocumentFormat.Csv : exportFormat = SpreadDocumentFormat.Xlsx;
    Action<ExportCellStyle> cellStyle = new Action<ExportCellStyle>(ChangeCellStyle);
    Action<ExportRowStyle> rowStyle = new Action<ExportRowStyle>(ChangeRowStyle);
    Action<ExportColumnStyle> columnStyle = new Action<ExportColumnStyle>(ChangeColumnStyle);

    var titlesToExclude = new HashSet<string> { "Primary", "Primary Benefits", "Secondary", "Address", "Client Invoice Address"};

    string fileName = string.Format("{0}.{1}", options.title, options.format);
    string mimeType = MimeMapping.GetMimeMapping(exportFormat.ToString());

    if (columnsData.Any())
    {
        columnsData = columnsData.Skip(1).ToList();
    }

    columnsData = columnsData.Where(column => !titlesToExclude.Contains(column.Title)).ToList();

    //foreach (var column in columnsData)
    //{
    //    if (column.Width == null || column.Width.Equals(new Unit()))
    //    {
    //        column.Width = new Unit("100px");
    //    }
    //}

    var data2 = Read();
    if (data2 == null || !data2.Any())
    {
        throw new InvalidOperationException("Data model should be provided");
    }

    var exportStream =
        data2.ToXlsxStream(columnsData, (string)options.title.ToString(), cellStyleAction: cellStyle, rowStyleAction: rowStyle, columnStyleAction: columnStyle);

    //var exportStream =
    //    data2.ToXlsxStream(testColumnsData, testOptions.title.ToString(), cellStyleAction: cellStyle, rowStyleAction: rowStyle, columnStyleAction: columnStyle);

    var fileStreamResult = new FileStreamResult(exportStream, mimeType);
    fileStreamResult.FileDownloadName = fileName;
    fileStreamResult.FileStream.Seek(0, SeekOrigin.Begin);

    return fileStreamResult;
}