Export igHierarchical Grid to excel using ig.Excel

Hiren shah posted over 8 years ago

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.

Top Replies

16310

Offline Hristo Anastasov posted over 8 years ago +1 suggested
Hi Sireh, It should be enough to just use xlRow.outlineLevel(indent) where indent = 1 for the first level child bands and increase it for every next...view reply »

Parents

16310

Suggested Answer
Offline Hristo Anastasov posted over 8 years ago

Hi Sireh,

It should be enough to just use xlRow.outlineLevel(indent) where indent = 1 for the first level child bands and increase it for every next hierarchy level. Anyway, you are working with the grid DOM and this is causing some issues - for example in the for loops where you refer the datacell the first cell in the <tr> is empty ( in the grid it contains only expand indicators). I suggest that you do not work with these, so you may modify your for loops to look like:

                    for (var dataCellIndex = 0;
                    dataCellIndex < dataRow.cells.length - 1;
                    dataCellIndex++) {
                        var dataCell = dataRow.cells[dataCellIndex+1];
                        xlRow.setCellValue(dataCellIndex, $(dataCell).text());
                    }

I have modified your sample and the exported sheet have expanding indicators that work. It needs further improving, but I did not want to hold the response so I'm sending you my progress so far. Please let me know if the result is ok and if so the approach can be further modified so that the child grid headers could be collapsed too.

ighier.zip
Cancel
Reply

Reject Answer

Cancel

Verified Answer

Offline Hiren shah posted over 8 years ago in reply to Hristo Anastasov

Hi Hristo,

Thanks for your help. I was able to export using your modified code. As you already mentioned there were few issues with the code. Here is the modified version.

function exportGrid() {
                //$.ig.GridExcelExporter.export($("#igdataGrid"), { fileName: "Manage_Generator_Mapping" }, {
                //    rowExported: function (sender, args) {
                        
                    //}
                //});              
                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-1;
                    headerCellIndex++) {
                        var headerCell = headerRow.cells[headerCellIndex + 1];
                         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);
                    xlRow.outlineLevel(0);
                    for (var dataCellIndex = 0;
                    dataCellIndex < dataRow.cells.length-1;
                    dataCellIndex++) {
                        var dataCell = dataRow.cells[dataCellIndex+1];
                        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);
                        xlHeaderRow.outlineLevel(1);
                        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);
                            xlRow.outlineLevel(1);
                            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, "Manage_Generator_Mapping.xlsx");
                }, function (error) {
                    alert('Error exporting: : ' + error);
                });}

16310

Offline Hristo Anastasov posted over 8 years ago in reply to Hiren shah

Thank you for your feedback, I am glad you have managed to resolve your issue. Please let me know if I may be of any further assistance to you.
Cancel
Reply

Cancel

Reply

70

Offline Hiren shah posted over 8 years ago in reply to Hristo Anastasov

Hi,

I made changes suggested by you and it worked on one page. But on the other page where I have same hierarchical grid, it is throwing some exception on

var workbook = new $.ig.excel.Workbook($.ig.excel.WorkbookFormat.excel2007);
var worksheet = workbook.worksheets().add('Sheet1'); //Error on this line -- Uncaught Sys.ParameterCountException: Sys.ParameterCountException: Parameter count mismatch.

Please help me out here.

Thanks,

Hiren.
Cancel
Reply

Cancel

Children

70

Offline Hiren shah posted over 8 years ago in reply to Hiren shah

Never mind, I found the solution. It was the script manager which was causing this.
Cancel
Reply

Cancel