Excel의 파워쉘: ImportExcel의 기능 활용

Microsoft Excel는 대부분의 사람들이 피할 수 없는 보편적인 도구 중 하나입니다. 많은 IT 전문가들은 Excel을 작은 데이터베이스로 사용하여 다양한 자동화 루틴에서 대량의 데이터를 저장합니다. 자동화 및 Excel의 가장 좋은 시나리오는 무엇인가요? PowerShell과 Excel입니다!

Excel 스프레드시트는 항상 스크립트 및 자동화하기가 어려웠습니다. 덜 기능적이고 (더 단순한) CSV 파일 대비파일과 달리, Excel 워크북은 그냥 단순한 텍스트 파일이 아닙니다. Excel 워크북은 복잡한 Component Object Model (COM) 객체를 조작하기 위해 PowerShell이 설치되어 있어야 했습니다. 이제는 그렇지 않습니다.

다행히도, 알찬 PowerShell 커뮤니티 회원인 Doug Finke가 일반인들을 위해 PowerShell 모듈인 ImportExcel을 만들어 냈습니다. ImportExcel 모듈은 모든 복잡성을 추상화합니다. 이를 통해 Excel 워크북을 쉽게 관리하고 PowerShell 스크립팅에 집중할 수 있습니다!

이 문서에서는 ImportExcel 모듈과 몇 가지 인기있는 사용 사례를 사용하여 PowerShell과 Excel로 수행할 수 있는 작업을 탐색해보겠습니다.

전제 조건

Windows 시스템에서 ImportExcel 모듈을 실행할 때는 별도의 종속성이 필요하지 않습니다. 그러나 macOS에서 작업하는 경우 mono-libgdiplus 패키지를 설치해야합니다. brew install mono-libgdiplus를 사용하여 설치하십시오. 이 문서의 모든 예제는 macOS를 사용하여 작성되었지만 모든 예제는 크로스 플랫폼에서 작동해야합니다.

macOS를 사용하는 경우 계속하기 전에 PowerShell 세션을 다시 시작하십시오.

ImportExcel 모듈 설치

Install-Module ImportExcel -Scope CurrentUser를 실행하여 PowerShell 갤러리에서 모듈을 다운로드하고 설치하십시오. 잠시 후에 사용할 준비가됩니다.

PowerShell 및 Excel을 사용하여 워크시트로 내보내기

일반적인 PowerShell cmdlet인 Export-Csv와 Import-Csv에 익숙할 수 있습니다. 이러한 cmdlet을 사용하여 PowerShell 개체를 CSV 파일로 읽고 내보낼 수 있습니다. 불행히도 Export-Excel 및 Import-Excel cmdlet은 제공되지 않습니다. 그러나 ImportExcel 모듈을 사용하여 직접 기능을 작성할 수 있습니다.

관련 문서: Import-Csv를 사용하여 PowerShell에서 CSV 파일 관리

시스템 관리자가 가장 흔히 요청하는 작업 중 하나는 PowerShell 개체를 Excel 워크시트로 내보내는 것입니다. ImportExcel 모듈의 Export-Excel cmdlet을 사용하면 쉽게 수행할 수 있습니다.

예를 들어, 로컬 컴퓨터에서 실행 중인 몇 가지 프로세스를 찾아서 Excel 워크북에 넣어야 할 수도 있습니다.

Export-Excel cmdlet은 Export-Csv와 같이 어떤 객체든지 받아들입니다. 이 cmdlet에 어떠한 종류의 객체든지 파이프할 수 있습니다.

PowerShell을 사용하여 시스템에서 실행 중인 프로세스를 찾으려면 Get-Process cmdlet을 사용하면 됩니다. 이 cmdlet은 각 실행 중인 프로세스와 각 프로세스에 대한 다양한 정보를 반환합니다. 이 정보를 Excel로 내보내려면, 생성될 Excel 워크북의 파일 경로를 제공하는 Export-Excel cmdlet을 사용하면 됩니다. 아래에 명령어 예제와 Excel 파일 스크린샷을 볼 수 있습니다.

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

축하합니다! 이제 Export-Csv와 마찬가지로 모든 정보를 내보냈습니다. 하지만, Export-Csv와는 달리 이 데이터를 더 화려하게 만들 수 있습니다. 워크시트 이름을 Processes로 지정하고, 데이터를 테이블로 만들고, 행의 크기를 자동 조정해 보겠습니다.

AutoSize 스위치 매개변수를 사용하여 모든 행의 크기를 자동 조정하고, TableName을 사용하여 모든 데이터가 포함된 테이블의 이름을 지정하고, WorksheetName 매개변수를 사용하여 Processes라는 매개변수 이름을 지정하면 아래 스크린샷에서 구축할 수 있는 내용을 확인할 수 있습니다.

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

Export-Excel cmdlet에는 Excel 워크북을 생성하기 위해 사용할 수 있는 다양한 매개변수가 있습니다. Export-Excel이 수행할 수 있는 모든 기능에 대한 자세한 정보는 Get-Help Export-Excel 명령을 실행하면 확인할 수 있습니다.

PowerShell을 사용하여 Excel로 가져오기

이전 섹션에서 processes.xlsx라는 파일에 정보를 내보냈습니다. 이제 이 파일을 다른 컴퓨터로 이동하여 PowerShell 및 Excel로 이 정보를 가져오고 읽어야 할 수도 있습니다. 문제 없습니다. Import-Excel을 사용할 수 있습니다.

가장 기본적인 사용법은 Path 매개변수를 사용하여 Excel 문서/워크북의 경로만 지정하면 됩니다. 아래에 표시된 대로 첫 번째 워크시트, 이 경우 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 cmdlet에는 다양한 매개변수가 있습니다. 이를 사용하여 모든 종류의 Excel 워크북을 읽을 수 있습니다. Import-Excel이 할 수 있는 모든 작업에 대한 전체 설명서를 보려면 Get-Help Import-Excel을 실행하세요.

PowerShell을 사용하여 Excel 셀 값 가져오기 (및 설정하기)

이제 PowerShell과 Excel을 사용하여 전체 워크시트를 읽는 방법을 알게 되었지만, 하나의 셀 값만 필요한 경우 어떻게 해야 할까요? 기술적으로는 Import-Excel을 사용하고 Where-Object를 사용하여 필요한 값을 필터링할 수는 있지만, 그렇게 하는 것은 효율적이지 않습니다.

대신, Open-ExcelPackage cmdlet을 사용하여 Excel 워크북을 PowerShell 객체로 “변환”할 수 있으며, 이후에 읽고 조작할 수 있습니다. 셀 값 찾기 위해서는 먼저 Excel 워크북을 메모리로 가져와야 합니다.

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

Open-ExcelPackage은 COM 객체를 직접 작업하는 경우 New-Object -comobject excel.application을 사용하는 것과 유사합니다.

다음으로, 워크북 내의 워크시트를 선택합니다.

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

이 과정은 excel.workbooks.open을 사용하여 COM 객체로 워크북을 열던 방식과 유사합니다.

워크시트를 변수에 할당한 후에는 개별 행, 열 및 셀로 자세히 들어갈 수 있습니다. 예를 들어, A1 행의 모든 셀 값을 찾아야 할 수도 있습니다. 아래와 같이 A1의 인덱스를 제공하는 Cells 속성을 참조하기만 하면 됩니다.

$worksheet.Cells['A1'].Value

워크시트의 셀 값을 변경하는 데는 다른 값을 할당하는 것도 가능합니다. 예를 들어 $worksheet.Cells['A1'] = '다른값'

메모리에 있을 때, Excel 패키지를 Close-ExcelPackage cmdlet을 사용하여 해제하는 것이 중요합니다.

Close-ExcelPackage $excel

PowerShell 및 Excel을 사용하여 워크시트를 CSV 파일로 변환하기

PowerShell 개체를 통해 Excel 워크시트의 내용을 보유한 경우, Excel 워크시트를 CSV로 “변환”하는 것은 해당 개체를 Export-Csv cmdlet에 보내기만 하면 됩니다.

관련 정보:Export-Csv: 개체를 CSV 파일로 변환하기

이전에 생성된 processes.xlsx 워크북을 사용하여 PowerShell 개체로 데이터를 가져오는 첫 번째 워크시트를 읽고, 해당 개체를 다음 명령을 사용하여 CSV로 내보냅니다.

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

이제 결과로 생성된 CSV 파일을 열면 해당 예제에서와 같이 Processes 워크시트 내에 동일한 데이터가 있는 것을 확인할 수 있습니다.

여러 워크시트 변환하기

여러 워크시트가 있는 Excel 워크북이 있는 경우, 각 워크시트에 대해 CSV 파일을 생성할 수도 있습니다. 이를 위해 Get-ExcelSheetInfo cmdlet을 사용하여 워크북의 모든 시트를 찾을 수 있습니다. 워크시트 이름을 얻은 후에는 해당 이름을 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의 다른 강력한 기능을 활용할 수 있습니다!