Excel (PowerShell) - Revision history

Sstrutt: Add Google Ads

2016-10-04T21:53:42Z

Add Google Ads

← Older revision		Revision as of 21:53, 4 October 2016
Line 1:		Line 1:
			{{#widget:Widget:GoogleAdSenseSkyscraper}}
	Using PowerShell you can easily export a table of data to a CSV file...		Using PowerShell you can easily export a table of data to a CSV file...
	<source lang="powershell">		<source lang="powershell">
Line 23:		Line 24:

	= Create From CSV =		= Create From CSV =
	Whilst its not necessary to create a CSV as an intermediate step, it can be beneficial to do so...		Whilst its not necessary to create a CSV as an intermediate step, it can be beneficial to do so because...
	* Its easier to create/maintain your script		* Its easier to create/maintain your script as you can see whats going on with the data
	* When the Excel automation fails you can attempt to repeat or complete manually, rather than re-running the data collection/creation part.		* When the Excel automation fails you can attempt to repeat or complete manually, rather than re-running the data collection/creation part.

Sstrutt: /* Limitations */ Minor reword

2013-06-18T21:09:38Z

Limitations: Minor reword

← Older revision		Revision as of 21:09, 18 June 2013
Line 6:		Line 6:

	= Limitations =		= Limitations =
	Microsoft Office applications are not built for background automation. It can be achieved, but there are some gotchas. Most notably when it comes to scheduling automation to run outside of an interactive user session. Which means you spend time crafting a wonderful that creates a beautifully formatted spreadsheet, which ~~then~~ fails to complete properly when run as a scheduled task when you're not logged into the machine its running from. It also means that every once in a [https://www.wikipedia.org/wiki/Blue_moon blue moon], it can still fail for no obvious reason.		Microsoft Office applications are not built for background automation. It can be achieved, but there are some gotchas. Most notably when it comes to scheduling automation to run outside of an interactive user session. Which means you spend time crafting a wonderful script that creates a beautifully formatted spreadsheet; but which fails to complete properly when run as a scheduled task when you're not logged into the machine its running from. It also means that every once in a [https://www.wikipedia.org/wiki/Blue_moon blue moon], it can still fail for no obvious reason.

	* See [http://support.microsoft.com/kb/257757 MS KB 257757 - Considerations for server-side Automation of Office] for further info.		* See [http://support.microsoft.com/kb/257757 MS KB 257757 - Considerations for server-side Automation of Office] for further info.

Sstrutt: Added Limitations and Save workaround

2012-05-09T12:42:45Z

Added Limitations and Save workaround

@@ Line 1: / Line 1: @@
 = Create From CSV =
-Basic example, converting an existing CSV file into a XLSX file...
+Whilst its not necessary to create a CSV as an intermediate step, it can be beneficial to do so...
 <source lang="powershell">
 $INfile = "VMs.csv"                                     # Has three columns, labelled VC, Name, Datastore

Sstrutt: Initial creation - content from PowerShell Examples page

2012-04-18T13:30:00Z

Initial creation - content from PowerShell Examples page

New page

= Create From CSV =
Basic example, converting an existing CSV file into a XLSX file...
<source lang="powershell">
$INfile = "VMs.csv" # Has three columns, labelled VC, Name, Datastore
$OUTfile = "VMs.xlsx"

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $False # Set to $true if you want actually see the script interact with Excel

$ExcelBook = $Excel.Workbooks.Add()

$Excel.Rows.Item(1).Font.Bold = $true # Bold heading text
$Excel.Rows.Item(1).VerticalAlignment = -4108 # Centre (vertically) heading
$Excel.Rows.Item(1).HorizontalAlignment = -4108 # Centre (horizontally) heading

$Excel.Cells.Item(1,1) = "VC"
$Excel.Cells.Item(1,2) = "VM Name"
$Excel.Cells.Item(1,3) = "Datastore"

$CSVfile = Import-Csv -Path $INfile

$row = 2

foreach($line in $CSVfile) {
$Excel.Cells.Item($row,1) = $line.VC
$Excel.Cells.Item($row,2) = $line.Name
$Excel.Cells.Item($row,3) = $line.Datastore
$row += 1
Write-Host "." -NoNewLine
}

$ExcelBook.SaveAs($OUTfile)
$Excel.Quit()
Remove-Variable -Name Excel
[gc]::Collect()
[gc]::WaitForPendingFinalizers()
</source>

= Formatting =
{|class="vwikitable"
|-
! Example !! Description
|-
| <code> $Excel.Rows.Item(1).Font.Bold = $True </code> || Make row 1 bold
|-
| <code> $Excel.Rows.Item(1).WrapText = $True </code> || Make row 1 wrap text (may affect row height)
|-
| <code> $Excel.Rows.Item(1).VerticalAlignment = -4108 </code> || Centre (vertically) text on 1st row
|-
| <code> $Excel.Rows.Item(1).HorizontalAlignment = -4108 </code> || Centre (horizontally) text on 1st row
|-
| <code> $Excel.Columns.Item(1).columnWidth = 12 </code> || Make column 1 have a width of 12
|-
| <code> $Excel.Cells.EntireColumn.AutoFit() </code> || Auto-fit all columns
|-
| <code> $Excel.Cells.EntireColumn.AutoFilter() </code> || Auto-filter all columns
|-
| <code> $Excel.Columns.Borders.LineStyle = 1 </code> || Give all cells a simple border
|}

To freeze panes...
<source lang="powershell">
[void]$Excel.Cells.Item(2,3).Select() # Select the appropriate cell to freeze around
$Excel.ActiveWindow.FreezePanes = $True # Freeze
</source>

== Conditional Formatting ==
See http://robertrobelo.wordpress.com/2010/10/07/excels-conditional-formatting-and-powershell/#comments

[[Category:PowerShell]]