在学习R的过程中发现一个非常好的工具包openxlsx废话不说，直接上代码
library(openxlsx)
#直接打开文件
openXL('input.xlsx')
#创建工作块
wb <- createWorkbook()
addWorksheet(wb, "input") #sheetname可以随意命名
addWorksheet(wb, "yield")
addWorksheet(wb, "cooling")
writeData(wb, 1, iris)
addFilter(wb, 1, row = 1, cols = 1:ncol(iris))
## Equivalently
writeData(wb, 2, x = iris, withFilter = TRUE)
## Similarly
writeDataTable(wb, 3, iris)
saveWorkbook(wb, file = "addFilterExample.xlsx", overwrite = TRUE) #简直就是要逆天，轻而易举将表格汇总
2. 添加风格
wb <- createWorkbook("My name here")
## Add a worksheets
addWorksheet(wb, "Expenditure", gridLines = FALSE)
##write data to worksheet 1
writeData(wb, sheet = 1, USPersonalExpenditure, rowNames = TRUE)
## create and add a style to the column headers
headerStyle <- createStyle(fontSize = 14, fontColour = "#FFFFFF", halign = "center",
fgFill = "#4F81BD", border="TopBottom", borderColour = "#4F81BD")
addStyle(wb, sheet = 1, headerStyle, rows = 1, cols = 1:6, gridExpand = TRUE)
## style for body
bodyStyle <- createStyle(border="TopBottom", borderColour = "#4F81BD")
addStyle(wb, sheet = 1, bodyStyle, rows = 2:6, cols = 1:6, gridExpand = TRUE)
setColWidths(wb, 1, cols=1, widths = 21) ## set column width for row names column
saveWorkbook(wb, "addStyleExample.xlsx", overwrite = TRUE)
3. 添加表格
## Create a new workbook
wb <- createWorkbook("Fred")
## Add 3 worksheets
addWorksheet(wb, "Sheet 1")
addWorksheet(wb, "Sheet 2", gridLines = FALSE)
addWorksheet(wb, "Sheet 3", tabColour = "red")
addWorksheet(wb, "Sheet 4", gridLines = FALSE, tabColour = "#4F81BD")
## Headers and Footers
addWorksheet(wb, "Sheet 5",
header = c("ODD HEAD LEFT", "ODD HEAD CENTER", "ODD HEAD RIGHT"),
footer = c("ODD FOOT RIGHT", "ODD FOOT CENTER", "ODD FOOT RIGHT"),
evenHeader = c("EVEN HEAD LEFT", "EVEN HEAD CENTER", "EVEN HEAD RIGHT"),
evenFooter = c("EVEN FOOT RIGHT", "EVEN FOOT CENTER", "EVEN FOOT RIGHT"),
firstHeader = c("TOP", "OF FIRST", "PAGE"),
firstFooter = c("BOTTOM", "OF FIRST", "PAGE"))
addWorksheet(wb, "Sheet 6",
header = c("&[Date]", "ALL HEAD CENTER 2", "&[Page] / &[Pages]"),
footer = c("&[Path]&[File]", NA, "&[Tab]"),
firstHeader = c(NA, "Center Header of First Page", NA),
firstFooter = c(NA, "Center Footer of First Page", NA))
addWorksheet(wb, "Sheet 7",
header = c("ALL HEAD LEFT 2", "ALL HEAD CENTER 2", "ALL HEAD RIGHT 2"),
footer = c("ALL FOOT RIGHT 2", "ALL FOOT CENTER 2", "ALL FOOT RIGHT 2"))
addWorksheet(wb, "Sheet 8",
firstHeader = c("FIRST ONLY L", NA, "FIRST ONLY R"),
firstFooter = c("FIRST ONLY L", NA, "FIRST ONLY R"))
## Need data on worksheet to see all headers and footers
writeData(wb, sheet = 5, 1:400)
writeData(wb, sheet = 6, 1:400)
writeData(wb, sheet = 7, 1:400)
writeData(wb, sheet = 8, 1:400)
## Save workbook
saveWorkbook(wb, "addWorksheetExample.xlsx", overwrite = TRUE)
4. 删除数据
wb <- createWorkbook()
addWorksheet(wb, "Worksheet 1")
x <- data.frame(matrix(runif(200), ncol = 10))
writeData(wb, sheet = 1, x = x, startCol = 2, startRow = 3, colNames = FALSE)
## delete some data
deleteData(wb, sheet = 1, cols = 3:5, rows = 5:7, gridExpand = TRUE)
deleteData(wb, sheet = 1, cols = 7:9, rows = 5:7, gridExpand = TRUE)
deleteData(wb, sheet = 1, cols = LETTERS, rows = 18, gridExpand = TRUE)
saveWorkbook(wb, "deleteDataExample.xlsx", overwrite = TRUE)
5.读取数据
xlsxFile <- system.file("readTest.xlsx", package = "openxlsx")
df1 <- read.xlsx(xlsxFile = xlsxFile, sheet = 1, skipEmptyRows = FALSE)
sapply(df1, class)
df2 <- read.xlsx(xlsxFile = xlsxFile, sheet = 3, skipEmptyRows = TRUE)
df2$Date <- convertToDate(df2$Date)
sapply(df2, class)
head(df2)
df2 <- read.xlsx(xlsxFile = xlsxFile, sheet = 3, skipEmptyRows = TRUE,
detectDates = TRUE)
sapply(df2, class)
head(df2)
wb <- loadWorkbook(system.file("readTest.xlsx", package = "openxlsx"))
df3 <- read.xlsx(wb, sheet = 2, skipEmptyRows = FALSE, colNames = TRUE)
df4 <- read.xlsx(xlsxFile, sheet = 2, skipEmptyRows = FALSE, colNames = TRUE)
all.equal(df3, df4)
wb <- loadWorkbook(system.file("readTest.xlsx", package = "openxlsx"))
df3 <- read.xlsx(wb, sheet = 2, skipEmptyRows = FALSE,
cols = c(1, 4), rows = c(1, 3, 4))
## URL
##
#xlsxFile <- "https://github.com/awalker89/openxlsx/raw/master/inst/readTest.xlsx"
#head(read.xlsx(xlsxFile))
6. 写入xlsx
## write to working directory
options("openxlsx.borderColour" = "#4F80BD") ## set default border colour
write.xlsx(iris, file = "writeXLSX1.xlsx", colNames = TRUE, borders = "columns")
write.xlsx(iris, file = "writeXLSX2.xlsx", colNames = TRUE, borders = "surrounding")
hs <- createStyle(textDecoration = "BOLD", fontColour = "#FFFFFF", fontSize=12,
fontName="Arial Narrow", fgFill = "#4F80BD")
write.xlsx(iris, file = "writeXLSX3.xlsx", colNames = TRUE, borders = "rows", headerStyle = hs)
## Lists elements are written to individual worksheets, using list names as sheet names if available
l <- list("IRIS" = iris, "MTCATS" = mtcars, matrix(runif(1000), ncol = 5))
write.xlsx(l, "writeList1.xlsx", colWidths = c(NA, "auto", "auto"))
## different sheets can be given different parameters
write.xlsx(l, "writeList2.xlsx", startCol = c(1,2,3), startRow = 2,
asTable = c(TRUE, TRUE, FALSE), withFilter = c(TRUE, FALSE, FALSE))
网友评论