This is a migrated thread and some comments may be shown as answers.

Grid export excel problem

3 Answers 714 Views
Grid
This is a migrated thread and some comments may be shown as answers.
roukfa
Top achievements
Rank 1
roukfa asked on 30 Sep 2019, 02:41 PM
Hi,

               We implemented the Kendo Ui Grid with great success in our project but faced 2 issues in the end when we implemented the export to Excel feature.

               We made 2 modification using CSS to the grid and when we export it to PDF, the changes done with CSS ( Adding conditional background color to specific row AND hiding the second Group Footer when we use aggregate function and add 2 different Group.) show up correctly BUT when we export the grid using Excel, the changes done in CSS doesn’t show up.

Here's a screenshot of our grid with the CSS applied to it :
(attached file)

Here’s a screenshot of the PDF generated with the CSS change showing :
(attached file)

Here’s a screenshot of the Excel generated from the Grid.  You can see that the color is not showing up and we see the footer twice, once for each group :
(attached file)


Here’s the code used to hide one of the footer :

<style>
       .k-group-footer {
             display: none !important;
       }

       .k-group-footer + .k-group-footer {
             display: table-row !important;
       }
</style>

And finally, here’s the code used to implement our Grid :

             @(Html.Kendo().Grid<PRR405Data.Models.MPremiumTransaction>
                    (Model.Transactions)
                    .Name("TransactionsDossierPrime")
                    .ToolBar(tools => tools.Template("<a class='k-button k-button-icontext' onclick='exportToPDF()'>" + PRR405.Resources.ConsultationHistorique.ConsultationHistorique.TelechargerPDF + "</a><a class='k-button k-button-icontext' onclick='exportToExcel()'>" + PRR405.Resources.ConsultationHistorique.ConsultationHistorique.TelechargerExcel + "</a>"))
                    .Pdf(pdf => pdf
                        .AllPages()
                        .AvoidLinks()
                        .Scale(0.8)
                    )
            .Excel(excel => excel
            .AllPages(true)
            .ForceProxy(false)
            .Filterable(true)

            )
                    .Columns(columns =>
                    {
                          columns.Bound(p => p.ENTR_NUM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.NoInscriptionHeader).Width(240).ClientGroupFooterTemplate(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.TotalTransactionsAcceptees);
                          columns.Bound(p => p.BR_CD).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.SuccursaleHeader).Width(90);
                          columns.Bound(p => p.AGNCY_CD).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.CodeAgenceHeader).Width(80);
                          columns.Bound(p => p.OCCSN_DR).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.ConducteurLabel).ClientTemplate(
                                              "# if (OCCSN_DR == 0) { #" +
                                                     @PRR405.Resources.SaisirPrime.SaisirPrime.PrincipalLabel +
                                              "# } else if (OCCSN_DR == 1) { #" +
                                                     @PRR405.Resources.SaisirPrime.SaisirPrime.OccasionnelLabel +
                                              "# } else if (OCCSN_DR == 2) { #" +
                                                     @PRR405.Resources.SaisirPrime.SaisirPrime.ConfieLabel +
                                              "# } #"
                                              ).Width(80);
                          columns.Bound(p => p.TXN_CD).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.CodeTransactionHeader).Width(100);
                          columns.Bound(p => p.PLCY_TYPE).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.IndicateurDeDureeHeader).Width(80);
                          columns.Bound(p => p.INSUR_NAME).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.NomHeader).Width(250);
                          columns.Bound(p => p.NIV).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.NIVHeader).Width(180);
                          columns.Bound(p => p.XFER_DATE).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.DebutTransactionHeader).Width(100);
                          columns.Bound(p => p.EXPIR_DATE).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.FinTransactionHeader).Width(100);
                          columns.Bound(p => p.BUS_TYPE).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.GenreAffaireHeader).Width(70);
                          columns.Bound(p => p.USE_TYPE).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.GenreDUtilisationHeader).Width(90);
                          columns.Bound(p => p.TERR).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.ZoneStatistiqueHeader).Width(90);
                          columns.Bound(p => p.DRIV_AGE).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.AgeHeader).Width(60);
                          columns.Bound(p => p.DRIVING_RC).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.DossierCondRCHeader).Width(80);
                          columns.Bound(p => p.DRIVING_CL).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.DossierCondCLTRHeader).Width(80);
                          columns.Bound(p => p.RCLIMI_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.RCLimiteHeader).Width(80);
                          columns.Bound(p => p.RCPREM_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.RCPrimeHeader).Width(70).ClientGroupFooterTemplate("#=sum#");
                          columns.Bound(p => p.RCCANCL_C).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.RCAnnulHeader).Width(60);
                          columns.Bound(p => p.TRLIMI_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.TRFranchHeader).Width(80);
                          columns.Bound(p => p.TRPREM_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.TRPrimeHeader).Width(70).ClientGroupFooterTemplate("#=sum#");
                          columns.Bound(p => p.TRCANCL_C).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.TRAnnulHeader).Width(60);
                          columns.Bound(p => p.CLLIMI_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.CLFranchHeader).Width(80);
                          columns.Bound(p => p.CLPREM_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.CLPrimeHeader).Width(70).ClientGroupFooterTemplate("#=sum#");
                          columns.Bound(p => p.CLCANCL_C).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.CLAnnulHeader).Width(60);
                          columns.Bound(p => p.ADLIMI_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.ADFranchHeader).Width(80);
                          columns.Bound(p => p.ADPREM_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.ADPrimeHeader).Width(70).ClientGroupFooterTemplate("#=sum#");
                          columns.Bound(p => p.ADCANCL_C).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.ADAnnulHeader).Width(60);
                          columns.Bound(p => p.RSLIMI_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.RSFranchHeader).Width(80);
                          columns.Bound(p => p.RSPREM_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.RSPrimeHeader).Width(70).ClientGroupFooterTemplate("#=sum#");
                          columns.Bound(p => p.RSCANCL_C).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.RSAnnulHeader).Width(60);
                          columns.Bound(p => p.FMLIMI_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.FMLimiteHeader).Width(80);
                          columns.Bound(p => p.FMPREM_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.FMPrimeHeader).Width(70).ClientGroupFooterTemplate("#=sum#");
                          columns.Bound(p => p.FMCANCL_C).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.FMAnnulHeader).Width(60);
                          columns.Bound(p => p.ITLIMI_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.ITLimiteHeader).Width(60);
                          columns.Bound(p => p.ITPREM_AM).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.ITPrimeHeader).Width(70).ClientGroupFooterTemplate("#=sum#");
                          columns.Bound(p => p.ITCANCL_C).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.ITAnnulHeader).Width(60);
                          columns.Bound(p => p.TOT_PRE_A).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.PrimeTotaleHeader).Width(60).ClientGroupFooterTemplate("#=sum#");
                          columns.Bound(p => p.TARIF_STD).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.TarifStandardHeader).Width(80);
                          columns.Bound(p => p.BTCH_CD).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.CodeLotHeader).Width(80);
                          columns.Bound(p => p.BATCH_TYPE).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.TypeLotHeader).Width(80);
                          columns.Bound(p => p.POST_DATE).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.DateTransmissionHeader).Format("{0: yyyy-MM-dd}").Width(100);
                          columns.Bound(p => p.CYCLE).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.ReleveHeader).Width(70);
                          columns.Bound(p => p.CYCLE_DATE).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.DateReleveHeader).Format("{0: yyyy-MM-dd}").Width(100);
                          columns.Bound(p => p.REJECT).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.CodeRejetHeader).Width(140);
                          columns.Bound(p => p.WARNINGS).Title(PRR405.Resources.ConsultationHistorique.ConsultationHistorique.AvertissementHeader).Width(300);

                    })
                    .Scrollable(s => s.Height("300px"))
                    .HtmlAttributes(new { style = "margin-bottom:20px;" })
                    .Sortable()
                    .Scrollable()
                    .Events(e => e.DataBound("rejectColor"))
                    //.Filterable()
                    .Resizable(resize => resize.Columns(true))
                    .DataSource(dataSource => dataSource
                    .Ajax()
                    .ServerOperation(false)
                    .Aggregates(aggregates =>
                    {
                          aggregates.Add(p => p.RCPREM_AM).Sum();
                          aggregates.Add(p => p.TRPREM_AM).Sum();
                          aggregates.Add(p => p.CLPREM_AM).Sum();
                          aggregates.Add(p => p.ADPREM_AM).Sum();
                          aggregates.Add(p => p.RSPREM_AM).Sum();
                          aggregates.Add(p => p.FMPREM_AM).Sum();
                          aggregates.Add(p => p.ITPREM_AM).Sum();
                          aggregates.Add(p => p.TOT_PRE_A).Sum();
                    })
                    .Group(groups => groups.Add(p => p.NIV))
                    .Group(groups => groups.Add(p => p.OCCSN_DR))
                    ))


       function rejectColor() {

             var grid = $("#TransactionsDossierPrime").data("kendoGrid");
             var data = grid.dataSource.data();
             $.each(data, function (i, row) {
                    if (row.REJECT != '') {
                          $('tr[data-uid="' + row.uid + '"] ').css("background-color", "#d9212a");
                    }
             })

       }


. . Mathieu Bédard
Développeur Full-Stack
M +1 4383330589 | T +1 5145433855
mbedard@uzinakod.com | uzinakod.com
33 rue Prince | Bureau 310 - H3C 2M7, Montréal, QC


.

3 Answers, 1 is accepted

Sort by
0
Tsvetomir
Telerik team
answered on 02 Oct 2019, 12:36 PM

Hi,

Thank you for the provided code snippets along with screenshots of the grid and the Excel file.

Generally, the Excel export functionality of the grid works entirely with the data. Therefore, any styles that are applied to the client-side would not be taken into consideration when exporting. However, in the excelExport event handler, you have access to the worksheet and you can customize its appearance there. Here is an example:

.Events(ev=>ev.ExcelExport("onExcelExport"))

            function onExcelExport(e){
              var dataRows = $.grep(e.workbook.sheets[0].rows, function(e){return e.type == "data"});
              $(dataRows[0].cells).each(function(e){this.background = "#800000"});
            },

As per the footer template, it might be due to the fact that there is only one sub-group in the main group. Therefore, the footer of the main group and the footer template of the sub-group are rendered. I am attaching a sample project which demonstrates this behavior. Can you confirm that you are facing the same on your side?

Looking forward to your reply.

 

Best regards,
Tsvetomir
Progress Telerik

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
roukfa
Top achievements
Rank 1
answered on 03 Oct 2019, 03:32 PM
Thank you for your reply,

               I managed to implement my logic to correctly add color on specific row of the excel using the ExcelExport event.

               Also, you are right, the double footer is related to have to group and only 1 element in one  of the group, so the ClientGroupFooterTemplate is repeated for each group.  Now, knowing this, is there a way to only render the ClientGroupFooterTemplate on a specific Group and not all of them?  If not, using the ExcelExport event, is there a way to access the Group Footer row so I could maybe remove one of them when the Excel is created.  So far, I could only access to row containing data, not the header or footer row…

Thank you again
0
Tsvetomir
Telerik team
answered on 04 Oct 2019, 12:57 PM

Hi, 

The approach that was provided in my previous response returns only the data items. However, if you would like to access all of the items, simply use the following line:

var dataRows = e.workbook.sheets[0].rows;

This would return the following collection:

https://www.screencast.com/t/jIKltKSx

Based on the type ("group-footer"), you can erase specific rows.

I hope you find this helpful.

 

Best regards,
Tsvetomir
Progress Telerik

Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Tags
Grid
Asked by
roukfa
Top achievements
Rank 1
Answers by
Tsvetomir
Telerik team
roukfa
Top achievements
Rank 1
Share this question
or