ycphs / openxlsx

openxlsx - a fast way to read and write complex xslx files
https://ycphs.github.io/openxlsx/
Other
220 stars 74 forks source link

adds functionality to delete a single column in the workbook #418

Closed DavZim closed 1 year ago

DavZim commented 1 year ago

This PR adds deleteDataColumn, which allows the user to delete a single column from a workbook, shifting the columns on the right to the left (closing a variant of this Issue on the old repo). The PR also adds some tests for the data as well as formulae.

The basic functionality (taken from ?deleteDataColumn) looks like this

library(openxlsx)
packageVersion("openxlsx")
#> [1] '4.2.5.9001'

wb <- createWorkbook()
addWorksheet(wb, "tester")

for (i in seq(5)) {
  mat <- data.frame(x = rep(paste0(int2col(i), i), 10))
  writeData(wb, sheet = 1, startRow = 1, startCol = i, mat)
  writeFormula(wb, sheet = 1, startRow = 12, startCol = i,
               x = sprintf("=COUNTA(%s2:%s11)", int2col(i), int2col(i)))
}
deleteDataColumn(wb, 1, col = 3)

read.xlsx(wb)
#>     x  x  x  x
#> 1  A1 B2 D4 E5
#> 2  A1 B2 D4 E5
#> 3  A1 B2 D4 E5
#> 4  A1 B2 D4 E5
#> 5  A1 B2 D4 E5
#> 6  A1 B2 D4 E5
#> 7  A1 B2 D4 E5
#> 8  A1 B2 D4 E5
#> 9  A1 B2 D4 E5
#> 10 A1 B2 D4 E5

Created on 2023-03-20 by the reprex package (v2.0.1)

Note how there is no column with C3 as this was deleted. The formulae are not evalutated as the workbook has not been opened with excel or libreofice yet.

DavZim commented 1 year ago

There is still a bug in the code at the moment. I will try to fix it, add a test and report back here!

DavZim commented 1 year ago

Now the bugs are fixed afaik. So from my viewpoint we would be good to merge. Let me know if you have any comments/ideas