How to export Excel worksheet columns into a html file with Powershell

Question: How can I read an Excel worksheet , select some columns and generate a html file from the output using a Powershell script?

I’d like to use this script to regularly extract the refreshed information from the Excel columns , create a html file , which I’ll publish to the internet.

Answer: It is possible to generate a html file using a few line of Powershell Code. In Powershell there are many different ways of skinning a cat . The method I’m presenting below, is similar to scripts I use on a daily basis.

I’ve included some links further down the page to other similar scripts regarding Powershell, Excel and different outputs.

The example below, creates a connection to an Excel worksheet, based on certain variables.

A Powershell System DataTable is created.

The Datatable is then populated with the values .

The datatable is outputted to a html file.

You can then do all sorts of things , such as Send email from Powershell with attachment out or ftp or dop into a designated directory

#some variables
$tableName = "excelImport" ; 
$filepath = "C:\SPT SQL Server Instances.xls"; 
$SheetName = "Inventory"
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath
$outputfile="\sql_server_instances.html"
$outputfilefull = $basepath + $outputfile

#create object to open Excel workbook
$Excel = New-Object -ComObject Excel.Application 
$Workbook = $Excel.Workbooks.Open($filepath) 
$Worksheet = $Workbook.Worksheets.Item($Sheetname) 
$startRow = 1

#create System.DataTable
$dt = new-object "System.Data.DataTable"
[void]$dt.Columns.Add("Server", [System.Type]::GetType("System.String")) 
[void]$dt.Columns.Add("Instance", [System.Type]::GetType("System.String"))
[void]$dt.Columns.Add("Reference_name", [System.Type]::GetType("System.String"))
[void]$dt.Columns.Add("Owner", [System.Type]::GetType("System.String"))

Do { 
    $ColValues1 = $Worksheet.Cells.Item($startRow, 1).Value()
    $ColValues2 = $Worksheet.Cells.Item($startRow, 2).Value()
    $ColValues3 = $Worksheet.Cells.Item($startRow, 3).Value()
    $ColValues4 = $Worksheet.Cells.Item($startRow, 4).Value()
    $dt.Rows.Add($ColValues1,$ColValues2,$ColValues3,$ColValues4)
    $startRow++
    
    }
    While ($Worksheet.Cells.Item($startRow,1).Value() -ne $null) 
   
$Excel.Quit() 

#out data table to a html file
$dt| select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $style -body "SQL Server Instances" | Set-Content $outputfilefull
     

Read More on Powershell scripts

SQL Server – Send email using Powershell - SQL Server DBA

SQL Server - Powershell and SQL Error Logs

SQL Server DBA Top 10 automation tasks

SQL Server - Top 10 DBA mistakes