C＃如何读取具有字符级格式的Excel单元格XML值？ （中的多个元素）

I am working on a small application that will read the contents of an excel worksheet and import all the data as strings into a windows form datagridview.

我正在开发一个小应用程序,它将读取excel工作表的内容并将所有数据作为字符串导入到windows form datagridview中。

I have successfully implemented Dietmar Schoder's code example to do this. Special thanks to him for posting it.

我已经成功实现了Dietmar Schoder的代码示例来实现这一目标。特别感谢他发布它。

http://www.codeproject.com/Tips/801032/Csharp-How-To-Read-xlsx-Excel-File-With-Lines-of?fid=1864959&df=10000&mpp=50&sort=Position&spc=Relaxed&tid=5149734

I am new to XML and have been stuck on this problem for a while now.

我是XML的新手,现在已经停留在这个问题上了一段时间。

The cells with "character level" formatting contain two or more separate <t> text values within the <si> xml element.

具有“字符级别”格式的单元格在 xml元素中包含两个或多个单独的 文本值。

Here is a snippet from the excel file's sharedstrings.xml file

以下是excel文件的sharedstrings.xml文件的片段

<si>
    <r>
      <rPr>
        <b/>
        <sz val="12"/>
        <color rgb="FFFF0000"/>
        <rFont val="Arial"/>
        <family val="2"/>
      </rPr>
      <t>Text A</t>
    </r>
    <r>
      <rPr>
        <b/>
        <sz val="12"/>
        <color theme="1"/>
        <rFont val="Arial"/>
        <family val="2"/>
      </rPr>
      <t xml:space="preserve"> Text B</t>
    </r>
  </si>

This cell contains the text "Text A Text B" but returns null because the cell has character level formatting and therefore two <t> tags. "Text A" has strike-through, colored differently or bold etc and "Text B" doesn't.

此单元格包含文本“Text A Text B”但返回null,因为单元格具有字符级格式,因此具有两个 标记。 “文字A”具有透视,颜色不同或粗体等,而“文字B”则没有。

The text values are assigned with the following line of code.

使用以下代码行分配文本值。

Text = Workbook.SharedStrings.si[Convert.ToInt32(_value)].t;

Is there anyway to concatenate the strings from both <t> elements before assigning it to the Text variable?

无论如何在将它们分配给Text变量之前连接两个 元素的字符串?

Edit: I think I have no narrowed the problem down to the sharedstrings.cs file and the deserialization of the sharedstrings.xml

编辑:我认为我没有将问题缩小到sharedstrings.cs文件和sharedstrings.xml的反序列化

SharedStrings = DeserializedZipEntry<sst>(GetZipArchiveEntry(zipArchive, @"xl/sharedStrings.xml"));

sst class:

[Serializable()]
[XmlType(Namespace = "http://schemas.openxmlformats.org/spreadsheetml/2006/main")]
[XmlRoot("sst", Namespace = "http://schemas.openxmlformats.org/spreadsheetml/2006/main")]

public class sst
{
    [XmlAttribute]
    public string uniqueCount;

    [XmlAttribute]
    public string count;

    [XmlElement("si")]
    public SharedString[] si;

    public sst() { }
}

public class SharedString
{
    public string t;        
}

I have been unable to edit this class in a way that will correctly interpret both t elements text values.

我无法以正确解释这两个元素文本值的方式编辑此类。

1 个解决方案

#1

Solved this one myself after studying more about xml serialization and many other similar questions to this one on here.

在研究了xml序列化以及许多其他类似问题后,我自己解决了这个问题。

sst class:

public class sst
{
    [XmlAttribute]
    public string uniqueCount;

    [XmlAttribute]
    public string count;

    [XmlElement("si")]
    public SharedString[] si;



    public sst() { }
}

public class SharedString
{
    public string t;

    [XmlElement("r")]
    public NestedString[] ns;

    public SharedString() { }

}

public class NestedString
{
    public string t;
}

And the assignment of cell's text:

并分配单元格的文本:

if (Workbook.SharedStrings.si[Convert.ToInt32(_value)].t != null)
{
   Text = Workbook.SharedStrings.si[Convert.ToInt32(_value)].t;
}
else if (Workbook.SharedStrings.si[Convert.ToInt32(_value)].ns != null)
{
   for (int i = 0; i < Workbook.SharedStrings.si[Convert.ToInt32(_value)].ns.Length; i++)
   {
     Text += Workbook.SharedStrings.si[Convert.ToInt32(_value)].ns[i].t;
   }
}