R语言openxlsx包学习

setwd("C:/data/rdata/我的R/openxlsx/")


###################加载包#############################
library(ggplot2)#图层语法数据可视化
library(ggthemes)#ggplot2主题包
library(tidyr)#数据框拉长与拉宽
library(readr)#高性能读写数据包
library(readxl)#高性能读写excel包
library(data.table)#高性能分组计算包
library(plyr)#分组计算包,比data.table速度慢,但是语法方便些
library(xlsx)#读写excel文件包,需要安装java
library(dplyr)#plyr包升级版
library(stringr)#文本处理包,支持正则表达式
#library(maptools)
library(openxlsx)



#已经命名的所有的颜色变量都在colours()中



#read.xlsx Read from an Excel file or Workbook object---------------------
#read.xlsx(xlsxFile, sheet = 1, startRow = 1, colNames = TRUE, rowNames = FALSE, detectDates = FALSE, skipEmptyRows = TRUE, skipEmptyCols = TRUE, rows = NULL, cols = NULL, check.names = FALSE,         namedRegion = NULL, na.strings = "NA", fillMergedCells = FALSE)






#write.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))







#writeData Write an object to a worksheet---------------------------
#writeData(wb, sheet, x, startCol = 1, startRow = 1, xy = NULL, colNames = TRUE, rowNames = FALSE, headerStyle = NULL, borders = c("none", "surrounding", "rows", "columns", "all"), borderColour = getOption("openxlsx.borderColour", "black"), borderStyle = getOption("openxlsx.borderStyle", "thin"),withFilter = FALSE, keepNA = FALSE, name = NULL, sep = ", ")

## Create Workbook object and add worksheets 
wb <- createWorkbook()
## Add worksheets 
addWorksheet(wb, "Cars") 
addWorksheet(wb, "Formula")
x <- mtcars[1:6,] 
writeData(wb, "Cars", x, startCol = 2, startRow = 3, rowNames = TRUE)
##################################################################################### ## Bordering
writeData(wb, "Cars", x, rowNames = TRUE, startCol = "O", startRow = 3, borders="surrounding", borderColour = "black") ## black border
writeData(wb, "Cars", x, rowNames = TRUE, startCol = 2, startRow = 12, borders="columns")
writeData(wb, "Cars", x, rowNames = TRUE,
          startCol="O", startRow = 12, borders="rows")

## Header Styles 
hs1 <- createStyle(fgFill = "#DCE6F1", halign = "CENTER", textDecoration = "italic", border = "Bottom")
writeData(wb, "Cars", x, colNames = TRUE, rowNames = TRUE, startCol="B", startRow = 23, borders="rows", headerStyle = hs1, borderStyle = "dashed")
hs2 <- createStyle(fontColour = "#ffffff", fgFill = "#4F80BD", halign = "center", valign = "center", textDecoration = "bold", border = "TopBottomLeftRight")
writeData(wb, "Cars", x, colNames = TRUE, rowNames = TRUE,startCol="O", startRow = 23, borders="columns", headerStyle = hs2)
saveWorkbook(wb, "writeDataExample.xlsx", overwrite = TRUE)







#readWorkbook Read from an Excel file or Workbook object----------------------
#readWorkbook(xlsxFile, sheet = 1, startRow = 1, colNames = TRUE, rowNames = FALSE, detectDates = FALSE, skipEmptyRows = TRUE, skipEmptyCols = TRUE, rows = NULL, cols = NULL, check.names = FALSE,namedRegion = NULL, na.strings = "NA", fillMergedCells = FALSE)

xlsxFile <- system.file("readTest.xlsx", package = "openxlsx") 
df1 <- readWorkbook(xlsxFile = xlsxFile, sheet = 1)
xlsxFile <- system.file("readTest.xlsx", package = "openxlsx")
df1 <- readWorkbook(xlsxFile = xlsxFile, sheet = 1, rows = c(1, 3, 5), cols = 1:3)














#addStyle Add a style to a set ofcells------------------
#输出的工作本中工作表列有筛选功能
if(file.exists("addFilterExample.xlsx")){
  file.remove("addFilterExample.xlsx")
}
wb <- createWorkbook() 
addWorksheet(wb, "Sheet 1") 
addWorksheet(wb, "Sheet 2") 
addWorksheet(wb, "Sheet 3")
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)




#addStyle Add a style to a set ofcells-------------------
#为excel文件工作表创建主题
## See package vignette for more examples. 
## Create a new workbook 
wb <- createWorkbook("My name here")
## Add a worksheets 
addWorksheet(wb, "Expenditure", gridLines = FALSE)
#gridLines网格线设置,false设置为空
##write data to worksheet 1 
writeData(wb, sheet = 1, USPersonalExpenditure, rowNames = TRUE) #rowNames为行名字

## 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)





#addStyle Add a style to a set ofcells---------------------
## 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") #表的底端的tab按钮的颜色
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)






#createStyle Create a cell style----------------------
## See package vignettes for further examples 
## Modify default values of border colour and border line style 
options("openxlsx.borderColour" = "#4F80BD") 
options("openxlsx.borderStyle" = "thin")
## Size 18 Arial, Bold, left horz. aligned, fill colour #1A33CC, all borders, 
style <- createStyle(fontSize = 18, fontName = "Arial", textDecoration = "bold", halign = "left", fgFill = "#1A33CC", border= "TopBottomLeftRight")
## Red, size 24, Bold, italic, underline, center aligned Font, bottom border 
style <- createStyle(fontSize = 24, fontColour = rgb(1,0,0), textDecoration = c("bold", "italic", "underline"), halign = "center", valign = "center", border = "Bottom")
# borderColour is recycled for each border or all colours can be supplied
# colour is recycled 3 times for "Top", "Bottom" & "Right" sides. 
createStyle(border = "TopBottomRight", borderColour = "red")
# supply all colours
createStyle(border = "TopBottomLeft", borderColour = c("red","yellow", "green"))







#createWorkbook Create a new Workbook object---------------
## Create a new workbook 
wb <- createWorkbook()
## Save workbook to working directory
saveWorkbook(wb, file = "createWorkbookExample.xlsx", overwrite = TRUE)






#insertPlot Insert the current plot into a worksheet-----------------
## Not run: ## Create a new workbook 
wb <- createWorkbook()
## Add a worksheet 
addWorksheet(wb, "Sheet 1", gridLines = FALSE)
## create plot objects 
require(ggplot2) 
p1 <- qplot(mpg, data=mtcars, geom="density", fill=as.factor(gear), alpha=I(.5), main="Distribution of Gas Mileage")
p2 <- qplot(age, circumference, data = Orange, geom = c("point", "line"), colour = Tree)
## Insert currently displayed plot to sheet 1, row 1, column 1 
print(p1) #plot needs to be showing 
insertPlot(wb, 1, width = 5, height = 3.5, fileType = "png", units = "in")
## Insert plot 2 print(p2)
insertPlot(wb, 1, xy = c("J", 2), width = 16, height = 10, fileType = "png", units = "cm")
## Save workbook 
saveWorkbook(wb, "insertPlotExample.xlsx", overwrite = TRUE) ## End(Not run)





#mergeCells Merge cells within a worksheet----------------------------
#合并单元格
## Create a new workbook 
wb <- createWorkbook()
## Add a worksheet 
addWorksheet(wb, "Sheet 1") 
addWorksheet(wb, "Sheet 2")
## Merge cells: Row 2 column C to F (3:6) 
mergeCells(wb, "Sheet 1", cols = 2, rows = 3:6)
## Merge cells:Rows 10 to 20 columns A to J (1:10) 
mergeCells(wb, 1, cols = 1:10, rows = 10:20)
## Intersecting merges 
mergeCells(wb, 2, cols = 1:10, rows = 1) 
mergeCells(wb, 2, cols = 5:10, rows = 2) 
mergeCells(wb, 2, cols = c(1,10), rows = 12) 
## equivalent to 1:10 as only min/max are used #mergeCells(wb, 2, cols = 1, rows = c(1,10)) 
# Throws error because intersects existing merge
## remove merged cells 
removeCellMerge(wb, 2, cols = 1, rows = 1) 
# removes any intersecting merges 
mergeCells(wb, 2, cols = 1, rows = 1:10) 
# Now this works
## Save workbook
saveWorkbook(wb, "mergeCellsExample.xlsx", overwrite = TRUE)





#openXL Open a Microsoft Excel file (xls/xlsx) or an openxlsx Workboo------------------
wb <- createWorkbook() 
x <- mtcars[1:6,] 
addWorksheet(wb, "Cars") 
writeData(wb, "Cars", x, startCol = 2, startRow = 3, rowNames = TRUE)
openXL(wb)






#saveWorkbook save Workbook to file
## Create a new workbook and add a worksheet 
wb <- createWorkbook("Creator of workbook") 
addWorksheet(wb, sheetName = "My first worksheet")
## Save workbook to working directory
saveWorkbook(wb, file = "saveWorkbookExample.xlsx", overwrite = TRUE)




#setColWidths Set worksheet column widths
#setColWidths(wb, sheet, cols, widths = 8.43, hidden = rep(FALSE, length(cols)), ignoreMergedCells = FALSE)

## Create a new workbook 
wb <- createWorkbook()
## Add a worksheet 
addWorksheet(wb, "Sheet 1")
## set col widths 
setColWidths(wb, 1, cols = c(1,4,6,7,9), widths = c(16,15,12,18,33))
## auto columns 
addWorksheet(wb, "Sheet 2") 
writeData(wb, sheet = 2, x = iris) 
setColWidths(wb, sheet = 2, cols = 1:5, widths = "auto")
## Save workbook
saveWorkbook(wb, "setColWidthsExample.xlsx", overwrite = TRUE)




#setRowHeights Description Set worksheet row heights--------------------------
## Create a new workbook 
wb <- createWorkbook()
## Add a worksheet 
addWorksheet(wb, "Sheet 1")
## set row heights
setRowHeights(wb, 1, rows = c(1,2,22,2,19), heights = c(24,28,32,42,33))
## overwrite row 1 height 
setRowHeights(wb, 1, rows = 1, heights = 40)
setRowHeights(wb,1,rows = 2,heights = "auto")
## Save workbook
saveWorkbook(wb, "setRowHeightsExample.xlsx", overwrite = TRUE)




#sheets Returns names ofworksheets.------------------------------
## Create a new workbook 
wb <- createWorkbook()
## Add some worksheets 
addWorksheet(wb, "Worksheet Name") 
addWorksheet(wb, "This is worksheet 2")
addWorksheet(wb, "The third worksheet")
## Return names of sheets, can not be used for assignment. 
names(wb) # openXL(wb)
names(wb) <- c("A", "B", "C") 
names(wb)
# openXL(wb)





#showGridLines Set worksheet gridlines to show or hide.-----------------------
wb <- loadWorkbook(file = system.file("loadExample.xlsx", package = "openxlsx")) 
names(wb) ## list worksheets in workbook 
showGridLines(wb, 1, showGridLines = FALSE) 
showGridLines(wb, "testing", showGridLines = FALSE)
saveWorkbook(wb, "showGridLinesExample.xlsx", overwrite = TRUE)

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,688评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,559评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,749评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,581评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,741评论 3 271
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,684评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,122评论 2 292
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,847评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,441评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,939评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,333评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,783评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,275评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,830评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,444评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,553评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,618评论 2 249

推荐阅读更多精彩内容