This project has moved. For the latest updates, please go here.

Powershell; WorkSheet Cell Address

Nov 19, 2010 at 8:00 AM
Edited Nov 19, 2010 at 8:31 AM

I'm trying to incorporate this assembly with my Powershell Script and I'm having problems setting value for a provided cell address.

[Reflection.Assembly]::LoadFrom("$ScriptDir\EPPlus.dll")

$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage $([System.IO.FileInfo]"C:\test1.xlsx")
$WorkSheet = $ExcelPackage.Workbook.Worksheets.Add("Sample Sheet 1")
$WorkSheet.Cells[1, 1].Value = "Cell 1"
$ExcelPackage.Save()
$ExcelPackage.Dispose()

I receive this error regarding the specified cell address:

 

Property 'Value' cannot be found on this object; make sure it exists and is settable.
At line:1 char:24
+ $WorkSheet.Cells[1, 1]. <<<< Value="Cell 1"
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

Is there an alternate way of specifying a cell address? I know that $WorkSheet.Cells["A1"].Value = "Cell 1" works, but I want to avoid doing this as it is harder to enumerate through columns.

Coordinator
Nov 20, 2010 at 10:34 AM

I have never used Powershell, so I dont know how it works with .net.

The Cell property is an indexer, so check how that works with powershell.

A wild guess is that it uses the item property. Something like this..

$WorkSheet.Cells.Item(1, 1).Value = "Cell 1"
or
$WorkSheet.Cells.Item(1, 1, 1, 2).Value = "Cell A1B1" // A1:B1
or
$WorkSheet.Cells.Item("A1").Value = "Cell 1"

...but this is just a guess. Check out how powershell handles indexers.

Jan

Nov 21, 2010 at 9:52 AM

That is PERFECT! Thank you!!!

Nov 22, 2010 at 8:52 AM

I should have said this earlier, but this an amazing project! Thank you!