Bonjour,

Je travaille actuellement sur de la mise en forme sur Excel à l'aide de R, je vous joins mon code pour avoir un exemple des données sur lesquelles je travailles (en l'occurrence cela correspond au data frame "df"). J'ai laissé un commentaire pour soit changer le chemin du fichier pour vous donner un aperçu du résultat ou alors il me semble qu'il y a une fonction qui permet de visualiser le résultat sans pour autant créer un fichier comme je l'ai fait.
Mon problème étant que sur un data frame plus grand le temps de traitement est assez long, environ 4min pour un data frame de 359 lignes, c'est pourquoi j'aimerais déjà comprendre pourquoi est-ce si long (le fait d'utiliser une boucle je pense, ou alors le nombre d'appel fonction qui sont dans la boucle ?) ? Et comment puis-je améliorer mon code pour que le temps de traitement soit plus court ?

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
 
library(readxl)
library(openxlsx)
 
df <- data.frame("Nom" = c("ABC", "DEF"),
                 "R" = c("R1", "R2"),
                 "Nom1" = c("AZER", "AZER"),
                 "R1" = c("A1", "B1"),
                 "C1" = c("C1", "C1"),
                 "D1" = c("D1", "D1"),
                 "P1" = c(10, 20),
                 "Nom2" = c("AZERTY", "AZERTY"),
                 "R2" = c("A2", "B2"),
                 "C2" = c("C2", "C2"),
                 "D2" = c("D2", "D2"),
                 "P2" = c(10, 20),
                 "Nom3" = c("AZERTYUI", "AZERTYUI"),
                 "R3" = c("A3", "B3"),
                 "C3" = c("C3", "C3"),
                 "D3" = c("D3", "D3"),
                 "P3" = c(10, 20)
                 )
wb <- createWorkbook()
sheet <- "Sheet1"
addWorksheet(wb = wb, sheetName = sheet)
 
mergeCells(wb = wb, sheet = sheet, cols = 1:15, rows = 1)
mergeCells(wb = wb, sheet = sheet, cols = 1:15, rows = 2)
writeData(wb = wb, sheet = sheet, x = "Example", startCol = 1, startRow = 1)
writeData(wb = wb, sheet = sheet, x = "Example", startCol = 1, startRow = 2)
 
a <- createStyle(halign = "center", valign = "center", border = "TopRightBottomLeft", borderStyle = "medium", textDecoration = "bold", fgFill = "#00AAFF")
f <- createStyle(halign = "center", valign = "center", textDecoration = "bold", border = "TopRightBottomLeft", borderStyle = "medium", fgFill = "#D3D3D3")
r <- createStyle(halign = "center", valign = "center", border = "TopRightBottomLeft", borderStyle = "medium")
nom <- createStyle(halign = "center", valign = "center", textDecoration = "bold", fontSize = 12)
pro <- createStyle(halign = "left", fontSize = 9)
blue <- createStyle(fgFill = "#0088FF")
Hpro <- createStyle(halign = "left", textDecoration = "bold")
condi <- createStyle(halign = "center", valign = "center", fontSize = 9, border = "TopRightBottomLeft", borderStyle = "medium", fgFill = "#D3D3D3")
white <- createStyle(fgFill = "#FFFFFF")
border <- createStyle(border = "TopRightBottomLeft", borderStyle = "medium")
yellow <- createStyle(fgFill = "#F1C40F", halign = "center", valign = "center", textDecoration = "bold", fontSize = 12)
title <- createStyle(fontSize = 18, textDecoration = "bold", halign = "center", valign = "center", border = "TopRightBottomLeft", borderStyle = "medium", fgFill = "#FFFFFF")
 
addStyle(wb = wb, sheet = sheet, style = title, rows = 1, cols = 1:15, gridExpand = TRUE)
addStyle(wb = wb, sheet = sheet, style = yellow, rows = 2, cols = 1:15, gridExpand = TRUE)
setRowHeights(wb = wb, sheet = sheet, rows = 1, heights = 100)
setRowHeights(wb = wb, sheet = sheet, rows = 2, heights = 20)
 
x <- 1
end <- nrow(df)
while (x <= end) {
  mergeCells(wb = wb, sheet = sheet, cols = 1, rows = (x + 2 + ((x - 1) * 4)):(x + 5 + ((x - 1) * 4))) # merge photos
 
  addStyle(wb = wb, sheet = sheet, style = white, rows = (x + 2 + ((x - 1) * 4)):(x + 5 + ((x - 1) * 4)), cols = 1:15, gridExpand = TRUE)
  addStyle(wb = wb, sheet = sheet, style = border, rows = (x + 2 + ((x - 1) * 4)):(x + 5 + ((x - 1) * 4)), cols = 1, gridExpand = TRUE)
 
  mergeCells(wb = wb, sheet = sheet, cols = 3:4, rows = (x + 2 + ((x - 1) * 4)):(x + 5 + ((x - 1) * 4))) # merge D
 
  writeData(wb = wb, sheet = sheet, x = df[x, "Nom"], startCol = 3, startRow = (x + 2 + ((x - 1) * 4)), colNames = FALSE)
 
  addStyle(wb = wb, sheet = sheet, style = nom, cols = 3, rows = (x + 2 + ((x - 1) * 4)), stack = TRUE)
 
  mergeCells(wb = wb, sheet = sheet, cols = c("E", "F", "G"), rows = (x + 2 + ((x - 1) * 4)))
  mergeCells(wb = wb, sheet = sheet, cols = c("E", "F", "G"), rows = (x + 2 + ((x - 1) * 4)) + 1)
  mergeCells(wb = wb, sheet = sheet, cols = c("I", "J", "K"), rows = (x + 2 + ((x - 1) * 4)))
  mergeCells(wb = wb, sheet = sheet, cols = c("I", "J", "K"), rows = (x + 2 + ((x - 1) * 4)) + 1)
  mergeCells(wb = wb, sheet = sheet, cols = c("M", "N", "O"), rows = (x + 2 + ((x - 1) * 4)))
  mergeCells(wb = wb, sheet = sheet, cols = c("M", "N", "O"), rows = (x + 2 + ((x - 1) * 4)) + 1)
 
  writeData(wb = wb, sheet = sheet, x = data.frame("a" = c(as.character(df[x, "Nom1"]), as.character(df[x, "D1"])),
                                                   "b" = "", "c" = "", "d" = "",
                                                   "e" = c(as.character(df[x, "Nom2"]), as.character(df[x, "D2"])),
                                                   "f" = "", "g" = "", "h" = "",
                                                   "i" = c(as.character(df[x, "Nom3"]), as.character(df[x, "D3"]))),
            startCol = "E", startRow = (x + 2 + ((x - 1) * 4)), colNames = FALSE)
  addStyle(wb = wb, sheet = sheet, style = condi, cols = c("E", "F", "G", "I", "J", "K", "M", "N", "O"), rows = (x + 2 + ((x - 1) * 4)) + 1, gridExpand = TRUE, stack = TRUE)
  addStyle(wb = wb, sheet = sheet, style = f, cols = c("E", "F", "G", "I", "J", "K", "M", "N", "O"), rows = (x + 2 + ((x - 1) * 4)), gridExpand = TRUE, stack = TRUE)
 
  writeData(wb = wb, sheet = sheet, x = data.frame("a" = c("R", as.character(df[x, "R"]))), startCol = "B", startRow = (x + 2 + ((x - 1) * 4)) + 2, colNames = FALSE)
  addStyle(wb = wb, sheet = sheet, style = Hpro, cols = "B", rows = (x + 2 + ((x - 1) * 4)) + 2, gridExpand = TRUE, stack = TRUE)
  addStyle(wb = wb, sheet = sheet, style = pro, cols = "B", rows = (x + 2 + ((x - 1) * 4)) + 3, gridExpand = TRUE, stack = TRUE)
 
  writeData(wb = wb, sheet = sheet, x = data.frame("a" = c("R", as.character(df[x, "R1"])), "b" = c("P", as.double(df[x, "P1"])), "c" = c("C", as.character(df[x, "C1"])), "d" = "",
                                                   "e" = c("R", as.character(df[x, "R2"])), "f" = c("P", as.double(df[x, "P2"])), "g" = c("C", as.character(df[x, "C2"])), "h" = "",
                                                   "i" = c("R", as.character(df[x, "R3"])), "j" = c("P", as.double(df[x, "P3"])), "k" = c("C", as.character(df[x, "C3"]))),
            startCol = "E", startRow = (x + 2 + ((x - 1) * 4)) + 2, colNames = FALSE)
 
  addStyle(wb = wb, sheet = sheet, style = a, rows = (x + 5 + ((x - 1) * 4)) - 1, cols = c("E", "F", "G", "I", "J", "K", "M", "N", "O"), gridExpand = TRUE, stack = TRUE)
  addStyle(wb = wb, sheet = sheet, style = r, rows = (x + 5 + ((x - 1) * 4)), cols = c("E", "F", "G", "I", "J", "K", "M", "N", "O"), gridExpand = TRUE, stack = TRUE)
  addStyle(wb = wb, sheet = sheet, style = blue, rows = (x + 5 + ((x - 1) * 4)) + 1, cols = 1:15, gridExpand = TRUE, stack = TRUE)
  setRowHeights(wb = wb, sheet = sheet, rows = (x + 5 + ((x - 1) * 4)) + 1, heights = 5)
  #print(x)
  x = x + 1
}
 
setColWidths(wb = wb, sheet = sheet, cols = c("B", "C"), widths = "auto")
setColWidths(wb = wb, sheet = sheet, cols = c("E", "F", "G", "I", "J", "K", "M", "N", "O"), widths = 10)
setColWidths(wb = wb, sheet = sheet, cols = c("H", "L"), widths = 1)
 
saveWorkbook(wb = wb, file = "Example.xlsx", overwrite = TRUE) # Mettre le chemin ou l'on veut que le fichier soit stocker
Cordialement,

Arkning