Von Excel zu R und wieder zurück

In diesem Beitrag möchten wir R und Excel zusammen nutzen. Dazu verwenden wird das Paket openxlsx. Es ermöglicht nicht nur den Import und Export von xlsx-Dateien. Man kann Excel-Dokumente zudem Formatieren, Formeln einbetten sowie Bilder und Plots hinzufügen. Und das ist nur ein kleiner Teil der Funktionen von openxlsx.


Pakete laden


library(openxlsx)

Eine Spieldatei anlegen


Als einfache xlsx-Datei erstellen wir eine unformatierte Excel-Datei mit dem mtcars-Datensatz als Inhalt.


# Exportiere mtcars
 openxlsx::write.xlsx(mtcars, "mtcars.xlsx")

Wir können die Datei auch mittels Befehl in Excel öffnen:


openxlsx::openXL("mtcars.xlsx")
Einfacher Excel- Export

Diese Datei hat definitiv noch Styling-Potenzial. Darüber werden wir später noch eingehen.


Excel-Dateien einlesen und Informationen über sie erhalten


Unsere Spieldatei mtcars.xlsx können wir nun mit dem Befehl read.xlsx()einlesen.


dataXlsxMtcars <- openxlsx::read.xlsx(xlsxFile = "mtcars.xlsx")
head(dataXlsxMtcars)
Eingelesener Datensatz aus Excel

Wenn wir mehrere Tabellenblätter haben, ist es gut über die Namen der Blätter zu arbeiten. Diese können im Vorfeld mit dem Befehl names() ausgelesen werden. Dazu müssen wir zunächst das Workbook als Objekt in R importieren.


# Import Workbook-Objekt
 wbMtcars <- openxlsx::loadWorkbook("mtcars.xlsx")
 
# Ausgabe der vorhandenen Tabellenblätter
 openxlsx::sheets(wbMtcars)



Unsere Erfahrung hat gezeigt, dass die Arbeit mit den Namen der Arbeitsblättern die Verwechselungsgefahr reduziert. Es kann passieren, dass beim Arbeiten mit den Nummern der Blätter die falsche Tabelle geladen wird.


Wir können uns auch zusätzliche Informationen über die Arbeitsmappe anzeigen lassen:


message("Den Autor anzeigen")
openxlsx::getCreators(wbMtcars)

message("Die Erstellungszeit der Datei anzeigen")
openxlsx::getDateOrigin(xlsxFile = "mtcars.xlsx")
Metainformationen zur Excel-Datei abfragen


Professionelle Excel-Dateien exportieren


Wir haben oben bereits die Spieldatei als Arbeitsmappe importiert. Nun können wir dem Workbook-Objekt zusätzliche Blätter hinzufügen.

Fangen wir einfach an, wir fügen ein Blatt hinzu, welches die Daten in einer Excel-Table anzeigt. Der Stil soll dabei “hell 13” sein.


# Neues Arbeitsblatt anlegen
 openxlsx::addWorksheet(wbMtcars,
  sheetName = "TabellenStil")
 
 # Daten ins Arbeitsblatt schreiben
 openxlsx::writeDataTable(wbMtcars,
  sheet = "TabellenStil",
  dataXlsxMtcars,
  tableStyle = "TableStyleLight13")
 
 # Mappe speichern
 openxlsx::saveWorkbook(wbMtcars,
  file = "mtcars_modifiziert.xlsx",
  overwrite = TRUE)

# Excel ansehen
 openxlsx::openXL("mtcars_modifiziert.xlsx")
Tabellenstil ändern mit openxlsx

Wir möchten auf einem weiteren Arbeitsblatt gerne zwei Plots anzeigen:


# Dies ist ein Beispiel aus der Dokumentation der Funktion 
 # insertPlot
 
 # Neues Arbetsblatt anlegen
 openxlsx::addWorksheet(wbMtcars,
   sheetName = "ggplot-Diagramme")
 
 ## Plots mit ggplot2 erstellen
 require(ggplot2)
## Loading required package: ggplot2
## Warning: package 'ggplot2' was built under R version 3.6.3
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)
 
 ## Einfügen des angezeigten Plot p1 zu Sheet "ggplot-Diagramme", Zeile 1, Spalte 1 
 
 print(p1) # Der Plot muss angezeigt werden

insertPlot(wbMtcars, 
  "ggplot-Diagramme", 
  width = 5, 
  height = 3.5, 
  fileType = "png", 
  units = "in")
 
 ## Plot 2 einfügen
 print(p2)

insertPlot(wbMtcars, 
  "ggplot-Diagramme", 
  xy = c("J", 2), 
  width = 16, 
  height = 10, 
  fileType = "png", 
  units = "cm")
 
 # Mappe speichern
 openxlsx::saveWorkbook(wbMtcars,
  file = "mtcars_modifiziert2.xlsx",
  overwrite = TRUE)
## Warning in file.create(to[okay]): kann Datei 'mtcars_modifiziert2.xlsx' nicht
 ## erzeugen. Grund 'Permission denied'
# Excel ansehen
 openxlsx::openXL("mtcars_modifiziert2.xlsx")
Plot in Excel-Datei einfügen


Nun wollen wir kurz auf Formeln und bedingte Formatierungen eingehen. Nehmen wir an, dass wir unter unserer Tabelle die Spalten zusammenfassen möchten als Median. Die Spalten sollen dann basierend auf ihrem Wert eingefärbt werden. Das 10% Quantil ist dabei blau, der Median weiß und das 90% Quantil färben wir hellgrün. Dazwischen werden die Farben ineinander übergehen.



# Formeln hinzufügen
 # Wir wenden die Vektor-Variante an (vgl. Dokumentation der Funktion writeFormula())
 # 1. Neues Arbeitsblatt anlegen
 openxlsx::addWorksheet(wbMtcars,
  sheetName = "Formeln")
 
# Wir fügen dem Datensatz eine neue Spalte mit IDs hinzu
 IDs <- 1:nrow(dataXlsxMtcars)
 newDataMtcars <- rbind(IDs = IDs, dataXlsxMtcars)
 
 openxlsx::writeData(wbMtcars,
  sheet = "Formeln",
  newDataMtcars)
 
# Formeln hinzufügen
 
 StartZeile <- 2
 StartSpalte <- 2
 EndZeile <- nrow(newDataMtcars) + 1
 EndSpalte <- ncol(newDataMtcars)
 
 Formelvektor <- paste0("MEDIAN(", 
  LETTERS[StartSpalte:EndSpalte], 
  StartZeile, 
  ":", 
  LETTERS[StartSpalte:EndSpalte], 
   EndZeile, 
  ")") 
 for(i in 1:length(Formelvektor)){
  writeFormula(wbMtcars, 
  sheet = "Formeln", 
  x = Formelvektor[i], 
  startCol = StartSpalte + i -1, startRow = EndZeile +1)
 }
  
# Mappe speichern
 openxlsx::saveWorkbook(wbMtcars,
  file = "mtcars_modifiziert3.xlsx",
  overwrite = TRUE)

# Excel ansehen
 openxlsx::openXL("mtcars_modifiziert3.xlsx")
 
Formeln zum Arbeitsblatt hinzufügen

# Formeln hinzufügen
 # Wir wenden die Vektor-Variante an (vgl. Dokumentation der Funktion writeFormula())
 # 1. Neues Arbeitsblatt anlegen
 openxlsx::addWorksheet(wbMtcars,
   sheetName = "Formeln + Formatierung")
 
 # Wir fügen dem Datensatz eine neue Spalte mit IDs hinzu
 IDs <- 1:nrow(dataXlsxMtcars)
 newDataMtcars <- rbind(IDs = IDs, dataXlsxMtcars)
 
 openxlsx::writeData(wbMtcars,
  sheet = "Formeln + Formatierung",
  newDataMtcars)
 
 # Formeln hinzufügen
 
 StartZeile <- 2
 StartSpalte <- 2
 EndZeile <- nrow(newDataMtcars) + 1
 EndSpalte <- ncol(newDataMtcars)
 
 Formelvektor <- paste0("MEDIAN(", 
  LETTERS[StartSpalte:EndSpalte], 
  StartZeile, 
  ":", 
  LETTERS[StartSpalte:EndSpalte], 
  EndZeile, 
  ")") 
 for(i in 1:length(Formelvektor)){
  writeFormula(wbMtcars, 
  sheet = "Formeln + Formatierung", 
  x = Formelvektor[i], 
  startCol = StartSpalte + i -1, startRow = EndZeile +1)
 }
 
 # Bedingte Formatierung für alle Spalten (-ID) und Zeilen (-Formel)
 for(i in 2:ncol(newDataMtcars)){
  Quantil <- quantile(newDataMtcars[i],
  na.rm = TRUE,
  probs = c(0.1, 0.5, 0.9))
  
  conditionalFormatting(wbMtcars, 
  sheet = "Formeln + Formatierung",
   rows = 2:(nrow(newDataMtcars)) + 1,
  cols = i,
  style = c("cornflowerblue", "white", "greenyellow"),
  rule = c(Quantil[1], Quantil[2], Quantil[3]),
  type = "colourScale")
  
 }
 
 
 MedianStyle <- createStyle(fontColour = "orange")
 addStyle(wb = wbMtcars,
  sheet = "Formeln + Formatierung",
  style = MedianStyle,
  rows = EndZeile + 1,
  cols = 1:ncol(newDataMtcars),
  gridExpand = TRUE,
  stack = TRUE)
 
 BorderStyle <- createStyle(border = c("top", "bottom", "left", "right"),
  borderColour = getOption("openxlsx.borderColour", "snow2"))
 addStyle(wb = wbMtcars,
  sheet = "Formeln + Formatierung",
  style = BorderStyle,
  rows = 1:EndZeile,
  cols = 1:ncol(newDataMtcars),
  gridExpand = TRUE,
  stack = TRUE)
 
 
 
 # Mappe speichern
 openxlsx::saveWorkbook(wbMtcars,
  file = "mtcars_modifiziert4.xlsx",
  overwrite = TRUE)
## Warning in file.create(to[okay]): kann Datei 'mtcars_modifiziert4.xlsx' nicht
 ## erzeugen. Grund 'Permission denied'
# Excel ansehen
 openxlsx::openXL("mtcars_modifiziert4.xlsx")
Bedingte Formatierung hinzufügen

Seitenlayout anpassen


Zum Abschluss legen wir noch das Seitenlayout fest. Dies ist für das Ausdrucken des Excel-Blattes relevant. In der Kopfzeile bringen wir den Namen des Excel-Blatts unter. Die Fußzeile soll die Seitenzahl und unseren Namen enthalten.

Wir nutzen nochmals die Inhalte von eben.


# Neues Arbeitsblatt anlegen
 openxlsx::addWorksheet(wbMtcars,
  sheetName = "Seitenlayout")
 
 # Wir fügen dem Datensatz eine neue Spalte mit IDs hinzu
 IDs <- 1:nrow(dataXlsxMtcars)
 newDataMtcars <- rbind(IDs = IDs, dataXlsxMtcars)
 
 openxlsx::writeData(wbMtcars,
  sheet = "Seitenlayout",
  newDataMtcars)
 
 # Formeln hinzufügen
 
 StartZeile <- 2
 StartSpalte <- 2
 EndZeile <- nrow(newDataMtcars) + 1
 EndSpalte <- ncol(newDataMtcars)
 
 Formelvektor <- paste0("MEDIAN(", 
  LETTERS[StartSpalte:EndSpalte], 
  StartZeile, 
  ":", 
  LETTERS[StartSpalte:EndSpalte], 
  EndZeile, 
  ")") 
 for(i in 1:length(Formelvektor)){
  openxlsx::writeFormula(wbMtcars, 
  sheet = "Seitenlayout", 
  x = Formelvektor[i], 
  startCol = StartSpalte + i -1, startRow = EndZeile +1)
 }
 
 # Bedingte Formatierung für alle Spalten (-ID) und Zeilen (-Formel)
 for(i in 2:ncol(newDataMtcars)){
  Quantil <- quantile(newDataMtcars[i],
  na.rm = TRUE,
  probs = c(0.1, 0.5, 0.9))
  
   openxlsx::conditionalFormatting(wbMtcars, 
  sheet = "Seitenlayout",
  rows = 2:(nrow(newDataMtcars)) + 1,
  cols = i,
  style = c("cornflowerblue", "white", "greenyellow"),
  rule = c(Quantil[1], Quantil[2], Quantil[3]),
  type = "colourScale")
  
 }
 
 
 MedianStyle <- openxlsx::createStyle(fontColour = "orange")
 openxlsx::addStyle(wb = wbMtcars,
  sheet = "Seitenlayout",
  style = MedianStyle,
  rows = EndZeile + 1,
  cols = 1:ncol(newDataMtcars),
  gridExpand = TRUE,
   stack = TRUE)
 
 BorderStyle <- openxlsx::createStyle(border = c("top", "bottom", "left", "right"),
  borderColour = getOption("openxlsx.borderColour", "snow2"))
 openxlsx::addStyle(wb = wbMtcars,
  sheet = "Seitenlayout",
  style = BorderStyle,
  rows = 1:EndZeile,
  cols = 1:ncol(newDataMtcars),
  gridExpand = TRUE,
  stack = TRUE)
 
## Seitenlayout festlegen
 # Druckausgabe: A4 (9) Hochformat, Ränder links & rechts 0.25 inch,
 # oben & unten 0.5 inch
 # Spalten auf 1 Seitenbreite darstellen
 openxlsx::pageSetup(wbMtcars,
  sheet = "Seitenlayout",
  orientation = "portrait",
   fitToWidth = TRUE,
  printTitleRows = 1,
  left = 0.25,
  right = 0.25,
  top = 0.5,
  bottom = 0.5,
  paperSize = 9)
 
 # Setze Fußzeile
 openxlsx::setHeaderFooter(wbMtcars,
  sheet = "Seitenlayout",
  header = c(NA, "Seitenlayouts mit openxlsx", NA),
  footer = c("masem research institute",
  "Seite &[Page] von &[Pages] Seiten",
  NA))
 
 
 # Mappe speichern
 openxlsx::saveWorkbook(wbMtcars,
  file = "mtcars_modifiziert_fin.xlsx",
  overwrite = TRUE)
 
 # Excel ansehen
 openxlsx::openXL("mtcars_modifiziert_fin.xlsx")
Seitenlayout der Excel anpassen


Fazit


Das R-Paket openxlsx bietet neben einer guten Import-Funktion auch viele Möglichkeiten ausdrucksstarke Excel-Mappen für nicht R-User zu erstellen. Von Styling-Fragestellungen bis zu Formeln können alle Wünsche von Excel-Nutzern befriedigt werden. Komplexere Aufgaben können dennoch in R verarbeitet und die Ergebnisse in Excel bereitgestellt werden. Zudem kann man die Arbeitsmappe mit R-Grafiken aus ggplot anreichern. Wir wünschen viel Spaß beim ausprobieren.