Inserting Data into an Existing Table

[ricewhitlam]

I have a template containing multiple tables. Other formulae in the workbook calculate based on references to these tables (mostly array formulae that operate on full columns of these tables). I would like to open the workbook in R via openxlsx2, overwrite the data that is in the table with new data (possibly with more or fewer rows), and then save this populated workbook without any of the references to the tables being broken. As far as I can tell, I can remove tables and create tables, but I cannot replace the data in an existing table. Is there a way to do this, either directly or in a roundabout way?

Attached is a very simple spreadsheet - to give a concrete example, suppose that I wanted to replace the data in columns a, b, c, and d on Sheet1 with {1,2,3,4}, {5,6,7,8}, {9,10,11,12}, and {13,14,15,16}. I would also want the arrays on Sheet2 to update correctly.
Example.xlsx

[JanMarvin]

Hi @ricewhitlam , appreciate your interest and the example, but currently this is not possible, it requires modifications to the table XML in addition to the worksheet and unfortunately for you I have no personal use case for this to dive into.
Maybe there will be some rainy day that will change this.

[ricewhitlam]

Darn! Appreciate your response.

[JanMarvin]

Something like this updates the table as requested:

library(openxlsx2)

# create example data frame
df <- as.data.frame(matrix(1:16, 4))
colnames(df) <- letters[1:4]

# load workbook and update cells
wb <- wb_load("https://github.com/JanMarvin/openxlsx2/files/11105093/Example.xlsx")
wb$add_data(sheet = 1, dims = "A2:D5", x = df, colNames = FALSE)

# update table xml
wb$tables$tab_ref <- "A1:D5"
wb$tables$tab_xml <- xml_attr_mod(wb$tables$tab_xml, c(ref = "A1:D5"))

# wb$open()

[ricewhitlam]

This works! Fantastic, I will get a lot of use out of this. When I have stuff working how I need it to I will share some of my code, in case it proves useful for anyone else doing something similar.

[ricewhitlam]

Ok, I am positive that this could be coded nicer than what I have here, but this does appear to work. The Replace_Table_Data function below will replace the data in an existing table. Big thanks for providing the code to adjust the table, @JanMarvin!

Extract_Excel_Reference <- function(str_ref){
  
  str_components <- strsplit(str_ref, split = "")[[1]]
  
  curr_total_str <- c()
  curr_type <- "character"
  
  final_list <- c()
  
  n <- length(str_components)
  
  for(i in seq_len(n)){
    
    char <- str_components[i]
    
    if(char == ":"){
      next
    }
    
    if(is.na(as.numeric(char))){
      new_type <- "character"
    } else{
      new_type <- "numeric"
    }
    
    if(new_type != curr_type){
      final_list <- c(final_list, curr_total_str)
      curr_total_str <- char
      curr_type <- new_type
      
      if(i == n){
        final_list <- c(final_list, curr_total_str)
      }
      
    } else{
      curr_total_str <- paste(c(curr_total_str, char), collapse = "")
      
      if(i == n){
        final_list <- c(final_list, curr_total_str)
      }
      
    }
    
  }
  
  return(final_list)
  
}

Replace_Table_Data <- function(wb, tab_name_list, datatable_list){
  
  for(i in seq_len(length(tab_name_list))){
    
    curr_tab_name <- tab_name_list[i]
    curr_datatable <- datatable_list[[i]]
    
    data_rows <- nrow(curr_datatable)
    data_cols <- ncol(curr_datatable)
    
    if(data_rows == 0){
      stop(paste0("datatable ", i, " is empty!"))
    }
    
    if(!(curr_tab_name %in% wb$tables$tab_name)){
      stop(paste0("tab_name ", i, " is not a name of one of the existing tables in the workbook!"))
    }
    
    existing_tab <- wb_read(wb, namedRegion = curr_tab_name)
    existing_rows <- nrow(existing_tab)
    existing_cols <- ncol(existing_tab)
    
    if(data_cols != existing_cols){
      stop(paste0("Number of columns in datatable ", i, " does not match number of columns in table!"))
    }
    
    curr_tab_index <- which(wb$tables$tab_name == curr_tab_name)
    curr_tab_sheet <- wb$tables$tab_sheet[curr_tab_index]
    curr_tab_ref <- wb$tables$tab_ref[curr_tab_index]
    curr_tab_ref_expanded <- Extract_Excel_Reference(curr_tab_ref)
    curr_tab_rows <- c(as.numeric(curr_tab_ref_expanded[2])+1, as.numeric(curr_tab_ref_expanded[4]))
    curr_tab_cols <- c(col2int(curr_tab_ref_expanded[1]), col2int(curr_tab_ref_expanded[3]))

    delete_data(wb, curr_tab_sheet, curr_tab_cols[1]:curr_tab_cols[2], curr_tab_rows[1]:curr_tab_rows[2])
    
    new_tab_ref_expanded <- curr_tab_ref_expanded
    new_tab_ref_expanded[4] <- data_rows+as.numeric(new_tab_ref_expanded[2])
    new_tab_ref <- paste(c(new_tab_ref_expanded[1], new_tab_ref_expanded[2], ":", new_tab_ref_expanded[3], new_tab_ref_expanded[4]), collapse = "")
    new_tab_ref_data <- paste(c(new_tab_ref_expanded[1], as.numeric(new_tab_ref_expanded[2])+1, ":", new_tab_ref_expanded[3], new_tab_ref_expanded[4]), collapse = "")
    
    wb$add_data(
      sheet = curr_tab_sheet, 
      dims = new_tab_ref_data, 
      x = curr_datatable,
      colNames = FALSE
    )
    
    wb$tables$tab_ref[curr_tab_index] <- new_tab_ref
    wb$tables$tab_xml[curr_tab_index] <- xml_attr_mod(wb$tables$tab_xml[curr_tab_index], c(ref = new_tab_ref))
    
  }
    
}

[JanMarvin]

Glad you find the function and the package useful. We might add something like the table update function to the package, but there are a couple of things to consider. Modifying rows of same width works as shown above, but removing or renaming rows is a bit trickier. I already had a somewhat working snippet, but it still requires another round of tweaks. I'll let you know once we provide a function to update tables.
Just something to consider: Similar to formulas, modified tables are not evaluated until the file is opened in spreadsheet software. And I was a bit surprised to be honest, that the second sheet was updated too.

[JanMarvin]

For future reference, adding data into the worksheet is possible, but tables bring their own XML file which has to be updated, unless it is overwritten with a same sized data region.

The table is constructed here:

openxlsx2/R/class-workbook.R

Lines 2063 to 2080 in 2a20ee4

	### full table
	table_attrs <- c(
	xmlns = "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
	`xmlns:mc` = "http://schemas.openxmlformats.org/markup-compatibility/2006",
	id = id,
	name = tableName,
	displayName = tableName,
	ref = ref,
	totalsRowCount = totalsRowCount,
	totalsRowShown = "0"
	#headerRowDxfId="1"
	)
	tab_xml_new <- xml_node_create(
	xml_name = "table",
	xml_children = c(autofilter, tableColumns, tableStyleXML),
	xml_attributes = table_attrs
	)

In the table attributes we'd have to update the total row count and ref, which should be something like "A1:D4". In addition the table XML carries XML children for all columns and if we have removed/renamed a column we have to update/remove it inside the XML.

Otherwise the table will contain only subsets (if we simply write more rows than before) or the table XML will be treated as broken (if we write more or less columns or columns with different names).

Ideally we should check if the data area we write to with wb_add_data() overlaps with a table and update it on the fly, but as stated, while I see much Excel stuff on a daily basis, none uses tables.