使用readxl包导入excel文件

  • 主要包含两个函数excel_sheets()，read_excel()
    01. 读取Excel 文件 - 图1

excel_sheet()

用于提取excel 中的表单

  1. # Load the readxl package
  2. library(readxl)
  4. # Print the names of all worksheets
  5. excel_sheets("urbanpop.xlsx")

用于读取excel 表单中的信息到R

read_excel()

  1. # The readxl package is already loaded
  3. # Read the sheets, one by one
  4. pop_1 <- read_excel("urbanpop.xlsx", sheet = 1)
  5. pop_2 <- read_excel("urbanpop.xlsx", sheet = 2)
  6. pop_3 <- read_excel("urbanpop.xlsx", sheet = 3)
  8. # Put pop_1, pop_2 and pop_3 in a list: pop_list
  9. pop_list <- list(pop_1, pop_2, pop_3)
  11. # Display the structure of pop_list
  12. str(pop_list)
  • 通过lapply 函数可以直接将提取的表单传递给read_excel() 函数。
  1. # The readxl package is already loaded
  3. # Read all Excel sheets with lapply(): pop_list
  4. pop_list <- lapply(excel_sheets('urbanpop.xlsx'), read_excel, path = "urbanpop.xlsx")
  6. # Display the structure of pop_list
  7. str(pop_list)

几个参数

默认参数设置为

01. 读取Excel 文件 - 图2

col_types

可以通过向量进行赋值，如text, blank, numeric, date 等。

sheet

选择Excel表格中选定的表单。

skip

类似之前readr包提及的skip。用于跳过某些行内容。

col_names

默认下col_names 值为TRUE，即函数不会自动命名。可以通过赋值或改为FALSE的方式，自定义命名或依靠函数自动命名。

这里可以使用一个小技巧，通过paste() 批量连接信息。

paste(“a”, 0:10)，即代表生成 “a0”, “a1”…”a10”

  1. # The readxl package is already loaded
  3. # Import the first Excel sheet of urbanpop_nonames.xlsx (R gives names): pop_a
  4. pop_a <- read_excel("urbanpop_nonames.xlsx", col_names = FALSE)
  6. # Import the first Excel sheet of urbanpop_nonames.xlsx (specify col_names): pop_b
  7. cols <- c("country", paste0("year_", 1960:1966))
  8. pop_b <- read_excel("urbanpop_nonames.xlsx", col_names = cols)
  11. # Print the summary of pop_a
  12. summary(pop_a)
  14. # Print the summary of pop_b
  15. summary(pop_b)

另外一种导入Excel 方式：gdata包

gdata 原理：

01. 读取Excel 文件 - 图3

gdata 和readxl 包对比

01. 读取Excel 文件 - 图4

主要因为readxl 包还在发展，很多功能不完善，而且可能语法会变换。

因此选择gdata 这个成熟的包学习，会更加保险一些。

read.xls() 导入文件

  1. # Import the second sheet of urbanpop.xls: urban_pop
  2. urban_pop <- read.xls("urbanpop.xls", sheet = "1967-1974")
  • 通过cbind() 可以添加data.frame或matrix 等信息
  • data_frame[-1]，可以去除第一列的信息。
  • na.omit可以用来除去data.frame 中的NA 信息。

例子

  1. # Add code to import data from all three sheets in urbanpop.xls
  2. path <- "urbanpop.xls"
  3. urban_sheet1 <- read.xls(path, sheet = 1, stringsAsFactors = FALSE)
  4. urban_sheet2 <- read.xls(path, sheet = 2, stringsAsFactors = FALSE)
  5. urban_sheet3 <- read.xls(path, sheet = 3, stringsAsFactors = FALSE)
  7. # Extend the cbind() call to include urban_sheet3: urban
  8. urban <- cbind(urban_sheet1, urban_sheet2[-1], urban_sheet3[-1])
  10. # Remove all rows with NAs from urban: urban_clean
  11. urban_clean <- na.omit(urban)
  13. # Print out a summary of urban_clean
  14. summary(urban_clean)

打通excel和R的包：XLConnect

一个应用了Java的包（安装可能需要java 环境）。

几乎可以实现使用R代码进行所有excel 可以进行的操作。

loadWorkbook()

加载excel 的表格。功能是创建在R中创建一个workbook，用于连接excel文件和R工作区。可以将其赋值给一个变量。

  1. # Load the XLConnect package
  2. library(XLConnect)
  4. # Build connection to urbanpop.xlsx: my_book
  5. my_book <- loadWorkbook("urbanpop.xlsx")

getsheet()

用于列出excel 文件中的所有列表

  1. getSheets(my_book)

readWorksheet()

读取表格信息。

readWorksheet 一般有四个参数。object 为表格对象，一般为需先经过loadWorkbook() 处理；sheet 表示表格信息，startCol 表示开始的行数，endCol 表示结束的行数。

  1. # XLConnect is already available
  3. # Build connection to urbanpop.xlsx
  4. my_book <- loadWorkbook("urbanpop.xlsx")
  6. # Import columns 3, 4, and 5 from second sheet in my_book: urbanpop_sel
  7. urbanpop_sel <- readWorksheet(my_book, sheet = 2, startCol = 3, endCol = 5)
  9. # Import first column from second sheet in my_book: countries
  10. countries <- readWorksheet(my_book, sheet = 2, startCol = 1, endCol = 1)
  12. # cbind() urbanpop_sel and countries together: selectioncbind(urbanpop_sel, countries)
  13. selection <- cbind(countries, urbanpop_sel)

使用XLConnect 修改数据

createSheet()

createSheet(object, name = )

创建一个空的表格

  1. # Add a worksheet to my_book, named "data_summary"
  2. createSheet(my_book, name = "data_summary")

writeWorksheet()

将新的表格信息写入到某个表格中。

writeWorksheet(object, new_object, sheet = )

  1. # Add data in summ to "data_summary" sheet
  2. writeWorksheet(my_book, summ, sheet = "data_summary")

saveWorkbook()

所有的编辑结束之后需要使用该函数进行文件的保存。（类似于进行excel操作后得保存文件，否则所有内容都付之东流了。）

saveWorkbook(object, flie = )

  1. # Save workbook as summary.xlsx
  2. saveWorkbook(my_book, file = "summary.xlsx")

renameSheet()

对表格进行重命名

renameSheet(object, 'old_name', 'new_name' )

  1. # Rename "data_summary" sheet to "summary"
  2. renameSheet(my_book,sheet = 4, "summary")

removeSheet()

移除整个表格

`removeSheet(object, sheet = )

  1. # Remove the fourth sheet
  2. removeSheet(my_book, sheet = 4)