read.table能否读入2007或2003excel文件？

shangzheng.xlsx (73.91 KB)

2014-2-5 19:13:33 上传

上面是数据文件。
代码：

1. read.table("shangzheng.xlsx",header=T)

复制代码

结果：



我尝试另存为2003excel文件，打开时，提示：
Error in make.names(col.names, unique = TRUE) :
  invalid multibyte string 1
In addition: Warning message:
In read.table("shangzheng.xls", header = T) :
  incomplete final line found by readTableHeader on 'shangzheng.xls'

建议使用xlsx包。

1. library(xlsx)
   
2. Data <- read.xlsx(
   
3.   "shangzheng.xlsx",
   
4.   sheetIndex = 1,
   
5.   startRow = 1,
   
6. )
   
7. head(Data)

复制代码

输出为：

1. > head(Data)
   
2.         time      kp     max     min      sp
   
3. 1 2012-07-11 2160.02 2176.49 2156.60 2175.38
   
4. 2 2012-07-12 2172.06 2197.04 2152.67 2185.49
   
5. 3 2012-07-13 2180.17 2198.66 2177.92 2185.90
   
6. 4 2012-07-16 2187.89 2188.06 2146.15 2147.96
   
7. 5 2012-07-17 2142.43 2165.33 2141.48 2161.19
   
8. 6 2012-07-18 2159.86 2170.30 2138.79 2169.10

复制代码

1. Save Excel files into text
Saving Excel files into CSV can be done directly from Excel or through some external tools that allows batch operations. Native R functions for text data import can so be used.

1. df = read.table("myfile.csv", header = TRUE)

复制代码

2. Copy and paste from Excel to R
This is a fast solutions, but it has one main drawbacks: it requires to open Excel file, select data and copy. By the way, this is the best compromise when you're in a hurry.

1. df = read.table("clipboard")

复制代码

3. ODBC connection

1. require(RODBC)
   
2. conn = odbcConnectExcel("myfile.xlsx") # open a connection to the Excel file
   
3. sqlTables(conn)$TABLE_NAME # show all sheets
   
4. df = sqlFetch(conn, "Sheet1") # read a sheet
   
5. df = sqlQuery(conn, "select * from [Sheet1 $]") # read a sheet (alternative SQL sintax)
   
6. close(conn) # close the connection to the file

复制代码

4. gdata package

1. require(gdata)
   
2. df = read.xls ("myfile.xlsx"), sheet = 1, header = TRUE)

复制代码

5. xlsReadWrite package

1. require(xlsReadWrite)
   
2. xls.getshlib()
   
3. df = read.xls("myfile.xls", sheet = 1)

复制代码

6. XLConnect package

1. require(XLConnect)
   
2. wb = loadWorkbook("myfile.xlsx")
   
3. df = readWorksheet(wb, sheet = "Sheet1", header = TRUE)

复制代码

7.xlsx package

1. require(xlsx)
   
2. read.xlsx("myfile.xlsx", sheetName = "Sheet1")
   
3. read.xlsx2("myfile.xlsx", sheetName = "Sheet1")

复制代码

kaifengedu 发表于 2014-2-5 19:15
建议使用xlsx包。输出为：

非常感谢！

耕耘使者 发表于 2014-2-5 20:54
非常感谢！

dxystata 发表于 2014-2-5 20:45
1. Save Excel files into text
Saving Excel files into CSV can be done directly from Excel or throug ...

精彩！
多谢兄弟！