R语言中的函数19：openxlsx::read.xlsx(), write.xlsx(), writeData(), writeDataTable()

最新推荐文章于 2025-02-23 14:09:19 发布

zoujiahui_2018

最新推荐文章于 2025-02-23 14:09:19 发布

阅读量6.6k

点赞数 4

分类专栏： # R语言中的函数文章标签： r语言开发语言

本文链接：https://blog.csdn.net/qq_18055167/article/details/127868749

版权

R语言中的函数专栏收录该内容

32 篇文章

订阅专栏

本文详细介绍了openxlsx库中的read.xlsx()函数用于读取xlsx文件，包括参数设置和实例演示；随后讲解了writeData()和writeDataTable()函数用于数据写入，以及write.xlsx()函数的使用方法和实例。这些函数在数据处理和Excel文件操作中扮演重要角色，适合数据分析和报告编写。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

read.xlsx()函数介绍

read.xlsx(
  xlsxFile,
  sheet,
  startRow = 1,
  colNames = TRUE,
  rowNames = FALSE,
  detectDates = FALSE,
  skipEmptyRows = TRUE,
  skipEmptyCols = TRUE,
  rows = NULL,
  cols = NULL,
  check.names = FALSE,
  sep.names = ".",
  namedRegion = NULL,
  na.strings = "NA",
  fillMergedCells = FALSE
)

xlsxFile:一个xlsx文件
sheet:sheet的名字或者索引
startRow:开始读取数据的行，文件前面的空行总是会被跳过并忽略startRow
colNames:如果TRUE,数据的第一行就被作为列名使用
rowNames:如果TRUE,数据的第一列会被作为行名使用
detectDates:如果TRUE,将会尝试识别时间对象
skipEmptyRows:如果TRUE, 空行将会跳过，否则数据行之后的空行用NA填充
skipEmptyCols:如果TRUE，空列将会跳过
rows:接受一个整数向量来指定需要读取的行号，如果是NULL则会读取全部的行
cols:接受一个整数向量来指定需要读取的列号，如果是NULL则会读取全部的列
check.names:如果TRUE,数据框中的变量名称将会被检查以保证其语法上是正确的
sep.names:接受一个字符，用去替代空的列名，默认值为’.’
na.strings:指定缺失值的字符串，空的单元格也会被认为是缺失值
fillMergedCells：如果TRUE,合并单元格中的值会分配给每一个被合并的单元格内

实例

在这里插入图片描述

writeData()和writeDataTable()函数介绍

writeData(
  wb,
  sheet,
  x,
  startCol = 1,
  startRow = 1,
  array = FALSE,
  xy = NULL,
  colNames = TRUE,
  rowNames = FALSE,
  headerStyle = openxlsx_getOp("headerStyle"),
  borders = openxlsx_getOp("borders", "none"),
  borderColour = openxlsx_getOp("borderColour", "black"),
  borderStyle = openxlsx_getOp("borderStyle", "thin"),
  withFilter = openxlsx_getOp("withFilter", FALSE),
  keepNA = openxlsx_getOp("keepNA", FALSE),
  na.string = openxlsx_getOp("na.string"),
  name = NULL,
  sep = ", ",
  col.names,
  row.names
)

writeDataTable(
  wb,
  sheet,
  x,
  startCol = 1,
  startRow = 1,
  xy = NULL,
  colNames = TRUE,
  rowNames = FALSE,
  tableStyle = openxlsx_getOp("tableStyle", "TableStyleLight9"),
  tableName = NULL,
  headerStyle = openxlsx_getOp("headerStyle"),
  withFilter = openxlsx_getOp("withFilter", TRUE),
  keepNA = openxlsx_getOp("keepNA", FALSE),
  na.string = openxlsx_getOp("na.string"),
  sep = ", ",
  stack = FALSE,
  firstColumn = openxlsx_getOp("firstColumn", FALSE),
  lastColumn = openxlsx_getOp("lastColumn", FALSE),
  bandedRows = openxlsx_getOp("bandedRows", TRUE),
  bandedCols = openxlsx_getOp("bandedCols", FALSE),
  col.names,
  row.names
)

wb: A Workbook object containing a worksheet.
sheet : The worksheet to write to. Can be the worksheet index or name.
x: Object to be written. For classes supported look at the examples.
startCol :A vector specifying the starting column to write to.
startRow:A vector specifying the starting row to write to.
array: A bool if the function written is of type array
xy: An alternative to specifying startCol and startRow individually. A vector of the form c(startCol, startRow).
colNames: If TRUE, column names of x are written.
rowNames : If TRUE, data.frame row names of x are written.
headerStyle: Custom style to apply to column names.
borders: Either “none” (default), “surrounding”, “columns”, “rows” or respective abbreviations. If “surrounding”, a border is drawn around the data. If “rows”, a surrounding border is drawn with a border around each row. If “columns”, a surrounding border is drawn with a border between each column. If “all” all cell borders are drawn.
borderColour :Colour of cell border. A valid colour (belonging to colours() or a hex colour code, eg see here).
borderStyle: Border line style
withFilter: If TRUE or NA, add filters to the column name row. NOTE can only have one filter per worksheet.
keepNA : If TRUE, NA values are converted to #N/A (or na.string, if not NULL) in Excel, else NA cells will be empty.
na.string: If not NULL, and if keepNA is TRUE, NA values are converted to this string in Excel.
name :If not NULL, a named region is defined.
sep: Only applies to list columns. The separator used to collapse list columns to a character vector e.g. sapply(x$list_column, paste, collapse = sep).
row.names, col.names: Deprecated, please use rowNames, colNames instead

write.xlsx(x, file,startCol=1,startRow=1, asTable = FALSE, overwrite = TRUE, ...)

x :一个data.frame或者list对象，满足可以被writeData()或writeDataTable()写出的要求
file: 用于写出xlsx文件的路径
startCol:开始写入数据的列
startRow:开始写入数据的行
asTable: 如果TRUE将调用writeDataTable()否则将调用writeData()写出数据
overwrite：如果是TRUE则重写已经存在的文件，默认是TRUE

实例

在这里插入图片描述

## Create Workbook object and add worksheets
mywb <- createWorkbook()

## Add worksheets
addWorksheet(mywb, "Cars")
addWorksheet(mywb, "test")

writeData(wb=mywb,
  sheet='test',
  x=df,
  startCol = 2,
  startRow = 2,
  colNames = TRUE,
  rowNames = TRUE,
  headerStyle = openxlsx_getOp("headerStyle"),
  borders = openxlsx_getOp("borders", "columns"),
  borderColour = openxlsx_getOp("borderColour", "red"),
  borderStyle = openxlsx_getOp("borderStyle", "thin"),
  keepNA=TRUE,
  na.string = '缺失值'
)

## Save workbook
saveWorkbook(mywb, "test.xlsx", overwrite = TRUE)

在这里插入图片描述

write.xlsx()函数介绍

write.xlsx(x, file,startCol=1,startRow=1, asTable = FALSE, overwrite = TRUE, ...)

x :一个data.frame或者list对象，满足可以被writeData()或writeDataTable()写出的要求
file: 用于写出xlsx文件的路径
startCol:开始写入数据的列
startRow:开始写入数据的行
asTable: 如果TRUE将调用writeDataTable()否则将调用writeData()写出数据
overwrite：如果是TRUE则重写已经存在的文件，默认是TRUE

实例

在这里插入图片描述