Powershell: How to create an empty Excel worksheet-XML via XmlTextWriter?

Question

I am currently writing a Powershell code to import a very large DataTable into an Excel-File without having Excel installed on that machine. All is fine till now, except that I dont know how to create the worksheet.xml structure via an XmlTextWriter.

This is the final XML structure that I need to create via XmlTextWriter:

<?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" >
    <sheetData/>
</worksheet>

This is the Powershell code that I have till now for that part:

# create the settings:
$settings = [System.Xml.XmlWriterSettings]::new()
$settings.Encoding = [System.Text.Encoding]::UTF8
$settings.Async = $true

# create the XML: 
$sb = [System.Text.StringBuilder]::new()
$wr = [System.Xml.XmlTextWriter]::create($sb, $settings)
[void]$wr.WriteStartDocumentAsync($true)
[void]$wr.WriteStartElement("worksheet") #, "http://schemas.openxmlformats.org/spreadsheetml/2006/main")
[void]$wr.WriteStartAttribute("r", "xmlns", "http://schemas.openxmlformats.org/2006/relationships")
[void]$wr.WriteStartElementAsync($null, "sheetdata", $null)
[void]$wr.WriteEndElementAsync()
[void]$wr.WriteEndElementAsync()
$wr.Flush()

# show result:
$sb.ToString()

But the result is not the expected XML:

<?xml version="1.0" encoding="utf-16" standalone="yes"?><worksheet r:xmlns="" xmlns:r="http://schemas.openxmlformats.org/2006/relationships"><sheetdata /></worksheet>

1. The encoding is still UTF-16 instead of UTF-8
2. I am getting an extra "r:xmlns"-entry (?)
3. I am not able to create the "xmlns"-entry PLUS the "xmlns:r"-entry for the same element
4. not all calls are running/are available in Async-Mode.

Can someone please help?

Answer 1

This is the final code I came up with based on the input from wasmachien:

# create the settings:
$settings = [System.Xml.XmlWriterSettings]::new()
$settings.Encoding = [System.Text.Encoding]::UTF8
$settings.Indent = $true
$settings.Async  = $true

# the XmlWriter MUST be based on a stream to allow custom encodings:
$stream = [System.IO.MemoryStream]::new()
$writer = [System.Xml.XmlTextWriter]::create($stream, $settings)

# create the XML:
[void]$writer.WriteStartDocumentAsync($true)
[void]$writer.WriteStartElementAsync($null, "worksheet", "http://schemas.openxmlformats.org/spreadsheetml/2006/main")
[void]$writer.WriteAttributeStringAsync("xmlns", "r", $null, "http://schemas.openxmlformats.org/2006/relationships")
[void]$writer.WriteStartElementAsync($null, "sheetdata", $null)
[void]$writer.WriteEndElementAsync()
[void]$writer.WriteEndElementAsync()
[void]$writer.WriteEndDocumentAsync()
[void]$writer.Flush()

# show result:
cls
$utf8 = [System.Text.Encoding]::UTF8
$utf8.GetString($stream.ToArray())
$stream.Dispose()

Here is the output:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<worksheet xmlns:r="http://schemas.openxmlformats.org/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <sheetdata xmlns="" />
</worksheet>

Answer 2

xmlns and xmlns:r are not attributes, they are namespace declarations.

A plain xmlns sets the default namespace (every element in the document with no specific namespace gets this one.). Namespace declarations of the form xmlns:xxx mean that every element in your document that is prefixed with xxx is in this namespace.

The documentation here suggests how to write these as follows:

//Write an element (this one is the root).
writer.WriteStartElement("bookstore");

//Write the namespace declaration.
writer.WriteAttributeString("xmlns", "bk", null, "urn:samples");

writer.WriteStartElement("book");

The encoding is in UTF-16 because you are writing the XML to a StringBuilder (and C# uses UTF-16 strings under the hood). If you write it to a stream, UTF-8 should be enabled by default.

$stream = [System.IO.File]::create("output.xml")
$wr = [System.Xml.XmlTextWriter]::create([System.IO.StreamWriter]::new($stream), $settings)

Don't forget to close your stream at the end: $stream.close()