Skip to contents

Modify column widths of a worksheet

Source: R/class-workbook-wrappers.R
col_widths-wb.Rd

Remove / set worksheet column widths to specified width or "auto".

Usage

wb_set_col_widths(
  wb,
  sheet = current_sheet(),
  cols,
  widths = 8.43,
  hidden = FALSE
)

wb_remove_col_widths(wb, sheet = current_sheet(), cols)

Arguments

wb

A wbWorkbook object.

sheet

A name or index of a worksheet, a vector in the case of remove_

cols

Indices of cols to set/remove column widths.

widths

Width to set cols to specified column width or "auto" for automatic sizing. widths is recycled to the length of cols. openxlsx2 sets the default width is 8.43, as this is the standard in some spreadsheet software. See Details for general information on column widths.

hidden

Logical vector recycled to the length of cols. If TRUE, the columns are hidden.

Details

The global min and max column width for "auto" columns is set by (default values show):

  • options("openxlsx2.minWidth" = 3)

  • options("openxlsx2.maxWidth" = 250) Maximum width allowed in Excel

NOTE: The calculation of column widths can be slow for large worksheets.

NOTE: The hidden parameter may conflict with the one set in wb_group_cols(); changing one will update the other.

NOTE: The default column width varies by spreadsheet software, operating system, and DPI settings used. Setting widths to specific value also is no guarantee that the output will have consistent column widths.

For automatic text wrapping of columns use wb_add_cell_style(wrap_text = TRUE)

See also

Other workbook wrappers: base_font-wb, creators-wb, grouping-wb, row_heights-wb, wb_add_chartsheet(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_pivot_table(), wb_add_slicer(), wb_add_worksheet(), wb_base_colors, wb_clone_worksheet(), wb_copy_cells(), wb_freeze_pane(), wb_merge_cells(), wb_save(), wb_set_last_modified_by(), wb_workbook()

Other worksheet content functions: filter-wb, grouping-wb, named_region-wb, row_heights-wb, wb_add_conditional_formatting(), wb_add_data(), wb_add_data_table(), wb_add_formula(), wb_add_pivot_table(), wb_add_slicer(), wb_add_thread(), wb_freeze_pane(), wb_merge_cells()

Examples

## Create a new workbook
wb <- wb_workbook()

## Add a worksheet
wb$add_worksheet("Sheet 1")

## set col widths
wb$set_col_widths(cols = c(1, 4, 6, 7, 9), widths = c(16, 15, 12, 18, 33))

## auto columns
wb$add_worksheet("Sheet 2")
wb$add_data(sheet = 2, x = iris)
wb$set_col_widths(sheet = 2, cols = 1:5, widths = "auto")

## removing column widths
## Create a new workbook
wb <- wb_load(file = system.file("extdata", "openxlsx2_example.xlsx", package = "openxlsx2"))

## remove column widths in columns 1 to 20
wb_remove_col_widths(wb, 1, cols = 1:20)