How to modifying a data table with openxlsx2?

[maximecharriere]

Copy of my question on StackOverflow https://stackoverflow.com/posts/78808673/edit

The data
We have an Excel data table, named data_table, in the sheet S1, positioned at an unknown position.

The goal
We want to load the file, modify the colum COL_1, and save the file with the modified data table. The best would be for the formatting and equations to remain unchanged.

Current solution
For now I have to delete the previous data table to modify it:

library(openxlsx2)

# Load the workbook
wb <- wb_load("data_table_test.xlsx")

# Get the data tables info
data_tables_info <- wb_get_tables(wb, sheet = "S1")

# Get the data table position
data_table_pos <- data_tables_info$tab_ref[data_tables_info$tab_name == "data_table"]

# Read the data table
data_table <- wb_to_df(wb, sheet = "S1", dims = data_table_pos)

# modify the data table
data_table$COL_1 <- as.integer(data_table$COL_1) + 1000

# remove the data table from the worksheet
wb <- wb_remove_tables(wb, sheet = "S1", table = "data_table")

# write the data back to the data table
wb <- wb_add_data_table(wb, x = data_table, dims = data_table_pos, sheet = "S1", table_name = "data_table")

# Save the workbook
wb_save(wb, "data_table_test_out.xlsx")

Question
How to modify the data of a data table in a worksheet ?

[JanMarvin]

Hi @maximecharriere , you could try something like the code below, but be aware, with tables updates are always a bit risky, because there might be filters and slicers attached to a table and they are not updated (I believe there is a warning with wb_update_table() regarding this). So if the table is sorted, the sorting will not automatically update. And elements that are not selected are simply hidden in xml, this is also not updated. Therefore with filters there might be additional issues.

library(openxlsx2)

# prepare example data
df <- data.frame(
  COL_1 = 1:4,
  COL_2 = c("foo", "boo", "foo", "boo"),
  COL_3 = letters[1:4]
)

# prepare example workbook
wb <- wb_workbook()$
  add_worksheet("S1")$
  add_data_table(x = df, dims = "C5", table_name = "data_table")$
  add_font(bold = TRUE, dims = "D7")$
  add_font(color = wb_color("red"), dims = "E8")$
  add_fill(color = wb_color("darkgreen"), dims = "E8")

# get table position
data_tables_info <- wb_get_tables(wb, sheet = "S1")
data_table_pos <- data_tables_info$tab_ref[data_tables_info$tab_name == "data_table"]

# Read the data table
data_table <- wb_to_df(wb, sheet = "S1", dims = data_table_pos)

# modify the data table
data_table$COL_1 <- as.integer(data_table$COL_1) + 1000

# get position to update
first_table_cell <- dims_to_dataframe(data_table_pos, fill = TRUE)[1, 1]
col1_pos         <- wb_dims(x = data_table, from_dims = first_table_cell, cols = "COL_1")

# write updated data
wb$add_data(x = data_table$COL_1, col_names = FALSE, dims = col1_pos)

if (interactive()) wb$open()

[maximecharriere]

The functions dims_to_dataframe, wb_dims, and you way of updating data with wb_add_data is very interesting. It's working well! I also understand the concern about data filtering.

• However, why wb$add_data will keep formating, but not wb$add_data_table?
• And how to update the entire data table with wb_add_data ? I archived to update the entire data table modifying your code in the following way, but I'm not sure it's the best way to get the dims of the value cells (without the headers, so as not to get the error in wb_check_overwrite_tables) :

library(openxlsx2)

# prepare example data
df <- data.frame(
  COL_1 = 1:4,
  COL_2 = c("foo", "boo", "foo", "boo"),
  COL_3 = letters[1:4]
)

# prepare example workbook
wb <- wb_workbook()$
  add_worksheet("S1")$
  add_data_table(x = df, dims = "C5", table_name = "data_table")$
  add_font(bold = TRUE, dims = "D7")$
  add_font(color = wb_color("red"), dims = "E8")$
  add_fill(color = wb_color("darkgreen"), dims = "E8")

# get table position
data_tables_info <- wb_get_tables(wb, sheet = "S1")
data_table_pos <- data_tables_info$tab_ref[data_tables_info$tab_name == "data_table"]

# Read the data table
data_table <- wb_to_df(wb, sheet = "S1", dims = data_table_pos)

# modify the data table
data_table$COL_1 <- as.integer(data_table$COL_1) + 1000
data_table$COL_3 <- letters[5:8]

# get position to update
first_value_cell <- dims_to_dataframe(data_table_pos, fill = TRUE)[2, 1]
values_pos         <- wb_dims(x = data_table, from_dims = first_value_cell)

# write updated data
wb$add_data(x = data_table, col_names = FALSE, dims = values_pos)

if (interactive()) wb$open()

[JanMarvin]

Hi @maximecharriere , to answer your questions

• wb_add_data_table() also keeps the styling as can be seen below. The styling is applied, before the table is written and it will remain (unless remove_cell_style = TRUE). This is because styles in openxlsx2 are supposed to stack. When writing into the worksheet the styles are merged. What happened in your original code though, you removed the table and everything else from the worksheet. Internally a function wb_clean_sheet() is called, because in wb_remove_table() the default of remove_data is FALSE.
  But, because the styles stack, as another solution to your initial question, you also could create a new workbook, with a new table and clone the style from the initial workbook onto this. I won't go further into this, you can find examples in the vignettes or in the book.

library(openxlsx2)

# prepare example data
df <- data.frame(
  COL_1 = 1:4,
  COL_2 = c("foo", "boo", "foo", "boo"),
  COL_3 = letters[1:4]
)

# prepare example workbook
wb <- wb_workbook()$
  add_worksheet("S1")$
  add_font(bold = TRUE, dims = "D7")$
  add_font(color = wb_color("red"), dims = "E8")$
  add_fill(color = wb_color("darkgreen"), dims = "E8")$
  add_data_table(x = df, dims = "C5", table_name = "data_table")

if (interactive()) wb$open()

• You can update multiple columns like this using multiple cols in wb_dims() in combination with the enforce option of wb_add_data().

# get table position
data_tables_info <- wb_get_tables(wb, sheet = "S1")
data_table_pos <- data_tables_info$tab_ref[data_tables_info$tab_name == "data_table"]

# Read the data table
data_table <- wb_to_df(wb, sheet = "S1", dims = data_table_pos)

# modify the data table
data_table$COL_1 <- as.integer(data_table$COL_1) + 1000
data_table$COL_3 <- letters[5:8]

# get position to update: multiple columns selected
first_value_cell <- dims_to_dataframe(data_table_pos, fill = TRUE)[1, 1]
values_pos       <- wb_dims(x = data_table, from_dims = first_value_cell, cols = c("COL_1", "COL_3"))

# write updated data: use enforce to update non consecutive cell regions
wb$add_data(x = data_table, col_names = FALSE, dims = values_pos, enforce = TRUE)

if (interactive()) wb$open()

And if you want to dig further into this

You can think of a worksheet as an R environment. You can place various elements into this environment. You can have dates, character or numeric vectors. But there is one specific object type that requires multiple identical sized vectors and if you want to address elements in this object, you need to know the objects name and the distinct column names. This object is a data frame.

In wb_workbook() objects it's the same. On a worksheet you can have multiple cells, each cell is independent. You can have single or multiple cell regions, with or without styling. Dates, characters, numerics. And there are tables. Tables require a quadratic cell region, carry a name, and require distinct column names. Restrictions for tables are similar to data frames: you can modify parts of a table (let's ignore the selection issues for a moment), but you cannot have parts of a data frame overlapping into another data frame, and you cannot have two data frames of the same name.

So how do we place data in the worksheet

The function that does this is an internal write_data2(). This function converts R objects into the openxlsx2 data structure and places them into the worksheet. This function is called by each function that writes into the worksheet. The two functions wb_add_data_table() and wb_add_data() internally use this, the main difference is, that wb_add_data_table() also constructs a table xml structure and runs a few additional checks. We cannot have dangling table definitions in wb$tables that do not reference a cell or sheet anymore and we cannot have intersecting tables or tables with identical names, otherwise spreadsheet software will complain.
Since wb_add_data_table() always runs the checks and always want's to create the table xml structure, we cannot use it to update a single cell or a cell region in a worksheet. For this we have to use wb_add_data().

What is the table xml structure?

It's something like this. It defines the table name "data_table", the cell region the table is placed into "C5:E9", and the individual columns of the table. This is what wb_add_data_table() created and what can be updated with wb_update_table(). Since this xml is stored separately, we have to check that tables are not accidentally overwritten.

wb$tables$tab_xml %>% as_xml()
#> <table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" id="1" name="data_table" displayName="data_table" ref="C5:E9" totalsRowCount="0" totalsRowShown="0">
#>  <autoFilter ref="C5:E9" />
#>  <tableColumns count="3">
#>   <tableColumn id="1" name="COL_1" />
#>   <tableColumn id="2" name="COL_2" />
#>   <tableColumn id="3" name="COL_3" />
#>  </tableColumns>
#>  <tableStyleInfo name="TableStyleLight9" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0" />
#> </table>

[maximecharriere]

Thank you @JanMarvin for your precise reply! It helps me a lot. I send you love ♥