CODEWITHSUNDEEP
rexcelxlsxmapply
Hack-R
Hack-R

Reputation: 23216

Write column values to an Excel spreadsheet in R with formatting

I'm trying to use the package xlsx to read in the style (formatting) of a template, then write out data from a dataframe to a .xlsx file with the same formatting. I'm following the closest example that I could find from the package's Wiki.

The problem is that when trying to write data from my dataframe it comes through as NULL, though the class of the column I'm trying to write matches the example in the Wiki, as does my syntax.

head(fer_sheet1[,1])

# [1] "HXXX3691X" "HXXX3789X" "HXXX3759X" "HXXX37350" "HXXX3722X" "HXXX3719X"

class(fer_sheet1[,1])

# [1] "character"

Final_Report_Formatted <- createWorkbook()
Contact_List <- createSheet(Final_Report_Formatted, "Contact List")
Contact_List_rows <- createRow(Contact_List, rowIndex = 1:43)
Contact_List_cells <- createCell(Contact_List_rows, 
                                 colIndex=1:length(fer_sheet1))
mapply(setCellValue, Contact_List_cells[1:43, 1], fer_sheet1[, 1])

# $`1`
# NULL
# 
# $`2`
# NULL
# 
# $`3`
# NULL

and so on, all the way down to:

# $`41`
# NULL
# 
# $`42`
# NULL
# 
# $`43`
# NULL

I could easily do this without the package xlsx, but the idea is to go ahead and use their package so that I can apply the formatting from the template workbook after populating it with data.

Interestingly, I also get back NULL values using the built in month.name data, like in the example:

mapply(setCellValue, Contact_List_cells[1:12, 1], month.name)

Upvotes: 0

Views: 837

Answers (1)

IRTFM
IRTFM

Reputation: 263301

I don't see any close/save workbook command. Generally this sort of operation is like writing a graphics plot to a file ... it needs a completion function. In this case that would be:

saveWorkbook(Final_Report_Formatted, "myChosenWBName.xlsx")

Upvotes: 1

Related Questions