Powershell Script – How to export Excel worksheet columns into a html file

12 January,2016 by Jack Vamvas

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

 

 

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Powershell Script – How to export Excel worksheet columns into a html file


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer