Create or open Excel file in powershell
function CreateOrOpenExcelFile()
{
param
(
[Parameter(Mandatory = $true, HelpMessage = "Excel file path.")]
[string] $ExcelFilePath,
[Parameter(Mandatory = $false, HelpMessage = "Excel Window visibility.")]
[bool] $WindowVisible = $true
)
$cultureUS = [System.Globalization.CultureInfo]'en-US'
[System.Threading.Thread]::CurrentThread.CurrentCulture = $cultureUS
#temporary continue if error, because it stops even when we want to continue
$ErrorActionPreference = "Continue"
$application = [Runtime.InteropServices.Marshal]::GetActiveObject('Excel.Application')
$ErrorActionPreference = "Stop"
if(-not $application)
{
$application = New-Object -comobject Excel.Application
}
$application.Visible = $WindowVisible
if (Test-Path $ExcelFilePath)
{
$workbook = $application.Workbooks.Open($ExcelFilePath, 2, $false)
}
else
{
$workbook = $application.Workbooks.Add()
$workbook.SaveAs($ExcelFilePath)
}
#we need to return also application, because of option to setup it later
return ($application, $workbook)
}
function GetExcelWorksheet()
{
param
(
[Parameter(Mandatory = $true, HelpMessage = "Excel workbook object.")]
[object] $Workbook,
[string] $SheetName = "Sheet1"
)
$worksheet = $Workbook.Worksheets | where {$_.name -eq $SheetName}
if (-not $worksheet)
{
$worksheet = $Workbook.Worksheets.Add()
$worksheet.name = $SheetName
}
return $worksheet
}
function Write-DataToExcelFile
{
param
(
[Parameter(Mandatory = $true, HelpMessage = "Excel file path.")]
[string] $ExcelFilePath,
[Parameter(Mandatory = $true, HelpMessage = "Object with input data e. g. hashtable, array, ...")]
[object] $InputData
)
#Add next sheet for 'Test Case Overview'
($application, $workbook) = CreateOrOpenExcelFile -ExcelFilePath $ExcelFilePath
$worksheetTC = GetExcelWorksheet -Workbook $workbook -SheetName "Data"
$row = 1
$col = 1
$cells = $worksheetTC.Cells
#if $InputData is simple array
foreach($data in $InputData)
{
#write value
$cells.item($row,$col) = $data.ToString()
$row++
#define cell name and create hyperlink to other cell
$cellValue = $data.ToString()
$cellName = "o1_{0}" -f $cellValue
($cells.Item($row,$col)).Name = $cellName
$targetCellName = "o2_{0}" -f $cellValue
$subAddress = "'{0}'!{1}" -f $sheetName2, $targetCellName #"'Test Overview'!A1"
$void = $worksheetTC.Hyperlinks.Add($cells.Item($row,$col) ,"" , $subAddress, "", $cellValue)
}
#read value
# if($cells.Item($row, $col).Value() -eq "Id")
# {
# $row++
# $cells.item($row,$col) = "cat"
# }
#turn off Error message for replacing existing file when saving it
$application.DisplayAlerts = $false
$workbook.SaveAs($ExcelFilePath)
}
#data 'raz, 'dva', 'tri' will be inserted to excel sheet 'Data'
Write-DataToExcelFile -InputData @{"raz", "dva", "tri") -ExcelFilePath $ExcelFilePath
cls
#============ Input parameters =============
$ExcelFilePath = "c:\temp\MyExcelFile2.xlsx"