EPPlus保存含空字符串返回值公式的单元格时<v>元素丢失问题咨询

问题描述 #

Excel中部分函数可返回空字符串，例如：

IF(ISBLANK(A1),"","Not blank")

CONCAT或CONCATENATE也是典型案例。当使用EPPlus保存（未修改）包含此类公式的Excel工作簿并重新加载时，单元格值会从空字符串变为null。

已在EPPlus v6.2.6（源码编译）和v6.2.7（NuGet获取）版本中验证该现象。

在Excel中重新打开原文件或保存后的文件时无此变化（推测是Excel加载时会重新计算公式），但对比XML结构发现：原Excel生成的文件中存在空的<v>元素，而EPPlus保存后的文件中该元素被省略。

该现象的根源在于ExcelXmlWriter.GetFormulaValue(object v, string prefix)方法——当字符串值为空时，该方法不会输出<v>元素。

核心疑问：此为预期行为还是bug？

重现步骤 #

1. 在Excel中新建空白工作簿，在B1单元格输入公式：

   =IF(ISBLANK(A1),"","Not blank")
   

2. 将工作簿保存为origIf.xlsx
3. 运行以下C#代码（需引用EPPlus库）：

   using System.Diagnostics;
   using System.IO;
   using OfficeOpenXml;
   
   static void Investigate()
   {
       const string baseDir = @"U:\Test\EPPlus_Null"; // 根据实际路径修改
       const string origFile = "origIf.xlsx";
       const string saveFile = "nomod-saved.xlsx";
       ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
       {
           Debug.WriteLine("Original workbook");
           using (var excelPackage = new ExcelPackage(Path.Combine(baseDir, origFile))) {
               ExcelRange a1Cell = excelPackage.Workbook.Worksheets["Sheet1"].Cells[1, 1];
               ExcelRange b1Cell = excelPackage.Workbook.Worksheets["Sheet1"].Cells[1, 2];
               ReportValue(a1Cell);
               ReportValue(b1Cell);
               excelPackage.SaveAs(Path.Combine(baseDir, saveFile));
           }
           Debug.WriteLine("Saved workbook");
           using (var excelPackage = new ExcelPackage(Path.Combine(baseDir, saveFile))) {
               ExcelRange a1Cell = excelPackage.Workbook.Worksheets["Sheet1"].Cells[1, 1];
               ExcelRange b1Cell = excelPackage.Workbook.Worksheets["Sheet1"].Cells[1, 2];
               ReportValue(a1Cell);
               ReportValue(b1Cell);
           }
       }
   }
   
   static void ReportValue(ExcelRange cell)
   {
       Debug.Write(cell.Address);
       if (cell.Value is null)
           Debug.WriteLine(" value is null");
       else if (cell.GetCellValue<string>() == "") {
           Debug.WriteLine(" value is empty string");
       }
       else {
           Debug.WriteLine(" value is " + cell.GetCellValue<string>());
       }
   }
   

运行输出 #

Original workbook
A1 value is null
B1 value is empty string
Saved workbook
A1 value is null
B1 value is null

注意：B1单元格值从空字符串变为null。

XML结构对比 #

Excel 2019生成的原始XML #

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac xr xr2 xr3" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xr:uid="{00000000-0001-0000-0000-000000000000}">
    <dimension ref="B1"/>
    <sheetViews>
        <sheetView tabSelected="1" workbookViewId="0">
            <selection activeCell="B1" sqref="B1"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
    <sheetData>
        <row r="1" spans="2:2" x14ac:dyDescent="0.25">
            <c r="B1" t="str">
                <f>IF(ISBLANK(A1),"","Not blank")</f>
                <v/>
            </c>
        </row>
    </sheetData>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

注意：B1单元格对应的<c>节点中存在空的<v/>元素。

EPPlus生成的XML #

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac xr xr2 xr3" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xr:uid="{00000000-0001-0000-0000-000000000000}">
    <dimension ref="B1"/>
    <sheetViews>
        <sheetView tabSelected="1" workbookViewId="0">
            <selection activeCell="B1" sqref="B1"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>
    <sheetData>
        <row r="1">
            <c r="B1" s="0" t="str">
                <f>IF(ISBLANK(A1),"","Not blank")</f>
            </c>
        </row>
    </sheetData>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
    <headerFooter/>
</worksheet>

注意：B1单元格对应的<c>节点中缺少<v>元素。

内容的提问来源于stack exchange，提问作者Steve Kidd