Puis-je écrire des fichiers xlsx identiques à partir d'un même cadre de données dans R ?

Question

Puis-je m'assurer que deux fichiers XLSX (écrits avec l'option openxlsx::write.xlsx ) sont identiques, lorsqu'on leur donne les mêmes données à écrire ? Je pense qu'il y a un horodatage écrit dans la feuille de calcul, ce qui signifie que les mêmes données écrites à plus d'une seconde d'intervalle créent un fichier différent.

Par exemple, lorsqu'ils sont écrits en succession rapide :

library(openxlsx)
write.xlsx(mtcars, "/tmp/t1.xlsx");write.xlsx(mtcars, "/tmp/t2.xlsx")

les fichiers sont identiques :

$ md5sum /tmp/t?.xlsx
c9b5f6509e20dd62b158debfbef376fe  /tmp/t1.xlsx
c9b5f6509e20dd62b158debfbef376fe  /tmp/t2.xlsx

mais si je dors entre deux écritures :

unlink("/tmp/t1.xlsx") # remove previous
unlink("/tmp/t2.xlsx")
write.xlsx(mtcars, "/tmp/t1.xlsx");Sys.sleep(2);write.xlsx(mtcars, "/tmp/t2.xlsx")

tout est différent :

$ md5sum /tmp/t?.xlsx
460945a610df3bc8a1ccdae9eb86c1fa  /tmp/t1.xlsx
a4865be49994092173792c9f7354e435  /tmp/t2.xlsx

Mon cas d'utilisation est un processus qui génère un fichier XLSX qui va dans un dépôt git. Si je l'automatise, le fichier XLSX va changer à chaque fois, même si les données sources n'ont pas changé. Je suppose que je pourrais tester si les données ont changé plus tôt dans le processus et ne pas générer un nouveau fichier XLSX, mais il semble plus facile de laisser git faire le test "est-ce que ça a changé ?" mais des métadonnées invisibles dans le XLSX empêchent cela. Traitez-moi de paresseux.

Les métadonnées XLSX peuvent-elles être définies pour empêcher cela ? Je suppose qu'il y a peut-être une "date de création" quelque part. Je me fiche que ce soit toujours 1970-01-01.

Défense préventive : Non, je ne peux pas utiliser un CSV, le XLSX comporte plusieurs feuilles et c'est ce que veulent mes utilisateurs finaux. Oui, je l'écris déjà aussi dans une base de données SQlite et c'est identique quand on y écrit les mêmes données.

Je ne pense pas que cela puisse être fait avec openxlsx tel quel, puisque la différence est due aux métadonnées XML créées : https://github.com/ycphs/openxlsx/blob/7742063a4473879490d789c552bb8e6cc9a0d2c7/R/baseXML.R#L77 où il met le courant Sys.time() dans le created champ.

Il semble y avoir deux sources de différence. Premièrement, il y a les métadonnées Excel écrites dans le fichier <dcterms:created> les métadonnées dans la structure du document MS Excel. Mais même en fixant cela de la même manière (par monkey-Parcheando openxlsx ) laisse quand même une différence car le document est regroupé en utilisant le format standard ZIP et que a également des en-têtes de datestamp.

Voici deux fichiers XLSX, décompressés, qui présentent tous les mêmes valeurs CRC-32, ce qui signifie que les fichiers qu'ils contiennent sont identiques :

Archive:  test1.xlsx
 Length   Method    Size  Cmpr    Date    Time   CRC-32   Name
--------  ------  ------- ---- ---------- ----- --------  ----
     587  Defl:N      234  60% 2022-01-31 15:22 b5dbec60  _rels/.rels
    1402  Defl:N      362  74% 2022-01-31 15:22 63422601  [Content_Types].xml
     284  Defl:N      173  39% 2022-01-31 15:22 f9153db0  docProps/app.xml
     552  Defl:N      278  50% 2022-01-31 15:22 37126cbe  docProps/core.xml
     696  Defl:N      229  67% 2022-01-31 15:22 14a147d3  xl/_rels/workbook.xml.rels
    4500  Defl:N      311  93% 2022-01-31 15:22 285db1ad  xl/printerSettings/printerSettings1.bin
     601  Defl:N      203  66% 2022-01-31 15:22 211e1d6e  xl/sharedStrings.xml
    1127  Defl:N      464  59% 2022-01-31 15:22 0d8ee71d  xl/styles.xml
    7075  Defl:N     1361  81% 2022-01-31 15:22 050f988c  xl/theme/theme1.xml
     950  Defl:N      382  60% 2022-01-31 15:22 1b8cce29  xl/workbook.xml
     612  Defl:N      223  64% 2022-01-31 15:22 f0584777  xl/worksheets/_rels/sheet1.xml.rels
   12729  Defl:N     2204  83% 2022-01-31 15:22 18057777  xl/worksheets/sheet1.xml
--------          -------  ---                            -------
   31115             6424  79%                            12 files
$ unzip -v test2.xlsx
Archive:  test2.xlsx
 Length   Method    Size  Cmpr    Date    Time   CRC-32   Name
--------  ------  ------- ---- ---------- ----- --------  ----
     587  Defl:N      234  60% 2022-01-31 15:22 b5dbec60  _rels/.rels
    1402  Defl:N      362  74% 2022-01-31 15:22 63422601  [Content_Types].xml
     284  Defl:N      173  39% 2022-01-31 15:22 f9153db0  docProps/app.xml
     552  Defl:N      278  50% 2022-01-31 15:22 37126cbe  docProps/core.xml
     696  Defl:N      229  67% 2022-01-31 15:22 14a147d3  xl/_rels/workbook.xml.rels
    4500  Defl:N      311  93% 2022-01-31 15:22 285db1ad  xl/printerSettings/printerSettings1.bin
     601  Defl:N      203  66% 2022-01-31 15:22 211e1d6e  xl/sharedStrings.xml
    1127  Defl:N      464  59% 2022-01-31 15:22 0d8ee71d  xl/styles.xml
    7075  Defl:N     1361  81% 2022-01-31 15:22 050f988c  xl/theme/theme1.xml
     950  Defl:N      382  60% 2022-01-31 15:22 1b8cce29  xl/workbook.xml
     612  Defl:N      223  64% 2022-01-31 15:22 f0584777  xl/worksheets/_rels/sheet1.xml.rels
   12729  Defl:N     2204  83% 2022-01-31 15:22 18057777  xl/worksheets/sheet1.xml

mais les fichiers diffèrent toujours :

$ md5sum test1.xlsx test2.xlsx 
27783e8b19631039a1c940db214f25e1  test1.xlsx
ba0678946aea1e01093ce25130b2c467  test2.xlsx

à cause des métadonnées du ZIP, visibles avec exiftool :

$ exiftool test*.xlsx | grep Zip | grep Date
Zip Modify Date                 : 2022:01:31 15:22:52
Zip Modify Date                 : 2022:01:31 15:22:54

Answer 1

Une solution de contournement possible consiste à redéfinir les paramètres suivants genBaseCore fonction dans openxlsx en utilisant assignInNamespace .

Dans l'exemple ci-dessous, xlsx est created un jour avant Sys.time() :

library(openxlsx)

genBaseCore <- function(creator = "", title = NULL, subject = NULL, category = NULL) {

  replaceIllegalCharacters <- function(v){

    vEnc <- Encoding(v)
    v <- as.character(v)

    flg <- vEnc != "UTF-8"
    if(any(flg))
      v[flg] <- iconv(v[flg], from = "", to = "UTF-8")

    v <- gsub('&', "&amp;", v, fixed = TRUE)
    v <- gsub('"', "&quot;", v, fixed = TRUE)
    v <- gsub("'", "&apos;", v, fixed = TRUE)
    v <- gsub('<', "&lt;", v, fixed = TRUE)
    v <- gsub('>', "&gt;", v, fixed = TRUE)

    ## Escape sequences
    v <- gsub("\a", "", v, fixed = TRUE)
    v <- gsub("\b", "", v, fixed = TRUE)
    v <- gsub("\v", "", v, fixed = TRUE)
    v <- gsub("\f", "", v, fixed = TRUE)

    return(v)
  }

  core <- '<coreProperties xmlns="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'

  core <- stringi:::stri_c(core, sprintf("<dc:creator>%s</dc:creator>", replaceIllegalCharacters(creator)))
  core <- stringi:::stri_c(core, sprintf("<cp:lastModifiedBy>%s</cp:lastModifiedBy>", replaceIllegalCharacters(creator)))

# Modify creation date here
  core <- stringi:::stri_c(core, sprintf('<dcterms:created xsi:type="dcterms:W3CDTF">%s</dcterms:created>', format(Sys.time()-86400, "%Y-%m-%dT%H:%M:%SZ")))

  if (!is.null(title)) {
    core <- stringi:::stri_c(core, sprintf("<dc:title>%s</dc:title>", replaceIllegalCharacters(title)))
  }

  if (!is.null(subject)) {
    core <- stringi:::stri_c(core, sprintf("<dc:subject>%s</dc:subject>", replaceIllegalCharacters(subject)))
  }

  if (!is.null(category)) {
    core <- stringi:::stri_c(core, sprintf("<cp:category>%s</cp:category>", replaceIllegalCharacters(category)))
  }

  core <- stringi:::stri_c(core, "</coreProperties>")

  return(core)
}

assignInNamespace("genBaseCore", genBaseCore, ns="openxlsx")

write.xlsx(mtcars, "test.xlsx")

<Created>2022-01-30T15:13:27Z</Created>

Answer 2

Vous pouvez essayer une enveloppe plus simple pour comparer les objets du classeur (en supposant que vous ayez lu le classeur précédent) et le comparer au classeur actuel.

library(openxlsx)
file1 <- temp_xlsx()
file2 <- temp_xlsx()
write.xlsx(mtcars, file1)
Sys.sleep(2)
write.xlsx(mtcars, file2)

wb1 <- loadWorkbook(file1)
wb2 <- loadWorkbook(file2)

all_equal_wb <- function(target, current) {
  exp <- "Workbook"
  attr(exp, "package") <- "openxlsx"
  stopifnot(identical(class(target), exp), identical(class(current), exp))
  target <- target$copy()
  current <- current$copy()
  target$core <- ""
  current$core <- ""
  # openxlsx::all.equal.Workbook
  all.equal(target, current)
}

all.equal(wb1, wb2)
#> [1] "Component \"core\": 1 string mismatch"
all_equal_wb(wb1, wb2)
#> [1] TRUE

Créé le 2022-01-31 par le paquet reprex (v2.0.1)

openxlsx::all.equal.Workbook() ne dispose pas de ce type de contrôle ( ... est ignorée) mais elle peut être ajoutée. Le paquet est toujours maintenu, alors n'hésitez pas à laisser un problème : https://github.com/ycphs/openxlsx/issues