R 简明教程

R - Excel File

Microsoft Excel 是最广泛使用的电子表格程序，它以 .xls 或 .xlsx 格式存储数据。R 可以使用一些专门的 Excel 程序包直接读取这些文件。少数此类程序包有 - XLConnect、xlsx、gdata 等。我们将使用 xlsx 程序包。R 还可以利用此程序包写入 Excel 文件。

Microsoft Excel is the most widely used spreadsheet program which stores data in the .xls or .xlsx format. R can read directly from these files using some excel specific packages. Few such packages are - XLConnect, xlsx, gdata etc. We will be using xlsx package. R can also write into excel file using this package.

Install xlsx Package

您可以在 R 控制台使用以下命令安装“xlsx”程序包。它可能要求安装此程序包所依赖的一些其他程序包。使用要求的程序包名称按照相同的命令安装其他程序包。

You can use the following command in the R console to install the "xlsx" package. It may ask to install some additional packages on which this package is dependent. Follow the same command with required package name to install the additional packages.

install.packages("xlsx")

Verify and Load the "xlsx" Package

使用以下命令验证并加载“xlsx”程序包。

Use the following command to verify and load the "xlsx" package.

# Verify the package is installed.
any(grepl("xlsx",installed.packages()))

# Load the library into R workspace.
library("xlsx")

当运行脚本时，我们得到以下输出。

When the script is run we get the following output.

[1] TRUE
Loading required package: rJava
Loading required package: methods
Loading required package: xlsxjars

Input as xlsx File

打开 Microsoft Excel。复制并粘贴以下数据到名为 sheet1 的工作表中。

Open Microsoft excel. Copy and paste the following data in the work sheet named as sheet1.

id	name      salary    start_date	dept
1	Rick	    623.3	  1/1/2012	   IT
2	Dan       515.2     9/23/2013    Operations
3	Michelle  611	     11/15/2014	IT
4	Ryan	    729	     5/11/2014	   HR
5	Gary	    43.25     3/27/2015  	Finance
6	Nina	    578       5/21/2013	   IT
7	Simon	    632.8	  7/30/2013	   Operations
8	Guru	    722.5	  6/17/2014	   Finance

此外，复制并粘贴以下数据到另一个工作表并将此工作表重命名为“city”。

Also copy and paste the following data to another worksheet and rename this worksheet to "city".

name	    city
Rick	    Seattle
Dan       Tampa
Michelle  Chicago
Ryan	    Seattle
Gary	    Houston
Nina	    Boston
Simon	    Mumbai
Guru	    Dallas

将 Excel 文件保存为“input.xlsx”。您应该将其保存在 R 工作区的当前工作目录中。

Save the Excel file as "input.xlsx". You should save it in the current working directory of the R workspace.

Reading the Excel File

input.xlsx 通过使用 read.xlsx() 函数读取，如下所示。结果存储为 R 环境中的一个数据框。

The input.xlsx is read by using the read.xlsx() function as shown below. The result is stored as a data frame in the R environment.

# Read the first worksheet in the file input.xlsx.
data <- read.xlsx("input.xlsx", sheetIndex = 1)
print(data)

当我们执行上述代码时，会产生以下结果 -

When we execute the above code, it produces the following result −

      id,   name,     salary,   start_date,   dept
1      1    Rick      623.30    2012-01-01    IT
2      2    Dan       515.20    2013-09-23    Operations
3      3    Michelle  611.00    2014-11-15    IT
4      4    Ryan      729.00    2014-05-11    HR
5     NA    Gary      843.25    2015-03-27    Finance
6      6    Nina      578.00    2013-05-21    IT
7      7    Simon     632.80    2013-07-30    Operations
8      8    Guru      722.50    2014-06-17    Finance