Чтение и запись файлов excel xlsx с помощью power shell

Microsoft Excel is used by millions of people to store, organize, and analyze data. Excel files (.xlsx, .xls) contain a wealth of useful data like reports, lists, calculations, etc. As a PowerShell scripter, you may often need to extract data from Excel files for use in your scripts.

With PowerShell, you can directly import and export Excel files into your scripts and programmatically work with the data. In this beginner’s guide, I’ll introduce you to the PowerShell Import-Excel cmdlet and show you how to use it to import data from Excel files.

We’ll also cover how to install the module, read specific worksheets and cell ranges, access and filter Excel data, and even update existing Excel files – all from within PowerShell! Whether you need to automate reports, process calculations, or extract data for analytics, this tutorial will show you how PowerShell provides easy Excel integration and automation. Read on to unlock the power of scripting Excel with PowerShell!

Table of contents

• Introduction to PowerShell ImportExcel Module
• Installing the ImportExcel Module in PowerShell
• Importing Data from Excel using Import-Excel in PowerShell
• Access Excel Data from PowerShell
• Advanced Data Management Techniques with PowerShell Import-Excel
• Update the Excel File using PowerShell
• Alternative: COM Interop to Read Excel Data
• Common Errors and Troubleshooting with PowerShell Import-Excel
• PowerShell Import-Excel Resources and Further Learning
• Conclusion

Introduction to PowerShell ImportExcel Module

The ImportExcel module is not included with PowerShell by default, so you’ll need to install it separately. Once installed, you can use the cmdlets provided by the module to interact with Excel spreadsheets from within PowerShell.

Installing the ImportExcel Module in PowerShell

Install-Module -Name ImportExcel

Install-Module -Name ImportExcel -Scope CurrentUser

Import-Module ImportExcel

Now, we are ready to import data from Excel to PowerShell!

Importing Data from Excel using Import-Excel in PowerShell

Importing data from Excel using PowerShell is a straightforward process. Once you have the ImportExcel module installed, you can use the Import-Excel cmdlet to get data from an Excel spreadsheet.

Here is the list of important parameters for the Import-Excel cmdlet:

These parameters provide flexibility in importing data from Excel files, allowing you to specify exactly what data to read and how to handle it.

Import Excel File in PowerShell

1. Launch a PowerShell session by opening the PowerShell console or PowerShell ISE.
2. Import the Import-Excel module by executing the following command: Import-Module -Name ImportExcel
3. Use the Import-Excel cmdlet to import the Excel file. For example: Import-Excel -Path C:\Path\To\Excel\File.xlsx
4. The Excel file will be imported as a PowerShell object, allowing users to access and manipulate its data using PowerShell cmdlets and syntax.

The cmdlet takes a path to an Excel file as its argument and returns a PowerShell object containing the data from the spreadsheet.

# Input File Path
$FilePath = "C:\Reports\EmpData.xlsx"
# Read the Excel file and store its data into the variable
$ExcelData = Import-Excel -Path $FilePath

The Import-Excel cmdlet reads the Excel file and stores it in the $ExcelData array variable.

Here is my Excel File:

Access Excel Data from PowerShell

The imported Excel data is stored as PSObjects within the variable. You can access it like any other PowerShell object. Here are some examples:

Read Specific Worksheet

To read a specific worksheet from the Excel file, use the -WorkSheetName parameter:

$Sheet1Data = Import-Excel -Path $FilePath -WorksheetName "Sheet1"

This will import only the “Sheet1” worksheet data.

You can import specific columns from the Excel Spreadsheet using:

$data = Import-Excel -Path "C:\Temp\EmpData.xlsx" -HeaderName 'Employee Name', 'Designation'

Let’s say you want to get the data from an Excel file with headers Name and Designation!

$ExcelData = Import-Excel -Path "C:\Temp\users.xlsx" -HeaderName 'Name','Designation'
#Get Each Row of data
$ExcelData | ForEach-Object { Write-host $_.Name Write-host $_.Designation
}

Read Cell Range

You can also read a specific cell range from a worksheet using -StartRow and -StartColumn parameters:

Import-Excel -Path $FilePath -WorksheetName "Sheet1" -StartRow 1 -StartColumn 1 -EndRow 5 -EndColumn 2

In this example, we are importing data from the “Sheet1” worksheet, focusing on rows 1 to 5 and columns 1 and 2.

Advanced Data Management Techniques with PowerShell Import-Excel

PowerShell Import-Excel provides a wide range of advanced data management techniques that can help you automate your data management tasks. Some of the most useful techniques include filtering, sorting, and grouping data.

Filter Data

To filter data, you can use the Where-Object cmdlet. This cmdlet allows you to specify a condition that must be met for each row in the data. Rows that do not meet the condition are filtered out.

$SalesData = Import-Excel -Path $FilePath -WorksheetName "Sales"
$USSales = $SalesData | Where-Object {$_.Region -eq "US"}

This will filter the Sales worksheet to only records for the US region. You can also filter using:

$FilteredData = Import-Excel -Path $FilePath | Where-Object {$_.Designation -eq 'Software Engineer'}

In this example, we are importing data from the “SalesData” worksheet, focusing on rows 2 to 10.

• To sort data, you can use the Sort-Object cmdlet. This cmdlet allows you to specify one or more columns to sort by and the order in which to sort them.
• To group data, you can use the Group-Object cmdlet. This cmdlet allows you to group data by one or more columns and perform calculations on each group.

Update the Excel File using PowerShell

You can also use the ImportExcel module to update or add data to an existing Excel file.

First, import the Excel file:

#Import Excel File
$ExcelData = Import-Excel -Path "C:\Temp\EmpData.xlsx" -WorksheetName "Sheet1"

Make changes to $ExcelData variable, like adding a new row:

#Add New Row
$NewRow = [PSCustomObject]@{ "Employee Name" = "Ada Lovelace" "Designation" = "Software Engineer" "Region" = "US"
}
$ExcelData += $NewRow

Finally, save the updated data back to the original Excel file:

#Save the Excel File
Export-Excel -Path "C:\Temp\EmpData.xlsx" -InputObject $ExcelData -ClearSheet

The -ClearSheet parameter clears any existing data first before exporting updated data.

Similarly, you can update, delete, or add new worksheets by modifying $ExcelData accordingly.

An alternative way to import Excel files in PowerShell without any external module is using COM Interop. You’ll need Excel installed to create or access Excel files with this method.

First, create an Excel COM object:

$Excel = New-Object -ComObject Excel.Application

Then open the desired Excel file:

$Workbook = $Excel.Workbooks.Open("C:\Reports\data.xlsx")

Now you can read cell values like:

$Value = $Workbook.Worksheets["Sheet1"].Cells.Item(1,1).Value()

The COM Interop method gives you fine-grained control over the Excel application. Here is a complete example:

# Create COM Excel object
$Excel = New-Object -ComObject Excel.Application
# Open Excel file
$Workbook = $Excel.Workbooks.Open("C:\Temp\EmpData.xlsx")
#Get the Worksheet
$Worksheet = $Workbook.Worksheets["Sheet1"]
# Read specific cells
$cellValue1 = $worksheet.Cells.Item(1, 1).Value()
$cellValue2 = $worksheet.Cells.Item(1, 2).Value()
# Display value
Write-Host "Cell A1 Value: " $cellValue1
Write-Host "Cell B1 Value: " $cellValue2
# Close Excel
$workbook.Close($false)
$Excel.Quit()

Common Errors and Troubleshooting with PowerShell Import-Excel

As with any tool, there may be errors that occur when using PowerShell Import-Excel. Some of the most common errors include issues with formatting, file paths, and permissions.

Error: “ImportExcel: The term ‘ImportExcel’ is not recognized as the name of a cmdlet, function, script file, or operable program.”

Solution: Install the ImportExcel module by running: Install-Module -Name ImportExcel

Error: Incorrect File Path

Error Message: Could not find file ‘C:\wrong\path\to\file.xlsx’. To fix, Make sure the file path is correct. If the path contains spaces, wrap it in quotes.

Error: “The Excel workbook could not be loaded.”

This usually means the file path or name is incorrect. Double-check the path and filename and make sure the file exists. Also try fully qualifying the path instead of using a relative path. You can also use the Test-Path cmdlet that allows you to test whether a file or directory exists at a specified path.

Error: Could not find sheet ‘WrongSheetName’ in workbook

Solution: Make sure the worksheet name is correct. Worksheet names are case-sensitive.

Error: “Document not open.”

This occurs if you try to manipulate or save a workbook without first opening it. Make sure to use the Open-ExcelPackage cmdlet to open the workbook before doing any other operations.

Error: “Workbook is already open.”

You can only have an ExcelPackage open in one PowerShell process at a time. Make sure to fully close workbooks with Close-ExcelPackage before opening them again.

Error: “Worksheet ‘Sheet1’ does not exist.”

The specified worksheet name does not match the actual worksheet name in the Excel file. Double-check the name, or use Get-ExcelSheetInfo to list all sheets.

Error: “The number of rows is invalid.”

If importing a specific row range, make sure the end row number is valid for the sheet. Get-ExcelSheetInfo can be used to check total rows.

To avoid errors, check workbook paths, sheet names, row numbers, and open/close workbooks properly. Use -ErrorAction Stop to surface errors. Check out the module documentation for additional guidance.

PowerShell Import-Excel Resources and Further Learning

If you’re interested in learning more about PowerShell Import-Excel, there are a number of resources available to help you get started. The ImportExcel module at PowerShell Gallery or GitHub is a great place to find additional modules and cmdlets that can be used in conjunction with PowerShell Import-Excel.

Conclusion

Importing and automating Excel files in PowerShell is easy and powerful with the help of the ImportExcel module. In this article, we covered how to install and use the ImportExcel module to read Excel data into PowerShell objects. You learned how to import specific worksheets, cell ranges, filter and process Excel data using PowerShell syntax.

We also saw how to update and export Excel files directly from your scripts. With the techniques and best practices outlined in this guide, you can take full advantage of this tool and become a more efficient data analyst. If you want to convert an Excel to CSV file, you can use the Export-CSV cmdlet. More here: How to Convert Excel to CSV in PowerShell?

Importing Excel files in PowerShell is quite easy using the free ImportExcel module. The key steps are:

• Install ImportExcel module
• Use Import-Excel cmdlet to read Excel file
• Access worksheet data as PowerShell objects
• Filter, and process Excel data using PowerShell syntax
• Update and export Excel files from PowerShell

I am trying to use Microsoft.Office.Interop.Excel.dll in my powershell script. The machine on which i am trying to use don’t have Office installed.
I used below code to add the dll

Add-Type -LiteralPath "D:\Software\microsoft.office.interop.excel\Office.dll"
Add-Type -LiteralPath "D:\Software\microsoft.office.interop.excel\Microsoft.Office.Interop.Excel.dll" 

when i am trying to create the object using

$excel = New-Object Microsoft.Office.Interop.Excel.ApplicationClass

I am getting error as:

New-Object : Exception calling ".ctor" with "0" argument(s): "Retrieving the COM class factory for component with CLSID
{00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 

All the Office PIAs (primary interop assemblies) do is provide a .NET-friendly layer on top of the COM APIs provided by the Office applications themselves.

Therefore, they can only work if Office is present as well.
The error you’re getting, 0x80040154, indicates that the underlying Excel COM class isn’t registered.

(Conversely, it is possible for PowerShell to talk to Office without the interop assemblies, and, when authoring code that needs to run on multiple machines, it may be preferable not to use them, given that their presence cannot be assumed. New-Object‘s -Strict switch issues a warning when the interop assemblies are being used.)

As for the ability to work with Office documents without an Office installation present:

• For Excel documents, consider using the popular ImportExcel module.

68 gold badges673 silver badges862 bronze badges

Похожее:

Полезные заготовки вызова утилит командной строки Power shell конвертировать файлы xlsx в csv Power shell и его возможности в уходящем году Скачать Все официальные темы Windows с сайта Microsoft 28.11.2019