Convert html tables to openxlsx2

[JanMarvin]

Similar to https://github.com/pteridin/flexlsx it should be possible for a third party package to convert html tables to openxlsx2. The benefit would be that all html generating packages, like pivottabler and gt, would be able to write to xlsx. Tough for this to work, we'd have to parse styled html into R and when I had a look that did not seem to simple.

In the meantime what works is using libreoffice --headless.

library(gt)

# Define the start and end dates for the data range
start_date <- "2010-06-07"
end_date <- "2010-06-14"

# Create a gt table based on preprocessed
# `sp500` table data
tab <- sp500 |>
  dplyr::filter(date >= start_date & date <= end_date) |>
  dplyr::select(-adj_close) |>
  gt() |>
  tab_header(
    title = "S&P 500",
    subtitle = glue::glue("{start_date} to {end_date}")
  ) |>
  fmt_currency() |>
  fmt_date(columns = date, date_style = "wd_m_day_year") |>
  fmt_number(columns = volume, suffixing = TRUE)

tab |> gt::gtsave("/tmp/test.html")

# convert html to xlsx
tmp <- "/tmp/test.html"
tmp_dir <- tempdir()
system2(
  # needs a path to soffice. C:/...
  "/Applications/LibreOffice.app/Contents/MacOS/soffice", 
  sprintf("--headless --calc --convert-to xlsx:\"Calc MS Excel 2007 XML\" %s --outdir %s", tmp, tmp_dir)
)

fl <- paste0(tmp_dir, "/", tools::file_path_sans_ext(basename(tmp)), ".xlsx")

library(openxlsx2)
wb <- wb_load(fl)
wb_to_df(wb)
#>                    S&P 500        NA        NA        NA        NA     NA
#> 2 2010-06-07 to 2010-06-14      <NA>      <NA>      <NA>      <NA>   <NA>
#> 3                     date      open      high       low     close volume
#> 4        Mon, Jun 14, 2010 $1,095.00 $1,105.91 $1,089.03 $1,089.63  4.43B
#> 5        Fri, Jun 11, 2010 $1,082.65 $1,092.25 $1,077.12 $1,091.60  4.06B
#> 6        Thu, Jun 10, 2010 $1,058.77 $1,087.85 $1,058.77 $1,086.84  5.14B
#> 7         Wed, Jun 9, 2010 $1,062.75 $1,077.74 $1,052.25 $1,055.69  5.98B
#> 8         Tue, Jun 8, 2010 $1,050.81 $1,063.15 $1,042.17 $1,062.00  6.19B
#> 9         Mon, Jun 7, 2010 $1,065.84 $1,071.36 $1,049.86 $1,050.47  5.47B