如何在Shiny应用中用DT::datatable或替代工具渲染Excel表格并自定义行列宽高？

Let’s break down how to replicate your Excel table’s exact dimensions in Shiny, whether you stick with DT::datatable or use a more Excel-like alternative.

Option 1: Tweak DT::datatable to Match Excel Dimensions #

First, you need to extract the actual column widths and row heights from your Excel file—we’ll use openxlsx for this since it lets us pull those metadata values. Then we’ll feed those into DT with some JavaScript and CSS adjustments.

Step 1: Extract Excel Dimensions #

library(openxlsx)
library(DT)
library(shiny)

# Load your Excel workbook
wb <- loadWorkbook("your_excel_file.xlsx")
target_sheet <- getSheetNames("your_excel_file.xlsx")[1] # Pick your target sheet

# Convert Excel column width units to pixels (1 Excel unit ≈ 8.43 pixels)
excel_col_widths <- getColWidths(wb, sheet = target_sheet)
dt_col_widths <- excel_col_widths * 8.43

# Convert Excel row height points to pixels (1 point ≈ 1.333 pixels)
excel_row_heights <- getRowHeights(wb, sheet = target_sheet)
dt_row_heights <- excel_row_heights * 1.333

Step 2: Render DT with Custom Dimensions #

Pass these converted values into datatable()—use columnDefs for column widths, a rowCallback for row heights, and optional CSS to match the header height:

ui <- fluidPage(
  # Match Excel's header row height
  tags$style(HTML(paste0("
    .dataTables_wrapper .dataTable thead th {
      height: ", dt_row_heights[1], "px;
    }
  "))),
  DTOutput("matching_table")
)

server <- function(input, output) {
  # Load your table data
  table_data <- read.xlsx("your_excel_file.xlsx", sheet = target_sheet)
  
  output$matching_table <- renderDT({
    datatable(
      table_data,
      options = list(
        # Set column widths (DT uses 0-indexed targets)
        columnDefs = lapply(seq_along(dt_col_widths), function(i) {
          list(targets = i - 1, width = paste0(dt_col_widths[i], "px"))
        }),
        # Set data row heights with JavaScript
        rowCallback = JS(paste0("
          function(row, data, index) {
            // Skip header row (index starts at 0 for data rows)
            $(row).css('height', '", dt_row_heights[index + 2], "px');
          }
        ")),
        scrollX = TRUE # Prevent column wrapping for wide tables
      )
    )
  })
}

shinyApp(ui, server)

Note: Adjust the index + 2 in the row callback if your Excel sheet has a different header/data structure.

Option 2: Use rhandsontable for Excel-like Behavior #

If you want a table that feels and acts more like Excel (with simpler dimension control and optional editability), rhandsontable is a perfect alternative. It has built-in functions to set column widths and row heights directly.

Example with rhandsontable #

library(rhandsontable)
library(openxlsx)
library(shiny)

wb <- loadWorkbook("your_excel_file.xlsx")
target_sheet <- getSheetNames("your_excel_file.xlsx")[1]
table_data <- read.xlsx("your_excel_file.xlsx", sheet = target_sheet)

# Reuse the dimension conversion code from Option 1
excel_col_widths <- getColWidths(wb, sheet = target_sheet)
ht_col_widths <- excel_col_widths * 8.43
excel_row_heights <- getRowHeights(wb, sheet = target_sheet)
ht_row_heights <- excel_row_heights * 1.333

ui <- fluidPage(
  rHandsontableOutput("excel_like_table")
)

server <- function(input, output) {
  output$excel_like_table <- renderRHandsontable({
    rhandsontable(table_data) %>%
      hot_cols(widths = ht_col_widths) %>%
      hot_rows(heights = ht_row_heights) %>%
      hot_table(readOnly = TRUE) # Set to FALSE if you want editable cells
  })
}

shinyApp(ui, server)

This will give you a table that mirrors your Excel sheet’s look and behavior without needing messy JavaScript workarounds.

内容的提问来源于stack exchange，提问作者Endre