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"
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 ?
No this is not the answer. I want my output as an excel file marked-up as Table inside EXCEL.
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")
I am looking to do the same thing, did you happen to find an answer?
You need to do this in excel, either manually or via a VBA macro.
Log in to answer this question.
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.
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.
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.
Not sure but you can try
writexl::write_xlsx()