How can I add a table to my rmarkdown html output that will paste into Excel with formatting?

Question

I'd like to create button (or a selectable table) in an rmarkdown file that allows a user to copy a table and paste it into word or excel with some pre-defined formatting.

Is there a way to produce:

• A copy function that preserves table formatting, especially borders to cells
• A copy function that captures the entirety of a table

Using this very simple dataframe:

data<-data.frame(Variable=c('Amount','Age','Happiness','Favorite Color'),
             Value=c(15,25,7,'Yellow'))

I want a user to be able to click a button in the html file such that when they paste into excel or word, they see:

or

depending on the formatting specified.

As it stands, I can make a table that has cell borders with kable,

data%>%
  kable("html",align = 'clc')%>%
  kable_styling(full_width = F,position="left",bootstrap_options = 
  c("striped","bordered"))

This table, in the html file generated using rmarkdown, displays cell borders and has acceptable spacing and clarity (columns are wide enough to display full column headers, and the gird clearly defines cells.

When I highlight the table and paste it into Excel, I get:

An unsatisfactory result.

Producing a table with DT, I use:

datatable(data,extensions='Buttons',rownames=FALSE,filter=c("top"),options=list(dom='Bfrtip',buttons=c('csv','copy','excel')))

This produces a table with a CSV, Copy, and Excel button.

The result of using the Copy button and pasting into Excel is:

A user has data that does paste, but lacks any formatting (and puts the title from the file two rows above the datatable itself?)

Is there a way to modify the code for the DT table's Copy button to include some specified formatting, such as a missing argument that gives an option to copy some formatting to clipboard as well? Or a way to produce a standalone button that I can store an excel-formatted table behind so that a user has an way to copy my table in an excel-friendly, formatted form?

Note: I don't want to write to an excel file from R. There seem to be many options for formatting tables and writing to Excel with openxlsx and solutions here: Write from R into template in excel while preserving formatting , but this does not answer my question.

Edit: There seems to be the ability to add the formatting to the Excel button, mentioned here: https://datatables.net/extensions/buttons/examples/html5/excelBorder.html

This gives me hope that perhaps the .attr() specification of the formatting can somehow be added to the copy button.

As a first step in trying to test whether or not I can even get this intermediate step functioning, I tried:

jscode<-"function ( xlsx ){var sheet = xlsx.xl.worksheets['sheet1.xml']; $('row c[r*=10]', sheet).attr( 's', '25' );   }"

datatable(data1,class='cell-border',extensions='Buttons',rownames=FALSE,filter=c("top"),options=list(dom='Blfrtip',buttons=list(list(extend='excel',customize=jscode))))

The result is a spinning-button-of-nothingness.

When I try:

library(jsonlite)
datatable(data1,class='cell-border',extensions='Buttons',rownames=FALSE,filter=c("top"),options=list(dom='Blfrtip',buttons=list(list(extend='excel',customize=fromJSON(jscode)))))

I receive an error:

Since the solution to this question seems to depend on some javascript (at least if the answer will be based on datatable, I've added the javascript tag.

Answer 1

This code should give you the whole table when copied and removes the table title (without any of the formatting though, maybe somebody can extend this answer):

library(DT)
DT::datatable(mtcars,
              filter = 'top', 
              class = 'cell-border stripe',
              extensions = 'Buttons',
              options = list(scrollY = 600,
                             scrollX = TRUE,
                             dom = 'lBfrtip',
                             lengthMenu=  list(c(10, 25, 100, -1), 
                                               c('10', '25', '100','All')),
                             buttons = list(
                               list(extend = "copy", text = "copy", 
                                    title= "",
                                    exportOptions = list(
                                    modifier = list(page = "all")
                                  )
                               )
                             ),
                             scrollCollapse= TRUE,
                             lengthChange = TRUE, 
                             widthChange= TRUE,
                             format = list(
                               header = TRUE
                             )))

#If you don't want rownames then you can have:
#DT::datatable(mtcars,
#              filter = 'top', 
#              class = 'cell-border stripe',
#              extensions = 'Buttons',
#              rownames = FALSE,
#              extensions = 'Buttons',
#              options = list(
#              ......)))

When you hit "Copy" and paste it in the excel you should see the following-

Further options related to DT can be found here: https://datatables.net/reference/option/

Answer 2

Library kableExtra will help you. Try this:

library(kableExtra)
data %>%
  kbl() %>%
  kable_paper("hover", full_width = F)

There are other styles, please search Rdocumentation. The table looks like this:

Then I copy:

Finally, paste in Excel:

Some times you need to paste as "Unicode Text" or "Text" (Can do it with special paste).

Answer 3

I found a working solution using javascript in the rmarkdown file, and the kableExtra package for table creation.

In producing the table with kableExtra, you use table.attr to add an ID to the html table that will ultimately be produced like this:

data%>%
  kable("html",align = 'clc',table.attr="id=tableofdata")%>%
  kable_styling(full_width = F,position="left",bootstrap_options = c("striped","bordered"))%>%
  add_header_above(c("Pretty Table"=2))

After the {r} code block, paste the following script, found here: How to copy table html (data and format) to clipboard using javascript (microsoft edge browser)

<script type="text/javascript">
function selectElementContents(el) {
    let body = document.body, range, sel;
    if (document.createRange && window.getSelection) {
        range = document.createRange();
        sel = window.getSelection();
        sel.removeAllRanges();
        try {
            range.selectNodeContents(el);
            sel.addRange(range);
        } catch (e) {
            range.selectNode(el);
            sel.addRange(range);
        }
    } else if (body.createTextRange) {
        range = body.createTextRange();
        range.moveToElementText(el);
        range.select();
    }
    document.execCommand("Copy");}

</script>

This creates the copy function in javascript. Next, create a button that uses this function to copy the table and the formatting to the clipboard. See that the same term we used as the table.attr ID in the creation of our table needs to be used here within getElementByID to copy the contents.

<button type="button" onclick="selectElementContents( document.getElementById('tableofdata') );">Copy Table</button>

Note:

I found that when I knit my .Rmd file and tried using the button that appears in Rstudio's browser, clicking the button didn't do what I wanted. When I opened the resulting html file Chrome, however, clicking the button copied the table with the formatting successfully. Had I not tried chrome, I may have dismissed this as a non-working solution. I haven't tried opening with different versions of popular browsers.

Once you knit to html, the button will appear under the table, looking something like:

After clicking the button, and pasting in Excel, the borders, bold heading, and title, show up!

Column width doesn't magically keep things looking good in excel, but that's a step for another day.