PowerShell操作Excel、CSV詳細(xì)介紹

2020-10-29 20:59:34

字體：大中小

供稿：網(wǎng)友

Powershell針對(duì)Excel的一些簡(jiǎn)單操作

　　稍微高級(jí)點(diǎn)的語(yǔ)言都會(huì)涉及到對(duì)COM對(duì)象的操作，作為微軟自己進(jìn)化的腳本語(yǔ)言，powershell有這個(gè)功能一點(diǎn)不稀奇，首先它使用 .NET Framework 提供的強(qiáng)大類(lèi)庫(kù)，很多特性和C#相似；其次，雖然作為腳本但它面向?qū)ο蟆Ｊ褂胮owershell來(lái)管理WMI和COM更簡(jiǎn)單。

　　用微軟官方的話(huà)概括powershell就是：

　　Windows PowerShell™ 是一種基于任務(wù)的命令行 shell 和腳本語(yǔ)言，專(zhuān)門(mén)用于管理系統(tǒng)。 Windows PowerShell™ 構(gòu)建于 .NET Framework 之上，能夠幫助 IT 專(zhuān)業(yè)人員和高級(jí)用戶(hù)控制和自動(dòng)管理 Windows 操作系統(tǒng)以及在 Windows 上運(yùn)行的應(yīng)用程序。

　　內(nèi)置 Windows PowerShell 命令（稱(chēng)為 cmdlet）可用于通過(guò)命令行管理企業(yè)中的計(jì)算機(jī)。使用 Windows PowerShell™ Provider，您可以像訪(fǎng)問(wèn)文件系統(tǒng)那樣輕松地訪(fǎng)問(wèn)數(shù)據(jù)存儲(chǔ)，如注冊(cè)表和證書(shū)存儲(chǔ)。此外，Windows PowerShell™ 還具有一個(gè)功能豐富的表達(dá)式分析程序和一種經(jīng)過(guò)充分開(kāi)發(fā)的腳本語(yǔ)言。

　　下面介紹powershell對(duì)excel的簡(jiǎn)單操作。

　　首先，我執(zhí)行一條命令來(lái)獲取系統(tǒng)當(dāng)前正在運(yùn)行的所有進(jìn)程：

Get-Process cmdlet顯示當(dāng)前本地進(jìn)程運(yùn)行狀態(tài)，包括處于打開(kāi)狀態(tài)的句柄數(shù)量、各種內(nèi)存占用視圖以及 CPU 使用率的快照。powershell和Linux shell一個(gè)很大的不同點(diǎn)就是大小寫(xiě)不敏感，無(wú)論是系統(tǒng)函數(shù)還是命令。這點(diǎn)可以減少犯錯(cuò)的概率。

　　您可能疑惑為什么講這個(gè)？對(duì)于系統(tǒng)管理員來(lái)說(shuō)，冗長(zhǎng)的數(shù)據(jù)背后隱藏著大量細(xì)節(jié)，get-process是獲取本地進(jìn)程運(yùn)行情況，當(dāng)然加上-computername參數(shù)后可以訪(fǎng)問(wèn)遠(yuǎn)程計(jì)算機(jī)，但目前這個(gè)對(duì)于網(wǎng)絡(luò)管理員來(lái)說(shuō)猶如海市蜃樓，所以不得不使用windows WMI和 Win32_Process WMI 類(lèi)來(lái)監(jiān)視遠(yuǎn)程系統(tǒng)并以實(shí)用方式顯示信息。如果認(rèn)為 Get-Process 的輸出內(nèi)容非常豐富，請(qǐng)?jiān)僮屑?xì)看看 Win32_Process 的輸出：

　很明顯，這個(gè)輸出很詳細(xì)，但是可讀性不強(qiáng)，管理員需要的是易于閱讀的內(nèi)存占用量報(bào)告。可以轉(zhuǎn)用Excel實(shí)現(xiàn)自動(dòng)操作。

　自動(dòng)化 Excel 有多難？實(shí)際上非常簡(jiǎn)單，因?yàn)?Microsoft 已創(chuàng)建了專(zhuān)門(mén)用于處理 Excel 的自動(dòng)化模型。程序 ID 是 Excel.Application，它是一個(gè) COM 對(duì)象。創(chuàng)建 Excel.Application 對(duì)象的實(shí)例時(shí)，Excel 會(huì)默認(rèn)啟動(dòng)并運(yùn)行，只是您看不見(jiàn)它。但是，可使用 visible 屬性讓 Excel 顯示出來(lái)。

以下代碼顯示了如何創(chuàng)建 Excel.Application 對(duì)象、查詢(xún) visible 屬性的狀態(tài)，然后將 visible 屬性設(shè)為 true：

復(fù)制代碼代碼如下:

PS > $excel = New-Object -ComObject Excel.Application   ==>創(chuàng)建一個(gè)excel應(yīng)用的COM對(duì)象
PS > $excel.Visible    ==>默認(rèn)新建的excel視圖不顯示，即沒(méi)有打開(kāi)
False
PS > $excel.Visible = $true    ==>看到下面的界面

　　然后，您會(huì)看到一個(gè)非常奇怪的 Excel 視圖，它看起來(lái)就像 Excel 應(yīng)用程序的一個(gè)外殼（如下圖）。沒(méi)有工作簿、沒(méi)有電子表格，―個(gè)是光禿禿的 Excel。

當(dāng)然，這個(gè)肯定是無(wú)法使用的，但是它是建立工作簿的前提，它是存放工作簿的容器。現(xiàn)在我們需要將一個(gè)工作簿添加到應(yīng)用程序中。為此，需要借助工作簿對(duì)象的 add 方法。工作簿對(duì)象是從主 Excel.Application 對(duì)象進(jìn)行訪(fǎng)問(wèn)的，正如您看到的，我們將工作簿對(duì)象存儲(chǔ)在一個(gè)名為 $workbook 的變量中：

復(fù)制代碼代碼如下:

$workbook = $excel.Workbooks.add()   ==>現(xiàn)在添加了一個(gè)空的工作簿

現(xiàn)在，需連接特定的電子表格。默認(rèn)情況下，工作簿添加到 Excel 時(shí)，會(huì)向其中添加三個(gè)電子表格（sheet1,sheet2,sheeet3）。這些電子表格可通過(guò)數(shù)字進(jìn)行標(biāo)識(shí)。在以下代碼行中，連接第一個(gè)電子表格并將返回的電子表格對(duì)象存儲(chǔ)在名為 $sheet 的變量中：

復(fù)制代碼代碼如下:

$sheet = $workbook.worksheets.Item(1)   ==>表示我使用的是第一個(gè)sheet

現(xiàn)在我再運(yùn)行一次$excel.Visible = $true來(lái)查看excel變成什么樣了（建好了工作簿并且選擇了sheet1）：

現(xiàn)在，可將數(shù)據(jù)寫(xiě)入該電子表格。Excel 電子表格中的信息存儲(chǔ)在單元格中。由于單元格位于電子表格之中，所以可使用 $sheet 變量中存儲(chǔ)的電子表格對(duì)象訪(fǎng)問(wèn)特定的單元格。具體方法是使用指代電子表格中行和列的數(shù)字。在 Excel 電子表格中，行是數(shù)字而列是字母，這一點(diǎn)有些讓人迷惑。但使用自動(dòng)化模型時(shí)，行和列均為數(shù)字。第一個(gè)數(shù)字是行，第二個(gè)數(shù)字是列。只需對(duì)特定單元格進(jìn)行賦值即可寫(xiě)入單元格：

復(fù)制代碼代碼如下:

$sheet.cells.item(1,1) = "Test"   ==>我在第一個(gè)單元格填入“Test”

這時(shí)，您再運(yùn)行$excel.Visible = $true，查看excel變成啥樣了：

嗯，意料之中，Excel 的一個(gè)不足是工作簿始終會(huì)創(chuàng)建三個(gè)電子表格，這有點(diǎn)浪費(fèi)，因?yàn)槲椰F(xiàn)在這個(gè)測(cè)試只需要第一個(gè)sheet。幸運(yùn)的是，可利用自動(dòng)化刪除多余的電子表格：使用工作表集合連接第三個(gè)電子表格并調(diào)用 delete 方法。執(zhí)行相同的操作刪除第二個(gè)電子表格：　

復(fù)制代碼代碼如下:

$workbook.workSheets.item(3).delete()
$workbook.WorkSheets.item(2).delete()

　　這樣只剩下sheet1了，接下來(lái)，重命名剩余的電子表格。這一點(diǎn)非常重要，因?yàn)槿绻麤Q定使用 ActiveX 數(shù)據(jù)對(duì)象 (ADO) 來(lái)查詢(xún) Excel 電子表格，將在連接字符串中使用此電子表格名稱(chēng)。因此，要使代碼易讀且直觀(guān)，電子表格應(yīng)具有邏輯名稱(chēng)。要重命名電子表格，只需向特定電子表格的 name 屬性賦一個(gè)值即可。以下代碼將第一個(gè)電子表格重命名為 "Processes"：　

復(fù)制代碼代碼如下:

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

　　現(xiàn)在，需連接重命名后的電子表格。使用工作表對(duì)象的 Item 方法并將電子表格的名稱(chēng)指定給它：　

復(fù)制代碼代碼如下:

$sheet = $workbook.WorkSheets.Item("Processes")

　這時(shí)我們?cè)俨榭磂xcel的樣子：

以上測(cè)試過(guò)程的代碼如下：

復(fù)制代碼代碼如下:

$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

下面我們開(kāi)始將process中的內(nèi)容移植到excel中。

　　電子表格的第一行將包含標(biāo)頭信息。將繪制邊框并使屬性名稱(chēng)顯示為粗體。由于數(shù)據(jù)將從第二行開(kāi)始，因此將值 2 賦給計(jì)數(shù)器變量 $x:

復(fù)制代碼代碼如下:

$x=2

　　　接下來(lái)的四行代碼創(chuàng)建四個(gè)枚舉類(lèi)型。枚舉類(lèi)型用于告訴 Excel 允許將哪些值填入特定的選項(xiàng)類(lèi)型。例如，xlLineStyle 枚舉用于確定所繪制線(xiàn)條的類(lèi)型：雙線(xiàn)條、虛線(xiàn)等。MSDN 上詳細(xì)介紹了這些枚舉值。
為使代碼更易于閱讀，針對(duì)將使用的每個(gè)枚舉類(lèi)型創(chuàng)建一個(gè)快捷別名。實(shí)際上，我們將把代表枚舉名稱(chēng)的字符串轉(zhuǎn)換成 [type]。　

復(fù)制代碼代碼如下:

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

　　現(xiàn)在需格式化第一行。使字體為粗體，將線(xiàn)條定義為 xlDashDot，允許自動(dòng)指定顏色，并將邊框?qū)挾仍O(shè)為中等粗細(xì)：

復(fù)制代碼代碼如下:

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
}

完成后，通過(guò)使用 item 方法選擇單元格并指定行和列的坐標(biāo)，向第一行賦值。接下來(lái)，使用直接賦值寫(xiě)入列標(biāo)題：

復(fù)制代碼代碼如下:

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

　　現(xiàn)在，需將 WMI 查詢(xún)產(chǎn)生的 $processes 變量中存儲(chǔ)的進(jìn)程信息放入適當(dāng)?shù)膯卧裰小Ｊ褂?foreach 語(yǔ)句遍歷進(jìn)程信息集合。將變量 $process 定義為集合枚舉器（占位符），并選擇將名稱(chēng)和 workingSetSize 屬性分別寫(xiě)入第一列和第二列。$x 變量將在此發(fā)揮作用。從第二行開(kāi)始，在遍歷進(jìn)程集合的同時(shí)，遞增 $x 變量的值以便它始終指向集合中的當(dāng)前行。通過(guò)以下代碼，即可對(duì) $processes 進(jìn)程信息集合中存儲(chǔ)的所有數(shù)據(jù)進(jìn)行整理分類(lèi)：　

復(fù)制代碼代碼如下:

foreach($process in $processes)
{
 $sheet.cells.item($x, 1) = $process.name
 $sheet.cells.item($x,2) = $process.workingSetSize
 $x++
} 

　　填寫(xiě)完 Excel 電子表格后，可以調(diào)整列的大小以使單元格與其中所存儲(chǔ)數(shù)據(jù)的尺寸相同。為此，可通過(guò)指定要使用的列坐標(biāo)來(lái)創(chuàng)建一個(gè)范圍；但是，也可以只使用電子表格的 usedRange 屬性。創(chuàng)建完范圍對(duì)象后，選擇 EntireColumn 屬性并使用 AutoFit 方法調(diào)整列的大小。由于該方法始終會(huì)返回?cái)?shù)據(jù)，我將結(jié)果傳送給 Out-Null cmdlet。從而避免控制臺(tái)上布滿(mǎn)了雜亂無(wú)用的信息。下面是所用的代碼：

復(fù)制代碼代碼如下:

$range = $sheet.usedRange
$range.EntireColumn.AutoFit() | out-null

　　至此，已完成所有任務(wù)此時(shí)的電子表格具有所有進(jìn)程的名稱(chēng)和內(nèi)存工作集，相當(dāng)不錯(cuò)。來(lái)看看完成后的成果：

以上結(jié)果是我電腦里正在運(yùn)行的進(jìn)程。我只選擇了兩個(gè)字段顯示。

關(guān)于整個(gè)腳本的代碼，如下：

復(fù)制代碼代碼如下:

$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

關(guān)于powershell寫(xiě)入excel的簡(jiǎn)單知識(shí)先介紹到這，其實(shí)，在運(yùn)行的過(guò)程的中您會(huì)發(fā)現(xiàn)，進(jìn)程中的每個(gè)字段填入excel的過(guò)程是非常緩慢的，因?yàn)樗且粋€(gè)cell一個(gè)cell填入的，而不是一下子拷貝到excel中的，這些將在之后的隨筆中介紹。

　　備注：這篇隨筆是參考微軟官方的一片博客并加入自己的理解寫(xiě)的，后續(xù)還將會(huì)簡(jiǎn)單介紹powershell操作excel&CSV的其它應(yīng)用。

　　如有錯(cuò)誤，歡迎指正，謝謝！

上一篇：Powershell中顯示隱藏文件的方法

下一篇：Python中調(diào)用PowerShell、遠(yuǎn)程執(zhí)行bat文件實(shí)例