使用OpenXML和SAX模板 [英] Using a template with OpenXML and SAX

问题描述

我正在使用在使用Open XML SDK解析和阅读大型Excel文件。我正在使用XLSX文件作为模板。

I'm creating a large XLSX file from a datatable, using the SAX method proposed in Parsing and Reading Large Excel Files with the Open XML SDK. I'm using an XLSX file as a template.

该帖子中描述的方法可以很好地替代现有的一张新表，但是我想复制模板中的标题行（字符串值，格式等），而不是使用datatable中的标题行作为原始代码。

The method described in that post works fine to substitute a new sheet in for an existing one, but I want to copy the header row from the sheet in the template (string values, formatting, etc), instead of just using the header row from the datatable as the original code does.

I我尝试了下面的代码，但是XLSX文件最后没有标题行中的文本 - 格式化被复制，而不是文本。我查看了XML文件中的工作表，看起来对我来说（引用了仍然具有字符串定义的sharedStrings.xml文件）。来自 Open XML SDK 2.0生产力工具的反映代码显示了稍微奇怪的结果是：单元格似乎没有设置文本值：

I've tried the code below, but the XLSX file ends up with no text in the header row - the formatting is copied, just not the text. I've looked in the XML file for the sheet and it looks OK to me (referencing the sharedStrings.xml file, which still has the definition of the strings). The reflected code from the Open XML SDK 2.0 Productivity Tool shows a slightly odd result though: the cells don't appear to have a text value set:

cellValue1.Text = "";

即使XML说：

<x:c r="A1" s="4" t="s">

OpenXmlReader使用的主要代码如下：

The main code used by the OpenXmlReader is below:

while (reader.Read())
{
    if (reader.ElementType == typeof(SheetData))
    {
        if (reader.IsEndElement)
            continue;

        // Write sheet element
        writer.WriteStartElement(new SheetData());

        // copy header row from template
        reader.Read();
        do
        {
            if (reader.IsStartElement)
            {
                writer.WriteStartElement(reader);
                        }
            else if (reader.IsEndElement)
            {
                writer.WriteEndElement();
            }
            reader.Read();
        } while (!(reader.ElementType == typeof(Row) && reader.IsEndElement));
        writer.WriteEndElement();

        // Write data rows
        foreach (DataRow dataRow in resultsTable.Rows)
        {
            // Write row element
            Row r = new Row();
            writer.WriteStartElement(r);

            foreach (DataColumn dataCol in resultsTable.Columns)
            {
                Cell c = new Cell();
                c.DataType = CellValues.String;
                CellValue v = new CellValue(dataRow[dataCol].ToString());
                c.Append(v);

                // Write cell element
                writer.WriteElement(c);
            }

            // End row
            writer.WriteEndElement();
        }

        // End sheet
        writer.WriteEndElement();
    }
    else
    {
        if (reader.IsStartElement)
        {
            writer.WriteStartElement(reader);
        }
        else if (reader.IsEndElement)
        {
            writer.WriteEndElement();
        }
    }
}

推荐答案

线索是生产力工具显示生成的页面上的标题单元格的空白值，并且缺少模板的验证公式。这些都是文本，它不是使用 OpenXmlReader.Read（）和 OpenXmlReader.WriteStartElement的组合从模板表复制到新工作表（）。

The clue was that the Productivity Tool was showing blank values for the header cells on the generated sheet, and also that the validation formulae from the template were missing. These are both text, which wasn't copied from the template sheet to the new sheet using the combination of OpenXmlReader.Read() and OpenXmlReader.WriteStartElement().

当元素是 OpenXmlLeafTextElement 然后 OpenXmlReader.GetText（） 方法将返回文本 - 这对于单元格和公式中的文本值都有效。

When the element is an OpenXmlLeafTextElement then the OpenXmlReader.GetText() method will return the text - this works for both text values in cells and for formulae.

工作代码如下所示：

while (reader.Read())
{
    if (reader.ElementType == typeof(SheetData))
    {
        if (reader.IsEndElement)
            continue;

        // Write sheet element
        writer.WriteStartElement(new SheetData());

        // read first row from template and copy into the new sheet
        reader.Read();
        do
        {
          if (reader.IsStartElement)
            {
                writer.WriteStartElement(reader);

                // this bit is needed to get cell values
                if (reader.ElementType.IsSubclassOf(typeof(OpenXmlLeafTextElement)))
                {
                    writer.WriteString(reader.GetText());
                }
            }
            else if (reader.IsEndElement)
            {
                writer.WriteEndElement();
            }
            reader.Read();
        } while (!(reader.ElementType == typeof(Row) && reader.IsEndElement));
        writer.WriteEndElement();

        // Write data rows
        foreach (DataRow dataRow in resultsTable.Rows)
        {
            // Write row element
            Row r = new Row();
            writer.WriteStartElement(r);

            foreach (DataColumn dataCol in resultsTable.Columns)
            {
                Cell c = new Cell();
                c.DataType = CellValues.String;
                CellValue v = new CellValue(dataRow[dataCol].ToString());
                c.Append(v);

                // Write cell element
                writer.WriteElement(c);
            }

            // End row
            writer.WriteEndElement();
        }

        // End sheet
        writer.WriteEndElement();
    }
    else
    {
        if (reader.IsStartElement)
        {
            writer.WriteStartElement(reader);
            // this bit is needed to get formulae and that kind of thing
            if (reader.ElementType.IsSubclassOf(typeof(OpenXmlLeafTextElement)))
            {
                writer.WriteString(reader.GetText());
            }
        }
        else if (reader.IsEndElement)
        {
            writer.WriteEndElement();
        }
    }
}

这篇关于使用OpenXML和SAX模板的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！