Hallo Leute,

heute gibt es gratis Superkräfte für Euch. Naja, also fast, es geht darum Excel-Dateien in R einzulesen und zu schreiben. Klingt nicht nach Superkräften? Sind aber die Grundlagen dafür, damit Ihr demnächst vollautomatisch Eure Excel-Reports berechnen und erstellen könnt.

 

Packages

Klaro, erweiterte R-Funktionalitäten bekommen wir über zusätzliche Packages. Also brauchen wir auch für Excel-Dateien ein Package. Doch welches, wie findet Ihr Euch zurecht im Package-Dschungel? Keine Panik, hier kommt schon die Antwort: Das am meisten genutzte Package heißt xlsx und wie jedes Package müsst ihr es einmal installieren, entweder in RStudio über Tools-> Install Packages oder einfach mit dem Befehl

install.packages("xlsx")

Falls ihr, aus welchen Gründen auch immer (Firmen-Policy, …) nicht auf den CRAN-Server zugreifen dürft, könnt ihr das Package natürlich auch herunterladen (https://cran.r-project.org/package=xlsx) und dann in RStudio über Tools -> Install Packages -> Install from Package Archive File

 

Für diejenigen unter Euch, die es lieber exotischer möchten, hier noch weitere Packages, die den Excel-Datei-Import und –Export ebenfalls beherrschen. Aber Achtung, im Folgenden benutze ich den Syntax vom Package xlsx

  • XLConnect
  • Openxlsx (das ist noch ganz interessant, da es nicht auf Java, sondern auf RCpp basiert)
  • R2excel (auf Github, zur Installation siehe
  • WriteXLS
  • Readxl (ok, gilt eigentlich nicht, nur zum Lesen von Excel-Dateien)

 

Lesen von xls und xlsx-Dateien

Zum Lesen von Excel-Dateien, egal ob xls- oder xlsx-Endung bietet das Package zwei Befehle, nämlich intuitiverweise read.xlsx und read.xlsx2. Dabei ist die 2. Funktion die schnellere, was aber nur bei wirklich großen Dateien eine Rolle spielt, aber sie ist auch etwas unkomfortabler.

read.xlsx(file, sheetIndex, sheetName=NULL, rowIndex=NULL, startRow=NULL, endRow=NULL, 
          colIndex=NULL, as.data.frame=TRUE, header=TRUE, colClasses=NA, keepFormulas=FALSE, 
          encoding="unknown", ...)
 
read.xlsx2(file, sheetIndex, sheetName=NULL, startRow=1, colIndex=NULL, endRow=NULL, 
           as.data.frame=TRUE, header=TRUE, colClasses="character", ...)

Also einfach Dateipfad und Blattnummer oder Blattname angeben und fertig.

Achtung Windows-Nutzer:  der Backslash \ ist in R für sogenannte Escpape-Sequenzen reserviert, daher entweder einen doppelten Backslash \\ benutzen oder einen einfachen Forward-Slash /.

 

Ich gebe eigentlich immer den Parameter stringsAsFactors=FALSE an, damit Textfelder als character und nicht als factor konvertiert werden. Zum anderen ist die startRow noch ziemlich wichtig, denn wenn Ihr ordentliche Excel-Nutzer seid (was Ihr natürlich seid, oder????), hat ein Arbeitsblatt einen Titel und Beschreibung, bevor die eigentliche Tabelle beginnt.

df <- read.xlsx("…/Blog/Excel/Demo.xlsx","Tab1",startRow=4, stringsAsFactors=FALSE)

Das war’s auch schon zum Einlesen. Ziemlich einfach, oder?

 

Zwei Dinge noch, die Euch das Excel-Tabellen-Leben einfacher machen. Zum einen kann man mit dem Parameter colClasses die Typen der Spalten angeben. Wenn man das nicht macht, versucht das Package diese während des Einlesens zu raten, was allerdings meistens gut geht.

df <- read.xlsx("…/Blog/Excel/Demo.xlsx","Tab1",startRow=4,colClasses=c("character","integer"),  stringsAsFactors=FALSE)

Zweiter Fallstrick sind Datetime-Angaben, hier gibt es manchmal Abweichungen zwischen Excel und R von 1 Sekunde. Aus 04.10.2018 13:48:41 in Excel wird dann 04.10.2018:13:48:40 in R, anscheinend wegen irgendwelcher Rundungen. Leider habe ich noch keine Abhilfe gefunden.

 

 

Excel-Dateien schreiben

Ok, große Überraschung, die beiden Befehle dafür lauten

write.xlsx(x, file, sheetName="Sheet1",col.names=TRUE, row.names=TRUE, append=FALSE, showNA=TRUE)
write.xlsx2(x, file, sheetName="Sheet1", col.names=TRUE, row.names=TRUE, append=FALSE, ...)

Wenn Ihr also einen tollen Datensatz gezaubert habt, einfach mit write.xlsx(dataframe,dateiName,row.names=FALSE) als Excel-Datei abspeichern. Ziemlich easy & cool.

 

Ok, das war’s erstmal. Die eigentliche Power liegt natürlich darin, dass man sich eine richtige Excel-Datei mit mehreren Arbeitsblättern, Autofiltern, Formatierungen usw. zusammenstellen kann. Demnächst schreibe ich etwas dazu, damit Ihr Superkräfte (also aus Sicht Eurer Kollegen) bekommt.

 

Bis dann,

Euer Holger