文章目录

• read.xlsx()函数介绍
• • 实例
• writeData()和writeDataTable()函数介绍
• • 实例
• write.xlsx()函数介绍
• • 实例

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

实例