Powershell play Excel & CSV (1)

Powershell for some simple Excel operations

A slightly advanced language involves operations on COM objects. As a script language developed by Microsoft, powershell has this function. First of all, itUse a powerful class library provided by. NET FrameworkMany features are similar to C #. Second, although it is used as a script, It Is Object-Oriented. It is easier to use powershell to manage WMI and COM.

In Microsoft's official words, powershell is:

Windows powershell is a task-based command line shell and script language used to manage systems. Built on the. NET Framework, Windows powershell helps IT professionals and advanced users control and automatically manage Windows operating systems and applications running on Windows.Program.

Built-in windows powershell commands (calledCmdletCan be used to manage computers in an enterprise through command lines. Use Windows powershellProviderYou can easily access data storage like accessing a file system, such as registry and certificate storage. In addition, Windows powershell has a rich array of functions, expression analysis programs, and a fully developed scripting language.

The following describes powershell's simple operations on Excel.

First, I run a command to obtain all processes currently running in the system:

The get-process cmdlet displays the running status of the local process, includingNumber of opened handles, memory usage views, and CPU usage snapshots. A major difference between powershell and Linux Shell is that it is case insensitive, whether it is a system function or a command. This can reduce the probability of making mistakes.

You may wonder why you are talking about this? For the system administrator, a large number of details are hidden behind lengthy data. Get-process is used to obtain the running status of local processes. Of course, after the-computername parameter is added, the remote computer can be accessed, however, for network administrators, this is like a mirage, so they have to use Windows WMI.And the win32_process WMI class to monitor remote systems and display information in a practical way. If you think that the get-process output is rich, take a closer look at the win32_process output:

Obviously, this output is very detailed, but the readability is not strong,What administrators need is a memory usage report that is easy to read. You can switch to excel for automatic operations.

How hard is Excel Automation? It is actually very simple, because Microsoft has created an automated model dedicated to processing Excel. The program id is Excel. application, which is a COM object. When creating an Excel. Application object instance, Excel starts and runs by default, but you cannot see it. However, you can use the visible attribute to display it in Excel. BelowCodeShows how to create an Excel. Application object, query the visible attribute status, and set the visible attribute to true:

 
PS>$ Excel= New-object-Comobject excel. Application ==> create an Excel application's COM Object PS>$ Excel. Visible => by default, the new Excel view is not displayed, that is, it is not opened.FalsePS>$ Excel. Visible =$ True => view the following interface
 

Then you will see a very strange Excel view, which looks like a shell of an Excel application (for example ). No workbooks, no workbooks, and a bald Excel file. Of course, this cannot be used, but it is the premise for creating a workbook. It is the container for storing the workbook. Now we needAdd a workbook to an application. Therefore, you need to use the add method of the workbook object. The workbook object is accessed from the main excel. Application object. As you can see, we store the workbook object in a variable named $ workbook:

 
$ Workbook=$ Excel. Workbooks. Add () ==> an empty Workbook is added.
 

Now, you need to connect to a specific workbook. By default, when a workbook is added to an Excel worksheet, three workbooks (sheet1, sheet2, and sheeet3) are added to the workbook ). These workbooks can be identified by numbers. In the following code line, connect to the first workbook and store the returned workbook object in a variable named $ sheet:

 
$ Sheet=$ Workbook. Worksheets. Item (1) ==> indicates that the first sheet is used.
 

Now I run $ excel. Visible = $ true again to check what Excel looks like (created the workbook and selected sheet1 ):

You can now write data to the workbook. The information in the Excel worksheet is stored in cells. Because cells are in workbooks, you can use the workbook objects stored in the $ sheet variable to access specific cells. The specific method is to use the reference workbookRows and columns. In an Excel worksheet, rows are numbers and columns are letters, which is confusing. HoweverWhen using an automated model, the rows and columns are numbers.. The first digit is the row, and the second digit is the column. You only need to assign values to specific cells to write data to cells:

 
$ Sheet. Cells. Item (1, 1) ="Test"==> Enter" test "in the first cell"
 

Then, run $ excel. Visible = $ true to see what Excel looks like:

Well,Unexpectedly, one disadvantage of Excel is that the workbook always creates three workbooks, which is a waste, because I only need the first sheet for this test. Fortunately, you can use automation to delete unnecessary workbooks: Use a worksheet set to connect to the third workbook and call the delete method. Perform the same operation to delete the second workbook:

$ Workbook. Worksheets. Item (3). Delete ()$ Workbook. Worksheets. Item (2). Delete ()
 

In this way, only sheet1 is left. Next, rename the remaining workbook. This is important because if you decide to use ActiveX Data Object (ADO) to query an Excel spreadsheet, the workbook name will be used in the connection string. Therefore, to make the code readable and intuitive, the workbook should have a logical name. To rename a workbook, you only need to assign a value to the name attribute of a specific workbook. The following code renames the first workbook as "processes ":

 
$ Workbook. Worksheets. Item (1). Name ="Processes"
 

Now, you need to connect to the renamed workbook. Use the item method of the worksheet object and specify the workbook name to it:

$ Sheet=$ Workbook. Worksheets. Item ("Processes")
 

Now let's look at the Excel format: 

The code for the above test process is 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 

Next we start to transplant the content in process to excel.
The first row of the workbook contains header information. Draw a border and make the attribute name bold. Because the data starts from the second row, the value 2 is assigned to the counter variable $ X:

 
$ X= 2
 

The following four lines of code create four enumeration types. The enumerated type is used to tell Excel which values can be entered into a specific option type. For example, xllinestyle enumeration is used to determine the type of the drawn line: Dual Line, dotted line, and so on. The enumerated values are described in detail on msdn. To make the code easier to read, create a quick alias for each Enumeration type that will be used. In fact, we will convert the string representing the enumeration name to [type].

 $ Linestyle = " Microsoft. Office. InterOP. Excel. xllinestyle  "   -  [Type]  $ Colorindex = "  Microsoft. Office. InterOP. Excel. xlcolorindex  "   -  [Type]  $ Borderweight = "  Microsoft. Office. InterOP. Excel. xlborderweight  "   - [Type]  $ Charttype = "  Microsoft. Office. InterOP. Excel. xlcharttype  "   - [Type]

Now you need to format the first line. Make the font bold, define the line as xldashdot, allow automatic color, and set the Border width to moderate width:

 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} 

After that, use the item method to select a cell, specify the coordinates of the row and column, and assign values to the first row. Next, use direct value assignment to write the column title:

 
$ 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 cell. Use the foreach statement to traverse the process information set. Define the variable $ process as a set enumerator (placeholder), and select to write the name and the workingsetsize attribute to the first and second columns respectively. $ X variables will be used here. Starting from the second row, while traversing the process set, increment the value of the $ X variable so that it always points to the current row in the set. Run the following code to sort all the data stored in the $ processes Process Information Collection:

Foreach($ Process In $ Processes){$ Sheet. Cells. Item ($ X, 1) =$ Process. Name$ Sheet. Cells. Item ($ X, 2) =$ Process. Workingsetsize$ X++}
 

After entering the Excel worksheet, you can adjust the column size so that the cells are the same size as the data stored in the workbook. You can create a range by specifying the column coordinates to use. However, you can also use only the usedrange attribute of the workbook. After the range object is created, select the entirecolumn attribute and use the autofit method to adjust the column size. Since this method always returns data, I send the result to the out-null cmdlet. This prevents the console from being filled with messy and useless information. The following code is used:

$ Range=$ Sheet. Usedrange$ Range. Entirecolumn. autofit () | out-Null
 

Now, all tasks have been completed. At this time, the workbook has the names of all processes and the memory working set, which is quite good. Let's take a look at the results:

The above result is a running process on my computer. I only select two fields for display.

The code for the entire script is 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  "   -  [Type]  $ Colorindex = " Microsoft. Office. InterOP. Excel. xlcolorindex  "   -  [Type]  $ Borderweight = "  Microsoft. Office. InterOP. Excel. xlborderweight  "   -  [Type]  $ Charttype = "  Microsoft. Office. InterOP. Excel. xlcharttype  "   - [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

 

This is a simple introduction to powershell writing to excel. In fact, during the running process, you will find that the process of filling in each field in the process is very slow, because it is filled in by a cell, rather than being copied to an Excel file, these will be introduced in subsequent articles.

Note: This article is based on a blog published by Microsoft and added to your understanding. In the future, we will briefly introduce other powershell applications for operating Excel & CSV.

If any error occurs, please correct it. Thank you!