Write excel file formatted as Table in R
3
0
Entering edit mode
6.9 years ago
erfan741 ▴ 80

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"

enter image description here

R Excel Table • 5.1k views
ADD COMMENT
0
Entering edit mode

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.

ADD REPLY
1
Entering edit mode

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.

ADD REPLY
0
Entering edit mode

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.

ADD REPLY
0
Entering edit mode

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

ADD REPLY
1
Entering edit mode
6.9 years ago

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 ?

ADD COMMENT
0
Entering edit mode

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

ADD REPLY
1
Entering edit mode
18 months ago
erfan741 ▴ 80

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")
ADD COMMENT
0
Entering edit mode
18 months ago
User1234 • 0

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

ADD COMMENT
0
Entering edit mode

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

ADD REPLY

Login before adding your answer.

Traffic: 1846 users visited in the last hour
Help About
FAQ
Access RSS
API
Stats

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.

Powered by the version 2.3.6