CODEWITHSUNDEEP
rexcel
Arani
Arani

Reputation: 823

How to delete data from a XLSX file while keeping formatting in R

I have 3 xlsx files with 7 sheets each. Each morning I have to delete the contents of each sheet to prepare for importing new data.

I want to automate the process using R. I can do this with Excel Macro, but an R script is what I want (that way I don't need to wait longer as macro enabled files are way slower).

I only want to get blank sheets while keeping the sheetnames, and all formatting (I have formatted all cells as Text, and cells have particular widths). Solutions using openxlsx package are more appreciated.

EDIT:

The sheets contain about 15 columns and at max 200 rows. Still non vectorized solutions are a bit slow.

Upvotes: 1

Views: 4724

Answers (2)

AkhlT
AkhlT

Reputation: 66

You can use deleteData function of openxlsx.

library(openxlsx)
wkbook <- loadWorkbook(file = "test.xlsx")
deleteData(wkbook , sheet = 1,cols = 1:100, rows = 1:100000, gridExpand = TRUE)

After deletion use you use WriteData function to insert/append data to existing file. Here I'm clearing rows till 100000 and columns till 100. You can identify the last row and last column of the workbook and delete accordingly.

Upvotes: 3

G5W
G5W

Reputation: 37641

This seems like a bit of a hack, but you can simply write an empty string to each cell that is in use and then save the resulting workbook.

library(openxlsx)
wb = loadWorkbook("Test.xlsx")

for(ss in 1:length(wb$worksheets)) {
    Rows = wb$worksheets[[ss]]$sheet_data$rows
    Cols = wb$worksheets[[ss]]$sheet_data$cols

    for(i in 1:length(Rows)) {
        writeData(wb,ss,"",  startCol = Cols[i], startRow = Rows[i])
    }
}
saveWorkbook(wb, "Test2.xlsx", overwrite = TRUE)

However, Why can't you just make the empty spreadsheet once as a template and then make a copy it every time you need to use it?

Upvotes: 2

Related Questions