CODEWITHSUNDEEP
rxlsx
michaelmccarthy404
michaelmccarthy404

Reputation: 508

How to align the cells of an XLSX file using R's xlsx package?

When creating an XLSX file using R's xlsx package, by default, columns with strings are justified to the left by default, and columns with integers are justified to the right (columns with a mix of integers and strings are also justified to the left). Ultimately, I want to standardize all columns by aligning them all to the left, but I'm having trouble doing so using xlsx. Using the below example, how can I align all cells to the left?

library(xlsx)

# Creating dataframe.
df <- data.frame(c(1, 2, 3),
                 c("one", "two", "three"),
                 c("1", "2", "3"))

# Creating a workbook using the XLSX package.
wb <- xlsx::createWorkbook(type = "xlsx")

# Creating a sheet inside the workbook.
sheet <- xlsx::createSheet(wb, sheetName = "Sheet0")

# Adding the full dataset into the sheet.
xlsx::addDataFrame(df, sheet, startRow = 1, startCol = 1, row.names = FALSE, col.names = FALSE)

# Saving the workbook.
xlsx::saveWorkbook(wb, "df.xlsx")

Upvotes: 5

Views: 5600

Answers (2)

Angela
Angela

Reputation: 1

Note for future readers: the solution described above eats a lot of memory. I had a 10Mb data.frame and the corresponding all.cells object turned out to be 1.1Gb.

If the only purpose is to have left-alignment of the cell, this can easier done like this:

openxlsx::write.xlsx(
    x = df,
    file = file,
    startRow = 1, 
    startColumn = 1, 
    rowNames = FALSE, 
    colNames = TRUE
  )

Upvotes: 0

michaelmccarthy404
michaelmccarthy404

Reputation: 508

I've solved the above question with the solution seen below:

library(xlsx)

# Creating dataframe.
df <- data.frame(c(1, 2, 3),
                 c("one", "two", "three"),
                 c("1", "2", "3"))

# Creating a workbook using the XLSX package.
wb <- xlsx::createWorkbook(type = "xlsx")

# Creating a sheet inside the workbook.
sheet <- xlsx::createSheet(wb, sheetName = "Sheet0")

# Adding the full dataset into the sheet.
xlsx::addDataFrame(df, sheet, startRow = 1, startCol = 1, row.names = FALSE, col.names = FALSE)

# Creating cell style needed to left-justify text.
cs <- CellStyle(wb) + Alignment(horizontal = "ALIGN_LEFT")

# Selecting rows to apply cell style to.
all.rows <- getRows(sheet, rowIndex = 1:nrow(df))

# Selecting cells within selected rows to apply cell style to.
all.cells <- getCells(all.rows)

# Applying cell style to selected cells.
invisible(lapply(all.cells, setCellStyle, cs))

# Saving the workbook.
xlsx::saveWorkbook(wb, "df.xlsx")

The solution involved the creation of a cell style which I stored in cs. Next, I selected each row and each cell each contained and applied the cell style to them using lapply().

Upvotes: 4

Related Questions