R 将. xlsx内容显示为HTML表

发布于03月15日

我可以使用openxlsx包和loadWorkbook函数读取格式化的.xlsx文件，并根据需要对其进行进一步编辑.

一百块？精确的样式并不特别重要，但保留合并的单元格才是.

I though the basictabler package would be the solution since it supports HTML display and saving to an .xlsx file. However, I cannot find how I can get my data into this package's BasicTable object format.
The ideal solution would be turning a single worksheet from an openxlsx workbook into this BasicTable object, which I could then use in Shiny.

根据我所发现的，您只能从头创建BasicTable，或者从一个常规的Systemrame对象创建，这意味着我必须手工合并所有需要的单元格.

I can't put a reprex of an Excel file, but I can picture a minimal example with a single merged cell.

推荐答案

你说得对，openxlsx::loadWorkbook就是合并的范围.

首先，让我们创建一个xlsx文件用于说明:

library(openxlsx)

# create XLSX file for illustration
wb <- createWorkbook()
## Add a worksheet
addWorksheet(wb, "Sheet 1")
## write iris dataset
writeData(wb, "Sheet 1", iris)
## Merge cells: Row 2 column C to F (3:6)
mergeCells(wb, "Sheet 1", cols = 2, rows = 3:6)
## Merge cells:Rows 10 to 20 columns B to E (2:5)
mergeCells(wb, "Sheet 1", cols = 2:5, rows = 10:20)
##
saveWorkbook(wb, "mergeCellsExample.xlsx", overwrite = TRUE)

现在让我们加载它，就像它被赋予使用一样，并提取有关合并的信息:

# load workbook
wb <- loadWorkbook("mergeCellsExample.xlsx")
# get merged cells
merges <- wb$worksheets[[1]]$mergeCells
# get excel ranges of the merges
excelRanges <- gsub(".*\"(.+)\".*", "\\1", merges)
# convert them to (rFrom, cFrom, rSpan, cSpan) for usage in 'basictabler'
RCRC <- function(excelRange) {
  cl <- cellranger::as.cell_limits(excelRange)
  list(
    "rFrom" = cl$ul[1],
    "cFrom" = cl$ul[2],
    "rSpan" = cl$lr[1] - cl$ul[1] + 1L,
    "cSpan" = cl$lr[2] - cl$ul[2] + 1L  
  )
}
rcrcList <- lapply(excelRanges, RCRC)

现在，让我们使用basictabler包:

## get the dataframe
dat <- read.xlsx("mergeCellsExample.xlsx", sheet = "Sheet 1")

## construct the table
library(basictabler)
tbl <- BasicTable$new()
tbl$addData(dat)

## merge the cells
lapply(rcrcList, function(rcrc) {
  invisible(
    do.call(tbl$mergeCells, rcrc)
  )
})

## show table
tbl$renderTable()