Flip HTML tables into Excel workbooks with PSParseHTML and PSWriteOffice

In the end I find yourself with helpful information trapped in an HTML desk. It might come from a vendor portal, a monitoring web page, an exported report, an outdated intranet, or a dashboard that by no means acquired an API.

The PowerShell model of the requirement is normally brief:

Get the desk from HTML and push it into Excel.

That’s nonetheless the objective. What modified is the place the work occurs. The parser and workbook code now reside in reusable .NET libraries, whereas the PowerShell modules present instructions on high.

Consequently, the identical implementation works for 2 audiences:

• C# builders can use the .NET libraries immediately.
• PowerShell customers can hold utilizing pipeline-friendly instructions.

I can repair the desk parser or workbook author as soon as and each paths profit.

Earlier than you begin

Use PowerShell 7 and set up the present public modules used for this text:

Set up-Module PSWriteOffice -Scope CurrentUser -Power
Import-Module PSWriteOffice
Set up-Module PSParseHTML -Scope CurrentUser -Power
Import-Module PSParseHTML

Run the examples from a folder the place you may write the generated information. The pattern HTML is included beneath, so you may strive the whole path earlier than pointing it at an actual web page.

The items

4 mission names seem within the examples. Right here is the sensible break up earlier than we get to the code.

HtmlTinkerX is the .NET HTML engine. It parses tables, together with headers, row and column spans, captions, metadata, hyperlink textual content, and non-obligatory hyperlink URLs, and returns reusable desk fashions.

PSParseHTML wraps HtmlTinkerX for PowerShell. Its ConvertFrom-HtmlTable command means a PowerShell script doesn’t want customized C# simply to learn a desk.

OfficeIMO.Excel creates and reads .xlsx information with out Excel COM automation. It owns worksheets, tables, information tables, datasets, charts, formatting, and workbook construction.

PSWriteOffice exposes that Excel engine to PowerShell by way of instructions comparable to Export-OfficeExcel, Get-OfficeExcel, Add-OfficeExcelChart, and Shut-OfficeExcel.

The circulate seems to be like this:

HTML web page or file
  -> HtmlTinkerX or PSParseHTML
  -> DataTable / DataSet / row objects
  -> OfficeIMO.Excel or PSWriteOffice
  -> native .xlsx workbook

This workflow extracts desk information; it doesn’t attempt to reproduce the webpage in Excel. The end result stays rows and columns that Excel can filter, chart, format, and edit.

Why I hold parsing and Excel separate

Placing HTML import immediately into OfficeIMO.Excel would make this one demo shorter, however it could additionally make the Excel library chargeable for net scraping, CSS, JavaScript, and each different format any individual would possibly wish to import subsequent.

I need OfficeIMO to work with regular .NET information shapes comparable to DataTable, DataSet, object sequences, and information readers. HtmlTinkerX has a special job: flip HTML into structured info. Neither library must know in regards to the different.

The connection occurs within the calling code. A C# service joins HtmlTinkerX with OfficeIMO.Excel; a PowerShell script pipes PSParseHTML into PSWriteOffice.

If ImportExcel already suits

The parser doesn’t require a specific workbook module. Its output is bizarre row objects, DataTable, or DataSet, so an present ImportExcel workflow can keep precisely the place it’s helpful.

This text makes use of PSWriteOffice as a result of the later examples add workbook construction, charts, and read-back, and the broader sequence additionally creates different Workplace codecs. A easy HTML-table export doesn’t require a brand new Excel device. The PSWriteOffice comparability web page exhibits command shapes, mission scope, and validated benchmark eventualities when you might want to evaluate them.

For C# builders

Save the pattern as service-status.html in your working listing. Each examples use it. The hyperlinks are fictional proof URLs, so exchange them when adapting the script.

<!doctype html>
<html lang="en">
<head><meta charset="utf-8"><title>Service standing</title></head>
<physique>
<desk id="companies">
  <thead><tr><th>Service</th><th>Well being</th><th>Incidents</th><th>Proprietor</th><th>Proof</th></tr></thead>
  <tbody>
    <tr><td>Identification Sync</td><td>98</td><td>1</td><td>Platform</td><td><a href="https://instance.com/id">Particulars</a></td></tr>
    <tr><td>Distant Entry</td><td>76</td><td>7</td><td>Safety</td><td><a href="https://instance.com/entry">Particulars</a></td></tr>
    <tr><td>Backup</td><td>92</td><td>2</td><td>Operations</td><td><a href="https://instance.com/backup">Particulars</a></td></tr>
    <tr><td>Monitoring</td><td>95</td><td>1</td><td>Operations</td><td><a href="https://instance.com/monitoring">Particulars</a></td></tr>
  </tbody>
</desk>
</physique>
</html>

In a .NET console mission, add HtmlTinkerX 3.0.1 and OfficeIMO.Excel 3.4.3 with dotnet add bundle. HtmlTinkerX converts the desk right into a DataTable, and OfficeIMO.Excel writes the workbook.

utilizing HtmlTinkerX;
utilizing OfficeIMO.Excel;
utilizing System;
utilizing System.Collections.Generic;
utilizing System.Knowledge;
utilizing System.IO;
utilizing System.Linq;

string html = File.ReadAllText("service-status.html");

Checklist<HtmlTableResult> htmlTables = HtmlParser.ParseTablesWithAngleSharpDetailed(
    html,
    replaceContent: null,
    replaceHeaders: null,
    allProperties: false,
    skipFooter: false,
    cleanHeaders: true,
    emptyValuePlaceholder: null,
    cellTextFormat: HtmlCellTextFormat.Compact,
    includeLinkUrls: true);

HtmlTableResult serviceTable = htmlTables.Single(desk =>
    string.Equals(desk.Metadata.Id, "companies", StringComparison.OrdinalIgnoreCase));
DataTable companies = serviceTable.ToDataTable("Providers", inferTypes: true);

utilizing var workbook = ExcelDocument.Create("ServiceStatus.xlsx");
ExcelSheet sheet = workbook.AddWorksheet("Providers");

sheet.InsertDataTableAsTable(
    companies,
    tableName: "Providers",
    model: ExcelTableStyle.TableStyleMedium2,
    includeAutoFilter: true);

sheet.AutoFitColumnsFor(Enumerable.Vary(1, companies.Columns.Depend));

workbook.Save();

If the HTML comprises a number of helpful tables, convert them to a DataSet and let OfficeIMO.Excel create one worksheet per desk.

DataSet dataSet = htmlTables.ToDataSet("ServiceStatus", inferTypes: true);

utilizing var workbook = ExcelDocument.Create("ServiceStatus.xlsx");

workbook.InsertDataSet(
    dataSet,
    createTables: true,
    tableStyle: ExcelTableStyle.TableStyleMedium2,
    includeHeaders: true,
    includeAutoFilter: true,
    autoFit: true);

workbook.Save();

The 2 libraries meet at an ordinary DataTable; PowerShell isn’t concerned on this model.

For PowerShell customers

In PowerShell, the identical handoff turns into a pipeline.

ConvertFrom-HtmlTable `
    -Path .service-status.html `
    -TableId 'companies' `
    -AsDataTable `
    -IncludeLinkUrls `
    -InferTypes |
    Export-OfficeExcel `
        -Path .ServiceStatus.xlsx `
        -WorksheetName 'Providers' `
        -TableName 'Providers' `
        -AutoFit `
        -FreezeTopRow `
        -BoldTopRow

The command reads one HTML desk, returns a DataTable, and writes a local Excel desk. The .xlsx comprises rows, columns, headers, filters, and values moderately than a screenshot of the web page.

For all tables within the HTML file, use -AsDataSet.

$tables = ConvertFrom-HtmlTable `
    -Path .service-status.html `
    -AsDataSet `
    -IncludeLinkUrls `
    -InferTypes

$tables | Export-OfficeExcel `
    -Path .ServiceStatus.xlsx `
    -AutoFit `
    -FreezeTopRow `
    -BoldTopRow

The PowerShell instructions are shorter, however they use the identical parser and workbook engine because the C# instance.

Add Excel conduct after import

As soon as the desk is in Excel, I can add workbook conduct moderately than stopping on the export.

$workbook = Get-OfficeExcel -Path .ServiceStatus.xlsx
ExcelSheet -Doc $workbook 'Providers' {
    ExcelColumn -ColumnName 'A' -Width 22
    ExcelColumn -ColumnName 'B' -Width 12
    ExcelColumn -ColumnName 'C' -Width 14
    ExcelColumn -ColumnName 'D' -Width 18
    ExcelColumn -ColumnName 'E' -Width 14
    ExcelColumn -ColumnName 'F' -Width 45
}
Add-OfficeExcelTableOfContents `
    -Doc $workbook `
    -SheetName 'Index' `
    -AddBackLinks

Add-OfficeExcelChart `
    -Doc $workbook `
    -Sheet 'Providers' `
    -Vary 'A1:B5' `
    -Row 8 `
    -Column 1 `
    -Sort BarClustered `
    -Title 'Service well being rating'

$workbook | Save-OfficeExcel
$workbook | Shut-OfficeExcel

The Shut-OfficeExcel command is intentional. I are not looking for a public PowerShell instance to require .Dispose() when the module can expose a traditional Shut-* command and deal with the article lifetime itself.

When to make use of this

I take advantage of this method when the desk already comprises the information I want:

• service standing pages
• product comparability tables
• vendor export pages
• documentation tables
• launch matrices
• monitoring summaries
• inner HTML reviews

I select one other device when the job is:

• rendering a complete webpage into Excel
• preserving CSS structure
• working JavaScript
• display screen scraping a browser-only utility
• changing an API when a correct API exists

If the location has a helpful API, use it. When the one helpful interface is an HTML desk, this pipeline provides me a repeatable extraction with out pretending to be a browser.

Why the modules modified

The older PowerShell-only modules had been handy, however parsing, transformation, workbook creation, formatting, and file dealing with typically ended up in the identical script module. That made enhancements tougher to reuse from C# and tougher to check independently.

The present break up is:

• .NET libraries personal reusable mechanics.
• PowerShell modules expose these mechanics in a pleasant manner.
• C# customers don’t want PowerShell.
• PowerShell customers don’t must care that C# is beneath.

That lets me enhance the HTML parser with out educating it about Excel, and enhance the workbook author with out educating it in regards to the net.

For this particular workflow, the clear break up is:

• HtmlTinkerX improves desk parsing.
• PSParseHTML improves PowerShell choice and output choices.
• OfficeIMO.Excel improves workbook and tabular-data help.
• PSWriteOffice improves the reporting expertise.

For the reader, the workflow stays easy: choose a desk and write a local workbook. Beneath, C# and PowerShell share the identical mechanics with out Excel COM or a browser runtime.