Using a template with OpenXML and SAX

Question

I am creating a large XLSX file from a datatable using the SAX method suggested in Parsing and reading large Excel files using the Open XML SDK . I am using an xlsx file as a template.

The method described in this post works fine to replace a new sheet with an existing one, but I want to copy the title bar from the sheet in the template (string values, formatting, etc.) instead of just using the title bar from datatable, like this makes the source code.

I tried the code below, but the XLSX file does not contain text in the title bar - formatting is copied, not text. I looked at the XML file for the worksheet and it looks good to me (link to the sharedStrings.xml file, which still contains the string definition). The reflected code from the Open XML SDK 2.0 Productivity Tool shows a slightly strange result: the cells have no text value:

cellValue1.Text = ""; 

although the XML says:

 <x:cr="A1" s="4" t="s"> 

The main code used by OpenXmlReader is given 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(); } } } 

Answer 1

It was indicated that the Productivity Tool showed empty values ​​for the header cells on the generated sheet, and there were no formulas for validating the template. These are both texts that were not copied from the template sheet to the new sheet using a combination of OpenXmlReader.Read() and OpenXmlReader.WriteStartElement() .

When the element is OpenXmlLeafTextElement , then OpenXmlReader.GetText() returns text - this works for both text values ​​in cells and formulas.

The working code is shown below:

 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(); } } }