VSTO创建的Excel文档中序列型下拉列表造成文档无法打开的问题

    由于工作并不是在手头这台机器上做的，所以只简单文字记录一下，希望能说得清楚。

    最近在搞一个Excel扩展的东西，用VSTO自动设计并且填写电子表格。……这是个对于个人休闲作品来说略显复杂的工程，仅C#代码量约达到3000行，还要设计十几个页面模板，并融合约千行VBA代码……。在调试环境中一切正常，一上到实际环境就出问题了……而且我完全不知道问题出在什么地方……可以想象其时我有多么沮丧了

    问题是这样的，当程序自动生成某个文档（如2.xlsm），并且填写完内容保存关闭后再打开，程序会直接崩溃。我费了不少时间来确定崩溃的原因——这个文件因为某种原因无法打开，而我将Excel.Application的visable、AskToUpdateLinks、DisplayAlerts等开关全部屏蔽，Excel无法告知我这个文件有问题，因此选择崩溃。

    好吧，把开关全打开，或者更简单一点，双击直接打开这个文件，Excel会弹出一个错误警告——“Excel在"2.xlsm"中发现不可读取的内容。是否恢复此工作簿的内容？如果信任此工作簿的来源，请单击"是"。”单击是，修复，Excel显示修复的内容是“已删除的功能：/xl/worksheets/sheet1.xml部分的数据有效性”。大概意思是，我生成的这个文件部分数据有效性的设置有问题了。

    事实上，在我设计的表格中有些栏确实需要从数据库中得到一个列表，然后将这个列表作为该栏的序列型数据有效项。但我百般检查也没有发现这些有效项有什么错误，尤其是使用测试数据时程序也不会出现任何问题，为什么到实际环境就会出错？

    幸而首先在Excel技巧网上看到一个神人的文章，得知原来Excel的文件都是ZIP压缩文件，改个后缀就可以看到内部的结构（以文件夹和文件的形式存放），所以改了后缀，在上面Excel提示的文件中找到了Sheet1.xml文件。打开研究一下吧。

    作为xml文件，格式一切正常，错误又在哪里呢？实际此刻已经非常接近问题所在了。在这个文件中<dataValidations count="表中有效项总个数">节点下，可以看到所有有效项。可以看到一个序列型的数据有效项通常是这么写的：

    -<dataValidation sqref="单元格区域地址" type="list" showErrorMessage="1" allowBlank="1">

        <formula1>"下拉项1,下拉项2,……"</formula1>

    </dataValidation>

    在查找问题时，我使用的并非测试数据，在实际数据中，有如这样形式的下拉列表项值：

    “狗狗”的苹果5

    “猫猫”的苹果6

    ……等等。

    那么，这些项在以上的xml结果中会是如下这个样子……

    -<dataValidation sqref="单元格区域地址" type="list" showErrorMessage="1" allowBlank="1">

        <formula1>"“狗狗”的苹果5,“猫猫”的苹果6,……"</formula1>

    </dataValidation>

    红色标出来的部分，难道是这个引号的问题，赶上SQL注入了？！ 赶紧改改，错误依旧…… 本来也是，我用的是中文的引号，应该完全没有问题吧（不知道如果原始数据有英文的引号会不会真的引起格式问题）。

    ……然后继续郁闷……直到最后，祭起删除大法，一点一点删除序列中的数据……直到数据长度缩短到一定程度时，正确了……

    尼玛，貌似是公式长度的问题啊，为毛从来木有听说过哪里有说明缓存不够的呢？问题是我的实际数据中这个列表就是有好几十项超长文字的啊……。赶紧查询一下，有文章说是因为2010版本以下只支持256个字符的公式，但我用的就是2010啊。打开2010试试，在数据有效项中手敲公式，果然，到了一定长度Excel就拒绝干活了……简单查询了一下，这应该和xml解释器的标签内容长度限制有关。xml本身是没有标签内容长度限制的，但解释器有……，不过我想说的是，Excel的这个长度限制也太小了……，我不过是20几句话而已嘛，不知道有没有办法可以自定义缓冲区的长度来着……

    问题又来了，在我的另一个程序里，同样的数据也做成下拉列表了啊？当然，这里还是有一点区别，在那个程序里，数据被导入到Excel表格的某个地方，并没有直接出现在公式里，而是以<formula1>="=$A$1:$A100"的引用形式出现而已。这种方法在有VSTO的地方就显得比较笨拙并且无法隐藏数据了……不过，暂时我只能想到这样的解决办法了。

    有时候发现，和微软的Bug做斗争有时候也挺有趣的，因此在这里记上一笔，与各位程序猿共勉。