Features of national automation MS Excel

It's no secret that there are programs that work perfectly with the English "culture" and begin to "fail" when it comes to national "cultures." Upon closer examination, it turned out that the MS Excel office program was just one of those numbers. How to get around 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 English-language MS Office 2010. It is required to collect information about the currently running system processes and create an Excel spreadsheet based on them. And for greater clarity, also build a pie chart. We will solve the problem using MS PowerShell.

Decision

So, let's start first - by 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 # Number of sheets in a newly created Excel workbook (default 3)

If you follow these three lines, then, due to the mismatch 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 - it is necessary to set the current regional settings to the value “en-US”:

# Retain the previous value of the regional settings
$ OldCulture = [System.Threading.Thread] :: CurrentThread.CurrentCulture
$ culture = [System.Globalization.CultureInfo] "en-US" # Set the regional settings en-US
# Block that will execute if somewhere in this script a
trap  error will occur
{
    # **************************************** ******************************************
    # **** There will be a code necessary for script shutdown ***
    # ******************************************** ***************************************
    # Restore previous regional settings and end the script
    [System.Threading.Thread] :: CurrentThread.CurrentCulture = $ OldCulture; break;
}
[System.Threading.Thread] :: CurrentThread.CurrentCulture = $ culture # Apply new regional settings

After that, we write the previous three commands, make the created Excel instance visible, and 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 a link to it)

The necessary table has been created, now we will take up its design. The first row of the table will contain the title. To visually separate it from the rest of the table, highlight the property names in bold and draw a title frame. For this purpose, we will use the xLineStyle, xlColorIndex, and xlBorderWeight enumerations. We will need the xlChartType enumeration to design the pie chart. To simplify our life even more, we will create aliases for each of the four types of enumerations used. To do this, you need to convert the string, which is the name of the enumeration type, into the type ([type]):

$ row = 2 # The first row of the table is occupied, 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 the columns of the table
$ sheet.cells.item (1,1) = "Name of Process"
$ sheet.cells.item (1,2) = "Working Set Size"

Now we put the process information stored in the $ processes variable in the corresponding table cells. We organize a loop to bypass the collection of process information. The $ process loop variable will store the current item in the collection. From it, we put the name of the process in the first column, and the value of the workingSetSize property in the second column.

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 of
$ range = $ sheet.usedRange
$ range.EntireColumn.AutoFit () | Out-null

It is time to add a chart to the workbook. The charts.add () function of the workbook will add a bar chart by default. And in order to get the diagram of the desired form, you need to set the value of the enumeration that determines the type of diagram. We select one of the possible values ​​of the type $ chartType - xl3DPieExploded (three-dimensional cut pie chart). Set the range defined in the $ range variable as the data source for the chart.

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

Now, for a bigger effect, we’ll rotate the created diagram 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 does not disappear, you need to save the created book. First, check the table with the same name using the Test-Path cmdlet. If we find such a table, then delete the old file using Remove-Item, and then save the current workbook to where the $ strPath variable points.

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

Now you need to close Excel. To do this, first release 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 look at the task manager now, you will see that the Excel process is still "hanging out" there. It is at least not beautiful. To prevent this, forcibly call the garbage collector, having previously set the $ excel variable to zero. And, of course, we will restore the previous regional settings.

$ excel = $ null
[GC] :: Collect ()
[GC] :: WaitForPendingFinalizers ()
 
# Restore previous regional settings
[System.Threading.Thread] :: CurrentThread.CurrentCulture = $ OldCulture

For greater convenience, you can make $ strPath a parameter, and copy everything listed above (apart from lyrical digressions) into one ps1 file. So, copied and ... run! And it works! Yes, and as expected! But only until the moment when an error pops up somewhere inside this file ... The
control will go to the “trap” block, and so far there is nothing except returning the previous regional settings. Therefore, after the crash, the unfinished Excel workbook will remain on the screen, and when it is closed, Excel will remain “hanging” in the list of running processes. Not a very bright prospect, is it?
So, to avoid this, add the following code to the “trap” 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 ()
}