How to Retain Number Formats when Inserting Data into an XLSX

Retaining the number formats when inserting data into an xlsx file in R language is a common issue faced by data analysts. Number formats include data types such as currency, percentage, date, and time. Losing these formats can lead to confusion and difficulty in understanding the data. However, with the right steps and tools, it is possible to retain the number formats while inserting data into an xlsx file.

In this article, we will discuss how to use the openxlsx package to retain number formats when inserting data into an xlsx file in R Programming Language. We will provide a fully executable example to demonstrate the steps needed to achieve this.

Concepts related to the topic:

• Data frames: A data frame is a tabular data structure in R that allows you to store and manipulate data in a table format. You can create a data frame from scratch, or read data from a file (e.g. CSV, xlsx) into a data frame using functions like read.csv or read_excel.
• Packages: Packages are collections of R functions, data, and documentation that extend the functionality of the base R system. To use a package in your R code, you need to install it first using the install.packages function, and then load it using the library function.
• Writing to xlsx files: To write data to an xlsx file in R, you can use one of several packages that provide functions for this purpose. Some examples include write_xlsx (from the writexl package), writeData (from the openxlsx package), and write.xlsx (from the xlsx package). These functions allow you to specify the data to write, the name of the file, and various other options such as sheet name and column formats.
• Number formats: Number formats in Excel allow you to control the appearance of numerical data in a cell. For example, you can specify a number format to display numbers with a certain number of decimal places or to use a thousand separator. In R, you can specify number formats using Excel-style format strings, such as “#,##0” for integers with thousand separators, or “#,##0.00” for numbers with two decimal places and thousand separators.
• Workbooks and sheets: An Excel workbook is a file that contains one or more sheets (also called worksheets). A sheet is a grid of cells that you can use to enter and organize data. When writing data to an xlsx file in R, you can either create a new workbook and add sheets to it, or write data to an existing sheet in a workbook.

1. Using the xlsx package

Install and load the xlsx package. This package provides the necessary functions to read and write data to an Excel file. This will create a dataframe with 3 rows and 3 columns. The first two columns contain numbers, one with decimal and the other without, and the last one is a date.

R

install.packages("xlsx")
library(xlsx)

df <- data.frame(
  decimal = c(1.23, 4.56, 7.89),
  integer = c(123, 456, 789),
  date = as.Date(c("2022-01-01",
                   "2022-02-01",
                   "2022-03-01"))
)

Create a new workbook and add a worksheet to it. This will create a new workbook and add a worksheet to it. Write the dataframe to the worksheet. This will write the dataframe to the worksheet.

R

wb <- createWorkbook()
CellStyle(wb)
sheet <- createSheet(wb, sheetName = "Sheet1")

addDataFrame(df, sheet)

This will save the workbook.

R

saveWorkbook(wb, "formatted_data.xlsx",
             overwrite = TRUE)

Output:

2. Using the openxlsx package

Using openxlsx package, we will be setting the format of the cells of the worksheet, as percentages with 2 decimal places, integers, and dates with the format yyyy-mm-dd. Install and load the openxlsx package. Create a data frame with numbers that you want to format.

R

install.packages("openxlsx",
                 dependencies = TRUE,
                 type = "source")
library(openxlsx)

df <- data.frame(
  number1 = c(1.234, 4.567, 7.890, 1.357),
  number2 = c(123, 456, 789, 0123),
  date = as.Date(c("2022-01-01", "2022-02-01",
                   "2022-03-01", "2022-04-01"))
)

We apply the percentage format to the number1 column using sprintf() function before writing the dataframe to the worksheet. Create a new workbook, add a sheet and write the dataframe to the worksheet.

R

df$number1 <- sprintf("%.2f%%", df$number1)

wb <- createWorkbook()
addWorksheet(wb, "Sheet1")
writeData(wb, "Sheet1", df)

Create styles for different formats.

R

percent_style <- createStyle(numFmt = "PERCENTAGE")
number_style <- createStyle(numFmt = "0")
date_style <- createStyle(numFmt = "DATE")

Step 6: Apply the styles to the cells. Apply the styles to the appropriate columns using the addStyle function. We set the rows parameter to 2:nrow(df)+1 to format the last row of the worksheet correctly.

R

addStyle(wb, "Sheet1",
         style = percent_style, rows = 2:nrow(df)+1,
         cols = 1)
addStyle(wb, "Sheet1",
         style = number_style, rows = 2:nrow(df)+1,
         cols = 2)
addStyle(wb, "Sheet1",
         style = date_style,rows = 2:nrow(df)+1,
         cols = 3)

Save the workbook to an xlsx file.

R

saveWorkbook(wb, "formatted_data.xlsx",
             overwrite = TRUE)

Output:

This code will correctly format the data in the worksheet, with the first column as the percentage with 2 decimal places, the second column as integers, and the third column as a date.

Conclusion:

Retaining number formats when inserting data into an xlsx file in R is important for the clarity and understanding of the data. There are several approaches that you can use to write data to an xlsx file in R and retain number formats. Some of the most popular packages for this purpose are writexl, openxlsx, and xlsx. Each of these packages provides functions that allow you to specify the data to write, the name of the file, and various other options such as sheet name and column formats. You can use Excel-style format strings to define the number formats that you want to apply to your data.