PowerShell operations Excel, CSV detailed _powershell

PowerShell Some simple actions for Excel

A slightly higher level of language involves operations on COM objects, and as Microsoft's own evolving scripting language, PowerShell has this feature that is not surprising, first using the powerful class libraries provided by the. NET Framework, many of which are similar to C #, and second, although as a script it is object-oriented. Using PowerShell to manage WMI and COM is simpler.

Using Microsoft's official words to generalize PowerShell is:

Windows powershell™ is a task-based command-line shell and scripting language that specializes in managing systems. Built on the. NET Framework, Windows powershell™ can help IT professionals and advanced users control and automate the Windows operating system and applications running on Windows.

The built-in Windows PowerShell command, called a cmdlet, can be used to manage computers in the enterprise from the command line. With Windows Powershell™provider, you can access data stores, such as the registry and certificate stores, as easily as you access the file system. In addition, Windows powershell™ features a rich expression analyzer and a fully developed scripting language.

The following is a brief introduction to PowerShell's simple operation of Excel.

First, I execute a command to get all the processes that the system is currently running:

The Get-process cmdlet displays the current local process run state, including the number of handles open, a variety of memory footprint views, and a snapshot of CPU usage. A big difference between PowerShell and the Linux shell is that case insensitive, whether it's a system function or a command. This can reduce the likelihood of mistakes.

You may wonder why you're talking about this? For a system administrator, there's a lot of detail behind the lengthy data, get-process is getting the local process running and, of course, having access to the remote computer with the-computername argument, but this is a mirage for the network administrator, So you have to use Windows WMI and Win32_Process WMI classes to monitor remote systems and display information in a practical way. If you think get-process output is very rich, please take a closer look at the output of Win32_Process:

Obviously, this output is very detailed, but the readability is not strong, the administrator needs is easy to read memory footprint report. You can switch to Excel to automate the operation.

How hard is it to automate Excel? is actually very simple, because Microsoft has created an automation model that is designed to work with Excel. The program ID is Excel.Application, which is a COM object. When you create an instance of a Excel.Application object, Excel starts and runs by default, but you cannot see it. However, you can use the Visible property to let Excel display it.

The following code shows how to create a Excel.Application object, query the state of the Visible property, and then set the Visible property to True:

Copy Code code as follows:

PS > $excel = New-object-comobject excel.application ==> Create a COM object that Excel applies
PS > $excel. Visible ==> Default New Excel View is not displayed, that is, not open
False
PS > $excel. Visible = $true ==> See the following interface

You will then see a very strange view of Excel, which looks like a shell of an Excel application (pictured below). There are no workbooks, no spreadsheets,-one is bare Excel.

Of course, this is definitely not available, but it's a prerequisite for creating a workbook, which is the container that holds the workbook. Now we need to add a workbook to the application. To do this, you need to use the Add method of the Workbook object. The Workbook object is accessed from the primary Excel.Application object, and as you can see, we store the workbook object in a variable named $workbook:

Copy Code code as follows:

$workbook = $excel. Workbooks.Add () ==> now adds an empty workbook

Now you need to connect to a specific spreadsheet. By default, when a workbook is added to Excel, three spreadsheets (SHEET1,SHEET2,SHEEET3) are added to it. These spreadsheets can be identified by numbers. In the following line of code, connect the first spreadsheet and store the returned spreadsheet object in a variable named $sheet:

Copy Code code as follows:

$sheet = $workbook. Worksheets.item (1) ==> said I was using the first sheet

Now I'm going to run the $excel once more. Visible = $true To see what Excel has become (the workbook is built and Sheet1 is selected):

You can now write data to the spreadsheet. The information in an EXCEL spreadsheet is stored in a cell. Because cells are in a spreadsheet, you can access specific cells by using the spreadsheet object stored in the $sheet variable. The method is to use numbers that refer to rows and columns in the spreadsheet. In an EXCEL spreadsheet, a row is a number and a column is a letter, which is somewhat confusing. However, when you use the automation model, both rows and columns are numbers. The first number is a row, and the second number is the column. You can write to a cell by assigning a value to a specific cell:

Copy Code code as follows:

$sheet. Cells.item (1,1) = "Test" ==> I fill in the first cell with "test"

At this point, you run $excel again. Visible = $true, see what Excel has become:

Well, unsurprisingly, one of the problems with Excel is that the workbook always creates three spreadsheets, which is a bit wasteful because I'm only going to need the first sheet for this test. Fortunately, you can use Automation to remove extra spreadsheets: Connect to a third spreadsheet using a worksheet collection and call the Delete method. Do the same action delete the second spreadsheet:

Copy Code code as follows:

$workbook. Worksheets.item (3). Delete ()
$workbook. Worksheets.item (2). Delete ()

This leaves only the Sheet1, and next, rename the remaining spreadsheet. This is important because if you decide to use an ActiveX data object (ADO) to query an EXCEL spreadsheet, the spreadsheet name will be used in the connection string. Therefore, for the code to be readable and intuitive, the spreadsheet should have a logical name. To rename a spreadsheet, simply assign a value to the Name property of a particular spreadsheet. The following code renames the first spreadsheet to "processes":

Copy Code code as follows:

$workbook. Worksheets.item (1). Name = "Processes"

Now you need to connect to the renamed spreadsheet. Use the Item method of the Worksheet object and assign the name of the spreadsheet to it:

Copy Code code as follows:

$sheet = $workbook. Worksheets.item ("Processes")

Then we'll look at what Excel looks like:

The code for the above test process is as follows:

Copy Code code as follows:

$excel = New-object-comobject Excel.Application
# $excel. Visible = $true
$workbook = $excel. Workbooks.Add ()
$sheet = $workbook. Worksheets.item (1)
$sheet. Cells.item (1,1) = "Test"
$workbook. Worksheets.item (3). Delete ()
$workbook. Worksheets.item (2). Delete ()
$workbook. Worksheets.item (1). Name = "Processes"
$excel. Visible = $true

Here we start porting the content in the process to Excel.

The first row of the spreadsheet contains the header information. The border is drawn and the property name is displayed in bold. Because the data will start at the second row, assign the value 2 to the counter variable $x:

Copy Code code as follows:

$x =2

The next four lines of code create four enumerated types. Enumeration types are used to tell Excel which values are allowed to fill in specific option types. For example, the XlLineStyle enumeration is used to determine the type of line drawn: Double lines, dashed lines, and so on. These enumeration values are described in detail on MSDN.
To make your code easier to read, create a shortcut alias for each enumerated type that will be used. In fact, we will convert the string representing the name of the enumeration to [type].

Copy Code code as follows:

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

Now you need to format the first line. Make the font bold, define the line as Xldashdot, allow the color to be automatically specified, and set the border width to medium weight:

Copy Code code as follows:

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
}

When you are done, assign a value to the first row by using the Item method to select the cell and specify the coordinates of the row and column. Next, use direct assignment to write the column headers:

Copy Code code as follows:

$sheet. Cells.item (1,1) = "Name of Process"
$sheet. Cells.item (1,2) = "Working Set Size"

Now you need to put the process information stored in the $processes variable generated by the WMI query into the appropriate cells. Use the foreach statement to traverse the process information collection. Define the variable $process as a collection enumerator (placeholder) and choose to write the name and WorkingSetSize properties to the first and second columns respectively. $x variables will play a role here. Starting with the second row, the value of the $x variable is incremented while traversing the process collection so that it always points to the current row in the collection. All of the data stored in the $processes process information collection can be sorted by using the following code:

Copy Code code as follows:

foreach ($process in $processes)
{
$sheet. Cells.item ($x, 1) = $process. Name
$sheet. Cells.item ($x, 2) = $process. WorkingSetSize
$x + +
}

After you finish filling out an Excel spreadsheet, you can resize the column so that the cell is the same size as the data it stores. To do this, you create a range by specifying the column coordinates you want to use, but you can also use only the UsedRange property of the spreadsheet. After you create the Range object, select the EntireColumn property and adjust the column size using the AutoFit method. Because the method always returns data, I send the results to the Out-null cmdlet. So as to prevent the console from being littered with useless information. The following is the code used:

Copy Code code as follows:

$range = $sheet. UsedRange
$range. Entirecolumn.autofit () | Out-null

So far, all tasks have been completed the spreadsheet at this time has the name of all processes and the working set of memory, which is pretty good. Take a look at the finished results:

The result is the process that is running on my computer. I have selected only two fields to display.

The code for the entire script is as follows:

Copy Code code as follows:

$processes =get-process
$excel = New-object-comobject Excel.Application
# $excel. Visible = $true
$workbook = $excel. Workbooks.Add ()
$sheet = $workbook. Worksheets.item (1)
$workbook. Worksheets.item (3). Delete ()
$workbook. Worksheets.item (2). Delete ()

$workbook. Worksheets.item (1). Name= "Processes"
$sheet = $workbook. Worksheets.item ("Processes")
$x = 2

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

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
}

$sheet. Cells.item (1,1) = "Name of Process"
$sheet. Cells.item (1,2) = "Working Set Size"

foreach ($process in $processes)
{
$sheet. Cells.item ($x, 1) = $process. Name
$sheet. Cells.item ($x, 2) = $process. WorkingSet
$x + +
} #end foreach

$range = $sheet. UsedRange
$range. Entirecolumn.autofit () | Out-null

About the simple knowledge of PowerShell write Excel first introduced to this, in fact, in the process of running, you will find that the process of each field in the process of filling Excel is very slow, because it is a cell a cell filled in, rather than a copy into Excel, These will be introduced in a later essay.

Note: This essay refers to Microsoft's official blog and adds its own understanding, followed by a brief introduction to other applications of the PowerShell operation Excel&csv.

If there is a mistake, please correct me, thank you!