Peculiarities of national automation of MS Excel

for anybody not a secret that there are programs that work well with the English "culture" and begin to "fail" when it comes to national "cultures". Upon closer inspection, it turned out that the office program MS Excel just from this number. How to get around some "features" of MS Excel and will be discussed in this article.

problem Statement

In the presence of a operating system Windows 7 with Russian regional settings and English MS Office 2010. You want to collect information about currently running processes system and create Excel spreadsheet. And for greater clarity, and even a pie chart to build. Problem to solve will be using MS PowerShell.

the Solution

first things first — gather information about OS processes and create an Excel object.Application:

$processes = Get-WmiObject -Class Win32_Process # the List of running processes
$excel = New-Object -ComObject Excel.Application
$excel.SheetsInNewWorkbook = 1 # the Number of sheets in the newly created Excel workbook (by default 3)

If we run these three lines, due to different regional settings of the OS and MS Office, the screen displays a mysterious message: "Exception setting "SheetsInNewWorkbook": "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"". This is simple — you need the current locale set to "en-US":

#Maintain old value regional settings
$OldCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
$culture = [System.Globalization.CultureInfo]en-US # Set the regional settings en-US
# Block executed if somewhere in this script, an error will occur
trap
{
# *******************************************************************************
# **** Here is the code necessary for the correct completion of the script ***
# *******************************************************************************
# Restoring the old regional settings and finish the script
[System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture; break;
}
[System.Threading.Thread]::CurrentThread.CurrentCulture = $culture # Apply new regional settings

After that, write the previous three commands, do the created instance of Excel visible, create a new workbook:

$excel.visible = $true
$workbook = $excel.workbooks.add()
$workbook.worksheets.item(1).Name = "Processes" # Rename a single table for convenience
$sheet = $workbook.worksheets.item("Processes") # Select the table “Processes” (get it link)

$row = 2 # the First row of the table is occupied, so the data will be recorded with the second line

$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]

Will format the first row of the table:

for($b = 1; $b -le 2; $b++)
{
$sheet.cells.item(1,$b).font.bold = $true
$sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
$sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
$sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
}

# Give meaningful names to columns of a table
$sheet.cells.item(1,1) = "Name of Process"
$sheet.cells.item(1,2) = "Working Set Size"

Now place information about the processes is stored in the $processes variable, in the appropriate cells of the table. Make a loop to bypass the collection of information about the processes. In the loop variable $process to store the current element in the collection. From it in the first column we put the name of the process, and secondly, the value of the property workingSetSize.

foreach($process in $processes)
{
$sheet.cells.item($row, 1) = $process.name
$sheet.cells.item($row, 2) = $process.workingSetSize

$x++
}

# Adjust the column width according to the contents
$range = $sheet.usedRange
$range.EntireColumn.AutoFit() | Out-Null

$workbook.chart.add() | Out-Null
$workbook.chart.item(1).Name = "Working Set Size"
$workbook.ActiveChart.chartType = $chartType::xl3DPieExploded
$workbook.ActiveChart.SetSourceData($range)

Now, for greater effect, roll created the chart to 360 degrees in increments of 15 degrees:

for($i = 1; $i -le 360; $i +=15)
{
$workbook.ActiveChart.rotation = $i
}

To all this beauty is not lost, you need to save your created book. First check whether a table with the same name using the cmdlet Test-Path. If such a table we find, then delete the old file using Remove-Item, and then save the current workbook where indicates the variable $strPath.

$strPath = "path\to\file\file_name.xlsx"
if(Test-Path $strPath)
{
Remove-Item $strPath
}
$excel.ActiveWorkbook.SaveAs($strPath)

Now Excel needs to be closed. To do this, first release the occupied resources ($sheet, $range), close the workbook and then Excel.

$sheet = $null
$range = $null

$workbook.Close($false)
$excel.Quit()

And all would be well, but if you now look in the task Manager you will see that the Excel process is still there "hanging". It is at least not nice. To avoid this, forcibly call the garbage collector, pre-zeroing the variable $excel. And, of course, restore the previous locale.

$excel = $null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

# Restoring the previous regional settings
[System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture

For you $strPath to make the parameter and all of the (than digression) to copy in a single. ps1 file. So, copied and... run! And it works! Yes, and as expected! But just before the moment, yet somewhere inside the file will not pop up an error...
Control passes to the block "trap", and there is, except for the return of the old regional settings and there is nothing. Therefore on the screen after the failure will remain unfinished Excel workbook, and at its closing in the list of running processes so I will "hang" Excel. Not much to look forward to, isn't it?
So, to avoid this, add in the block "trap" (instead of a comment with the string "Here is...") with the following code:
{
$sheet = $null
$range = $null

$workbook.Close($false)
}
if($excel -ne $null)
{
$excel.Quit()
$excel = $null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()
}

Article based on information from habrahabr.ru