xmlpowershellasynchronousworksheetxmltextwriter

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

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?

Solution

  • 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>