Search results

Excel Export in ASP.NET Core Tree Grid control

24 Sep 2021 / 3 minutes to read

The excel export allows exporting TreeGrid data to Excel document. You need to use the excelExport method for exporting. To enable Excel export in the treegrid, set the allowExcelExport as true.

tagHelper
export.cs
Copied to clipboard
<ejs-treegrid id="TreeGrid" dataSource="@ViewBag.datasource" height="220" childMapping="Children" treeColumnIndex="1" toolbar="@(new List<string>() { "ExcelExport"})" allowExcelExport="true" allowPaging="true" toolbarClick="toolbarClick">
    <e-treegrid-pagesettings pageSize="7"></e-treegrid-pagesettings>
    <e-treegrid-columns>
        <e-treegrid-column field="TaskId" headerText="Task ID" textAlign="Right" width="90"></e-treegrid-column>
        <e-treegrid-column field="TaskName" headerText="Task Name" width="180"></e-treegrid-column>
        <e-treegrid-column field="StartDate" headerText="Start Date" textAlign="Right" format="yMd" type="date" width="90"></e-treegrid-column>
        <e-treegrid-column field="Duration" headerText="Duration" textAlign="Right" width="80"></e-treegrid-column>
    </e-treegrid-columns>
</ejs-treegrid>

<script>
    function toolbarClick(args) {
        if (args['item'].text === 'Excel Export') {
            var treegrid = document.getElementById("TreeGrid").ej2_instances[0];
            treegrid.excelExport();
        }
    }
</script>
Copied to clipboard
public IActionResult Index()
{
    var tree = TreeData.GetDefaultData();
    ViewBag.datasource = tree;
    return View();
}

To customize excel export

The excel export provides an option to customize mapping of the treegrid to excel document.

Export hidden columns

The excel export provides an option to export hidden columns of treegrid by defining includeHiddenColumn as true.

tagHelper
hidden-column.cs
Copied to clipboard
<ejs-treegrid id="TreeGrid" dataSource="@ViewBag.datasource" height="220" childMapping="Children" treeColumnIndex="1" toolbar="@(new List<string>() { "ExcelExport"})" allowExcelExport="true" allowPaging="true" toolbarClick="toolbarClick">
    <e-treegrid-pagesettings pageSize="7"></e-treegrid-pagesettings>
    <e-treegrid-columns>
        <e-treegrid-column field="TaskId" headerText="Task ID" textAlign="Right" width="90"></e-treegrid-column>
        <e-treegrid-column field="TaskName" headerText="Task Name" width="180"></e-treegrid-column>
        <e-treegrid-column field="StartDate" headerText="Start Date" textAlign="Right" format="yMd" type="date" width="90"></e-treegrid-column>
        <e-treegrid-column field="Duration" headerText="Duration" textAlign="Right" width="80" visible="false"></e-treegrid-column>
    </e-treegrid-columns>
</ejs-treegrid>

<script>
    function toolbarClick(args) {
        if (args['item'].text === 'Excel Export') {
            var excelExportProperties = {
                includeHiddenColumn: true
            };
            var treegrid = document.getElementById("TreeGrid").ej2_instances[0];
            treegrid.excelExport(excelExportProperties);
        }
    }
</script>
Copied to clipboard
public IActionResult Index()
{
    var tree = TreeData.GetDefaultData();
    ViewBag.datasource = tree;
    return View();
}

Show or Hide columns on Exported Excel

You can show a hidden column or hide a visible column while printing the treegrid using toolbarClick and excelExportComplete events.

In the toolbarClick event, based on args.item.text as Excel Export. We can show or hide columns by setting visible property of e-treegrid-column to true or false respectively.

In the excelExportComplete event, We have reversed the state back to the previous state.

In the below example, we have Duration as a hidden column in the treegrid. While exporting, we have changed Duration to visible column and StartDate as hidden column.

tagHelper
show-hide.cs
Copied to clipboard
<ejs-treegrid id="TreeGrid" dataSource="@ViewBag.datasource" height="220" childMapping="Children" treeColumnIndex="1" toolbar="@(new List<string>() { "ExcelExport"})" allowExcelExport="true" allowPaging="true" toolbarClick="toolbarClick" excelExportComplete="excelExportComplete">
    <e-treegrid-pagesettings pageSize="7"></e-treegrid-pagesettings>
    <e-treegrid-columns>
        <e-treegrid-column field="TaskId" headerText="Task ID" textAlign="Right" width="90"></e-treegrid-column>
        <e-treegrid-column field="TaskName" headerText="Task Name" width="180"></e-treegrid-column>
        <e-treegrid-column field="StartDate" headerText="Start Date" textAlign="Right" format="yMd" type="date" width="90"></e-treegrid-column>
        <e-treegrid-column field="Duration" headerText="Duration" textAlign="Right" width="80" visible="false"></e-treegrid-column>
    </e-treegrid-columns>
</ejs-treegrid>

<script>
    function toolbarClick(args) {
        if (args['item'].text === 'Excel Export') {
            var treegrid = document.getElementById("TreeGrid").ej2_instances[0];
            var cols = treegrid.grid.columns;
            cols[2].visible = false;
            cols[3].visible = true;
            treegrid.excelExport();
        }
    }
    function excelExportComplete(args) {
        var treegrid = document.getElementById("TreeGrid").ej2_instances[0];
        var cols = treegrid.grid.columns;
        cols[3].visible = false;
        cols[2].visible = true;
    }
</script>
Copied to clipboard
public IActionResult Index()
{
    var tree = TreeData.GetDefaultData();
    ViewBag.datasource = tree;
    return View();
}

Conditional Cell Formatting

TreeGrid cells in the exported Excel can be customized or formatted using excelQueryCellInfo event. In this event, we can format the treegrid cells of exported PDF document based on the column cell value.

In the below sample, we have set the background color for Duration column in the exported excel by args.cell and backgroundColor property.

tagHelper
conditional-cell.cs
Copied to clipboard
<ejs-treegrid id="TreeGrid" dataSource="@ViewBag.datasource" height="220" childMapping="Children" treeColumnIndex="1" toolbar="@(new List<string>() { "ExcelExport"})" allowExcelExport="true" allowPaging="true" toolbarClick="toolbarClick" excelQueryCellInfo="excelQueryCellInfo" queryCellInfo="queryCellInfo">
    <e-treegrid-pagesettings pageSize="7"></e-treegrid-pagesettings>
    <e-treegrid-columns>
        <e-treegrid-column field="TaskId" headerText="Task ID" textAlign="Right" width="90"></e-treegrid-column>
        <e-treegrid-column field="TaskName" headerText="Task Name" width="180"></e-treegrid-column>
        <e-treegrid-column field="StartDate" headerText="Start Date" textAlign="Right" format="yMd" type="date" width="90"></e-treegrid-column>
        <e-treegrid-column field="Duration" headerText="Duration" textAlign="Right" width="80"></e-treegrid-column>
    </e-treegrid-columns>
</ejs-treegrid>

<script>
    function toolbarClick(args) {
        if (args['item'].text === 'Excel Export') {
            var treegrid = document.getElementById("TreeGrid").ej2_instances[0];
            treegrid.excelExport();
        }
    }
    function queryCellInfo(args) {
        if (args.data['Duration'] == 0 && args.column.field === 'Duration') {
            args.cell.style.background = '#336c12';
        } else if (args.data['Duration'] < 3 && args.column.field === 'Duration') {
            args.cell.style.background = '#7b2b1d';
        }

    }
    function excelQueryCellInfo(args) {
        if (args.column.field == 'Duration') {
            if (args.value === 0 || args.value === "") {
                args.style = { backColor: '#336c12' };
            }
            else if (args.value < 3) {
                args.style = { backColor: '#7b2b1d' };
            }
        }

    }
</script>
Copied to clipboard
public IActionResult Index()
{
    var tree = TreeData.GetDefaultData();
    ViewBag.datasource = tree;
    return View();
}

Theme

The excel export provides an option to include theme for exported excel document.

To apply theme in exported Excel, define the theme in exportProperties.

tagHelper
theme.cs
Copied to clipboard
<ejs-treegrid id="TreeGrid" dataSource="@ViewBag.datasource" height="220" childMapping="Children" treeColumnIndex="1" toolbar="@(new List<string>() { "ExcelExport"})" allowExcelExport="true" allowPaging="true" toolbarClick="toolbarClick">
    <e-treegrid-pagesettings pageSize="7"></e-treegrid-pagesettings>
    <e-treegrid-columns>
        <e-treegrid-column field="TaskId" headerText="Task ID" textAlign="Right" width="90"></e-treegrid-column>
        <e-treegrid-column field="TaskName" headerText="Task Name" width="180"></e-treegrid-column>
        <e-treegrid-column field="StartDate" headerText="Start Date" textAlign="Right" format="yMd" type="date" width="90"></e-treegrid-column>
        <e-treegrid-column field="Duration" headerText="Duration" textAlign="Right" width="80"></e-treegrid-column>
    </e-treegrid-columns>
</ejs-treegrid>


<script>
    function toolbarClick(args) {
        if (args['item'].text === 'Excel Export') {
            var exportProperties = {
                theme:
                {
                    header: { fontName: 'Segoe UI', fontColor: '#666666' },
                    record: { fontName: 'Segoe UI', fontColor: '#666666' },
                    caption: { fontName: 'Segoe UI', fontColor: '#666666' }
                }
            };
            var treegrid = document.getElementById("TreeGrid").ej2_instances[0];
            treegrid.excelExport(exportProperties);
        }
    }

</script>
Copied to clipboard
public IActionResult Index()
{
    var tree = TreeData.GetDefaultData();
    ViewBag.datasource = tree;
    return View();
}

By default, material theme is applied to exported excel document.

The excel export provides an option to include header and footer content for exported excel document.

tagHelper
header-footer.cs
Copied to clipboard
<ejs-treegrid id="TreeGrid" dataSource="@ViewBag.datasource" height="220" childMapping="Children" treeColumnIndex="1" toolbar="@(new List<string>() { "ExcelExport"})" allowExcelExport="true" allowPaging="true" toolbarClick="toolbarClick">
    <e-treegrid-pagesettings pageSize="7"></e-treegrid-pagesettings>
    <e-treegrid-columns>
        <e-treegrid-column field="TaskId" headerText="Task ID" textAlign="Right" width="90"></e-treegrid-column>
        <e-treegrid-column field="TaskName" headerText="Task Name" width="180"></e-treegrid-column>
        <e-treegrid-column field="StartDate" headerText="Start Date" textAlign="Right" format="yMd" type="date" width="90"></e-treegrid-column>
        <e-treegrid-column field="Duration" headerText="Duration" textAlign="Right" width="80"></e-treegrid-column>
    </e-treegrid-columns>
</ejs-treegrid>

<script>
    function toolbarClick(args) {
        if (args['item'].text === 'Excel Export') {
            var excelExportProperties = {
                header: {
                    headerRows: 7,
                    rows: [
                        { cells: [{ colSpan: 4, value: "Northwind Traders", style: { fontColor: '#C67878', fontSize: 20, hAlign: 'Center', bold: true, } }] },
                        { cells: [{ colSpan: 4, value: "2501 Aerial Center Parkway", style: { fontColor: '#C67878', fontSize: 15, hAlign: 'Center', bold: true, } }] },
                        { cells: [{ colSpan: 4, value: "Suite 200 Morrisville, NC 27560 USA", style: { fontColor: '#C67878', fontSize: 15, hAlign: 'Center', bold: true, } }] },
                        { cells: [{ colSpan: 4, value: "Tel +1 888.936.8638 Fax +1 919.573.0306", style: { fontColor: '#C67878', fontSize: 15, hAlign: 'Center', bold: true, } }] },
                        { cells: [{ colSpan: 4, hyperlink: { target: 'https://www.northwind.com/', displayText: 'www.northwind.com' }, style: { hAlign: 'Center' } }] },
                        { cells: [{ colSpan: 4, hyperlink: { target: 'mailto:support@northwind.com' }, style: { hAlign: 'Center' } }] },
                    ]
                },
                footer: {
                    footerRows: 4,
                    rows: [
                        { cells: [{ colSpan: 4, value: "Thank you for your business!", style: { hAlign: 'Center', bold: true } }] },
                        { cells: [{ colSpan: 4, value: "!Visit Again!", style: { hAlign: 'Center', bold: true } }] }
                    ]
                },
            };
            var treegrid = document.getElementById("TreeGrid").ej2_instances[0];
            treegrid.excelExport(excelExportProperties);
        }
    }
</script>
Copied to clipboard
public IActionResult Index()
{
    var tree = TreeData.GetDefaultData();
    ViewBag.datasource = tree;
    return View();
}

File Name for Exported document

You can assign the file name for the exported document by defining fileName property in ExcelExportProperties.

tagHelper
file-name.cs
Copied to clipboard
<ejs-treegrid id="TreeGrid" dataSource="@ViewBag.datasource" height="220" childMapping="Children" treeColumnIndex="1" toolbar="@(new List<string>() { "ExcelExport"})" allowExcelExport="true" allowPaging="true" toolbarClick="toolbarClick">
    <e-treegrid-pagesettings pageSize="7"></e-treegrid-pagesettings>
    <e-treegrid-columns>
        <e-treegrid-column field="TaskId" headerText="Task ID" textAlign="Right" width="90"></e-treegrid-column>
        <e-treegrid-column field="TaskName" headerText="Task Name" width="180"></e-treegrid-column>
        <e-treegrid-column field="StartDate" headerText="Start Date" textAlign="Right" format="yMd" type="date" width="90"></e-treegrid-column>
        <e-treegrid-column field="Duration" headerText="Duration" textAlign="Right" width="80"></e-treegrid-column>
    </e-treegrid-columns>
</ejs-treegrid>

<script>
    function toolbarClick(args) {
        if (args['item'].text === 'Excel Export') {
            var excelExportProperties = {
                fileName: "new.xlsx"
            };
            var treegrid = document.getElementById("TreeGrid").ej2_instances[0];
            treegrid.excelExport(excelExportProperties);
        }
    }
</script>
Copied to clipboard
public IActionResult Index()
{
    var tree = TreeData.GetDefaultData();
    ViewBag.datasource = tree;
    return View();
}

To persist collapsed state

You can persist the collapsed state in the exported document by defining isCollapsedStatePersist property as true in TreeGridExcelExportProperties parameter of excelExport method.

tagHelper
is-collapsed.cs
Copied to clipboard
<ejs-treegrid id="TreeGrid" dataSource="@ViewBag.datasource" height="220" childMapping="Children" treeColumnIndex="1" toolbar="@(new List<string>() { "ExcelExport"})" allowExcelExport="true" allowPaging="true" toolbarClick="toolbarClick">
    <e-treegrid-pagesettings pageSize="7"></e-treegrid-pagesettings>
    <e-treegrid-columns>
        <e-treegrid-column field="TaskId" headerText="Task ID" textAlign="Right" width="90"></e-treegrid-column>
        <e-treegrid-column field="TaskName" headerText="Task Name" width="180"></e-treegrid-column>
        <e-treegrid-column field="StartDate" headerText="Start Date" textAlign="Right" format="yMd" type="date" width="90"></e-treegrid-column>
        <e-treegrid-column field="Duration" headerText="Duration" textAlign="Right" width="80"></e-treegrid-column>
    </e-treegrid-columns>
</ejs-treegrid>

<script>
    function toolbarClick(args) {
        if (args['item'].text === 'Excel Export') {
            var excelExportProperties = {
              isCollapsedStatePersist: true
            };
            var treegrid = document.getElementById("TreeGrid").ej2_instances[0];
            treegrid.excelExport(excelExportProperties);
        }
    }
</script>
Copied to clipboard
public IActionResult Index()
{
    var tree = TreeData.GetDefaultData();
    ViewBag.datasource = tree;
    return View();
}

You can refer to our ASP.NET Core Tree Grid feature tour page for its groundbreaking feature representations. You can also explore our ASP.NET Core Tree Grid example ASP.NET Core Tree Grid example to knows how to present and manipulate data.