PowerShell का उपयोग करके Excel फ़ाइल से डेटा पढ़ना और लिखना

इस लेख में हम दिखाएंगे कि PowerShell स्क्रिप्ट से सीधे Excel वर्कशीट से डेटा कैसे पढ़ें और लिखें। आप कंप्यूटर, सर्वर, इन्फ्रास्ट्रक्चर, Active Directory आदि पर इन्वेंट्री और विभिन्न रिपोर्ट जनरेट करने के लिए PowerShell के साथ Excel का उपयोग कर सकते हैं।

PowerShell का उपयोग करके Excel स्प्रेडशीट से डेटा कैसे पढ़ें

Excel शीट्स को PowerShell से एक अलग COM ऑब्जेक्ट (Component Object Model) के माध्यम से एक्सेस किया जा सकता है। इसके लिए कंप्यूटर पर Excel इंस्टॉल होना आवश्यक है।

Excel सेल में डेटा एक्सेस करने का तरीका दिखाने से पहले, Excel फ़ाइल में प्रस्तुति परतों की आर्किटेक्चर को समझना उचित है। नीचे दी गई आकृति Excel ऑब्जेक्ट मॉडल में 4 नेस्टेड प्रस्तुति परतों को दिखाती है:

• Application Layer — चल रहे Excel ऐप से संबंधित;
• WorkBook Layer — एक ही समय में कई वर्कबुक (Excel फ़ाइलें) खुली हो सकती हैं;
• WorkSheet Layer — प्रत्येक XLSX फ़ाइल में कई शीट हो सकती हैं;
• Range Layer — यहां आप विशिष्ट सेल या सेल रेंज में डेटा एक्सेस कर सकते हैं।

सरल उदाहरण: कर्मचारी सूची से डेटा पढ़ना

आइए PowerShell का उपयोग करके कर्मचारियों की सूची वाली Excel फ़ाइल में डेटा एक्सेस करने के एक सरल उदाहरण पर नज़र डालें।

सबसे पहले, COM ऑब्जेक्ट का उपयोग करके अपने कंप्यूटर पर Excel ऐप (एप्लिकेशन लेयर) चलाएं:

$ExcelObj = New-Object -comobject Excel.Application

कमांड चलाने के बाद, Excel आपके कंप्यूटर पर पृष्ठभूमि में लॉन्च होगा। Excel विंडो दिखाने के लिए, COM ऑब्जेक्ट के Visible गुण को बदलें:

$ExcelObj.visible=$true

आप सभी Excel ऑब्जेक्ट गुण इस तरह प्रदर्शित कर सकते हैं:

$ExcelObj| fl

फिर आप एक Excel फ़ाइल (वर्कबुक) खोल सकते हैं:

$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")

प्रत्येक Excel फ़ाइल में कई वर्कशीट हो सकती हैं। आइए वर्तमान Excel वर्कबुक में वर्कशीट की सूची प्रदर्शित करें:

$ExcelWorkBook.Sheets| fl Name, index

फिर आप इच्छित शीट खोल सकते हैं (इसके नाम या इंडेक्स से):

$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_users")

आप इस कमांड का उपयोग करके वर्तमान (सक्रिय) Excel वर्कशीट का नाम प्राप्त कर सकते हैं:

$ExcelWorkBook.ActiveSheet | fl Name, Index

फिर आप Excel वर्कशीट में सेल से मान प्राप्त कर सकते हैं। आप वर्तमान Excel वर्कशीट पर सेल मान प्राप्त करने के लिए विभिन्न विधियों का उपयोग कर सकते हैं: रेंज, सेल, कॉलम या पंक्ति का उपयोग करके। उसी सेल से डेटा प्राप्त करने के उदाहरण नीचे देखें:

$ExcelWorkSheet.Range("B4").Text
$ExcelWorkSheet.Range("B4:B4").Text
$ExcelWorkSheet.Range("B4","B4").Text
$ExcelWorkSheet.cells.Item(4, 2).text
$ExcelWorkSheet.cells.Item(4, 2).value2
$ExcelWorkSheet.Columns.Item(2).Rows.Item(4).Text
$ExcelWorkSheet.Rows.Item(4).Columns.Item(2).Text

PowerShell का उपयोग करके Active Directory उपयोगकर्ता जानकारी को Excel स्प्रेडशीट में निर्यात करना

आइए PowerShell से Excel डेटा एक्सेस करने का एक व्यावहारिक उदाहरण देखें। मान लीजिए, हम Excel फ़ाइल में प्रत्येक उपयोगकर्ता के लिए Active Directory से कुछ जानकारी प्राप्त करना चाहते हैं। उदाहरण के लिए, उनका फोन नंबर (telephoneNumber विशेषता), विभाग और ईमेल पता।

AD उपयोगकर्ता विशेषताओं के बारे में जानकारी प्राप्त करने के लिए, हम PowerShell Active Directory मॉड्यूल से Get-ADUser cmdlet का उपयोग करेंगे।

# PowerShell सत्र में Active Directory मॉड्यूल आयात करें
import-module activedirectory
# पहले Excel वर्कबुक खोलें:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_Users")
# XLSX वर्कशीट में भरी हुई पंक्तियों की संख्या प्राप्त करें
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# कॉलम 1 में सभी पंक्तियों के माध्यम से लूप करें जो पंक्ति 2 से शुरू होती हैं (इन सेल में डोमेन उपयोगकर्ता नाम होते हैं)
for($i=2;$i -le $rowcount;$i++){
 $ADusername=$ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
 # AD में उपयोगकर्ता विशेषताओं के मान प्राप्त करें
 $ADuserProp = Get-ADUser $ADusername -properties telephoneNumber,department,mail|select-object name,telephoneNumber,department,mail
 # AD से प्राप्त डेटा से सेल भरें
 $ExcelWorkSheet.Columns.Item(4).Rows.Item($i) = $ADuserProp.telephoneNumber
 $ExcelWorkSheet.Columns.Item(5).Rows.Item($i) = $ADuserProp.department
 $ExcelWorkSheet.Columns.Item(6).Rows.Item($i) = $ADuserProp.mail
}
# XLS फ़ाइल सहेजें और Excel बंद करें
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)

परिणामस्वरूप, Excel फ़ाइल में प्रत्येक उपयोगकर्ता के लिए AD जानकारी वाले कॉलम जोड़े गए हैं।

एक और उदाहरण: प्रिंट स्पूलर सेवा स्थिति रिपोर्ट

आइए PowerShell और Excel का उपयोग करके रिपोर्ट बनाने का एक और उदाहरण देखें। मान लीजिए, आप सभी डोमेन सर्वर्स पर Print Spooler सेवा स्थिति के बारे में Excel रिपोर्ट बनाना चाहते हैं।

आप Active Directory में सर्वर्स की सूची प्राप्त करने के लिए Get-ADComputer cmdlet का उपयोग कर सकते हैं, और सर्वर्स पर सेवा की स्थिति को दूरस्थ रूप से जांचने के लिए WinRM Invoke-Command cmdlet का उपयोग कर सकते हैं।

# एक Excel ऑब्जेक्ट बनाएं
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $true
# एक वर्कबुक जोड़ें
$ExcelWorkBook = $ExcelObj.Workbooks.Add()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
# वर्कशीट का नाम बदलें
$ExcelWorkSheet.Name = 'Spooler Service Status'
# तालिका का हेडर भरें
$ExcelWorkSheet.Cells.Item(1,1) = 'Server Name'
$ExcelWorkSheet.Cells.Item(1,2) = 'Service Name'
$ExcelWorkSheet.Cells.Item(1,3) = 'Service Status'
# तालिका हेडर को बोल्ड बनाएं, फ़ॉन्ट आकार और कॉलम चौड़ाई सेट करें
$ExcelWorkSheet.Rows.Item(1).Font.Bold = $true
$ExcelWorkSheet.Rows.Item(1).Font.size=15
$ExcelWorkSheet.Columns.Item(1).ColumnWidth=28
$ExcelWorkSheet.Columns.Item(2).ColumnWidth=28
$ExcelWorkSheet.Columns.Item(3).ColumnWidth=28
# डोमेन में सभी Windows सर्वर्स की सूची प्राप्त करें
$computers = (Get-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Name
$counter=2
# प्रत्येक कंप्यूटर से कनेक्ट करें और सेवा स्थिति प्राप्त करें
foreach ($computer in $computers) {
 $result = Invoke-Command -Computername $computer –ScriptBlock { Get-Service spooler | select Name, status }
 # सर्वर से प्राप्त डेटा से Excel सेल भरें
 $ExcelWorkSheet.Columns.Item(1).Rows.Item($counter) = $result.PSComputerName
 $ExcelWorkSheet.Columns.Item(2).Rows.Item($counter) = $result.Name
 $ExcelWorkSheet.Columns.Item(3).Rows.Item($counter) = $result.Status
 $counter++
}
# रिपोर्ट सहेजें और Excel बंद करें:
$ExcelWorkBook.SaveAs('C:\ps\Server_report.xlsx')
$ExcelWorkBook.close($true)

आप PowerShell का उपयोग विभिन्न परिदृश्यों में Excel एक्सेस करने के लिए कर सकते हैं। उदाहरण के लिए, आप आसान Active Directory रिपोर्ट बना सकते हैं या Excel से AD डेटा अपडेट करने के लिए PowerShell स्क्रिप्ट बना सकते हैं।