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

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
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 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
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

• 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, ...)
1

• 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

write.xlsx()函数介绍

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

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

实例