PowerShell query Excel Records

The part-time of the beans is Australia purchase (provide direct mail or domestic supply in Australia, need to contact 650) this.width=650; "src=" Http://img.baidu.com/hi/jx2/j_0057.gif "alt=" j_0057.gif "/>", many times need to be customer service to send customers a courier number, these are the warehouse packing personnel daily statistics in Excel sent to me.

For example, I have a lot of xlsx Express number documents, the format is probably like this

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/82/0B/wKioL1dJGkKz1zlwAABPc2zGISE993.png "title=" 4.PNG " alt= "Wkiol1djgkkz1zlwaabpc2zgise993.png"/>

Sometimes, in order to find an order number, you need to check the QQ chat record to find the corresponding invoice record and then also have to find the corresponding Excel table, very troublesome, often in order to query a ticket need to toss 2 minutes. In order to solve the problem of this finding, the beans were written in PowerShell with a simple script for processing.

First, the good news is that PS5 already has a lot of ready-made Excel modules, so you don't have to configure Excel with ComObject anymore. After all, ComObject belongs to an outdated technology, and PowerShell is based on. NET, and Microsoft itself does not recommend any way to automate Office, and it does not support the various bugs that arise.

I'm using the following module, and his principle is called. NET DLL files, so efficiency and stability are far more than the ComObject object.

https://www.powershellgallery.com/packages/ImportExcel/2.2.4

POWERSHELL5 can be downloaded directly inside the installation, after the installation can be directly called.

Install-module-name Importexcel

The basic idea is simple, recursively query the directory where xlsx files are located, import all the files, and then filter according to the conditions. Very simple script but it saved me a lot of time.

The script is as follows

Function get-deliveryinfo{    [cmdletbinding ()]        Param     (        # param1 help  description                 [string]         $user,         [string] $path = "C:\users\yli\Documents\ Outbound record \xlsx\*.xlsx",         [string] $address = "*"      )      begin{}      process{                $result =get-childitem  $path  -recurse| import-excel| where-object {($_. Name  -like  "* $user *")  -and  ($_. Address  -like  "* $address *")}                     }     end{            $result       }}

See, for example, a client surnamed Chen from Guangxi.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/82/0B/wKioL1dJGUORdF50AAD0o4UR8VA505.png "title=" 3.PNG " alt= "Wkiol1djguordf50aad0o4ur8va505.png"/>

Success!

In order to facilitate the use of non-it people such as daughter-in-law, I made an image interface

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/82/0B/wKioL1dJH6bySN8NAAEDewkp8ms055.png "title=" 5.PNG " alt= "Wkiol1djh6bysn8naaedewkp8ms055.png"/>

It's nice to be able to use simple scripts to solve real problems in your life. 650) this.width=650; "src=" Http://img.baidu.com/hi/jx2/j_0028.gif "alt=" J_0028.gif "/>

This article is from the "Mapo Tofu" blog, please be sure to keep this source http://beanxyz.blog.51cto.com/5570417/1784014

PowerShell query Excel Records