R et Gestion d'EXCEL 2013

Bonjour,

voici ma config :

Windows 7
R 2.7.2 (je sais c'est vieux mais j'utilise les packages Rcom et Rodbc non dispo sur les versions récentes)
Excel 2013

Depuis plusieurs années je réalise des alimentations de base de données avec R et Excel (Mon fournisseur de données est Factset). En gros avec R je créé des fichiers Excel et insert des formules puis active le data downloading de Factset et je finis par enregistrer le fichier. Une fois fais j'ouvre le fichier, je récupère la data et l'intègre à mon serveur ORACLE. Je kill systématiquement Excel après chaque opération car sinon le proc Excel sature en mémoire.

Mon soucis est que depuis mon passage en Excel 2013 j'ai des Freeze du Proc Excel sans savoir pourquoi. Ces freezes peuvent se manifester à n'importe quel endroit de mon alimentation.

Quelqu'un a t-il déjà rencontré ce type de PB avec Excel 2013 ?

Cela fait 3 mois que j'essaye de le résoudre mais sans succès :(.

Du coup je ne vois vois que 2 solutions possibles :

1 Trouver quelqu'un sur les forums qui a déjà résolu ce soucis sans que ça m'oblige à changer de version de R ou d'Excel (Repasser à Excel 2011 me demandera d'âppres négos avec mes services IT groupe )

2 Migrer sur une nouvelle version de R et trouver une nouvelle manière de gérer Excel et des connexions ODBC vers ma base de données. Je dispose sur un autre poste de R 3.2.2 avec Statconn mais je ne comprends pas comment faire pour gérer l'objet com Excel avec çà ... Avez-vous des liens utiles pour que j'arrive à apprendre à gérer Excel avec cette version de R ?

Je sais qu'il n'ai pas idéale de gérer des alimentations de la sorte, j'ai un projet de migration de cette base sur des infras plus stables, mais malheuresement je dois encore faire avec pour les 6 prochains mois. Je dois donc absolument trouver une solution pour stabiliser la situation.

Voici des Exemples de fonctions que j'utilise auj pour gérer Excel

Fonction qui créer l'objet Com Excel

Code:

1
2
3
4
5
6
7
8
9
10
11
xlStart = function(visible = FALSE, displayalert = FALSE, Interactive = FALSE,
  ScreenUpdating = FALSE ) {
	if( ! "rcom" %in% .packages() ) library("rcom")
	xl <<- comCreateObject("Excel.Application")
  xl[["Visible"]] <- visible
  xl[["DisplayAlerts"]] <- displayalert
  xl[["Interactive"]] <- Interactive
  xl[["ScreenUpdating"]] <- ScreenUpdating	
	xlIsRunning() -> ans
	return(ans)
}

Fonction qui kill l'objet Com Excel

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
xlStop = function() {
	if( ! xlIsRunning() ) stop("Excel is not running. Use xlStart().")
	if( xlVisible() ) xlVisible(FALSE) -> silent
	xl <<- NULL
	raz <- rm( list = "xl", envir = .GlobalEnv )
	raz <- gc( verbose = FALSE )
  Sys.sleep(2)
  lapply(blGetTaskIDMain("EXCEL"),function(i){
      blKill(i)
  })->raz  	
	! xlIsRunning() -> ans
	return(ans)
}

Fonction qui permet de retrouver l'ID d'un proc

Code:

1
2
3
4
5
6
7
 
blGetTaskIDMain = function(exe) {
	system("c:/exeR/tlist.exe", intern = TRUE, show = FALSE) -> z
	zz <- do.call(rbind,lapply(apply(as.matrix(z),1,function(x) unlist(strsplit(trim.leading(x),
    " "))),function(y) y[1:2]))
	zz[ grep(exe, zz[,2]),1]
}

Fonction de Création d'un Workbook

Code:

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
 
# ----------------------------------------------------------------------------
# xlAddWorkbook
# ----------------------------------------------------------------------------
 
# Description
# Add a new workbook
 
# Arguments
# path ------------ Full path to the new workbook to be created
# sheets ---------- A vector of sheet names (defaults to Sheet1... Sheet5)
 
# Details
# Returns an 'xlWorkbook' object - e.g. a list which first element is the
# COM object representing the workbook itself and each of the followings
# beein pointers to the sheets. 
 
xlAddWorkbook = function(path, sheets = paste("Sheet", 1:5, sep =""),
	author = NA, title = NA, subject = NA, comments = NA  ) {
	if( ! xlIsRunning() )
		stop("Excel is not running. Use xlStart().")
	if( ! is.character(sheets) )
		stop("sheets must be a vector of names")
 
	length(sheets) -> n
	xl[["SheetsInNewWorkbook"]] -> save
	xl[["SheetsInNewWorkbook"]] <- n
 
	xl[["Workbooks"]]$Add() -> wb
	lapply(1:n, function(i) {
		wb[["Worksheets", i]][["Name"]] <- sheets[i]
    wb[["Worksheets", i]] -> ws
		ws[["Name"]] <- sheets[i]
		return(ws)
		ws <- NULL
	} ) -> Sheets
	names(Sheets) <- sheets
 
	if( ! is.na(author) ) author -> wb[["Author"]]
	if( ! is.na(title) ) title -> wb[["Title"]]
	if( ! is.na(subject) ) subject -> wb[["Subject"]]
	if( ! is.na(comments) ) comments -> wb[["Comments"]]
 
	xl[["SheetsInNewWorkbook"]] <- save
 
	wb$SaveAs(path) -> ans 
	if(is.null(ans) || !ans ) stop("could not create ", sQuote(path))
 
	c( list(wb = wb), Sheets ) -> res
	attr(res, "class") <- "xlWorkbook"
	wb <- NULL
 
	return(res)
}

Fonction coller du textes / données dans une sheet Excel

Code:

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
# ----------------------------------------------------------------------------
# xlPutArray
# ----------------------------------------------------------------------------
 
# Description
# Writes an R array in Excel
 
# Arguments
# x --------------- An R array or vector
# ws -------------- A pointer to the worksheet where to write the data
# cs -------------- Upper-left cell's reference as a numeric of length 2
# rownames -------- Logical. Should the function export row names?
# colnames -------- Logical. Should the function export column names?
# date.format ----- A string representing user's prefered Excel date format
 
# Details
# In this function, cs only refers to the upper-left cell of the range where
# to export data.
# The function will perform date conversion automatically provided dates are 
# given as standard R Date objects.
 
xlPutArray = function(x, ws, cs, rownames = FALSE, colnames = FALSE,
	date.format = "aaaa-mm-jj") {
	if( ! xlIsRunning() )
		stop("Excel is not running. Use xlStart().")
  if( ! xlIsWorksheet(ws) )
    stop("'ws' must be a worksheet")
 
	if( ! is.numeric(cs[1]) | ! is.numeric(cs[2]) | length(cs) != 2 )
		stop("cs must be a numeric vector of length 2")
 
	if( is.null( dim(x) ) ) as.matrix(x) -> x
	nrow(x) -> nrx
	ncol(x) -> ncx
 
  if (nrx !=0)
  {
  	if(rownames) {
      data.frame(x = rownames(x), x) -> x
      ncx = ncx +1
  	}
 
  	if(colnames) {
 
    	xlRange(ws, list(cs, c(cs[1],
                          cs[2] + ncx - 1) ) ) -> rr
 # 		ws[["Range", ws[["Cells", cs[1], cs[2]]],
#  			ws[["Cells", cs[1], cs[2] + ncx - 1 ]] ]] -> rr
  		temp <- if( is.null(colnames(x)) ) {
  			paste("c", 1:ncx, sep = "")
  			} else { colnames(x) }
  		rr[["Value2"]] <- t(temp)
  		cs[1] + 1 -> cs[1]
  		rr <- NULL
 
  	}
 
  	lapply(1:ncx, function(i) {
 
  		x[, i] -> xi
  		list( c(cs[1], cs[2] +i-1), c(cs[1]+nrx-1, cs[2]+i-1) ) -> pp
      xlRange(ws, pp) -> rr
      #ws[["Range", ws[["Cells", cs[1], cs[2] + i - 1 ]],
      #   ws[["Cells", cs[1] + nrx - 1, cs[2] + i - 1 ]] ]] -> rr
      #  		
  		class(xi) -> case
  		if( case != "numeric" ) {
 
  			if( case == "Date") {
  				xlDate(xi) -> xx
  				rr[["Value2"]] <- as.matrix(xx)
  				rr[["NumberFormat"]] <- date.format
  			}
 
  			if( case %in% c("character", "factor") ) {
  				rr[["Value2"]] <- as.matrix( as.character(xi) )
  			}
 
  		} else {
  			rr[["Value2"]] <- as.matrix(xi)
  		}
 
  		rr <- NULL
 
  	} ) -> silent
 
  	ws <- NULL
  	length(silent) == ncx -> ans
	} else { ans <- NULL}
	return(ans)
}

Merci d'avance pour votre aide

Bonjour,

Faute de pouvoir bien répondre à l'ensemble de tes problématiques en raison de sa complexité, je te ferais les remarques suivantes :

L'ensemble des versions de R gère les liens ODBC
Pour quelle raison inclure du code d’Excel dans R. Au lieu de cela pourquoi ne pas plutôt séparé le traitement sous R puis alimenté un fichier Excel pour Traité Excel ensuite dans l'environnement Excel.

Au plaisir de te lire.

Salut,

Ok, merci pour les ODBC, je ne retourvais plus le package RODBC...je me suis bien déchiré la dessus.

Par contre je ne suis pas sur de comprendre ta remarque sur Excel. Ce que je fais concretement c'est ouvrir un object COM Excel, créer un workbook contenant toutes les formules d'extraction de données puis je l'enregistre et le ferme. Avec l'objet COM (ou API) de mon fournisseur de données j'appel une fonction qui va activer l'extraction des données via les formules créées. Une fois fais je referme le fichier et pour finir je rouvre un COM Excel pour lire le résultat et l'intégrer à ma base de données. Mon alim qui tourne en auto tous les lundi matin fait cette opération dans les 200 fois. Mon problème est que mon objet COM Excel freeze sans explications et bloque l'execution de mon script R sans planter et donc sans envoyer un message d'erreur. Je n'avais aucun problème avec Excel 2011 c'est juste depuis mon passage en 2013 que je rencontre ce soucis. :(

Je ne vois pas comment mettre dans Excel cette étape.

Merci pour votre aide

RE bonjour,

n'ayant pas trouver de solution propre, ce freeze venant certainement d'un problem de gestion du processus Excel par windows j'ai fais le bourrin. Dorénavant chaque appel d'Excel est surveillé selon des temps de traitements mis en dur (ex telle tâche est censée durer max 20 min) si je vois que ça prend plus de temps alors je kill Excel et son processuss et relance le traitement. Pas jolie jolie mais au moins ça marche :).

Merci