Rich output from PowerShell scripts in SpreadCommander

Disclaimer: I am author of SpreadCommander

SpreadCommander is an office program with tools for data analysis. This is the first article in serie that describes usage of SpreadCommander.

Goal of the console in SpreadCommander is to allow to build office documents from PowerShell script. Output shall allow different text formatting - bold, italic, underline text, back and fore colors, styles, images, tables and more. It shall be possible to use script's output to generate office documents.

Console in SpreadCommander has few parts - Book (general output from console), Spreadsheet (output tables), Data (also output tables), Heap (view different files in project).

Rich output is implemented with new PowerShell cmdlets. In addition to standard cmdlets such as Write-Host, SpreadCommander provides new cmdlets that allow to customize output.

Write-Text

Outputs text into console similar to Write-Host cmdlet. Allows to specify additional parameters like character properties, paragraph styles and others.

Add-BookParagraphStyle 'Header1' -FontName:'Segoe UI' -FontSize:24 -Bold `
    -Alignment:Center -OutlineLevel:1 -SpacingBefore:120 `
    -SpacingAfter:120 -Replace;
Add-BookParagraphStyle 'Text' -FontName:'Times New Roman' -FontSize:12 -Alignment:Justify -SpacingBefore:20 -SpacingAfter:20 -Replace;

Write-Text -ParagraphStyle:'Header1' 'Introduction to Spread Commander';
Write-Text -ParagraphStyle:'Text' 'Hello, Spread Commander!';

Write-Html

Outputs HTML-formatted string into console. It supports large subset of HTML tags, however it is not web browser and cannot output very complex HTML.

Write-Html -ParagraphStyle:'Text' @'
<p align=justify><b>SpreadCommander</b> allows to output 
<i>HTML-formatted text</i>.</p>
'@;

Write-Markdown

Outputs markdown-formatted text into console.

Write-Markdown -ParagraphStyle:'Text' @'
*HTML-formatted text* can be encoded in **Markdown** style.
'@;

Write-Latex

Converts Latex-formatted text into image and outputs it into console.

Write-Latex @(
    'B''=-\nabla \times E', 
    'E''=\nabla \times B - 4\pi j',
    'e^{ix} = \cos{x} + i \sin{x}');

Write-SyntaxText

Outputs text with highlighted syntax.

Write-SyntaxText -Syntax:'PowerShell' "Write-Host 'Hello, Wolrd!'"

Write-ErrorMessage

Outputs text formatted as error. Can be used to output non-terminating errors. SpreadCommander sets ErrorActionPreference to Stop, to make errors thrown in its cmdlets to be terminating, so cmdlet Write-ErrorMessage is recommended to output error/warning messages.

Write-ErrorMessage 'Error happened.';

Write-Image

Outputs image into console.

Write-Image '~\Images\flag-Olympic-Games.jpg' -ScaleX:0.4 -ScaleY:0.4;

~ in path is project's root path.

Write-Content

Outputs content of existing file into Book. MS Word, RTF, TXT, HTML, markdown (.md, .markdown, .mdown) files.

Write-DataTable

Outputs content of list or DataTable into Book. Internally list is exported into spreadsheet, formatted, and then copied into Book. Output to Spreadsheet is also possible and will be demostrated in future articles.

$olympic_medals = @(
    [PSCustomObject]@{'Country' = 'USA';         'Gold' = 39; 'Silver' = 41; 'Bronze' = 33; 'Total' = 113};
    [PSCustomObject]@{'Country' = 'China';       'Gold' = 38; 'Silver' = 32; 'Bronze' = 18; 'Total' = 88};
    [PSCustomObject]@{'Country' = 'Japan';       'Gold' = 27; 'Silver' = 14; 'Bronze' = 17; 'Total' = 58};
    [PSCustomObject]@{'Country' = 'Britain';     'Gold' = 22; 'Silver' = 21; 'Bronze' = 22; 'Total' = 65};
    [PSCustomObject]@{'Country' = 'ROC';         'Gold' = 20; 'Silver' = 28; 'Bronze' = 23; 'Total' = 71};
    [PSCustomObject]@{'Country' = 'Australia';   'Gold' = 17; 'Silver' = 7;  'Bronze' = 22; 'Total' = 46};
    [PSCustomObject]@{'Country' = 'Netherlands'; 'Gold' = 10; 'Silver' = 12; 'Bronze' = 14; 'Total' = 36};
    [PSCustomObject]@{'Country' = 'France';      'Gold' = 10; 'Silver' = 12; 'Bronze' = 11; 'Total' = 33};
    [PSCustomObject]@{'Country' = 'Germany';     'Gold' = 10; 'Silver' = 11; 'Bronze' = 16; 'Total' = 37};
    [PSCustomObject]@{'Country' = 'Italy';       'Gold' = 10; 'Silver' = 10; 'Bronze' = 20; 'Total' = 40}
);

$olympic_medals |
    Write-DataTable -TableStyle:Medium20 -Formatting:"format column 'Gold' with ColorScale='Blue,Red', ForeColor='White'";

Write-SpreadTable

Outputs existing spreadsheet table into Book. Creating spreadsheet from PowerShell will be described in one of future articles.

Write-Chart

Generates and outputs chart. SpreadCommander supports multiple chart types - Bar, Line, Spline, Pie, Funnel, Area, RangeArea, Gannt, Polal, Radar and many others.

$olympic_medals |
    New-Chart Bar 'Country' @('Gold', 'Silver', 'Bronze') |
    Add-ChartTitle 'Olympic Medals' -Font:'Tahoma,18,Italic' |
    Set-ChartLegend -ShadowColor:Gray -AlignmentHorizontal:Center `
        -AlignmentVertical:BottomOutside -Direction:LeftToRight |
    Write-Chart -Width:2000 -Height:1600;

Write-Map

Generates and outputs geo map.

$tokyo      = [PSCustomObject] @{ Latitude = 35.6895;  Longitude = 139.6917;  Name = 'Tokyo' };
$beijing    = [PSCustomObject] @{ Latitude = 39.904;   Longitude = 116.4075;  Name = 'Beijing' };
$paris      = [PSCustomObject] @{ Latitude = 48.8566;  Longitude = 2.3522;    Name = 'Paris' };
$milan      = [PSCustomObject] @{ Latitude = 45.4669;  Longitude = 9.19;      Name = 'Milan' };
$losAngeles = [PSCustomObject] @{ Latitude = 34.05;    Longitude = -118.25;   Name = 'Los-Angeles' };
$brisbane   = [PSCustomObject] @{ Latitude = -27.4677; Longitude = 153.028;   Name = 'Brisbane' };

New-Map -BackColor:White | 
    Add-MapLayerImage Bing Road | 
    Add-MapLayerVectorItems |

    Add-MapItem Pushpin @($tokyo.Latitude,      $tokyo.Longitude)      $tokyo.Name |
    Add-MapItem Pushpin @($beijing.Latitude,    $beijing.Longitude)    $beijing.Name |
    Add-MapItem Pushpin @($paris.Latitude,      $paris.Longitude)      $paris.Name |
    Add-MapItem Pushpin @($milan.Latitude,      $milan.Longitude)      $milan.Name |
    Add-MapItem Pushpin @($losAngeles.Latitude, $losAngeles.Longitude) $losAngeles.Name |
    Add-MapItem Pushpin @($brisbane.Latitude,   $brisbane.Longitude)   $brisbane.Name |

    Add-MapItem Line @($tokyo.Latitude,      $tokyo.Longitude)      @($beijing.Latitude,    $beijing.Longitude)    -StrokeColor:Gold -StrokeWidth:5 -Geodesic | 
    Add-MapItem Line @($beijing.Latitude,    $beijing.Longitude)    @($paris.Latitude,      $paris.Longitude)      -StrokeColor:Gold -StrokeWidth:5 -Geodesic | 
    Add-MapItem Line @($paris.Latitude,      $paris.Longitude)      @($milan.Latitude,      $milan.Longitude)      -StrokeColor:Gold -StrokeWidth:5 -Geodesic | 
    Add-MapItem Line @($milan.Latitude,      $milan.Longitude)      @($losAngeles.Latitude, $losAngeles.Longitude) -StrokeColor:Gold -StrokeWidth:5 -Geodesic | 
    Add-MapItem Line @($losAngeles.Latitude, $losAngeles.Longitude) @($brisbane.Latitude,   $brisbane.Longitude)   -StrokeColor:Gold -StrokeWidth:5 -Geodesic | 

    Write-Map -CenterPoint:@(0.0, 0.0) -Width:2000 -Height:1600 -ZoomLevel:1;

Save-Book

Saves Book to file.

Save-Book '~\Output\Book.docx';

Output to Spreadsheet, Charts, Maps, using SQL will be reviewed in future articles.