Features of national automation MS Excel

It is not a secret for anyone that there are programs that work well with the English “culture” and start “buggy” when it comes to national “cultures”. Upon closer examination, it turned out that MS Excel office software is just one of these numbers. How to bypass some of the "features" of MS Excel and will be discussed in this article.

Formulation of the problem

There is a Windows 7 operating system with Russian regional settings and an English-language MS Office 2010. It is required to collect information about the currently running system processes and create an Excel table based on them. And for greater clarity, and even build a pie chart. We will solve the problem using MS PowerShell.

Decision

So let's start first with collecting information about the OS processes and creating an Excel.Application object:

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

If you now run these three lines, then, due to the discrepancy between the regional settings of the OS and MS Office, a “mysterious” message will appear on the screen: “Exception setting“ SheetsInNewWorkbook ”:“ Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD)) "". This is solved simply - the current regional settings should be set to en-US:

# Save the previous value of the regional settings
$ OldCulture = [ System . Threading Thread ] :: CurrentThread . CurrentCulture
$ culture = [ System . Globalization . CultureInfo ] "en-US" # Setting the regional settings en-US
# Block that will be executed if an error occurs somewhere in this script
trap
{
************************************************* *******************************
# **** Here will be the code necessary for the correct completion of the script ***
************************************************* *******************************
# Restore the previous regional settings and finish the script
[ System . Threading Thread ] :: CurrentThread . CurrentCulture = $ OldCulture ; break ;
}
[ System . Threading Thread ] :: CurrentThread . CurrentCulture = $ culture # Applying new regional settings

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

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

The necessary table is created, now we will be engaged in its registration. The first line of the table will contain the heading. To visually separate it from the rest of the table, select the property names in bold and draw a header frame. For this purpose we will use the xLineStyle, xlColorIndex and xlBorderWeight enumerations. We need the xlChartType enumeration to form a pie chart. To further simplify your life, create pseudonyms for each of the four types of enumerations used. To do this, convert the string, which is the name of the enumeration type, to type ([type]):

$ row = 2 # The first row of the table is busy, so the data will be written from the second row

$ 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 ]

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
}

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

Now we will place the information about the processes stored in the $ processes variable into the corresponding cells of the table. Arrange a loop to bypass the collection of process information. The $ process loop variable will store the current item in the collection. From it in the first column we place the name of the process, and in the second - 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 width of the columns according to the content
$ range = $ sheet . usedRange
$ range . EntireColumn . AutoFit ( ) | Out - Null

It's time to add a chart to the workbook. The workbook function “charts.add ()” will add a bar chart by default. And in order to get the diagram of the desired type, you need to set the value of the enumeration that determines the type of diagram. Choose one of the possible values ​​of the $ chartType type - xl3DPieExploded (three-dimensional split pie chart). As the data source for the chart, set the range defined in the $ range variable.

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

Now, for more effect, let's twist the created diagram by 360 degrees in increments of 15 degrees:

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

In order that all this beauty is not lost, you need to save the created book. First, check for the presence of a table with the same name using the Test-Path cmdlet. If we find such a table, we will delete the old file using a Remove-Item, and then we will save the current workbook where the $ strPath variable points.

$ strPath = "path \ t o \ f ile \ f ile_name.xlsx"
if ( Test - Path $ strPath )
{
Remove - Item $ strPath
}
$ excel . ActiveWorkbook . SaveAs ( $ strPath )

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

$ sheet = $ null
$ range = $ null

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

And everything would be fine, but if you now look in the task manager, you will see that the Excel process is still “hanging out” there. It is at least not beautiful. To prevent this, we will force the garbage collector to force, having previously reset the $ excel variable. And, of course, we will restore the old regional settings.

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

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

For more convenience, you can make $ strPath a parameter, and copy everything (with lyrical digressions) into one ps1 file. So, copied and ... run! And it works! Yes, and as expected! But only until such time as an error pops up somewhere inside this file ...
The office will go to the “trap” block, and there, for the time being, apart from the return of the previous regional settings, there is nothing. Therefore, an unfinished Excel book will remain on the screen after a crash, and when it closes, Excel will remain in the list of running processes. Not a very bright prospect, is it?
So, to avoid this, add the following trap to the block (instead of the comment with the line “There will be ...”):

if ( $ workbook - ne $ null )
{
$ sheet = $ null
$ range = $ null

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