We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Excel export - Apply custom styles and export all pages

Hello

Im using a custom button to make export my table as an Excel file.

Heres the code for the function:

excelExport() {
        let excelExportProperties = {
            exportType: 'AllPages',
            fileName: "Cashflow report.xlsx",
            theme: {
                header: {bold: true, backColor: 'd8bfd8', fontName: 'Segoe UI', fontColor: '#666666' },
                record: {backColor:'ffc0cb', fontName: 'Segoe UI', fontColor: '#666666' },
                caption: {backColor:'A7C7E7', fontName: 'Segoe UI', fontColor: '#666666' }
            }
        }
        this.pivotObj.grid.excelExport(excelExportProperties);
        //this.pivotObj.excelExportModule.exportToExcel('Excel',excelExportProperties)
    }


Im using virtuaization in my table, and I'd like to export ALL of the content when exporting to Excel.

When I use this.pivotObj.grid.excelExport(excelExportProperties); it applies my custom style, but it doesn't export all of the grid content, only the current view.

When i use this.pivotObj.excelExportModule.exportToExcel('Excel',excelExportProperties) it DOES NOT apply my custom style, but it exports all of the table contents.

How can I both export all of the table contents AND apply my custom style as well?

Heres my pivot table component: 

<PivotViewComponent
                            id='PivotView'                          
                            dataSourceSettings={this.getDataSourceSettings()}
                            ref={(scope) => { this.pivotObj = scope; }}
                            width={'100%'}                                              
                            height={'1200'}                                                
                            gridSettings={this.gridSettings}      
                            allowConditionalFormatting={true}                  
                            showFieldList={true}  
                            showGroupingBar={false}  
                            showToolbar={true}
                            exportAllPages={true}
                            toolbar={this.toolbarOptions}
                            toolbarRender={this.beforeToolbarRender.bind(this)}
                            showTooltip = { true}
                            allowExcelExport={true}
                            allowPdfExport={true}                                          
                            drillThrough={this.drillThrough.bind(this)}  
                            enginePopulated={this.enginePopulated.bind(this)}                                            
                            load={this.load.bind(this)}
                            dataBound={this.dataBound.bind(this)}
                            allowDrillThrough={true}
                            drill={this.drill.bind(this)}                                      
                            enableVirtualization={true}
                            beforeExcelExport={this.beforeExport.bind(this)}                        
                            >
                            <Inject services={[ConditionalFormatting, GroupingBar, Toolbar, PDFExport, ExcelExport, FieldList, DrillThrough, VirtualScroll ]} />
                        </PivotViewComponent>


Thanks in advance


1 Reply

AP AngelinFaithSheeba PaulvannanRajadurai Syncfusion Team April 4, 2023 01:59 PM UTC

Hi Hector,


Using excelQueryCellInfo and excelHeaderQueryCellInfo event, you can apply custom styles to the pivot table cells. Furthermore, you can apply styles to the header and footer of the Excel document by using header and footer properties within the excelExportProperties. Please refer the below code example.


Code example:

let gridSettings = {

    columnWidth: 140,

    // Bind excelQueryCellInfo and excelHeaderQueryCellInfo event inside the gridSettings.

    excelQueryCellInfo: excelQueryCellInfo.bind(this),

    excelHeaderQueryCellInfo: excelHeaderQueryCellInfo.bind(this),

  };

 

  function onClick() {

    let excelExportProperties = {

      fileName: 'excelexport.xlsx',

      header: {

        footerRows: 6,

        rows: [

          {

            cells: [

              {

                colSpan: 4,

                value: 'Pivot Table',

                // Apply styles to the header here.

                style: { fontColor: '#C67878', fontSize: 20, hAlign: 'Center'bold: trueunderline: true,

                },

              },

            ],

          },

        ],

      },

      footer: {

        headerRows: 8,

        rows: [

          {

            cells: [

              {

                colSpan: 4,

                value: 'Thank you for your business!',

                // Apply styles to the footer here.

                style: { hAlign: 'Center'bold: true },

              },

            ],

          }

        ],

      },

    };

    pivotObj.excelExport(excelExportProperties);

  }

  function excelQueryCellInfo(args) {

    //triggers every time for header cell while rendering.

    // Apply custom styles to the pivot cells based on your needs here.

    if(args.value>5000){

        args.style={

            backColor: '#00ffff',

            bold: true

        };

    }

  }

  function excelHeaderQueryCellInfo(args) {

    //triggers every time for header cell while rendering.

    // Apply custom styles to the column header cells based on your needs here.

    if (args.cell.value == "FY 2015") {

        args.style.bold = false;

    }

  }

 


Output screenshot:


Meanwhile we have prepared a sample for your reference.


Sample:  https://stackblitz.com/edit/react-wesxqa-7rxmnx?file=index.js,index.html


Please see the below UG document to know more about the Excel export, excelQueryCellInfo and excelHeaderQueryCellInfo.


Document: https://ej2.syncfusion.com/react/documentation/pivotview/excel-export

https://ej2.syncfusion.com/react/documentation/pivotview/excel-export#excelquerycellinfo

https://ej2.syncfusion.com/react/documentation/pivotview/excel-export#excelheaderquerycellinfo


Regards,

Angelin Faith Sheeba.


Loader.
Up arrow icon