פוורשל ואקסל: לנצל את הכוח של ImportExcel

Microsoft Excel היא אחת מהכלים הנפוצים האוניברסליים שאנחנו רובנו לא יכולים להתחמק מהם, גם אם ניסינו. רבים ממקצועני המחשבים משתמשים ב-Excel כמסד נתונים קטן המאחסן כמויות עצומות של מידע בשונות תהליכי אוטומציה. מה התרחש ב- PowerShell ו- Excel?

גיליונות ה-Excel תמיד היו מפורסמים בקרב הסקריפטרים והמתכנתים. להבדיל מגרסאו הפחות מודגשת (ופשוטה יותר) של קובץ CSV, גיליונות ה-Excel אינם רק קבצי טקסט פשוטים. גיליונות ה-Excel דורשים פונקציות PowerShell מורכבות של רכיבי האובייקטים מחשבים (COM) ולכן הייתה חובה להתקין את Excel. אבל כבר לא עוד.

באושר, איש קהילת PowerShell עירבובי־תוכנה חכם, דאג פינקה, יצר מודול PowerShell בשם ImportExcel עבורנו, המתוך אותה העצמות. המודול ImportExcel מספק ממשק אבסטרקטי המסתיר את כל הרכיבים המורכבים הללו. הוא מקל על ניהול גיליונות Excel ואפשרות יישות סקריפטינג באמצעות PowerShell!

במאמר זה, נסקור את מה שניתן לעשות עם PowerShell ו-Excel באמצעות המודול ImportExcel וכמה תרחישים פופולריים.

דרישות חובה

כאשר מפעילים את מודול ImportExcel על מערכת Windows, אין צורך בתלויות נפרדות. אולם, אם אתה עובד על macOS, תצטרך להתקין את החבילה mono-libgdiplus באמצעות brew install mono-libgdiplus. כל הדוגמאות במאמר זה יובנו באמצעות macOS אך כל הדוגמאות אמורות לעבוד בצורה חוצת-פלטפורמה.

אם אתה משתמש ב macOS, וודא שתפעיל מחדש את הסשן של PowerShell לפני שתמשיך.

התקנת מודול ImportExcel

התחל בהורדה והתקנה של המודול דרך גלריית PowerShell על ידי הרצת Install-Module ImportExcel -Scope CurrentUser. לאחר מספר רגעים, תהיה לך אפשרות להתחיל.

שימוש ב-PowerShell וב-Excel לייצוא לגליון עבודה

יתכן שאתה מכיר את ה-cmdlets הסטנדרטיים של PowerShell Export-Csv ו־Import-Csv. cmdlets אלו מאפשרים לך לקרוא ולייצא אובייקטים של PowerShell לקבצי CSV. למרבה המזל, אין Export-Excel ו־Import-Excel cmdlets. אך באמצעות מודול ImportExcel, תוכל לבנות את הפונקציונליות שלך.

נושאים קשורים: ניהול קבצי CSV ב-PowerShell עם Import-Csv

אחת הבקשות הנפוצות ביותר של מנהל מערכות היא לייצא אובייקטים של PowerShell לגליון עבודה של Excel. באמצעות ה-cmdlet Export-Excel במודול ImportExcel, תוכל בקלות להפוך את זה למציאות.

לדוגמה, אולי תצטרך למצוא כמה תהליכים שרצים על המחשב המקומי שלך ולהוציא אותם לגיליון עבודה של Excel.

פקודת ה- Export-Excel מקבלת כל אובייקט בדיוק בדיוק כמו ש- Export-Csv עושה. תוכל להפנות כל סוג של אובייקט לפקודה זו.

כדי למצוא תהליכים שרצים על מערכת באמצעות PowerShell, השתמש בפקודת ה- Get-Process שמחזירה כל תהליך שרץ ומידע שונה על כל תהליך. כדי לייצא את המידע הזה ל- Excel, השתמש בפקודת ה- Export-Excel וספק את נתיב הקובץ לגיליון ה- Excel שיהיה נוצר. תוכל לראות דוגמה לפקודה ולצילום מסך של קובץ ה- Excel שנוצר למטה.

Get-Process | Export-Excel -Path './processes.xlsx'

ברכות! כעת ייצאת את כל המידע בדיוק כמו Export-Csv אך, לעומת זאת Export-Csv, ניתן להפוך את הנתונים להרבה יותר מרשימים. נקבע ששם גליון העבודה יהיה תהליכים, המידע יהיה בתוך טבלה והשורות יהיו בגודל אוטומטי.

על ידי שימוש במפרמטר המתג AutoSize כדי להתאים אוטומטית את גודל כל השורות, TableName כדי לציין את שם הטבלה שיכלול את כל הנתונים והמפרמטר WorksheetName בשם Processes, ניתן לראות בתמונה למטה איך ניתן לבנות.

Get-Process | Export-Excel -Path './processes.xlsx' -AutoSize -TableName Processes -WorksheetName Proccesses

הפקודה Export-Excel כוללת מספר פרמטרים שניתן להשתמש בהם ליצירת חוברות עבודה שונות. לרשימה מלאה של כל האפשרויות של Export-Excel, הריץ את Get-Help Export-Excel.

שימוש ב-PowerShell לייבוא ל-Excel

אולי יש לך מידע שיוצא לקובץ בשם processes.xlsx בחלק הקודם. אולי עכשיו יש צורך להעביר את הקובץ הזה למחשב אחר ולייבא/לקרוא את המידע הזה עם PowerShell ו-Excel. אין בעיה. יש לך את Import-Excel בתפקידך.

בשימוש הבסיסי ביותר, אתה צריך לספק רק את הנתיב למסמך/חוברת ה-Excel באמצעות מפרמטר Path כפי שמוצג למטה. יתבצע קריאה לגיליון הראשון, במקרה זה גיליון Processes, ויוחזרו אובייקטים של PowerShell.

Import-Excel -Path './processes.xlsx'

אולי יש לך מספר גדול של גליונות בחוברת ה-Excel? ניתן לקרוא גיליון מסויים באמצעות מפרמטר WorksheetName.

Import-Excel -Path './processes.xlsx' -WorkSheetname SecondWorksheet

האם יש צורך לקרוא רק עמודות מסוימות מגיליון ה-Excel? השתמש במפרמטר HeaderName כדי לציין רק את העמודות שברצונך לקרוא.

Import-Excel -Path './processes.xlsx' -WorkSheetname Processes -HeaderName 'CPU','Handle'

הפקודה Import-Excel מכילה פרמטרים נוספים שאפשר להשתמש בהם כדי לקרוא חוברות Excel שונות. לקבלת מידע מלא על כל האפשרויות של Import-Excel, הרץ Get-Help Import-Excel.

שימוש ב-PowerShell כדי לקבל (וגם לשנות) ערכי תאים ב-Excel

כעת אתה יודע כיצד לקרוא לוח עבודה שלם באמצעות PowerShell ו-Excel, אך מה קורה אם תרצה רק ערך יחיד מתא מסוים? בעצם, אתה יכול להשתמש ב-Import-Excel ולסנן את הערך שאתה צריך עם Where-Object, אך זה לא יהיה יעיל מדי.

במקום זאת, באמצעות הפקודה Open-ExcelPackage, תוכל "להמיר" חוברת עבודה של Excel לאובייקט של PowerShell שיתאים לקריאה ולעריכה. כדי למצוא ערך בתא, תפתח תחילה את חוברת העבודה של Excel כדי להביא אותה לזיכרון.

$excel = Open-ExcelPackage -Path './processes.xlsx'

הפקודה Open-ExcelPackage דומה לשימוש ב-New-Object -comobject excel.application כאשר אתה עובד ישירות עם אובייקטי COM.

לאחר מכן, בחר בגליון העבודה בתוך החוברת.

$worksheet = $excel.Workbook.Worksheets['Processes']

התהליך הזה דומה לאובייקט COM באמצעותו אפשר לפתוח חוברות עם excel.workbooks.open.

כשיש לך את הגליון העבודה שוייך למשתנה, אתה יכול כעת לחקוף אל עמודות, שורות ותאים יחידים. אולי אתה צריך למצוא את כל ערכי התאים בשורה A1. פשוט עליך להתייחס למאפיין Cells ולספק אינדקס של A1 כמו שמוצג למטה.

$worksheet.Cells['A1'].Value

ניתן גם לשנות את ערכם של תאים בגליון עבודה על ידי הקצאה של ערך שונה, לדוגמה: $worksheet.Cells['A1'] = 'ערך שונה'

פעם אחת בזיכרון, חשוב לשחרר את חבילת ה-Excel באמצעות הפקודה Close-ExcelPackage.

Close-ExcelPackage $excel

המרת גליונות עבודה לקבצי CSV בעזרת PowerShell ו-Excel

ברגע שיש לך את תוכן גליון העבודה באמצעות אובייקטי PowerShell, "המרת" גליונות עבודה של Excel ל-CSV דורשת פשוט לשלוח את אותם אובייקטים לפקודת Export-Csv.

קשור:Export-Csv: המרת אובייקטים לקבצי CSV

באמצעות גליון העבודה processes.xlsx שנוצר בשלב קודם, קרא את הגליון הראשון שמביא את כל הנתונים לאובייקטים של PowerShell, ואז ייצא את אותם אובייקטים ל-CSV באמצעות הפקודה הבאה.

Import-Excel './processes.xlsx' | Export-Csv -Path './processes.csv' -NoTypeInformation

אם תפתח את קובץ ה-CSV התוצאה כעת, תראה את אותם הנתונים בתוך גליון העבודה Processes (בדוגמה זו).

המרת מספר גליונות עבודה

אם יש לך חוברת Excel עם מספר גליונות עבודה, תוכל גם ליצור קובץ CSV עבור כל גליון עבודה. כדי לעשות זאת, תוכל למצוא את כל הגליונות בחוברת באמצעות הפקודה Get-ExcelSheetInfo. פעם שיש לך את שמות הגליונות, תוכל להעביר את השמות האלה לפרמטר WorksheetName וגם להשתמש בשם הגליון כשם של קובץ ה-CSV.

למטה תמצא את הקוד הדוגמא הדרוש באמצעות PowerShell ו-Excel.

## למצוא כל גיליון בחוברת
$sheets = (Get-ExcelSheetInfo -Path './processes.xlsx').Name
## לקרוא כל גיליון וליצור קובץ CSV עם אותו השם
foreach ($sheet in $sheets) {
	Import-Excel -WorksheetName $sheet -Path './processes.xlsx' | Export-Csv "./$sheet.csv" -NoTypeInformation
}

מסקנה

באמצעות PowerShell ו-Excel, ניתן לייבא, לייצא, ולנהל נתונים בחוברות Excel בדיוק כמו שתעשה עם קבצי CSV מבלי להתקין את Excel!

במאמר זה, למדת יסודות של קריאה וכתיבת נתונים בחוברת Excel אך זה רק פוגע בפני השטח. באמצעות PowerShell ומודול ה-ImportExcel, תוכל ליצור תרשימים, טבלאות פיבוט, ולנצל תכונות חזקות נוספות של Excel!