Remove "title" row above header in shiny datatable excel output?

Question

In my shiny app, I'm using:

# server.R
  output$out_table = DT::renderDataTable(
    func_to_creat_dataframe(),
    rownames= FALSE,
    extensions = c('Buttons'),
    options = list(
      pageLength = 96,
      lengthMenu = c(96, 384, 1536),
      dom = 'Blfrtip',
      buttons = c('copy', 'csv', 'excel', 'pdf', 'print')
    )
  )

# UI.R
DT::dataTableOutput('out_table')

...and when I use the "Excel" button to export the table, the exported table has a "title" row directly above the header row. This title row consists of one merged cell that spans across the entire header. How do I remove this? This title row interferes with downstream processing of the file, and it's completely unnecessary, so I don't see why it appears to be default for the datatable file export buttons.

Stéphane Laurent · Accepted Answer · 2019-11-06 17:52:51Z

4

Try that:

DT::datatable(
  iris,
  rownames= FALSE,
  extensions = c('Buttons'),
  options = list(
    pageLength = 96,
    lengthMenu = c(96, 384, 1536),
    dom = 'Blfrtip',
    buttons = list(
      'copy', 
      'csv', 
      list(extend = 'excel', title = NULL), 
      'pdf', 
      'print'
    )
  )
)

answered Nov 6, 2019 at 17:52

Stéphane Laurent

85.3k18 gold badges141 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

sharchaea Over a year ago

Thanks for the suggestion! It works except, at least for Mac 2011 Excel, I get an error/warning stating:

Excel could not open the document because some of the content is unreadable. Do you want to open and repair this workbook?

Clicking Open and repair works. The excel repair log file shows:

</summary><repairedParts summary="Following is a list of repairs:"><repairedPart xml:space="preserve">Repaired Part: /xl/worksheets/sheet1.xml part. </repairedPart></repairedParts></recoveryLog>

Kevin Over a year ago

Doesn't work on Windows 2013 Excel either. I get the same issue as above

Stéphane Laurent Over a year ago

@Kevin And this works when there is a title? Strange;

Kevin Over a year ago

@StéphaneLaurent, didn't try. I just don't have title as an input at all. Adding title = NULL creates an unrepairable workbook though

Stéphane Laurent Over a year ago

@Kevin This bug has been encountered in the past. DT uses the buttons extension version 1.5.2, it is strange that this bug still occurs.

|

Collectives™ on Stack Overflow

Remove "title" row above header in shiny datatable excel output?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related