Write excel file formatted as Table in R

Question

[erfan741]

In Excel, by selecting all the sheet and clicking on "Format as Table" one can change the format of the sheet to table which allows interactive filtering of the columns. How do I generate an excel file using R, with the same formatting? I am using XLSX package in R, but apparently it doesnt have this feature.

Edit: Because of some confusion, this is what OP meant by "Format as Table"

[Jake Warner]

Is your data already in a data frame? Have you tried write.csv()? Excel has no problem reading .csv files. Come to think of it, excel can read txt outputs from write.table(...,sep='\t') as well.

[Joe]

I think the OP wants to output a .xlsx file which can have markup applied to in R, such that when it's read in to Excel, it is pre-formatted as a table (not just a spreadsheet). As for an answer, if XLSX doesn't have that functionality, you could check out some of the other packages listed here: https://www.r-bloggers.com/r-the-excel-connection/ I don't know categorically whether they do or not though, I've never tried it myself.

[erfan741]

Yes This is what I want, But I can't find a way of doing that. It s strange that these packages don't have this simple functionality.

[Chirag Parsania]

Not sure but you can try writexl::write_xlsx()

Answer 1

[Antonio R. Franco]

I use to export the data using the comma "," as a delimitator, and this creates a csv file that Excel can open without problems Can this be an answer to your question ?

[erfan741]

No this is not the answer. I want my output as an excel file marked-up as Table inside EXCEL.

Answer 2

[erfan741]

Oh my, cant believe 5.4 yrs already past by since I left this question here. Yes I figured it out. You need to use "openxlsx" package in R. It has many functionalities to save the best format of table as an excel file. A simple workflow is here, assuming you have a data frame (myDataFrame) ready to be saved in R:

wb <- createWorkbook()
addWorksheet(wb,"SheetName")
writeDataTable(wb, 1, myDataFrame, tableStyle = "TableStyleMedium2")

Answer 3

[User1234]

I am looking to do the same thing, did you happen to find an answer?

[Michael]

You need to do this in excel, either manually or via a VBA macro.