none
How to write in Excel via powershell RRS feed

Answers

  • Yes, as far as I know writing to each cell individually is the only way.

    You can automate it... here's a function -  I think mjolinor wrote it/posted it - which will take an Object and import the data into Excel...

    function Export-Excel {
    	[cmdletBinding()]
    	Param(
    		[Parameter(ValueFromPipeline=$true)]
    		[string]$junk        )
    	begin{
    		$header=$null
    		$row=1
    		$xl=New-Object -ComObject Excel.Application
    		$wb=$xl.WorkBooks.add(1)
    		$ws=$wb.WorkSheets.item(1)
    		$xl.Visible=$true
    	}
    	process{
    		if(!$header){
    			$i=0
    			$header=$_ | Get-Member -MemberType NoteProperty | select name
    			$header | %{$ws.cells.item(1,++$i)=$_.Name}
    		}
    		$i=0
    		++$row
    		foreach($field in $header){
    			$ws.cells.item($row,++$i)=$($_."$($field.Name)")
    		}
    	}
    	end{
    		$xl.Quit()
    		Remove-Variable xl
    	}
    }


    Admiral Ackbar says...

    Wednesday, November 7, 2012 9:56 PM

All replies

  • Simple example...

    $xl=New-Object -ComObject Excel.Application
    $wb=$xl.WorkBooks.Open('C:\Temp\Servers.xls')
    $ws=$wb.WorkSheets.item(1)
    $xl.Visible=$true
    
    $ws.Cells.Item(1,1)=1
    
    $wb.SaveAs('c:\temp\Servers_New.xls')
    $xl.Quit()
    


    Admiral Ackbar says...

    Wednesday, November 7, 2012 6:27 PM
  • Thanks for your responce.

    Could you please assist me further, following addeding single Column how about many, do i have to write cell item indivually for all?

    $Type = Read-Host "Type User Name"
    $Name =  (Get-User $Type | Select Name).Name
    $Depart =  (Get-User $Name | Select Department).Department
    $Email =  (Get-User $Name | Select WindowsEmailaddress).WindowsEmailaddress

    $a = New-Object -comobject Excel.Application

    $a.Visible = $True

    $b = $a.Workbooks.Add()
    $c = $b.Worksheets.Item(1)

    $c.Cells.Item(1,1) = "Display Name"
    $c.Cells.Item(1,2) = "Department Name"
    $c.Cells.Item(1,3) = "Email"
    $c.Cells.Item(2,1) = "$Name"
    $c.Cells.Item(2,2) = "$Depart"
    $c.Cells.Item(2,3) = "$Email"


    Support@Mytechnet.me

    Wednesday, November 7, 2012 6:58 PM
  • Yes, as far as I know writing to each cell individually is the only way.

    You can automate it... here's a function -  I think mjolinor wrote it/posted it - which will take an Object and import the data into Excel...

    function Export-Excel {
    	[cmdletBinding()]
    	Param(
    		[Parameter(ValueFromPipeline=$true)]
    		[string]$junk        )
    	begin{
    		$header=$null
    		$row=1
    		$xl=New-Object -ComObject Excel.Application
    		$wb=$xl.WorkBooks.add(1)
    		$ws=$wb.WorkSheets.item(1)
    		$xl.Visible=$true
    	}
    	process{
    		if(!$header){
    			$i=0
    			$header=$_ | Get-Member -MemberType NoteProperty | select name
    			$header | %{$ws.cells.item(1,++$i)=$_.Name}
    		}
    		$i=0
    		++$row
    		foreach($field in $header){
    			$ws.cells.item($row,++$i)=$($_."$($field.Name)")
    		}
    	}
    	end{
    		$xl.Quit()
    		Remove-Variable xl
    	}
    }


    Admiral Ackbar says...

    Wednesday, November 7, 2012 9:56 PM