让.csv支持样式、公式、注释、下拉列表、多工作表

发表于 分类于 Waline：
本文字数： 2.4k 阅读时长 ≈ 2 分钟
让.csv文件可以像.xlsx文件一样保存样式、公式、注释等额外信息以及支持多工作表的Excel插件

前言

众所周知，.csv文件是使用逗号进行分隔的纯文本文件，可以直接用文本编辑器进行编辑，也可以使用Excel打开当作表格来编辑每个单元格的内容。虽然使用Excel打开的.csv文件可以编辑公式和样式，但是保存后就会丢失这些信息，只会保留单元格的纯文本内容。为了让.csv文件也可以像.xlsx文件一样可以保存公式和样式等额外信息，需要一些额外的功能支持。

为什么不直接使用.xlsx呢？

因为.xlsx文件并不是普通的纯文本文件，在进行版本控制时很不友好，需要使用外部工具进行对比和合并。而且当出现非同行的修改时，也要手动进行合并而不能像普通文本一样自动合并。在多人协作的情况下会造成不小的麻烦。而.csv文件是纯文本，不同的行的改动可以自动合并，查看修改记录也非常方便。

Csv公式、样式的支持

在云风大佬的这篇策划们离不开的 Excel一文中尝试了用另一种自定义的格式取代Excel。虽然看起来比较麻烦，但是提供了非常好的思路，仍然使用Excel软件进行编辑，但是打开时通过Excel插件进行处理，这样就能实现样式和公式等功能。

我开发的这个Excel插件名为Csvpp（pp有plus plus或者pre-processer的含义）。它是一个基于VSTO的Excel插件，可以让.csv文件像.xlsx文件一样保存样式和公式等额外信息。

  • 对于样式信息：为了避免多人协作冲突，使用一个单独的同名.json文件进行保存，放在同路径的cache/文件夹，这个cache文件夹可以不加入版本管理。
  • 对于公式信息：直接保存在单元内容里，使用:=补充在单元格内容之后
  • 对于注释信息：直接保存在单元内容里，使用:#补充在单元格内容之后

实现细节

当插件检测到打开的Excel文件是一个.csv时：

  1. 首先尝试解析单元格中的注释，也就是单元格中:#之后的内容，如有，为此单元格添加注释
  2. 然后尝试解析单元中的公式，也就是单元格中:=之后的内容，如有，为此单元格添加公式
  3. 最后会尝试加载相同路径下cache/文件夹中{文件名}.json作为样式配置，诸如单元格背景色、字体、列宽、冻结行列等信息。该样式配置的格式可以自定义和扩展，只要能使用Json进行序列化和反序列化的数据均可支持。

当插件检测到保存的Excel文件是一个.csv时按照上述相反的顺序进行处理即可。

额外扩展

对于配置表文件，可以做一些默认支持

  • 自动识别标题行，并默认设置标题行背景色以及冻结到标题行
  • 自动识别数据列的类型，为枚举类型的数据列设置下拉列表（数据验证）。每个枚举对应的选项由EnumList.json进行配置，格式为：
    1
    2
    3
    4
    {
      "Enum1":"EVal1,EVal2,EVal3",
      "Enum2":"111,222,333"
    }

公式优化

对于公式，如果直接使用A1的形式，如果发生了行列变化并且执行了自动合并，那么保存的公式大概率就是错的。为此，插件还会对公式进行如下正则替换：

  • 如果是同行的单元格引用，替换为[@英文字段]
  • 如果是同列的单元格引用，替换为[#相对行号]

目前暂未对不同行不同列的公式进行替换，如果要支持，应该采用[@英文字段#相对行号]的形式。

多工作表的支持

为了支持csv的多工作表，我定义了一种以.csvm为后缀的纯文本文件，其中内容只有一列，每行都是csv文件的相对路径名。使用Excel打开此文件，会自动将列出的csv文件作为子工作表，从而可以在同一个Excel窗口中编辑多个csv文件。

实现细节

  • 当Excel打开csv或其他纯文本文件时（比如.csvm，别忘了它也只是一个纯文本文件），其实也会为它创建一个工作簿、一个工作表、一个窗口。
  • 当Excel保存工作簿时，会自动将内容保存到原来的文件中，即使它只是一个纯文本文件（只不过不会保存样式和公式等信息）

基于以上几点，插件就可以实现自定义的多工作表支持：

  • 当插件检测到打开的工作簿是一个csvm时，自动读取其中的csv文件列表，然后逐个打开这些csv文件，也就获得了对应的工作簿、工作表和窗口。为了能在同一个窗口里编辑，插件会将这些工作簿中的工作表全部移动到csvm的工作簿中，然后将它们的窗口隐藏。
  • 当插件检测到保存的工作簿是一个csvm时，自动将工作簿中除了第一个工作表之外的其他工作表（第一个工作表是csvm自己的）移动回被隐藏的工作簿，然后逐个执行保存操作。

插件方案

关于在Excel中开发插件，主要有三种方案：

  1. VBA（Visual Basic for Applications）：这是Excel内置的宏语言，可以直接在Excel中编写和运行。
  2. VSTO（Visual Studio Tools for Office）：这是微软官方提供的开发Office插件的工具，使用C#或VB.NET进行开发。它提供了丰富的API，可以访问和操作Excel的各种功能。缺点是需要安装Visual Studio，并且只能在Windows上运行。
  3. Office Add-ins（基于Web的插件）：这是微软推荐的现代Office插件开发方式，使用HTML、CSS和JavaScript进行开发。它可以跨平台运行，支持Windows、Mac和Web版的Excel。缺点是功能受限，无法访问所有的Excel功能。

插件最开始采用VBA进行编写，插件使用.xlam的格式发布，作为Excel加载项嵌入到Excel中。但是后续难以维护（无法版本管理）和扩展（VBA对于JSON格式的支持较差）。

后来尝试Office Add-ins，但是完全无法自动执行，也无法访问本地文件。

最终改用VSTO，可以接入.NET Framework的生态系统，使用C#进行开发，功能强大且易于维护和扩展。插件使用ClickOnce进行发布，插件文件本身保存在共享盘上，用户只需要双击即可安装为COM加载项，后续可以在启动Excel时自动检测更新。

相关文章