Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Excel Export Enhancements #7294

Open
IgnatovDan opened this issue Mar 15, 2019 · 2 comments

Comments

Projects
None yet
3 participants
@IgnatovDan
Copy link
Contributor

commented Mar 15, 2019

The Problem

The current DataGrid export implementation does not allow you to customize Excel files in the following ways:

  • add a header and footer
  • add boolean values, images, rich texts, formulas, hyperlinks
  • format values
  • border a cell
  • add new worksheets
  • customize the workbook or worksheet
  • export several widgets into one file

The Proposed Solution

We plan to use the third-party ExcelJS library for Excel file customization and add the 'exportDataGrid' utility for exporting the DataGrid.

Note: At the moment, we are testing the proposed solution and want to collect feedback and find out whether this solution covers most scenarios. After we finish the research, we will decide whether to reimplement the DataGrid's export in a similar way.

Add header and footer

let dataGridOptions = {
    onExporting: e => {
        var workbook = new ExcelJS.Workbook();    
        var worksheet = workbook.addWorksheet('Main sheet');
      
        exportDataGrid({
            workbook, worksheet, dataGrid: e.component, topLeftCell: { row: 5, column: 1 },
            saveEnabled: false
        }).then(function(dataGridRange) {
            Object.assign(
            worksheet.getRow(2).getCell(2),
            { value: "My Header", font: { bold: true, size: 16, underline: 'double' } }
            );

            Object.assign(
            worksheet.getRow(dataGridRange.to.row + 2).getCell(2),
            { value: "My Footer", font: { bold: true, size: 16, underline: 'double' } }
            );
            return Promise.resolve();
        }).then(function() {
            return workbook.xlsx.writeBuffer();
        }).then(function(buffer) { 
            saveAs(new Blob([buffer], { type: "application/octet-stream" }), "DataGrid.xlsx"); 
        });
        e.cancel = true; 
    } 
};  

header_footer_excel_screen

Export two grids into one worksheet

let buttonOptions = {
    text: "Export Grids",
    onClick: function() {
        var dataGrid1 = $("#gridContainer1").dxDataGrid("instance");
        var dataGrid2 = $("#gridContainer2").dxDataGrid("instance");
        var workbook = new ExcelJS.Workbook();    
        var worksheet = workbook.addWorksheet('Main sheet');

        Object.assign(
            worksheet.getRow(2).getCell(2),
            { value: "Costs:", font: { bold: true, size: 16, underline: 'double' } }
        );
        Object.assign(
            worksheet.getRow(2).getCell(7),
            { value: "Ratings:", font: { bold: true, size: 16, underline: 'double' } }
        );

        exportDataGrid({
            worksheet, dataGrid: dataGrid1, topLeftCell: { row: 4, column: 2 },
            saveEnabled: false
        }).then(function(dataGridRange) {
            return exportDataGrid({
            worksheet, dataGrid: dataGrid2, topLeftCell: { row: 4, column: (dataGridRange.to.column + 2) },
            saveEnabled: true, workbook
            });
        });
    }
};

two_grids_one_sheet_excel_screen

Export grids into separate worksheets

let buttonOptions = {
    text: "Export Grids",
    onClick: function() {
        var dataGrid1 = $("#gridContainer1").dxDataGrid("instance");
        var dataGrid2 = $("#gridContainer2").dxDataGrid("instance");
        var workbook = new ExcelJS.Workbook();    
        var worksheet1 = workbook.addWorksheet('Costs');
        var worksheet2 = workbook.addWorksheet('Ratings');

        Object.assign(
            worksheet1.getRow(2).getCell(2),
            { value: "Costs:", font: { bold: true, size: 16, underline: 'double' } }
        );
        Object.assign(
            worksheet2.getRow(2).getCell(2),
            { value: "Ratings:", font: { bold: true, size: 16, underline: 'double' } }
        );

        exportDataGrid({
            worksheet: worksheet1, dataGrid: dataGrid1, topLeftCell: { row: 4, column: 2 },
            saveEnabled: false
        }).then(function() {
            return exportDataGrid({
            worksheet: worksheet2, dataGrid: dataGrid2, topLeftCell: { row: 4, column: 2 },
            saveEnabled: true, workbook
            });
        });
    }
};  

two_grids_two_sheets_excel_screen

Implementation Details

ExcelJS library

ExcelJS is a library for reading, manipulating, and writing spreadsheet data and styles to Excel and JSON. See ExcelJS: Browser for more information.

FileSaver library is used

FileSaver is a library for saving files on a client. See FileSaver.js for more information.

Try It

Live Sandboxes

  1. Add header and footer
  2. Export two grids into one worksheet
  3. Export two grids each into a new worksheet
  4. Simple export scenario
  5. Customize file name
  6. Customize workbook, worksheet
  7. Customize cell
  8. Manually generate and save XLSX file
  9. Show dxLoadPanel

Installation

Link exportDxDataGrid, ExcelJS, and FileSaver libraries.

We Need Your Feedback

Take a Quick Poll

Do you need these capabilities when exporting DataGrid in your projects?

Get Notified of Updates

Subscribe to this thread or to our Facebook and Twitter accounts for updates on this topic.

@hassankani

This comment has been minimized.

Copy link

commented Apr 8, 2019

The initial issue that this one was created from, 5165, included a reference to being able to customise the exported cell appearance for pivotgrids, but that seems to now not be included in here. Will the above changes include customisation of cells exported from pivotgrid too? Or is that being covered elsewhere? Specifically, we need to able to export pivotrgrids to excel which include color-coding of cell backgrounds.

@IgnatovDan

This comment has been minimized.

Copy link
Contributor Author

commented Apr 9, 2019

Thank you for contacting us. I fully understand your idea.
Although I cannot promise you that we will implement it in the near future, we will take your opinion into account.
For DataGrid we considered this scenario and published our current results at DevExtreme DataGrid – Excel Data Export Customization Enhancements (v18.2). For PivotGrid, we will manage this scenario in a similar way.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.