by arnoldus at 2012-08-23 04:45:01
I am new to Powershell and have previously only dabbled in Visual Basic 5, and I sometimes still have difficulties understanding some object oriented stuff.by JeffH at 2012-08-23 05:56:20
My question is how to inherit/import a constant?
For example, here is some code for making and XLSX using EPPLUS. The code I’m trying to get to work is indicated and commented.
The code is translated from C# and VB.net examples
for the “ExcelHorizontalAlignment.Center” & “ExcelFillStyle.Solid” I get the error
[quote]The term ‘ExcelHorizontalAlignment.Center’ is not recognized as the name of a cmdlet, function, script file, or ope…[/quote]
for the “.setcolor(color.gray)” the script throws an immediate error upon running and stops:
[quote]Missing ‘)’ in method call.
At C:\Users\Arnold\Documents\excel-EPPlus.ps1:38 char:32
+ $fill.BackgroundColor.SetColor( <<<< Color.Gray)
+ CategoryInfo : ParserError: (CloseParenToken:TokenId) , ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MissingEndParenthesisInMethodCall[/quote]
$ExcelPackage = New-Object OfficeOpenXml.ExcelPackage $([System.IO.FileInfo]$savename)
$WorkSheet = $ExcelPackage.Workbook.Worksheets.Add(“Pick”)
$worksheet.Cells.Style.Font.Name = “Arial”
$WorkSheet.Cells.item(“A2”).value = "This week - “+ (get-date).dayofweek +” "+ $todayDate
$WorkSheet.Cells.item(“A2”).Style.Font.Bold = $true
$WorkSheet.Cells.item(“A2:G2”).Merge = $true
#$worksheet.Cells.item(“A2”).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center # <----------- TROUBLE HERE
$cell = $worksheet.cells.item(“A2”)
$fill = $cell.Style.Fill
#$fill.PatternType = ExcelFillStyle.Solid # <--------------------------------------------------------------- TROUBLE HERE
#$fill.BackgroundColor.SetColor(Color.Gray) # <-------------------------------------------------------------- TROUBLE HERE
You are jumping in the deep end of the PowerShell pool here. Office constants are fickle. Sometimes they work without having to do much, other times as you’ve seen, you need to define them in advance. Take a look at my slides and demo scripts from a talk I gave on integrating Office with PowerShell at http://bit.ly/IYXpOF. I also have some Excel demos that take an alternative approach with constants.by arnoldus at 2012-08-24 10:17:44
I saw your examples, but I think they only are specific to Office.by poshoholic at 2012-08-24 12:19:55
I’m not using office, but are using a package to do the OpenXML file construction for me.
I was talking in general of how one would make sure the constants are imported from an external package.
As far as I know, there is no general rule for importing constants. It depends on the package you are importing. If the package is a .NET library with constant enumerations or values defined as static types or static properties on classes, then you get those automatically. Since PowerShell is based on .NET, if your library is not based on .NET and if there is no interop interface to your library that is based on .NET then I don’t know that you’re going to get the constants you’re looking for into PowerShell at all without redefining them yourself in PowerShell script.by arnoldus at 2012-08-25 02:57:54
Thank you.by arnoldus at 2012-08-26 03:05:46
I used .NET Reflector to peek in the assembly and see the structure (or one could of course go and read the source files on an OSS project)
I found out that my constant was equal to 2, but also that I could acces it with
[assembly.structure.name]::enumname. I was until now under the impression that the constants were already imported, but you really need to pinpoint their location, and you can’t do that without either .NET Reflector or the source code.
To be clear, the situation is solved. You can also just use " " to lookup your constantby poshoholic at 2012-08-26 19:07:50
The working code is:
$worksheet.Cells.item(“A2”).Style.HorizontalAlignment = “Center” #ExcelHorizontalAlignment.Center
$fill.PatternType = “Solid” # ExcelFillStyle.Solid
$fill.BackgroundColor.SetColor(“Gray”) # (Color.Gray)
If you’re working with objects in memory that are enumerations, never forget GetType as well. You can identify if an object is an enumeration like this:
If it’s an enumeration, then you can see the enumeration type name like this:
and the values like this:
Those tips should help you get much further without .NET Reflector or the source code.