我能否在R中使用相同的数据框架编写相同的xlsx文件？

Question

当我使用 openxlsx::write.xlsx 写入相同数据时，我能否确保两个 XLSX 文件完全相同？我认为电子表格中会写入一个时间戳，这意味着同样的数据在一秒钟以上的间隔内编写将创建不同的文件。

例如，当快速连续写入时：

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

文件完全相同：

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

但是如果我在写入之间睡觉：

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

这都不一样：

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

我的用例是生成一个XLSX文件的流程，该文件将被放入git存储库中。如果我自动化这个流程，那么即使源数据没有更改，XLSX文件也会每次都发生变化。我想在流程的早期测试数据是否已更改，如果没有更改，则不生成新的XLSX文件，但让git进行“是否已更改？”测试似乎更容易，但XLSX中的隐形元数据会破坏它。叫我懒。

XLSX的元数据能否设置以防止此问题？我猜可能有一个“创建日期”之类的东西。我不在乎这一直是1970-01-01。

预防性策略：不能使用CSV，XLSX有多个工作表，这是我的最终用户想要的。是的，我已经将其写入SQlite数据库，并且在向其写入相同数据时完全相同。

我认为这不能像现在这样使用openxlsx，因为差异是由于创建了元数据XML：https://github.com/ycphs/openxlsx/blob/7742063a4473879490d789c552bb8e6cc9a0d2c7/R/baseXML.R#L77，其中它将当前的Sys.time()放入created字段。

似乎有两个差异来源。首先，有Excel元数据写入到MS Excel文档结构内的<dcterms:created>元数据中。但即使将其设置为相同（通过monkey-patching openxlsx），仍然会留下差异，因为文档使用标准ZIP格式捆绑，并且那里也有日期头。

这是两个XLSX文件，未压缩，显示了所有相同的CRC-32值，因此其中的文件是相同的：

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

但是文件仍然不同：

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

由于 ZIP 元数据，可以使用 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

- Spacedman

---

如果你在 Excel 中打开这两个版本，并将它们保存为 xml，你会发现唯一的区别在于字段 <Created>2022-01-31T14:24:33Z</Created>。 - Waldi

@Waldi 我解压了 Excel 文件（.xlsx 文件实际上是伪装成 .zip 文件的），找到了确切的元数据条目差异以及 openxlsx 设置它的位置。除了分叉该软件包之外，我看不出有什么简单的方法可以解决这个问题。也许可以考虑使用其他软件包... - Spacedman

在生成xlsx之前使用系统命令设置系统时间，然后再重置它呢？类似system(sudo date --set[...])这样的方式。缺点可能在于设置日期和xls之间存在不同的延迟时间... - Grzegorz Sapijaszko

@Spacedman 你为什么在意呢？文件元数据的变化，特别是“创建时间”的变化，并不意味着数据有所不同。这并不意味着结果无法重现。只有当你想使用文件的哈希值/校验和来检查文件是否已更改而不是比较数据时，才需要关心它。 - Panagiotis Kanavos

@Spacedman，如果在完全相同的时间内由不同的用户生成相同的文件，则会出现差异，因为将保存不同的用户名。 - Panagiotis Kanavos

@PanagiotisKanavos 我关心这个问题是因为 Git 无法区分两个仅有不同创建时间的 Excel 文件和两个数据完全不同的 Excel 文件。我只想在数据不同时提交到 Git。 - Spacedman

Answer 1

一种可能的解决方法是通过使用assignInNamespace重新定义openxlsx命名空间中的genBaseCore函数。

在下面的示例中，xlsx文件是在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>

- Waldi

---

关闭。通过这种猴子补丁，您可以在XLSX中获得相同的内容，但XLSX是一个zip文件，并且每个文件中都有一个ZIP头，以及那个...有一个修改时间！我会将此添加到我的问题中... - Spacedman

Answer 2

您可以尝试使用更简单的包装器来比较工作簿对象（假设您已经读取了先前的工作簿），并将其与当前工作簿进行比较。

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

^{由reprex包（v2.0.1）于2022年1月31日创建}

openxlsx::all.equal.Workbook()没有这种控制（...被忽略），但可以添加。该软件包仍在维护中，因此请随时提出问题：https://github.com/ycphs/openxlsx/issues

- Jordan

---

在R中拥有相同的工作簿对象并不能解决在git存储库上有不同XLSX文件的问题，因此git会认为发生了更改。 - Spacedman

那么生成 xlsx 文件的程序就无法检查新工作簿是否需要上传到仓库了吗？如在其他地方所述，您看到的唯一区别是存储在 XML 中的创建时间。我想这可能可以实现到 openxlsx 中（修改创建时间），但是我想您必须接受使用虚假时间的情况（不确定排除时间是否会产生不良副作用）。 - Jordan

似乎更容易构建数据框架，将其转储到XLSX中，然后让git决定是否与构建数据不同，而不是构建数据，读取现有数据，比较新旧数据，如果没有更改则不写入。这就是我所说的“我可以在流程的早期测试数据是否已更改”，但我正在尝试避免这种情况。 - Spacedman

如果有多个时间戳需要管理，那么这可能看起来很容易，但现在似乎并不是这样。我想这就是你的答案。 - Jordan