Exporting and formatting of table data to Excel

[sciordia]

Greg, do you plan to incorporate new export formats directly from Reactable apart from CSV? It would be interesting to have an option to download and export the table data (filtered or raw) to excel. I have been researching and there are libraries in react like MUI that use the exceljs package [link] to generate a formatted excel file from the table data. It would be great to have this option in Reactable.

I leave you a link on the mui website where they give more details of the integration:
https://mui.com/x/react-data-grid/export/#excel-export

Reactable is already brilliant and necessary in my day to day work but it would be very interesting to have the option to export to Excel. What do you think?.

Thank you very much for all your work.

Best regards,
Sergio

[glin]

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.

[sciordia]

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