Exporting and formatting of table data to Excel #385

Activity

1. glin commented on Aug 5, 2024

   

   glin

   on Aug 5, 2024

   Owner

   More actions

   I was going to make an Excel export example a while back but just didn't have time. That one was going to use SheetJS I believe. I've heard of ExcelJS and I know there are a bunch of other competing libraries, but good to know that MUI chose ExcelJS.

   Here's the last issue where someone did this with SheetJS, that you might find useful: #291

   So we'd start with adding a similar example to the docs using either of these. For a built-in feature, we'd need to evaluate the libraries more closely to see which is the best to pull in, or if it's even worth pulling them in. External libraries are usually challenging because of the added download size (especially for an optional feature) and maintenance cost, keeping up with security fixes, and etc. Unlike in JavaScript, it's not as easy to conditionally bring in code only when you use it, but we could probably get close by having ExcelJS/SheetJS be a separate file that gets conditionally included as a widget dependency somehow.
2. glin

   added

   enhancementNew feature or requestNew feature or request

   on Aug 5, 2024
3. sciordia commented on Aug 5, 2024

   

   sciordia

   on Aug 5, 2024

   Author

   More actions

   Thank you for replying @glin .

   It is true that there are several libraries that can be used but it all depends on the goal you have in mind. In my case I need not only to export to excel but also for the file to have some formatting (or style) and this can be easily achieved with exceljs. I don't know SheetJS in terms of functionality so you will have to evaluate that part yourself.

   Here's an example... Right now the code I started from is this one:

   [Shiny App]

   library(shiny)
   library(reactable)
   library(htmltools)
   
   ui <- fluidPage(
     tags$head(
       tags$script(src = "https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.4.0/exceljs.min.js"),
       tags$script(src = "https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.4.1/papaparse.min.js"),
       includeScript("./js/export_excel.js")
     ),
     titlePanel("Reactable Example"),
     fluidRow(
       column(width = 12,
              actionButton("export", "Export to Excel"),
              reactableOutput("contents")
       )
     )
   )
   
   server <- function(input, output, session) {
     
     # Rendering the reactable table
     output$contents <- renderReactable({
       reactable(
         iris,
         defaultPageSize = 20,
         showPageSizeOptions = TRUE,
         pageSizeOptions = c(10, 20, 50, 100),
         resizable = TRUE,
         searchable = TRUE,
         rowStyle = list(cursor = "pointer"),
         theme = reactableTheme(cellPadding = "8px 12px")
       )
     })
     
     observeEvent(input$export, {
       session$sendCustomMessage("exportExcel", list())
     })
   }
   
   shinyApp(ui, server)
   

   [export_excel.js]

   function downloadExcelFromTSV(tsv) {
     var wb = new ExcelJS.Workbook();
     var ws = wb.addWorksheet('Sheet1');
   
     // Parse the TSV to an array of objects
     var rows = Papa.parse(tsv, { header: true, delimiter: '\\t' }).data;
     var headers = Object.keys(rows[0] || {});
   
     // Create headers
     ws.columns = headers.map(header => ({
       header: header,
       key: header,
       style: { font: { name: 'Arial', size: 12, bold: true } }
     }));
   
     // Add data
     rows.forEach(row => {
       ws.addRow(row);
     });
   
     // Format data cells
     ws.eachRow((row, rowNumber) => {
       row.eachCell((cell, colNumber) => {
         if (rowNumber > 1) { // Apply format only to data rows
           cell.style = { font: { name: 'Arial', size: 12 }, alignment: { horizontal: 'left' } };
         }
       });
     });
   
     wb.xlsx.writeBuffer().then(function(buffer) {
       var blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
       var url = window.URL.createObjectURL(blob);
       var a = document.createElement('a');
       a.href = url;
       a.download = 'filtered_data.xlsx';
       document.body.appendChild(a);
       a.click();
       document.body.removeChild(a);
       window.URL.revokeObjectURL(url);
     });
   }
   
   // Custom message handler for Shiny to export to Excel
   Shiny.addCustomMessageHandler('exportExcel', function(message) {
     var tsv = Reactable.getDataCSV('contents', { sep: '\\t' });
     downloadExcelFromTSV(tsv);
   });
   

   What I have done is to use as data input for the javascript file the output data from reactable (a TSV file is generated using the API) and this is processed and transformed into the output of the exceljs library. I have also added some lines of code so you can see that it is easy to format the excel (change the font-type to 'Arial' and font-size '12').

   I hope this helps you to make a decision.

   Thanks for taking our suggestions into account, good work.

   Best regards,
   Sergio

   👍React with 👍2Reacted by Greg Lin and William Gearty

   👀React with 👀1Reacted by William Gearty