Populating a Word Document from a Sharepoint List

I thought this would be simpler since Sharepoint and Word are so closely related. A mail merge based on list data perhaps? Maybe it’s possible with an office data connection file but I couldn’t figure it out.

Well, it was useful to me so here’s how I did it with Powershell and Word automation. The example here is a list of standards and the output will be a list of those standards formatted on one Word document. The soap call from powershell comes from somewhere on the net with an added soapaction header. The list has been created with folder items so it needs to list the items recursively – this wouldn’t apply to a normal list. You’ll figure it out as you start looking at the xml attributes. Best way to make progress with these sharepoint soap calls is to use Wireshark in combination with Stramit Caml Viewer.

Here we go…

function Execute-SOAPRequest 
( 
        [Xml]    $SOAPRequest,
        [String] $SOAPAction,
        [String] $URL 
) 
{ 
        write-host "Sending SOAP Request To Server: $URL" 
        $soapWebRequest = [System.Net.WebRequest]::Create($URL) 
        $soapWebRequest.Headers.Add("SOAPAction","`"" + $SOAPAction + "`"") 

        $soapWebRequest.ContentType = "text/xml;charset=`"utf-8`"" 
        $soapWebRequest.Accept      = "text/xml" 
        $soapWebRequest.Method      = "POST" 
        
        write-host "Authenticating"
        $soapWebRequest.Credentials = [System.Net.CredentialCache]::DefaultCredentials
        if($soapWebRequest.Proxy -ne $null)     {
            $soapWebRequest.Proxy.Credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials
        }
    
        write-host "Initiating Send." 
        $requestStream = $soapWebRequest.GetRequestStream() 
        $SOAPRequest.Save($requestStream) 
        $requestStream.Close() 
        
        write-host "Send Complete, Waiting For Response." 
        $resp = $soapWebRequest.GetResponse() 
        $responseStream = $resp.GetResponseStream() 
        $soapReader = [System.IO.StreamReader]($responseStream) 
        $ReturnXml = [Xml] $soapReader.ReadToEnd() 
        $responseStream.Close() 
        
        write-host "Response Received." 

        return $ReturnXml 
} 

function Add-Standard
(
        [String] $Standard,
        [String] $Justification,
        $Doc
)
{
$p = $Doc.Paragraphs.Add()
$p.Range.Text = $Standard
$p.Format.Style = "Heading 1"
$p.Range.InsertParagraphAfter()

$p = $Doc.Paragraphs.Add()
$p.Range.Text = $Justification
$p.Range.InsertParagraphAfter()
}

$d4 = [xml] @"
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope  xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" 
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
            <listName>{CF6E48BA-650A-489E-83AB-8EF1E545388A}</listName>
            <queryOptions>
                <QueryOptions>
                    <ViewAttributes Scope="Recursive"/>
                    <IncludeMandatoryColumns>False</IncludeMandatoryColumns>
                </QueryOptions>
            </queryOptions>
        </GetListItems>
    </soap:Body>
</soap:Envelope>
"@


# create a document from list data!
$w = new-object -com Word.Application
$w.Visible = $true
$d = $w.Documents.Add()

Execute-SOAPRequest -SOAPRequest $d4 -SOAPAction "http://schemas.microsoft.com/sharepoint/soap/GetListItems" -URL "http://<some sharepoint site>/sites/CTO/_vti_bin/lists.asmx" |
foreach {$_.Envelope.Body.GetListItemsResponse.GetListItemsResult.listitems.data.row |
         foreach {Add-Standard -Standard $_.ows_Standard -Justification $_.ows_Justification  -d $d }
}

$d.SaveAs([ref]"c:\temp\standards.docx")
$d.Close()
$w.Quit()