How to use xlsx package to insert blank column in Excel

[AZaremba]

How can I insert a blank column into an existing Excel file while retaining the data in that column?

I have time series data across multiple columns (one year per column) with summary data at the end (Min, Max, Percentiles).

I want to use R to insert a column that contains a new year's data, but the code I have currently pastes on top of the summary data.

Code has been generalized below:

# Add necessary packages
library(rJava)
library(xlsx)
library(xlsxjars)

# Import data. Assume 1 column of data
df <- read.csv("file.csv", header = TRUE)

# Create a workbook using library(xlsx) function
workbook <- loadWorkbook("existing_workbook.xlsx")

# Import sheet names from above workbook
sheets <- getSheets(workbook)

# Add the created dataframe into the workbook.
# Assume the workbook has 100 existing columns and data is to be added to column 99
addDataFrame(df, sheets$correct_sheet, startColumn = 99, row.names = FALSE)

#Save the created workbook
saveWorkbook(workbook, "R_Output.xlsx")

What this ultimately does is overwrite the data in column 99, which I would prefer be shifted one column to the right.

Is this possible?

[cole]

Sorry for the delayed response here. The right approach would be to read the data from the spreadsheet (using i.e. read.xlsx or read.xlsx2), edit the data frame in R (i.e. add the column where you want it), and then write out the workbook with a new sheets$correct_sheet. The reason for this is because of the overwriting you mention and how painful it will be to "shift the data over one column to the right." You definitely can do this latter approach, but it will likely require a bit of ugly programming on your part.