Convert Excel to HTML using PowerShell

Conversion from Excel to HTML can be done by combination of Import-Excel from PowerShell module ImportExcel and ConvertTo-Html cmdlet. Lets use Excel file generated in previous post. Conversion is done by one-liner:

Import-Excel .\Services.xlsx | ConvertTo-Html

Output is:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>HTML TABLE</title>
</head><body>
<table>
<colgroup><col/><col/><col/></colgroup>
<tr><th>Name</th><th>Status</th><th>StartType</th></tr>
<tr><td>AarSvc_dc323</td><td>Running</td><td>Manual</td></tr>
<tr><td>AdobeARMservice</td><td>Running</td><td>Automatic</td></tr>
<tr><td>AESMService</td><td>Running</td><td>Automatic</td></tr>
<tr><td>AJRouter</td><td>Stopped</td><td>Manual</td></tr>
<tr><td>ALG</td><td>Stopped</td><td>Manual</td></tr>
<tr><td>AMD Crash Defender Service</td><td>Running</td><td>Automatic</td></tr>
<tr><td>AMD External Events Utility</td><td>Running</td><td>Automatic</td></tr>
<tr><td>AppHostSvc</td><td>Running</td><td>Automatic</td></tr>
<tr><td>AppIDSvc</td><td>Stopped</td><td>Manual</td></tr>
<tr><td>Appinfo</td><td>Running</td><td>Manual</td></tr>
</table>
</body></html>

If we want to save HTML directly into file, Out-File cmdlet is added:

Import-Excel .\Services.xlsx | ConvertTo-Html | Out-File Services.html -Encoding utf8

Convert to Base64 encoded string using PowerShell one-liner

PowerShell doesn’t provide built-in cmdlets for conversion from string to Base64 encoded string. This task can be done by combination of .NET methods Encoding.GetBytes and Convert.ToBase64String. PowerShell one-liner is then simple:

[System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes("MyTestInput"))

We can even omit System namespace to simplify one-liner:

[Convert]::ToBase64String([Text.Encoding]::UTF8.GetBytes("MyTestInput"))

Output is Base64 encoded string:

TXlUZXN0SW5wdXQ=

More about using .NET in PowerShell can be found in documentation.

Create infrastructure for Azure WebJob using Bicep

In my previous posts I wrote about creating infrastructure for Azure WebJob using PowerShell or Terraform. Most recent approach for creating Azure resources is Azure Bicep. Bicep is a Domain Specific Language (DSL) for deploying Azure resources declaratively. Bicep code is transpiled to standard ARM Template. Bicep file for creating infrastructure for Azure WebJob is:

var baseName = resourceGroup().name
var baseNameLower = toLower(baseName)

resource appServicePlan 'Microsoft.Web/serverfarms@2018-02-01' = {
  name: baseName
  location: resourceGroup().location
  sku: {
    name: 'B1'
    capacity: 1
  }
}

resource appService 'Microsoft.Web/sites@2018-11-01' = {
  name: baseNameLower
  location: resourceGroup().location
  properties: {
    serverFarmId: appServicePlan.id
    clientAffinityEnabled: false
  }
}

resource config 'Microsoft.Web/sites/config@2018-11-01' = {
  parent: appService
  name: 'web'
  properties: {
    use32BitWorkerProcess: false
    alwaysOn: true
  }
}

resource appSettings 'Microsoft.Web/sites/config@2018-11-01' = {
  parent: appService
  name: 'appsettings'
  properties: {
    APPINSIGHTS_INSTRUMENTATIONKEY: appInsights.properties.InstrumentationKey
  }
}

resource connectionStrings 'Microsoft.Web/sites/config@2018-11-01' = {
  parent: appService
  name: 'connectionstrings'
  properties: {
    AzureWebJobsDashboard: {
      value: 'DefaultEndpointsProtocol=https;AccountName=${baseNameLower};AccountKey=${listKeys(storageAccounts.id, '2019-06-01').keys[0].value};EndpointSuffix=${environment().suffixes.storage}'
      type: 'Custom'
    }
    AzureWebJobsStorage: {
      value: 'DefaultEndpointsProtocol=https;AccountName=${baseNameLower};AccountKey=${listKeys(storageAccounts.id, '2019-06-01').keys[0].value};EndpointSuffix=${environment().suffixes.storage}'
      type: 'Custom'
    }
  }
}

resource storageAccounts 'Microsoft.Storage/storageAccounts@2019-06-01' = {
  name: baseNameLower
  location: resourceGroup().location
  kind: 'StorageV2'
  sku: {
    name: 'Standard_LRS'
    tier: 'Standard'
  }
}

resource appInsights 'Microsoft.Insights/components@2015-05-01' = {
  name: baseName
  location: resourceGroup().location
  kind: 'web'
  properties: {
    Application_Type: 'web'
  }
} 

Resource group is then deployed using standart Azure CLI commands like using ARM template:

az group create --name WebJobTest --location westeurope
az deployment group create --resource-group WebJobTest --template-file azuredeploy.bicep --verbose

Azure Blob Storage with private endpoint throws System.Xml.XmlException

When accessing Azure Blob Storage from intranet using private endpoint it may throw confusing System.Xml.XmlException with message “The ‘meta’ start tag on line 4 position 2 does not match the end tag of ‘head’. Line 118, position 3.”. This exceptions occurs when e. g. blob is uploaded to storage in following context:

  • Azure Blob Storage is accessed using private endpoint
  • Intranet network requires to use dedicated proxy when communicating with external network resources
  • NuGet packages Azure.Storage.Blobs and Azure.Identity are used to access Azure Blob Storage from .NET Core app

First step to resolve this issue is to enable logging adding code:

using var listener = AzureEventSourceListener.CreateConsoleLogger(EventLevel.Verbose);

In the detailed logs we can see that it is possible to access Azure AD endpoint https://login.microsoftonline.com, but try to access Azure Blob Storage endpoint returns error 503 Service Unavailable because application proxy is not set correctly. Body of HTTP response can’t be parsed as valid XML which causes exception. To resolve this issue the requests to Azure AD login.microsoftonline.com have to use proxy, but requests to Azure Blob Storage have to bypass proxy. We can achieve this adding code:

HttpClient.DefaultProxy = new WebProxy("myproxy", true, new[] { "mystorage.blob.core.windows.net" });

Now is possible to access Azure Blob Storage from .NET Core app.


Udemy course: Migrate Windows service to Azure

Deploy Windows service using Terraform

Terraform can be used for deployment of Windows service by combination of provisioner together with PowerShell cmdlets.


Udemy course: Migrate Windows service to Azure

Following Terraform code placed in main.tf file can be used to install and uninstall of Windows service:

variable "binary_path" {
  type = string
  description = "Binary path"
}

variable "service_name" {
  type = string
  description = "Service name"
}

resource "null_resource" "win_service" {
  triggers = {
    service_name = var.service_name
  }
  provisioner "local-exec" {
    when = create
    command = "New-Service -BinaryPathName ${var.binary_path} -Name ${var.service_name} -StartupType Automatic; Start-Service -Name ${var.service_name}"
    interpreter = ["PowerShell", "-Command"]
  }
  provisioner "local-exec" {
    when = destroy
    command = "Stop-Service -Name ${self.triggers.service_name}; (Get-WmiObject -Class Win32_Service -Filter \"Name='${self.triggers.service_name}'\").Delete()"
    interpreter = ["PowerShell", "-Command"]
  }
}

Install Windows service using:

terraform apply -var 'binary_path={binary_path}' -var 'service_name={service_name}' -auto-approve

Uninstall Windows service using:

terraform destroy -var 'binary_path={binary_path}' -var 'service_name={service_name}' -auto-approve

Before running this commands replace variables {binary_path} and {service_name} with required values.