Solution: Use the ImportExcel Powershell module from PSGallery. In order to install the module run the following command:
Find-Module -Name ImportExcel | Install-Module
You can also have the module without installing it. Simple copy the module installation folder from a computer on which the module already have been installed (e.g. C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.0.1).
Import-Module 'C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.0.1\ImportExcel.psd1'
Example - Change the value of the cell A2 of the Worksheet named "Worksheet" in "MyFile.xlsx" excel file:
# Create excel file
$ExcelFileName = 'C:\Temp\Test.xlsx'
$ExcelFile = Export-Excel -Path $ExcelFileName -ClearSheet -WorksheetName $ExcelWorksheetName1 -PassThru -AutoSize
# Open Excel file
$MyExcelFile = Open-ExcelPackage -Path 'C:\Temp\DMI\MyFile.xlsx'
# Add worksheets to excel file
Add-Worksheet -ExcelPackage $ExcelFile -WorksheetName "Worksheet2"
Add-Worksheet -ExcelPackage $ExcelFile -WorksheetName "Worksheet3"
# Open Worksheet named 'Worksheet1'
$MyExcelWorksheet = $MyExcelFile.Workbook.Worksheets['Worksheet1']
# Add value into the cell A2 of the worksheet
$MyExcelWorksheet.Cells["A2"].Value = 'Testing'
# Set color to cells
Set-ExcelRange -Worksheet $ExcelFileWorksheet -Range "A1:A4" -BackgroundColor Green -FontColor Yellow -AutoSize
# Close and save Excel file
Close-ExcelPackage -ExcelPackage $MyExcelFile
More ImportExcel URLs:
- Using the Import-Excel module: Part 1 Importing
- Using the Import Excel module part 2: putting data into .XLSx files
- More tricks with PowerShell and Excel