войти
 
ГлавнаяНовостиБиблиотекаОбучениеСкачатьМероприятияСообществоФорумыПоддержкаО проекте
Ресурсы для ИТ-профессионалов >
> 
> 
Test if comobject exist in function

Отвечено Test if comobject exist in function

  • 7 мая 2012 г. 8:50
     
      С кодом
    Нужно войти
    0

    Hello everyone,

    I'm writing a little script in powershel

    the main part is not important since its working perfectly.

    I'm trying to optimize my code by writing a excel function. I'd like to put some data in excel and i want to do it via a function.

    function excel{
param(
	[string]$datas = "",
	[string]$nbcolo = "0",
	[string]$intRow = "0"
)
	for($i=1;$i -le $nbcolo;$i++){
		$c.Cells.Item($intRow,$i) = $datas.split(";")[$i-1];
	}
}

$a = New-Object -comobject Excel.Application
$a.Visible = $True
$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)
$truc = "lol;lol1;lol2;lol3"
$truc2 = "lol;lol1;lol2;lol4"

#calling the excel function and filling 
excel -datas $truc -nbcolo 4 -introw 1
excel -datas $truc2 -nbcolo 4 -introw 2

    I want to put the creation of the excel comobject inside my function.

    And everytime the function is called, i want to check if the comobject already exist.

    like this :

    function excel{
param(
	[string]$datas = "",
	[int32]$nbcolo = 0,
	[int32]$intRow = 0
)
	If(!($a)){
		$a = New-Object -comobject Excel.Application
		$a.Visible = $True
		$b = $a.Workbooks.Add()
		$c = $b.Worksheets.Item(1)
	}else{
		for($i=1;$i -le $nbcolo;$i++){
			$c.Cells.Item($intRow,$i) = $datas.split(";")[$i-1];
		}
	}
}

    But it doesnt seem to work. It open a new excel windows every time the function is called...

    I'm missing something but what ???

    Thanks for your help

     

Все ответы

  • 7 мая 2012 г. 9:07
     
     Отвечено
    Нужно войти
    0
    What happens is that because the $a variable is defined in the scope of the function, that the moment the function ends the variables defined in there are also lost. You can circumvent this issue by defining the $a $b and $c variable as a variable for the whole script by using $script:a,$script:b,$script:c to define the objects. This way you define the object to be present for the duration of the script and based on your information that is what you are looking for.
    • Помечено в качестве ответа TonQ 7 мая 2012 г. 9:20
    •  
     
  • 7 мая 2012 г. 9:10
     
     
    Нужно войти
    0

    The way you have written your code in the second example will cause that behaviour.  It is doing what you are asking:  New-Object, each time.

    What you can do is declare the com object outside the function, and pass it to the function as an argument.

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)

     
  • 7 мая 2012 г. 9:17
     
     Отвечено С кодом
    Нужно войти
    0

    For example:

    $a = New-Object -comobject Excel.Application
function ManipulateExcelInstance {
    param($Instance)
    
        $instance.Visible = $True
        $b = $instance.Workbooks.Add()
	$c = $b.Worksheets.Item(1)
    }
 
 ManipulateExcelInstance -Instance $a

    Grant Ward, a.k.a. Bigteddy

    What's new in Powershell 3.0 (Technet Wiki)


     
  • 7 мая 2012 г. 9:20
     
     
    Нужно войти
    0

    Both solutions worked, but i used Jaap's solution.

    Thanks !

     
  • 7 мая 2012 г. 9:24
     
      С кодом
    Нужно войти
    0

    The example you are giving you are needlessly recreating the $b and $c objects, which is not necessary. If you are going to place this outside the function you should either place all objects out of the variable or define them as global variables. I would recommend doing it like this:

    function excel{
param(
	[string]$datas = "",
	[int32]$nbcolo = 0,
	[int32]$intRow = 0
)
	If(!($a)){
		$script:a = New-Object -comobject Excel.Application
		$script:a.Visible = $True
		$script:b = $a.Workbooks.Add()
		$script:c = $b.Worksheets.Item(1)
	}else{
		for($i=1;$i -le $nbcolo;$i++){
			$c.Cells.Item($intRow,$i) = $datas.split(";")[$i-1];
		}
	}
}

    Or if you want to place it outside of the function:

    $a = New-Object -comobject Excel.Application
$a.Visible = $True
$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)

function excel{
param(
	[string]$datas = "",
	[int32]$nbcolo = 0,
	[int32]$intRow = 0
)
	for($i=1;$i -le $nbcolo;$i++){
		$c.Cells.Item($intRow,$i) = $datas.split(";")[$i-1];
	}
}

     
  • 7 мая 2012 г. 14:45
     
     
    Нужно войти
    0

    This is why we build pipelines.

    Ask Grant about that. 

    Use begin{} to grab Excel,  Use process{} to add to Excel in the pipeline and use end{} to save and shut down excel. 

    As long as the function is called repeatedly in a pipeline it will only open the application once per pipeline.

    ¯\_(ツ)_/¯


     
  • 7 мая 2012 г. 15:38
     
     
    Нужно войти
    0

    I ve  finished my script. One thing to know in my case is that my main function is recursive.

    i ve put all my functions inside a module, so i just to import the module and use the main function.

    but i've identified one problem:

    I dont close the excel object, so when i actually close excel and the want to reuse the function i got errors with the excel function (the object steel exist but excel is close (the process is still running))

    will your solution resolve this problem ?

    i can always cose the object in my main function after the recursion ..

     
  • 7 мая 2012 г. 16:03
     
      С кодом
    Нужно войти
    0

    I ve  finished my script. One thing to know in my case is that my main function is recursive.

    i ve put all my functions inside a module, so i just to import the module and use the main function.

    but i've identified one problem:

    I dont close the excel object, so when i actually close excel and the want to reuse the function i got errors with the excel function (the object steel exist but excel is close (the process is still running))

    will your solution resolve this problem ?

    i can always cose the object in my main function after the recursion ..

    We cannot see your code so it is hard to know what you are asking about.  What does recursion have to do with closing Excel?

    If Excel runs in a scirpt it will shut down when the PowerSHell window has closed.  If you want to force it to shuutdown you need to dereference every object you have created.

    Here is an example:

    # name no extension - extension will be supplied by Excel.
Param(
   [Parameter(
        HelpMessage='name no extension - extension will be supplied by Excel')
   ][string]$filepath='f:\projects\scripts\installer'
)   
# generate data dor Excel
Get-ChildItem HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall |
    Get-ItemProperty |
    sort displayname|
    select Displayname,DisplayVersion ,Publisher ,InstallDate ,HelpLink ,UninstallString |
    export-csv installer.csv -notype
# open and load Excel
$xl=New-Object -com Excel.Application
#$xl.visible=$true
$wb=$xl.Workbooks.Open($filepath+'.csv')
# format sheet
$sheet=$wb.Worksheets.Item(1)
[void]$sheet.UsedRange.Columns.AutoFit()
$header=$sheet.UsedRange.Rows.Item(1)
$header.Rows.Item(1).cells.Interior.ColorIndex = 19
$header.Rows.Item(1).cells.Font.ColorIndex = 11
$header.Rows.Item(1).cells.Font.ColorIndex = 21
$header.Rows.Item(1).cells.Font.Bold = $True
# save as macro disabled workbook Excel will add xslx extension
$xl.DisplayAlerts=$false
$wb.SaveAs($filepath,([Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault))
$fullname=$wb.FullName
$wb.Close()
$xl.Quit()
while(![System.Runtime.InteropServices.Marshal]::ReleaseComObject($header)){'Excel reference removed'}
while(![System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet)){'Excel reference removed'}
while(![System.Runtime.InteropServices.Marshal]::ReleaseComObject($wb)){'Excel reference removed'}
while(![System.Runtime.InteropServices.Marshal]::ReleaseComObject($xl)){'Excel reference removed'}
Remove-Variable xl,wb,header,sheet
&($fullname)

    Note the way the variables are released at the end.  Each variable created or used to reference an Excel object needs to be dereferenced until the reference count is zero.

    As long as the is any refrence ot the automation object it will not shutdown.  If you exit PowerSHell Windows will do this for you as it releases the resources assigned to PowerShell.  Handles and COM references are all resources.

    e

    ¯\_(ツ)_/¯