Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
70
Export igHierarchical Grid to excel using ig.Excel
posted

Hi,

We had a requirement in our project to export the IG Hierarchical grid data to excel worksheet with grouping. I found from the forum that IgniteUI JQuery control currently does not support export to excel for igHierarchical grid. Hence, I have created my own method to export the grid to excel. So, far I have got it working where I see the parent grid and child grid in the excel file. However, I also need the grouping indicator to expand/collapse the child grid rows. I tried using the ig.Excel.RowColumnBase class's method outlineLevel() and ie.excel.DisplayOptions class's showExpansionIndicatorBelowGroupedRows but none them are producing the required result.  Below is my excel export method...

function exportToExcel() {

var workbook = new $.ig.excel.Workbook($.ig.excel.WorkbookFormat.excel2007);
                var worksheet = workbook.worksheets().add('Sheet1');
                //var displayOptions = new $.ig.excel.DisplayOptions().showExpansionIndicatorBelowGroupedRows(true);
               // worksheet.displayOptions. = displayOptions;
                var rowBase = new $.ig.excel.RowColumnBase();
                var xlRowIndex = 0;
                var headersTable = $("#igdataGrid").igGrid("headersTable")[0];
                for (var headerRowIndex = 0;
                headerRowIndex < headersTable.rows.length - 2;
                headerRowIndex++, xlRowIndex++) {
                    var headerRow = headersTable.rows[headerRowIndex];
                    var xlHeaderRow = worksheet.rows(xlRowIndex);
                    var computedStyle = window.getComputedStyle(headerRow);
                    var xlColorInfo = new $.ig.excel.WorkbookColorInfo(computedStyle.color);
                    xlHeaderRow.cellFormat().font().colorInfo(xlColorInfo);
                    for (var headerCellIndex = 0;
                    headerCellIndex < headerRow.cells.length;
                    headerCellIndex++) {
                        var headerCell = headerRow.cells[headerCellIndex];
                        worksheet.columns(headerCellIndex).setWidth(
                        headerCell.offsetWidth, $.ig.excel.WorksheetColumnWidthUnit.pixel);
                        var xlHeaderCell = xlHeaderRow.cells(headerCellIndex);
                        var computedStyle = window.getComputedStyle(headerCell);
                        xlHeaderCell.cellFormat().fill(
                        $.ig.excel.CellFill.createSolidFill(computedStyle.backgroundColor));
                        xlHeaderCell.value($(headerCell).text());
                    }
                }
                var rows = $("#igdataGrid").igGrid("rows");
                
                var childGridTable, childHeaderRow;
                for (var dataRowIndex = 0;
                dataRowIndex < rows.length;
                dataRowIndex++, xlRowIndex++) {
                    var dataRow = rows[dataRowIndex];
                    var xlRow = worksheet.rows(xlRowIndex);
                    rowBase.index = xlRowIndex;                      rowBase.outlineLevel(1);
                    for (var dataCellIndex = 0;
                    dataCellIndex < dataRow.cells.length;
                    dataCellIndex++) {
                        var dataCell = dataRow.cells[dataCellIndex];
                        xlRow.setCellValue(dataCellIndex, $(dataCell).text());
                    }
                    
                    //expand the first child grid to get the headers name.
                    var parentGrid = $("#igdataGrid").igHierarchicalGrid("rootWidget"),
                        rowDomElement = parentGrid.rowAt(dataRowIndex);
                    $("#igdataGrid").igHierarchicalGrid("expand", rowDomElement);
                    childGridTable = $("#igdataGrid").igHierarchicalGrid("allChildren")[dataRowIndex];
                    //childHeaderRow = childHeadersTable.find("thead").find('span.ui-iggrid-headertext');                                           
                                        
                    //extract all the childgrid data
                    //var childRows = $("#igdataGrid").find("table[data-childgrid=true]").data().igGrid.dataSource.data();
                    
                    //$.ig.excel.DisplayOptions.showExpansionIndicatorBelowGroupedRows(true);
                    var childHeadersTable = $(childGridTable).igGrid("headersTable")[0];
                    childRows = $(childGridTable).igGrid("rows");
 
                    if (childRows.length > 0) {                        
                        xlRowIndex++;
                        //write the child header row                        
                        var headerRow = childHeadersTable.rows[0];
                        var xlHeaderRow = worksheet.rows(xlRowIndex);
                         rowBase.index = xlRowIndex;                          rowBase.outlineLevel(2);
                        var computedStyle = window.getComputedStyle(headerRow);
                        var xlColorInfo = new $.ig.excel.WorkbookColorInfo(computedStyle.color);
                        xlHeaderRow.cellFormat().font().colorInfo(xlColorInfo);
                        for (var headerCellIndex = 0;
                        headerCellIndex < headerRow.cells.length;
                        headerCellIndex++) {
                            var headerCell = headerRow.cells[headerCellIndex];
                            //worksheet.columns(headerCellIndex + 1).setWidth(
                            //headerCell.offsetWidth, $.ig.excel.WorksheetColumnWidthUnit.pixel);
                            var xlHeaderCell = xlHeaderRow.cells(headerCellIndex + 1);
                            var computedStyle = window.getComputedStyle(headerCell);
                            xlHeaderCell.cellFormat().fill(
                            $.ig.excel.CellFill.createSolidFill(computedStyle.backgroundColor));
                            xlHeaderCell.value($(headerCell).text());
                        }
                        
 
                        xlRowIndex++;
                        for (var index = 0;
                            index < childRows.length;
                            index++, xlRowIndex++) {
                            var childRow = childRows[index];
                            var xlRow = worksheet.rows(xlRowIndex);
                            rowBase.index = xlRowIndex;                              rowBase.outlineLevel(2);
                            for (var childGridCellIndex = 0;
                            childGridCellIndex < childRow.cells.length;
                            childGridCellIndex++) {
                                var dataCell = childRow.cells[childGridCellIndex];
                                xlRow.setCellValue(childGridCellIndex + 1, $(dataCell).text());
                            }
                        }
                    }
 
                    $("#igdataGrid").igHierarchicalGrid("collapse", rowDomElement);
                }
                workbook.save({ type: 'blob' }, function (data) {
                    saveAs(data, "igGrid.xlsx");
                }, function (error) {
                    alert('Error exporting: : ' + error);
                });
     }

I would really appreciate your help on this.
Thanks.

 

Parents Reply Children
No Data