Last active
March 3, 2023 16:51
-
-
Save abix-/274a6a09bb8ba5e6d1b6ea8423541d0d to your computer and use it in GitHub Desktop.
Converting between OneNote Tables and PowerShell PSObject
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function Get-1NotePage { | |
| <# | |
| .SYNOPSIS | |
| Finds the ID of a OneNote page | |
| .DESCRIPTION | |
| http://thebackend.info/powershell/2017/12/onenote-read-and-write-content-with-powershell/ | |
| .EXAMPLE | |
| Get-1NotePage -Name "PowerShell Test" | |
| #> | |
| [cmdletbinding()] | |
| [Alias('Get-OneNotePage')] | |
| param ( | |
| [string]$PageName, | |
| [string]$SectionName | |
| ) | |
| $OneNote = New-Object -ComObject OneNote.Application | |
| [xml]$Hierarchy = "" | |
| $OneNote.GetHierarchy("", [Microsoft.Office.InterOp.OneNote.HierarchyScope]::hsPages, [ref]$Hierarchy) | |
| if($SectionName) { | |
| # Get the Section ID | |
| $Section = $Hierarchy.notebooks.notebook.section | ?{$_.Name -eq "$SectionName"} | |
| # Get the Section Hierarchy | |
| [xml]$SectionHierarchy = "" | |
| $OneNote.GetHierarchy("$($Section.ID)", [Microsoft.Office.InterOp.OneNote.HierarchyScope]::hsPages, [ref]$SectionHierarchy) | |
| $Pages = $SectionHierarchy.section.page | |
| } else { | |
| $Pages = $Hierarchy.notebooks.notebook.section.page | |
| } | |
| Write-Output (($Pages | ?{$_.Name -eq "$PageName"}) | Select Name,ID) | |
| } | |
| function Get-1NoteContent { | |
| <# | |
| .SYNOPSIS | |
| Reads OneNote XML content into an array of PowerShell objects | |
| Supports OneNote Strings and OneNote Tables | |
| .DESCRIPTION | |
| Ideas: http://thebackend.info/powershell/2017/12/onenote-read-and-write-content-with-powershell/ | |
| .EXAMPLE | |
| Get-1NoteContent -PageName "PowerShell Test" | |
| Returns an array of PowerShell objects | |
| .EXAMPLE | |
| Get-1NoteContent -PageName "Service Status" -OutDefault | |
| Uses Out-Default to format output instead of Write-Output | |
| Useful for viewing results in console but unable to save output to variable | |
| #> | |
| [cmdletbinding()] | |
| [Alias('Out-OneNoteContent')] | |
| param ( | |
| [string]$PageName, | |
| [string]$SectionName, | |
| [parameter(ValueFromPipelineByPropertyName,ValueFromPipeline)][string]$PageID, | |
| [switch]$OutDefault | |
| ) | |
| begin { | |
| $OneNote = New-Object -ComObject OneNote.Application | |
| [xml]$Hierarchy = "" | |
| $OneNote.GetHierarchy("", [Microsoft.Office.InterOp.OneNote.HierarchyScope]::hsPages, [ref]$Hierarchy) | |
| } | |
| process { | |
| $results = @() | |
| # Get Page ID | |
| if($PageName) { | |
| $Pages = Get-1NotePage -PageName $PageName -SectionName $SectionName | |
| } elseif($PageID) { | |
| $Pages = [pscustomobject][ordered]@{ | |
| ID = $PageID | |
| } | |
| } | |
| foreach($PageID in $Pages.ID) { | |
| # Get the XML Page Content | |
| [xml]$PageContent = "" | |
| $OneNote.GetPageContent($PageID,[ref]$PageContent) | |
| # Each Note is an OE object | |
| foreach($OE in $PageContent.Page.Outline.OEChildren.OE) { | |
| # Get Note Data | |
| if($OE.T."#cdata-section") { | |
| $results += [pscustomobject][ordered]@{ | |
| Page = $PageContent.Page.Name | |
| Data = $OE.T."#cdata-section" | |
| } | |
| # Add Child OE - Bullets and Numbering | |
| $listResults = $() | |
| foreach($child in $OE.OEChildren.OE) { | |
| $listPrefix = $null | |
| if($child.List.Bullet) { | |
| $listPrefix = "-" | |
| } elseif($child.List.Number) { | |
| $listPrefix = $child.List.Number.Text | |
| } | |
| # Add List data with newline | |
| $listResults += "$listPrefix$($child.T."#cdata-section")`n" | |
| } | |
| # Add list results | |
| if($listResults.count -gt 0) { | |
| $results += [pscustomobject][ordered]@{ | |
| Page = $PageContent.Page.Name | |
| Data = $listResults | |
| } | |
| } | |
| } | |
| # Process Note Table | |
| if($OE.Table -ne $null) { | |
| $columns = @($OE.Table.Columns.Column) | |
| # Headers are in the first row | |
| $headers = @($OE.Table.Row[0].Cell.OEChildren.OE.T."#cdata-section") | |
| $tableResults = @() | |
| # Row | |
| foreach($row in $OE.Table.Row | Select-Object -Skip 1) { | |
| $rowObject = New-Object PSObject | |
| # Column | |
| for($i = 0; $i -lt $columns.count; $i++) { | |
| $cell = $row.Cell[$i] | |
| # Get columnn header. Remove HTML tags | |
| $header = $headers[$i] -replace '<[^>]+>','' | |
| # Get cell value. Remove HTML tags | |
| $value = $cell.OEChildren.OE.T."#cdata-section" -replace '<[^>]+>','' | |
| if(!$header) { | |
| $header = "Column$i" | |
| } | |
| # Add cell value to rowObject | |
| $rowObject | Add-Member -MemberType NoteProperty -Name $header -Value $value | |
| } | |
| $tableResults += $rowObject | |
| } | |
| $results += [pscustomobject][ordered]@{ | |
| Page = $PageContent.Page.Name | |
| Data = $tableResults | |
| } | |
| } | |
| } | |
| } | |
| if($OutDefault) { | |
| foreach($item in $results) { | |
| Write-Output $item.Data | Out-Default | |
| } | |
| } else { | |
| Write-Output $results | |
| } | |
| } | |
| } | |
| function Out-1Note { | |
| <# | |
| .SYNOPSIS | |
| Send a Note to a OneNote Page | |
| Note can be a string or an array of PSObjects | |
| .DESCRIPTION | |
| This functions uses COM-object to interact with OneNote. It requires OneNote 2013 and .NET 4.0 to function correctly | |
| By default new Notes are created in the 'Unfiled Notes' section | |
| PowerShell PSObjects are converted to OneNote XML Tables | |
| Original: http://asaconsultant.blogspot.com/2015/03/getting-things-done.html | |
| .EXAMPLE | |
| Out-1Note -Note "Remember to call Frank on 12/12 by lunch" | |
| Creates a new page in OneNote in the Unfiled Notes section. The PageName is the Note | |
| .EXAMPLE | |
| Out-1Note -Note "Remember to call Frank on 12/12 by lunch" -PageName "Don't forget" | |
| Creates a new page in OneNote in the Unfiled Notes section named "Dont' forget" | |
| .EXAMPLE | |
| Out-1Note -Note @(Get-Service | Select Name,Status) -PageName "PowerShell Test" -SectionName "Testing" | |
| Creates a new page in OneNote in the Testing section with a table of the Get-Service array | |
| .EXAMPLE | |
| Out-1Note -Note "I like turtles" -PageName "PowerShell Test" -SectionName "Testing" -Append | |
| Appends a Page with a Note. If multiple pages are found, the Note is appended to the LastModified page | |
| #> | |
| [cmdletbinding()] | |
| [Alias('Out-OneNote')] | |
| Param( | |
| [Parameter(Mandatory=$true)]$Note, | |
| [string]$PageName, | |
| [string]$SectionName = "Unfiled Notes", | |
| [switch]$Append | |
| ) | |
| # http://stackoverflow.com/questions/8186819/creating-new-one-note-2010-page-from-c-sharp | |
| $f = $MyInvocation.InvocationName | |
| Write-Verbose -Message "$f - START" | |
| $onenote = New-Object -ComObject OneNote.Application -ErrorAction SilentlyContinue | |
| if (!$onenote) { | |
| throw "Error - Unable to create OneNoe application object (COMobject error)" | |
| } | |
| $scope = [Microsoft.Office.Interop.OneNote.HierarchyScope]::hsPages | |
| [ref]$xml = "" | |
| $onenote.GetHierarchy($null, $scope, $xml) | |
| $schema = @{one="http://schemas.microsoft.com/office/onenote/2013/onenote"} | |
| Write-Verbose -Message "$f - Finding section in OneNote" | |
| $xpath = "//one:Notebook/one:Section" | |
| Select-Xml -Xml ([xml]$xml.Value) -Namespace $schema -XPath $xpath | foreach { | |
| if ($psitem.Node.Name -eq $SectionName) { | |
| $SectionID = $psitem.Node.ID | |
| $SectionPath = $psitem.Node.Path | |
| $SectionPages = $psitem.Node.Page | |
| } | |
| } | |
| if (!$SectionID) { | |
| throw "Unable to find Section with name $SectionName" | |
| } | |
| [ref]$PageXML = "" | |
| if(!$Append) { | |
| Write-Verbose -Message "$f - Creating new page" | |
| [ref]$newpageID ="" | |
| $onenote.CreateNewPage("$SectionID",[ref]$newpageID,[Microsoft.Office.Interop.OneNote.NewPageStyle]::npsBlankPageWithTitle) | |
| if (!$newpageID.Value) { | |
| throw "Unable to create new OneNote page" | |
| } | |
| Write-Verbose -Message "$f - Getting page content" | |
| $onenote.GetPageContent($newpageID.Value,[ref]$PageXML,[Microsoft.Office.Interop.OneNote.PageInfo]::piAll) | |
| if (!$PageXML) { | |
| throw "Unable to get OneNote page content" | |
| } | |
| Write-Verbose -Message "$f - Parsing new page xml content to find the title element" | |
| [Reflection.Assembly]::LoadWithPartialName("System.Xml.Linq") | Out-Null | |
| $xDoc = [System.Xml.Linq.XDocument]::Parse($PageXML.Value) | |
| $title = $xDoc.Descendants() | where Name -like "*}T" | |
| if (!$title) { | |
| throw "Unable to get title of new onenote page" | |
| } | |
| if(!$PageName) { | |
| $title.Value = "$Note" | |
| } elseif($PageName) { | |
| $title.Value = "$PageName" | |
| } | |
| Write-Verbose -Message "$f - Setting page title to $($title.Value)" | |
| $onenote.UpdatePageContent($xDoc.ToString()) | |
| } else { | |
| # Find the page in the section | |
| $Page = @($SectionPages | ?{$_.Name -eq $PageName} | Sort-Object LastModifiedTime | Select-Object -Last 1) | |
| if($Page.Count -ne 1) { | |
| Write-Output "Section:`"$SectionName`" with Page:`"$PageName`" not found. Abort" | |
| return | |
| } | |
| # Get the page content | |
| $onenote.GetPageContent($Page.ID,[ref]$PageXML,[Microsoft.Office.Interop.OneNote.PageInfo]::piAll) | |
| } | |
| if($Note -is [array]) { | |
| if(!$Append) { | |
| # Start Page | |
| $NewContent = @" | |
| <one:Page xmlns:one="http://schemas.microsoft.com/office/onenote/2013/onenote" ID="$($newpageID.Value)" > | |
| <one:Outline> | |
| <one:Position x="36.0" y="86.4000015258789" z="0" /> | |
| <one:Size width="117.001953125" height="40.28314971923828" /> | |
| <one:OEChildren> | |
| <one:OE> | |
| <one:Table bordersVisible="true"> | |
| <one:Columns> | |
| "@ | |
| } else { | |
| # Append Page | |
| $NewContent = @" | |
| <one:OE> | |
| <one:Table bordersVisible="true"> | |
| <one:Columns> | |
| "@ | |
| } | |
| # Columns | |
| $headers = @($Note[0].PSObject.Properties | Select-Object -ExpandProperty Name) | |
| for($i = 0; $i -lt $headers.count; $i++) { | |
| $width = 100/$headers.count | |
| $NewContent += "<one:Column index='$i' width='$width' />" | |
| } | |
| $NewContent += "</one:Columns>" | |
| # Column Headers | |
| $NewContent += "<one:Row>" | |
| for($i = 0; $i -lt $headers.count; $i++) { | |
| $NewContent += "<one:Cell><one:OEChildren><one:OE><one:T><![CDATA[$($headers[$i])]]></one:T></one:OE></one:OEChildren></one:Cell>" | |
| } | |
| $NewContent += "</one:Row>" | |
| # Rows | |
| foreach($item in $note) { | |
| $NewContent += "<one:Row>" | |
| for($i = 0; $i -lt $headers.count; $i++) { | |
| $NewContent += "<one:Cell><one:OEChildren><one:OE><one:T><![CDATA[$($item.($headers[$i]))]]></one:T></one:OE></one:OEChildren></one:Cell>" | |
| } | |
| $NewContent += "</one:Row>" | |
| } | |
| if(!$Append) { | |
| # End Page | |
| $NewContent += "</one:Table></one:OE></one:OEChildren></one:Outline></one:Page>" | |
| } else { | |
| # End Table | |
| $NewContent += "</one:Table></one:OE>" | |
| } | |
| } else { | |
| # New page with Note | |
| if(!$Append) { | |
| $NewContent = @" | |
| <one:Page xmlns:one="http://schemas.microsoft.com/office/onenote/2013/onenote" ID="$($newpageID.Value)" > | |
| <one:Outline> | |
| <one:Position x="36.0" y="86.4000015258789" z="0" /> | |
| <one:Size width="117.001953125" height="40.28314971923828" /> | |
| <one:OEChildren> | |
| <one:OE> | |
| <one:T><![CDATA[$Note]]></one:T> | |
| </one:OE> | |
| </one:OEChildren> | |
| </one:Outline> | |
| </one:Page> | |
| "@ | |
| } else { | |
| #New OE with Note | |
| $NewContent = @" | |
| <one:OE> | |
| <one:T><![CDATA[$Note]]></one:T> | |
| </one:OE> | |
| "@ | |
| } | |
| } | |
| if(!$Append) { | |
| Write-Host "$f - Inserting Note at $sectionPath" | |
| $onenote.UpdatePageContent($NewContent) | |
| } else { | |
| Write-Host "$f - Appending Note at $sectionPath" | |
| $lastOEChildrenIndex = $PageXML.Value.LastIndexOf("</one:OEChildren>") | |
| $updatedPageXML = $PageXML.Value.Insert($lastOEChildrenIndex,$NewContent) | |
| $onenote.UpdatePageContent($updatedPageXML) | |
| } | |
| } | |
| function Search-1Note { | |
| <# | |
| .SYNOPSIS | |
| Searches OneNote for a term | |
| .DESCRIPTION | |
| This functions uses COM-object to interact with OneNote. It requires OneNote 2013 and .NET 4.0 to function correctly | |
| Using -Categories will show Search categories similar to searching in the OneNote application | |
| Output can be piped to Get-1NoteContent | |
| .EXAMPLE | |
| Search-1Note "Remember to call" | |
| .EXAMPLE | |
| Search-1Note "Remember to call" | Get-1NoteContent | |
| #> | |
| [cmdletbinding()] | |
| [Alias('Search-OneNote')] | |
| param ( | |
| [string]$Search = "PowerShell", | |
| [switch]$Categories | |
| ) | |
| $OneNote = New-Object -ComObject OneNote.Application | |
| [xml]$Hierarchy = "" | |
| $OneNote.GetHierarchy("", [Microsoft.Office.InterOp.OneNote.HierarchyScope]::hsPages, [ref]$Hierarchy) | |
| # Search OneNote | |
| [xml]$searchHierarchy = "" | |
| $OneNote.FindPages("","$Search",[ref]$searchHierarchy) | |
| $notebooks = @() | |
| if($searchHierarchy.Notebooks.UnfiledNotes) { | |
| $notebooks += $searchHierarchy.Notebooks.UnfiledNotes | |
| } | |
| if($searchHierarchy.Notebooks.Notebook) { | |
| $notebooks += $searchHierarchy.Notebooks.Notebook | |
| } | |
| $results = @() | |
| foreach($notebook in $notebooks) { | |
| foreach($section in $notebook.section) { | |
| foreach($page in $section.page) { | |
| $PagePath = "$($notebook.name) > $($section.Name)" | |
| $results += [pscustomobject][ordered]@{ | |
| PageName = $page.name | |
| Path = $PagePath | |
| LastModifiedTime = $page.lastModifiedTime | |
| PageID = $page.ID | |
| } | |
| } | |
| } | |
| foreach($sectionGroup in $notebook.SectionGroup) { | |
| foreach($section in $sectionGroup.Section) { | |
| foreach($page in $section.page) { | |
| $PagePath = "$($notebook.name) > $($sectionGroup.Name) > $($section.Name)" | |
| $results += [pscustomobject][ordered]@{ | |
| PageName = $page.name | |
| Path = $PagePath | |
| LastModifiedTime = $page.lastModifiedTime | |
| PageID = $page.ID | |
| } | |
| } | |
| } | |
| } | |
| } | |
| $results = $results | Sort-Object -Property LastModifiedTime -Descending | |
| if($Categories) { | |
| # Filter the results to In Title, On Page, and Recycle Bin | |
| # These categories are the similar to the OneNote application search results | |
| $recycleResults = $results | ?{$_.Path -like "*OneNote_RecycleBin*"} | |
| $notRecycleResults = $results | ?{$_.Path -notlike "*OneNote_RecycleBin*"} | |
| # In Title Results are not in a Recycle Bin | |
| $inTitleResults = $notRecycleResults | ?{$_.PageName -like "*$Search*"} | |
| $notInTitleResults = $notRecycleResults | ?{$_.PageName -notlike "*$Search*"} | |
| # On Page Results are not in Title or in Recycle Bin | |
| $onPageResults = $notInTitleResults | ?{$_.Path -notlike "*OneNote_RecycleBin*"} | |
| Write-Output "In Title: `"$Search`" $($inTitleResults.count)" | |
| Write-Output $inTitleResults | Select-Object PageName,Path | Out-Default | |
| Write-Output "On Page: `"$Search`" $($onPageResults.count)" | |
| Write-Output $onPageResults | Select-Object PageName,Path | Out-Default | |
| Write-Output "Recycle Bins: `"$Search`" $($recycleResults.count)" | |
| Write-Output $recycleResults | Select-Object PageName,Path | Out-Default | |
| } else { | |
| Write-Output $results | |
| } | |
| } | |
| Out-1Note -Note "Remember to call Frank on 12/12 by lunch" | |
| Out-1Note -Note "Remember to call Frank on 12/12 by lunch" -PageName "Never forget" | |
| Out-1Note -Note @(Get-Service | Select Name,Status) -PageName "Never forget" -SectionName "Testing" | |
| Out-1Note -Note "I like turtles" -PageName "Never forget" -SectionName "Testing" -Append | |
| Get-1NoteContent -PageName "Never forget" | |
| Get-1NoteContent -PageName "Never forget" -SectionName "Testing" | |
| Get-1NoteContent -PageName "Never forget" -SectionName "Testing" -OutDefault | |
| Search-1Note "Remember to call" -Categories | |
| Search-1Note "Remember to call" | Get-1NoteContent |
Nice work but just its seems that one little bug slipping through......if you have a Onenote page like this:
Topic1:
- Line1
- Line2
Topic2:
- Line1
- Line2
Topic3:
- Line1
- Line2
If there is a way to capture the list items that would be awesome!
After some testing the items are an array of OE elements nested inside another OE.
I added a foreach loop to extract the item into an array separated by newlines.
This works for Bullets and Numbering.
Let me know if you have any issues.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Nice work but just its seems that one little bug slipping through......if you have a Onenote page like this:
Topic1:
Topic2:
Topic3:
In other words, normal line ending in colon, followed by normal bullet (or numbered) list, results in only the top-level items returned as in this example shown below:
Get-1NoteContent -PageName 'Testpage'and if using
Get-1NoteContent -PageName 'Testpage' -OutDefaultIf there is a way to capture the list items that would be awesome!