OpenXML SDK 2.5不可读内容

Question

我正在处理一个已经拥有所有公式和格式的Excel文件，我通过添加一个带表格的工作表来向其中添加数据。当我在Excel中打开该文件时，会出现以下错误信息：

"Excel完成了文件级别的验证和修复，此工作簿的某些部分可能已被修复或丢失。已删除记录：/xl/worksheets/sheet6.xml 部分的单元格信息"

然而，当我以相同内容手动创建文件并打开时，则不会出现任何问题。 我还发现了Open XML 2.5生产力工具，它可以针对生成的文件运行验证，结果称没有问题。

当我在这两个文件上进行比较时，我发现生成的文件看起来是这样的。

        <x:c r="B462" t="inlineStr">
            <x:is>
                <x:t>1150828</x:t>
            </x:is>
        </x:c>

虽然手动创建的文件中的单元格看起来像这样。

       <c s="80" r="B462">
         <v>
           1150828
         </v>
       </c>

显然这里有一个差异，但我不知道如何纠正它，也不知道这个差异是否是错误的实际原因。但是，既然其他所有东西看起来都一样，我不知道还能是什么其他原因。

哦，还有几件事情，这个文件不起作用，但我能够使用另一个不包含表格的文件，当我加入表格时，问题就会出现，所以我至少知道这一点。

另外，如果你要建议我使用ClosedXML，请不要这样做。我已经用过它，但它似乎会随机地留下格式问题，我无法弄清楚原因，这就是为什么我转向OpenXML SDK的原因。

以下是一些C#代码。

 dt.Load(reader);
                            RowCount = dt.Rows.Count;
                            ColumnCount = dt.Columns.Count;

                                workbookPart = spreadDoc.WorkbookPart;
                                SheetDimension sheetDimension = new SheetDimension() { Reference = "A1:" + ColumnLetters[ColumnCount - 1] + (RowCount + 1) };

                                worksheetPart = Program.GetWorksheetPart(workbookPart, reportStep.ExcelSheetName);
                                worksheetPart.Worksheet.SheetDimension = sheetDimension;

                                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                                string relId = workbookPart.Workbook.Descendants<Sheet>().First(s => reportStep.ExcelSheetName.Equals(s.Name)).Id;

                                if (reportStep.ExcelTableExists)
                                {
                                    TableDefinitionPart tableDef = null;

                                    int looper = 0;
                                    foreach (WorksheetPart wsp in spreadDoc.WorkbookPart.WorksheetParts)
                                    {
                                        if (wsp.TableDefinitionParts.Where(tbl => tbl.Table.DisplayName.Value.Equals(reportStep.ExcelTableName)).Count() == 1)
                                        {
                                            tableDef = spreadDoc.WorkbookPart.WorksheetParts.ElementAt(looper).TableDefinitionParts.Where(tbl => tbl.Table.DisplayName.Value.Equals(reportStep.ExcelTableName)).FirstOrDefault();
                                            tableDef.Table.Reference.Value = "A1:" + (ColumnLetters[ColumnCount - 1] + (RowCount +1) ).ToString();
                                            tableDef.Table.AutoFilter.Reference.Value = "A1:" + (ColumnLetters[ColumnCount - 1] + (RowCount +1)).ToString();
                                           // tabledefinitionPart = Program.GetTablePart(wsp, reportStep.ExcelTableName, ColumnCount, RowCount);
                                        }
                                        looper++;
                                    }


                                }

                                sheetData = Chef.Program.ExportDataTable(dt, sheetData);
                                Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name == reportStep.ExcelSheetName);









public static TableDefinitionPart GetTablePart(WorksheetPart worksheet, string tablename, int columnCount, int rowCount)
    {
        uint CellRange = (uint)(columnCount);
        TableColumns tableColumns1 = new TableColumns() { Count = (UInt32Value)(CellRange) };

        var tableDefPart = worksheet.TableDefinitionParts.Where(tbl => tbl.Table.DisplayName.Value.Equals(tablename)).FirstOrDefault();
            //worksheet.WorksheetPart.TableDefinitionParts.AddNewPart<TableDefinitionPart>(tablename);
        var table = new Table() { HeaderRowCount = (uint)columnCount, Name = tablename, DisplayName = tablename, Reference = "A1:" + ColumnLetters[columnCount -1] + (rowCount + 1), TotalsRowShown = false };
        TableStyleInfo tableStyleInfo1 = new TableStyleInfo()
        {
            Name = "TableStyleMedium2",
            ShowFirstColumn = false,
            ShowLastColumn = false,
            ShowRowStripes = true,
            ShowColumnStripes = false
        };

        table.Append(tableStyleInfo1);
       // table.Append(tableColumns1);
        tableDefPart.Table = table;
        return tableDefPart;
    }

编辑部分，添加所请求的额外方法 更新于9/5/15

我已经删除了添加标题值的代码，因为它们已经是Excel文件基础模板的一部分。还删除了指定单元格数据类型的操作，以保留模板已有的单元格数据类型设置。

       public static SheetData ExportDataTable2(System.Data.DataTable exportData, SheetData sheetData)
    {
        //loop through each data row  
        DataRow contentRow;
        int startRow = 2;
        for (int i = 0; i < exportData.Rows.Count; i++)
        {
            contentRow = exportData.Rows[i];
            sheetData.AppendChild(createContentRow(contentRow, i + startRow));
        }

        return sheetData;
    }


    private static Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
    {
        Cell cell = new Cell();

       // cell.DataType = CellValues.Number;
        cell.CellReference = getColumnName(columnIndex) + rowIndex;
        cell.CellValue = new CellValue(cellValue.ToString());

        return cell;
    }

    private static Row createContentRow(DataRow dataRow, int rowIndex)
    {

        Row row = new Row
        {
            RowIndex = (UInt32)rowIndex
        };

        for (int i = 0; i < dataRow.Table.Columns.Count; i++)
        {
            Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
          //  dataCell.DataType = CellValues.SharedString;
            row.AppendChild(dataCell);
        }

        return row;
    }

- scripter78

---

请问你可以发布 Chef.Program.ExportDataTable 方法的代码吗？你列出的 XML 看起来不错，生成的 XML 使用的是内联字符串，而手动创建的则使用了 共享字符串表。 - petelids

你走在正确的道路上，我相信。我已经添加了额外的方法来展示数据是如何被添加的。 - scripter78

其实，我认为手动创建文件中的单元格值是一个数字（而不是对共享字符串表的引用），而生成的文件中的单元格值是一个内联字符串。修改你的代码，将数字值插入到单元格中，而不是内联字符串。这可能与表格有关（因为你说没有表格的生成文件工作正常），或许表格要求单元格值为数字。 - Stipo

所以，前几天晚上我实际上改变了代码，删除了行cell.DataType = CellValues.String;，虽然现在它可以正常工作，字段得到填充，其他表格的计算也可以正常工作，但我仍然会收到错误提示，说是无法读取内容。如果这只是为了我的个人使用，那么我就不管它了，但由于这些文件将被发送给我们公司的领导，我必须找出是什么原因导致了这个问题。所以，根据我的理解，不指定单元格数据类型只是保持模板已经有的数据类型。 - scripter78

我已经追踪到问题出在单元格数据输入方式上，因为我可以打开并保存文件，而不添加任何数据，它可以正常工作。我正在更新以上代码以反映我修改后的当前状态。 - scripter78

Answer 1

看起来你使用了以下示例OpenXML SDK 2.0: 将 DataTable 导出到 Excel作为你代码的基础。这是创建单元格的原始代码：

private Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
{
    Cell cell = new Cell();

    cell.DataType = CellValues.InlineString;
    cell.CellReference = getColumnName(columnIndex) + rowIndex;
    InlineString inlineString = new InlineString();
    Text t = new Text();

    t.Text = cellValue.ToString();
    inlineString.AppendChild(t);
    cell.AppendChild(inlineString);

    return cell;
}

您的原始代码与以下行完全相同：

cell.DataType = CellValues.String;

看出区别了吗？然后你将其改为：

private static Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
    {
        Cell cell = new Cell();

       // cell.DataType = CellValues.Number;
        cell.CellReference = getColumnName(columnIndex) + rowIndex;
        cell.CellValue = new CellValue(cellValue.ToString());

        return cell;
    }

好的，问题在于您没有正确设置cell.DataType。它需要与单元格内容同步，否则您将从Excel中获得此类错误。在前一种情况下，您将内容设置为inline string，但数据类型为String。在后一种情况下，将数据类型设置为Number（即使您已注释该行 - Number是单元格的默认数据类型），但内容并不总是数字（相同的函数用于列标题 - 毕竟，它被称为createTextCell）。

为了解决问题，请使用示例中的原始代码或以下代码：

private static Cell createTextCell(int columnIndex, int rowIndex, object cellValue)
{
    Cell cell = new Cell();
    cell.DataType = CellValues.String;
    cell.CellReference = getColumnName(columnIndex) + rowIndex;
    cell.CellValue = new CellValue(cellValue.ToString());
    return cell;
}

最后，如果您需要存储共享的字符串、数字、日期等，请阅读文档并设置适当的属性。我同意OpenXml API并不是非常直观，但这就是我们所拥有的。

编辑：根据您的评论，似乎您真正的问题并不完全是这个。下面是一个高性能示例，用于导出带有不同数据类型列的DataTable：

public static class ExcelExporter
{
    public static void ExportDataTable(DataTable table, SheetData data)
    {
        var cellFactory = new CellFactory[table.Columns.Count];
        for (int i = 0; i < table.Columns.Count; i++)
            cellFactory[i] = GetCellFactory(table.Columns[i].DataType);
        int rowIndex = 0;
        data.AppendChild(CreateHeaderRow(rowIndex++, table));
        for (int i = 0; i < table.Rows.Count; i++)
            data.AppendChild(CreateContentRow(rowIndex++, table.Rows[i], cellFactory));
    }
    private static Row CreateHeaderRow(int rowIndex, DataTable table)
    {
        var row = CreateRow(rowIndex);
        for (int i = 0; i < table.Columns.Count; i++)
        {
            var cell = CreateTextCell(i, rowIndex, table.Columns[i].ColumnName);
            row.AppendChild(cell);
        }
        return row;
    }
    private static Row CreateContentRow(int rowIndex, DataRow dataRow, CellFactory[] cellFactory)
    {
        var row = CreateRow(rowIndex);
        for (int i = 0; i < dataRow.Table.Columns.Count; i++)
        {
            var cell = cellFactory[i](i, rowIndex, dataRow[i]);
            row.AppendChild(cell);
        }
        return row;
    }
    private static Row CreateRow(int index) { return new Row { RowIndex = (uint)index + 1 }; }
    private delegate Cell CellFactory(int columnIndex, int rowIndex, object cellValue);
    private static CellFactory GetCellFactory(Type dataType)
    {
        CellFactory factory;
        return CellFactoryMap.TryGetValue(dataType, out factory) ? factory : TextCellFactory;
    }
    private static readonly CellFactory TextCellFactory = CreateTextCell;
    private static readonly CellFactory DateCellFactory = CreateDateCell;
    private static readonly CellFactory NumericCellFactory = CreateNumericCell;
    private static readonly CellFactory BooleanCellFactory = CreateBooleanCell;
    private static readonly Dictionary<Type, CellFactory> CellFactoryMap = new Dictionary<Type, CellFactory>
    {
        { typeof(bool), BooleanCellFactory },
        { typeof(DateTime), DateCellFactory },
        { typeof(byte), NumericCellFactory },
        { typeof(sbyte), NumericCellFactory },
        { typeof(short), NumericCellFactory },
        { typeof(ushort), NumericCellFactory },
        { typeof(int), NumericCellFactory },
        { typeof(uint), NumericCellFactory },
        { typeof(long), NumericCellFactory },
        { typeof(ulong), NumericCellFactory },
        { typeof(float), NumericCellFactory },
        { typeof(double), NumericCellFactory },
        { typeof(decimal), NumericCellFactory },
    };
    private static Cell CreateTextCell(int columnIndex, int rowIndex, object cellValue)
    {
        return CreateCell(CellValues.String, columnIndex, rowIndex, ToExcelValue(cellValue));
    }
    private static Cell CreateDateCell(int columnIndex, int rowIndex, object cellValue)
    {
        // NOTE: CellValues.Date is not supported in older Excel version.
        // In all Excel versions dates can be stored with CellValues.Number and a format style.
        // Since I have no styles, will export them just as text
        //var cell = CreateCell(CellValues.Number, columnIndex, rowIndex, ToExcelDate(cellValue));
        //cell.StyleIndex = ...;
        //return cell;
        return CreateCell(CellValues.String, columnIndex, rowIndex, 
            cellValue != null && cellValue != DBNull.Value ? ((DateTime)cellValue).ToShortDateString() : null);
    }
    private static Cell CreateNumericCell(int columnIndex, int rowIndex, object cellValue)
    {
        return CreateCell(CellValues.Number, columnIndex, rowIndex, ToExcelValue(cellValue));
    }
    private static Cell CreateBooleanCell(int columnIndex, int rowIndex, object cellValue)
    {
        // NOTE: CellValues.Boolean is not supported in older Excel version
        //return CreateCell(CellValues.Boolean, columnIndex, rowIndex, ToExcelValue(cellValue));
        return CreateCell(CellValues.String, columnIndex, rowIndex, ToExcelValue(cellValue));
    }
    private static Cell CreateCell(CellValues dataType, int columnIndex, int rowIndex, string cellValue)
    {
        var cell = new Cell();
        if (dataType != CellValues.Number) cell.DataType = dataType;
        cell.CellReference = GetColumnName(columnIndex) + (rowIndex + 1);
        cell.CellValue = new CellValue(cellValue ?? string.Empty);
        return cell;
    }
    private static string ToExcelValue(object value)
    {
        if (value == null || value == DBNull.Value) return null;
        return Convert.ToString(value, CultureInfo.InvariantCulture);
    }
    private static DateTime ExcelBaseDate = new DateTime(1900, 1, 1);
    private static string ToExcelDate(object value)
    {
        const int days29Feb1900 = 59;
        if (value == null || value == DBNull.Value) return null;
        var date = ((DateTime)value).Date;
        var days = (date - ExcelBaseDate).Days + 1;
        if (days >= days29Feb1900) days++;
        return days.ToString(CultureInfo.InvariantCulture);
    }
    private static string GetColumnName(int index) { return ColumnNameTable[index]; }
    private static readonly string[] ColumnNameTable = BuildColumnNameTable();
    private static string[] BuildColumnNameTable()
    {
        var table = new string[16384];
        var sb = new StringBuilder();
        for (int i = 0; i < table.Length; i++)
            table[i] = sb.BuildColumnName(i);
        return table;
    }
    private static string BuildColumnName(this StringBuilder sb, int index)
    {
        const int startLetter = 'A';
        const int letterCount = 'Z' - startLetter + 1;
        sb.Clear();
        while (true)
        {
            var letter = (char)(startLetter + (index % letterCount));
            sb.Insert(0, letter);
            if (index < letterCount) break;
            index = (index / letterCount) - 1;
        }
        return sb.ToString();
    }
}

关键在于，在处理过程中，不要检查每个值的类型，而是根据其数据类型在开始时为每个列准备不同的创建单元格方法。

- Ivan Stoev

---

这里有一件事，如果我注释掉cell.DataType = CellValues.String; 这行代码，文件会生成错误，但在Excel进行修复后仍然可以正常工作。 - scripter78

如果我保留那行代码，它就不会出错，但是除非我在Excel中对表格中的每一列执行“文本分列”技巧，否则它的计算无法正常工作。这两个选项都是不可接受的。 - scripter78

我刚刚做了这个更改： private static Cell createTextCell2(int columnIndex, int rowIndex, object cellValue) { Cell cell = new Cell(); if(cellValue is string) { cell.DataType = CellValues.String; } if(cellValue is int) { cell.DataType = CellValues.Number; } } - scripter78

支持所有标准数据类型，主要关注字符串、整数和日期。是的，我正在查询数据库，将结果返回到数据表中，并将数据表传递给ExportDataTable2。 - scripter78

你能等明天吗？因为这里已经接近午夜了。我认为我可以准备一个更好的示例，用高性能导出不同数据类型的 DataTable。 - Ivan Stoev

Answer 2

我之前在使用OpenXml SDK时也遇到了文件无效的问题。你可以参考一下OpenXml Power Tools，它们解决了我所有的问题 :) 另外，建议你切换到OpenXml Sdk 2.6版本，以避免与System.IO.Packaging相关的问题。希望这能帮助到你！

- Rafael Regh