पावरशेल के साथ एक्सेल फाइल में डेटा पढ़ें और लिखें

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

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

• अनुप्रयोग परत - चल रहे एक्सेल ऐप से संबंधित है;
• कार्यपुस्तिका परत - एकाधिक कार्यपुस्तिकाएं (एक्सेल फ़ाइलें) एक ही समय में खुली हो सकती हैं;
• कार्यपत्रक परत - प्रत्येक XLSX फ़ाइल में कई पत्रक हो सकते हैं;
• श्रेणी परत - यहां आप विशिष्ट सेल या सेल रेंज में डेटा एक्सेस कर सकते हैं।

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

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

सबसे पहले, COM ऑब्जेक्ट का उपयोग करके अपने कंप्यूटर पर एक्सेल ऐप (एप्लिकेशन लेयर) चलाएं:
$ExcelObj = New-Object -comobject Excel.Application

कमांड चलाने के बाद आपके कंप्यूटर पर बैकग्राउंड में एक्सेल लॉन्च हो जाएगा। एक्सेल विंडो दिखाने के लिए, दृश्यमान . बदलें COM ऑब्जेक्ट की संपत्ति:

$ExcelObj.visible=$true

$ExcelObj| fl

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

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

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

$ExcelWorkBook.Sheets| fl Name, index

फिर आप एक शीट खोल सकते हैं जो आप चाहते हैं (उसके नाम या अनुक्रमणिका द्वारा):

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

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

$ExcelWorkBook.ActiveSheet | fl Name, Index

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

$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 का उपयोग करके Excel स्प्रेडशीट में निर्यात करना

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

# Importing Active Directory module into PowerShell session
import-module activedirectory
# Open an Excel workbook first:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_Users")
# Get the number of filled in rows in the XLSX worksheet
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# Loop through all rows in Column 1 starting from Row 2 (these cells contain the domain usernames)
for($i=2;$i -le $rowcount;$i++){
$ADusername=$ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
# Get the values of user attributes in AD
$ADuserProp = Get-ADUser $ADusername -properties telephoneNumber,department,mail|select-object name,telephoneNumber,department,mail
# Fill in the cells with the data received from 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
}
# Save the XLS file and close Excel
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)

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

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

# Create an Excel object
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $true
# Add a workbook
$ExcelWorkBook = $ExcelObj.Workbooks.Add()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
# Rename a worksheet
$ExcelWorkSheet.Name = 'Spooler Service Status'
# Fill in the head of the table
$ExcelWorkSheet.Cells.Item(1,1) = 'Server Name'
$ExcelWorkSheet.Cells.Item(1,2) = 'Service Name'
$ExcelWorkSheet.Cells.Item(1,3) = 'Service Status'
# Make the table head bold, set the font size and the column width
$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
# Get the list of all Windows Servers in the domain
$computers = (Get-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Name
$counter=2
# Connect to each computer and get the service status
foreach ($computer in $computers) {
$result = Invoke-Command -Computername $computer –ScriptBlock { Get-Service spooler | select Name, status }
# Fill in Excel cells with the data obtained from the server
$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++
}
# Save the report and close Excel:
$ExcelWorkBook.SaveAs('C:\ps\Server_report.xlsx')
$ExcelWorkBook.close($true)

आप विभिन्न परिदृश्यों में एक्सेल तक पहुँचने के लिए पॉवरशेल का उपयोग कर सकते हैं। उदाहरण के लिए, आप एक्सेल से एडी डेटा अपडेट करने के लिए आसान सक्रिय निर्देशिका रिपोर्ट बना सकते हैं या पावरशेल स्क्रिप्ट बना सकते हैं।