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")
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)
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")
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")
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")
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 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")
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")
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.
Comments