Removing blank rows and columns from a worksheet

[Jeff-White-AZ]

I am working with spreadsheets that often pass through Google Sheets. Google Sheets has the annoying tendency to default to a sheet extent from A1 to Z999. Once downloaded to Excel, if you add data of, for example, 8 columns and 100 rows, manipulate the data as needed with openxlsx2 as a data frame, and write the data back, the blank rows and columns remain because they are still part of the worksheet defined in the wb.
Based on my previous experience with openxlsx2 and searching the documentation, it appears that there is no simple way to directly delete rows or columns from a worksheet.
The option I can see is to read the information from the worksheet, delete the worksheet with wb_remove_worksheet(), and then rebuild the worksheet. However, the worksheets have a fair amount of formatting, that I would prefer not to have to reconstruct.
Is there a simpler approach? Have I missed something?

[JanMarvin]

Hi @Jeff-White-AZ ,

I don’t understand this

Once downloaded to Excel, if you add data of, for example, 8 columns and 100 rows, manipulate the data as needed with openxlsx2 as a data frame, and write the data back, the blank rows and columns remain because they are still part of the worksheet defined in the wb.

Are these columns and rows part of the data frame or of the workbook? Is only the dimension tag in the worksheet set to “A1:Z9999” or is every cell available in the cc object of openxlsx2?

It’s correct though, that removing columns and rows is not possible with the package.

[JanMarvin]

For me it looks unchanged, but I don't use google sheets at all, therefore no idea what the default behavior looks like.

library(openxlsx2)

# pre upload
wb <- write_xlsx(mtcars, "/tmp/mtcars.xlsx")
wb$worksheets[[1]]$dimension
#> [1] "<dimension ref=\"A1:K33\"/>"
wb$worksheets[[1]]$sheet_data$cc %>% head()
#>    r row_r c_r c_s       c_t c_cm c_ph c_vm v f f_t f_ref f_ca f_si
#> 1 A1     1   A     inlineStr                                       
#> 2 B1     1   B     inlineStr                                       
#> 3 C1     1   C     inlineStr                                       
#> 4 D1     1   D     inlineStr                                       
#> 5 E1     1   E     inlineStr                                       
#> 6 F1     1   F     inlineStr                                       
#>                     is typ
#> 1  <is><t>mpg</t></is>   4
#> 2  <is><t>cyl</t></is>   4
#> 3 <is><t>disp</t></is>   4
#> 4   <is><t>hp</t></is>   4
#> 5 <is><t>drat</t></is>   4
#> 6   <is><t>wt</t></is>   4

# post download
wb <- wb_load("~/Downloads/mtcars(1).xlsx")
wb$worksheets[[1]]$dimension
#> character(0)
wb$worksheets[[1]]$sheet_data$cc %>% head()
#>    r row_r c_r c_s c_t c_cm c_ph c_vm v f f_t f_ref f_ca f_si is
#> 1 A1     1   A   1   s                0                         
#> 2 B1     1   B   1   s                1                         
#> 3 C1     1   C   1   s                2                         
#> 4 D1     1   D   1   s                3                         
#> 5 E1     1   E   1   s                4                         
#> 6 F1     1   F   1   s                5

[Jeff-White-AZ]

First to note that I just tested this problem again with Google Sheets. The default sheet has dimensions of A1:Z999, but in my test case, I only entered data from A1:C10. Upon downloading as xlsx, re-opening with Excel, and typing ctrl-end (to reach the "end" of the workbook), the cell C10 is active, so Google or MS has improved the behavior of the downloaded files.
Nonetheless, I still have many files where the extent of sheets as determined by ctrl-end is much larger than the dimensions of the actual data. This occasionally causes problems when users try to navigate to the end of their data, or the data are read without explicit handling of rows and columns without data.
To clarify what I wrote before, the data might occupy A4:G105 and would be processed as a dataframe. But if the initial dimension of the worksheet, as indicated by ctrl-end, is much larger, e.g., A1:Z250 and I write the modified data frame back to A4:G105, the dimension of the sheet appears to remain A1:Z250.
I am looking for a simple way to reduce the sheet dimension to A1:G105.
From the comment, that openxlsx2 doesn't have functions to remove columns or rows, it looks like the best approach is:

1. Eliminate empty rows and columns before processing the workbooks.
2. Or, developing a function that copies the data and formatting from a sheet, removes the sheet, and then rebuilds the sheet.
   The command wb$worksheets[[1]]$dimension looks useful for controlling this process.
   Thanks for your response.

[JanMarvin]

If the issue is in the dimension tag, that’s just a single node to modify. And you should be able to get the dimensions of the actual data with wb_to_df(). Disable column names and remove empty columns. For reference openxlsx uses something like <dimension/> iirc, and the file is still readable in spreadsheet software.

Otherwise, if you are able, you could send me an example sheet. The mail address is in the GitHub profile. I somehow doubt, that the cells are filled, otherwise googles exported xlsx files would all be quite huge.

[JanMarvin]

Something I hacked together yesterday night and sent to Jeff

library(openxlsx2)

wb_remove_blank_cells <- function(wb) {

  wb <- wb$clone()

  for (i in seq_along(wb$worksheets)) {

    # reduce worksheet to cells that are filled
    cc <- wb$worksheets[[i]]$sheet_data$cc
    if (any(cc$f != "")) stop("cannot clean up worksheets with formulas")
    sel <- cc$v != "" | cc$is != ""
    cc <- cc[sel, ]
    wb$worksheets[[i]]$sheet_data$cc <- cc

    # get filled worksheet dimension
    cols <- col2int(unique(cc$c_r))
    rows <- as.integer(unique(cc$row_r))

    top_left     <- paste0(int2col(min(cols)), min(rows))
    bottom_right <- paste0(int2col(max(cols)), max(rows))

    # update xml, dimension and active cell
    wb$worksheets[[i]]$dimension  <- sprintf("<dimension ref=\"%s:%s\"/>", top_left, bottom_right)
    wb$worksheets[[i]]$freezePane <- sprintf("<selection activeCell=\"%s\" sqref=\"%s\"/>", top_left, top_left)

    wscols <- wb$worksheets[[i]]$unfold_cols()
    wscols[min(wscols$min) <= max(cols), ]
    wb$worksheets[[i]]$fold_cols(col_df = wscols)

    wsrows <- wb$worksheets[[i]]$sheet_data$row_attr
    wsrows[wsrows$r <= max(rows), ]
    wb$worksheets[[i]]$sheet_data$row_attr <- wsrows

    # set top left cell
    wb$set_sheetview(sheet = i, top_left_cell = top_left)
  }

  invisible(wb)
}