Làm cách nào để đọc dữ liệu từ Excel trong powershell?

Một trong những công cụ phổ biến mà hầu hết các chuyên gia CNTT nên sử dụng ít nhất một lần trong công việc của họ là Microsoft Excel. Nhưng thật không may, nhiều chuyên gia CNTT sử dụng Excel như một kho lưu trữ cơ sở dữ liệu nhỏ dẫn đến hàng tấn dữ liệu

Bài viết này sẽ hướng dẫn chúng ta các kiến ​​thức cơ bản về cách sử dụng PowerShell để tự động hóa việc đọc dữ liệu trong sổ làm việc Excel

Đọc Giá trị Excel trong PowerShell

Chúng ta có thể lưu một sổ làm việc Excel vào một đối tượng PowerShell mà sau đó có thể đọc và thao tác bằng cách sử dụng lệnh COM New-Object -ComObject Excel.Application. Đảm bảo bạn đã cài đặt gói Office 2016 trên máy của mình để các lệnh hoạt động

Hãy lấy tệp Excel bên dưới làm ví dụ

Sample.xlsx

Name,Salary
John,1000
Paul,2000
Mary,1500
Matt,3000

Để tìm một giá trị ô, hãy mở sổ làm việc Excel để đưa nó vào bộ nhớ bằng cách sử dụng đoạn mã sau

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.workbooks.Open('C:\PS\Sample.xlsx')

Tiếp theo, chọn trang tính bên trong sổ làm việc. Đối số chấp nhận một giá trị số nguyên, trong đó 1 là Trang tính 1, 2 là Trang tính 2, v.v.

$sheet = $workbook.Sheets.Item(1)

Sau khi gán trang tính cho một biến, chúng ta có thể xem chi tiết từng hàng, cột và ô. Có lẽ chúng ta cần tìm giá trị ô cho một ô cụ thể

Sau đó, chúng ta cần tham chiếu thuộc tính ô cung cấp tọa độ hàng và cột

$cellValue = $sheet.Cells.Item(2,2).Value2

Tuy nhiên, nếu chúng ta không muốn tọa độ ô mà muốn địa chỉ tên của nó, chúng ta có thể sử dụng hàm

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.workbooks.Open('C:\PS\Sample.xlsx')

0 để hiển thị nó

Mã ví dụ

$cellAddress = $sheet.Cells.Item(2,2).Address()
Write-Output $cellAddress

đầu ra

Bây giờ với tất cả các lệnh này, chúng ta có thể tạo một đoạn mã mẫu sẽ xuất ra tất cả dữ liệu của một cột cụ thể bằng PowerShell

Trước tiên, bạn có thể lưu bảng tính Excel dưới dạng tệp CSV, sau đó sử dụng Nhập-CSV để nhập và sau đó sử dụng nó để đặt thuộc tính AD

Thứ hai, bạn có thể truy cập bảng tính theo chương trình. Cái này khó hơn RẤT NHIỀU. Cân nhắc sử dụng https. //github. com/dfinke/ImportExcel Mở một cửa sổ mới mô-đun

Nó thực sự không khó hơn nhiều và OP có thể tìm thấy nhiều thứ hơn họ có thể làm. Đây là một mồi

AdamTheAutomator Mở một cửa sổ mới. PowerShell và Excel Mở một cửa sổ mới

Tôi nghĩ rằng "khó hơn RẤT NHIỀU" ít nhiều sẽ là về việc thiết lập các điều kiện tiên quyết của bạn để chạy nó, vì OP là mới đối với PowerShell. Chính sách thực thi có thể cần được cập nhật và mô-đun cần được cài đặt. Cũng có thể có sự cố với cài đặt nếu TLS không được đặt đúng cách, nơi nó có thể không truy cập được vào kho lưu trữ

Mặc dù vậy, tôi không chắc chắn 100% về phần Chính sách thực thi vì nó liên quan đến việc cài đặt một mô-đun và có thể cần phải thay đổi từ mặc định bất kể có sử dụng Import-Excel hay không

https. // tài liệu. Microsoft. com/en-us/powershell/module/microsoft. vỏ bọc quyền lực. bảo mật/thiết lập chính sách thực thi. Mở một cửa sổ mới

Khoảng 1/2 trang sẽ liệt kê các giá trị bạn có thể sử dụng cho Chính sách thực thi và ý nghĩa của chúng

#Get the current execution policy:
Get-ExecutionPolicy

#Set the execution policy (I leave mine at RemoteSigned)
Set-ExecutionPolicy RemoteSigned

Định cấu hình TLS

https. // tài liệu. Microsoft. com/en-us/azure/databox-online/azure-stack-edge-gpu-configure-tls-settings Mở một cửa sổ mới

Tôi chỉ gặp rắc rối với điều này trên một vài máy chủ của mình để có thể cài đặt một mô-đun. Có thể không cần thiết trong môi trường của bạn vì nó có thể đã được đặt đúng cách và có thể chỉ cần thực hiện nếu bạn không thể cài đặt mô-đun ImportExcel

Xem qua một bảng tính với hơn 3000 hàng, tôi muốn hỏi liệu ai đó có thể giúp tôi trong quá trình thực hiện để PowerShell đọc dữ liệu cho tôi trong bảng tính và xuất nó cho tôi vào các biến của tôi không

Tôi có 5 biến mà tôi cần ghi dữ liệu vào bảng tính của mình

$GPO = [cột Tên GPO]

$Group = [cột Tên nhóm]

$Drive = [Cột Drive]

$Nhận xét = [Cột Nhận xét]

$Target = [cột Liên kết mục tiêu đơn vị tổ chức]

Hy vọng rằng ai đó có thể giúp tôi và giúp tôi tiết kiệm rất nhiều thời gian khỏi phải nhập thông tin này theo cách thủ công

PowerShell không đi kèm với hỗ trợ riêng cho. các tệp xlsx mặc dù. Đó là lý do tại sao trước đây người dùng sử dụng cách xuất dữ liệu excel sang csv, sau đó sử dụng

Install-Module -Name ImportExcel -Force

2để đọc dữ liệu đã xuất vào PowerShell

Cách giải quyết này tạo ra nhiều công việc hơn và có một số nhược điểm khác. Nhờ có mô-đun miễn phí ImportExcel, việc đi thêm tuyến đường qua csv không còn cần thiết nữa. Bây giờ bạn có thể trực tiếp đọc và viết. dữ liệu xlsx. Microsoft Office không bắt buộc

Trong bài viết này, bạn sẽ học cách đọc và viết. xlsx và. xlsm chỉ trong một dòng mã. Ngoài ra, tôi cung cấp cho bạn

Install-Module -Name ImportExcel -Force

3, tự động chuyển đổi. tập tin xls để. xlsx và. các loại tệp xlsm. Điều đó quan trọng vì ImportExcel chỉ có thể xử lý các giao diện hiện đại. xlsx và. các loại tệp xlsm. càng già. tệp excel xls sử dụng định dạng nhị phân độc quyền mà chỉ excel mới biết cách đọc

Install-Module -Name ImportExcel -Force

3 có thể rất hữu ích khi bạn cần chuyển đổi hàng loạt tệp excel cũ sang định dạng hiện đại

Nó cũng minh họa cách truy cập mô hình đối tượng excel và quan trọng hơn là cách giải phóng các đối tượng COM để bạn không bị rò rỉ bộ nhớ và quá trình ma

Thêm hỗ trợ Excel vào PowerShell

Cảm ơn Doug Finke và mô-đun miễn phí tuyệt vời của anh ấy ImportExcel, đọc và viết. xlsx bây giờ rất nhanh - không cần cài đặt Office. Chỉ cần tải xuống và cài đặt mô-đun miễn phí này từ Thư viện PowerShell

Install-Module -Name ImportExcel -Scope CurrentUser -Force

Nếu bạn có sẵn đặc quyền của Quản trị viên, bạn có thể muốn cài đặt mô-đun cho Tất cả người dùng để thay thế. Điều này đảm bảo mô-đun khả dụng cho tất cả người dùng, nhưng quan trọng hơn, nó làm cho mô-đun khả dụng cho cả Windows PowerShell và PowerShell 7

Install-Module -Name ImportExcel -Force

Khi bạn cài đặt các mô-đun trong phạm vi Người dùng hiện tại, các mô-đun chỉ khả dụng cho phiên bản PowerShell mà bạn đã sử dụng để thực hiện cài đặt, do đó, bạn sẽ phải cài đặt mô-đun hai lần ở các vị trí khác nhau

Đọc và ghi tệp Excel

Hai lệnh ghép ngắn quan trọng nhất từ ​​mô-đun này là

• Install-Module -Name ImportExcel -Force
  

  5. có một đường dẫn đến một. xlsx và trả về tất cả dữ liệu từ trang tính mặc định. Sử dụng tham số -WorksheetName để chỉ định một trang tính nhất định. Thí dụ

  # import excel file and show in gridview (make sure file exists!)
  $Path = "c:\path\to\some\excel.xlsx"
  Import-Excel -Path $Path | Out-GridView
  

• Install-Module -Name ImportExcel -Force
  

  6. lưu tất cả dữ liệu đường ống vào *. tập tin xlsx. Sử dụng tham số -WorksheetName để chỉ định một trang tính nhất định. Theo mặc định, dữ liệu hiện có trên trang tính sẽ bị ghi đè. Thí dụ

  # create am excel sheet with all local user accounts
  Get-LocalUser | Export-Excel
  

Chơi với dữ liệu mẫu

Hãy chơi với các lệnh excel mới. Viết file excel đơn giản. chuyển dữ liệu sang

Install-Module -Name ImportExcel -Force

6 để tạo tệp excel mới

$Path = "$env:temp\listOfServices.xlsx"
Get-Service | Export-Excel -Path $Path -AutoSize -AutoFilter -FreezeTopRow -BoldTopRow -ClearSheet -WorksheetName 'List of Services' -Show

Để chơi với

Install-Module -Name ImportExcel -Force

5, trước tiên hãy truy xuất một số tệp dữ liệu mẫu trong thế giới thực

Tải xuống dữ liệu mẫu

Tìm dữ liệu mẫu excel thật dễ dàng. chỉ cần google cho Tải xuống dữ liệu mẫu Excel để tìm ra các url. Chúng đến dưới dạng các tệp riêng lẻ và kho lưu trữ ZIP. Để mang lại trải nghiệm thú vị khi tải xuống, tôi đã tạo một loạt chức năng trợ giúp

Để tải xuống các tệp, chỉ cần sử dụng

Install-Module -Name ImportExcel -Force

9 và

# import excel file and show in gridview (make sure file exists!)
$Path = "c:\path\to\some\excel.xlsx"
Import-Excel -Path $Path | Out-GridView

0

# use TLS1.2 with HTTPS:
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# creates folder if it does not yet exist:
filter Assert-FolderExists
{
  $exists = Test-Path -Path $_ -PathType Container
  if (!$exists) { 
    Write-Warning "$_ did not exist. Folder created."
    $null = New-Item -Path $_ -ItemType Directory 
  }
}

# download, unblock and extract zip files
filter Download-Zip($Path)
{
  # download to temp file:
  $temp = "$env:temp\temp.zip"
  Invoke-WebRequest -Uri $_ -OutFile $temp
  # unblock:
  Unblock-File -Path $temp
  # extract archive content:
  Expand-Archive -Path $temp -DestinationPath $Path -Force
  
  # report
  $zip = [System.IO.Compression.ZipFile]::OpenRead($temp)
  $zip.Entries | ForEach-Object { Write-Warning "Download: $_" }
  $zip.Dispose()
  
  # remove temp file:
  Remove-Item -Path $temp
}

# test whether filename is valid:
function Test-ValidFileName($FileName)
{
  $FileName.IndexOfAny([System.IO.Path]::GetInvalidFileNameChars()) -eq -1
}

# download and unblock file:
filter Download-File($Path, $FileName)
{
  # does the url specify a filename?
  if ([string]::IsNullOrWhiteSpace($FileName))
  {
    # take filename from url:
    $FileName = $_.Split('/')[-1]
    # remove url parameters:
    $FileName = $FileName.Split('?')[0]
    # test for valid file name:
    $isValid = Test-ValidFileName -FileName $FileName
    if (!$isValid)
    {
      throw "Url contains no valid file name. $FileName is not valid. Use parameter -FileName to specify a valid filename."
    }
  }
  
  $filePath = Join-Path -Path $Path -ChildPath $FileName
  Invoke-WebRequest -Uri $_ -OutFile $filePath
  # unblock:
  Unblock-File -Path $Path
  
  Write-Warning "Download: $FileName"
}

# create local folder for downloaded files:
($OutPath = "$env:temp\excelsampledata") | Assert-FolderExists

# download various excel sample files:
'https://www.contextures.com/SampleData.zip' | Download-Zip -Path $OutPath
'https://go.microsoft.com/fwlink/?LinkID=521962' | Download-File -Path $OutPath -FileName financial.xlsx
'http://www.principlesofeconometrics.com/excel/theories.xls' | Download-File -Path $OutPath 
'http://www.principlesofeconometrics.com/excel/food.xls' | Download-File -Path $OutPath 
'https://www.who.int/healthinfo/statistics/whostat2005_mortality.xls?ua=1' | Download-File -Path $OutPath 
'https://www.who.int/healthinfo/statistics/whostat2005_demographics.xls?ua=1' | Download-File -Path $OutPath 

Khi bạn chạy mã này, nó sẽ tải xuống một loạt tệp mẫu excel

WARNING: Download: SampleData.xlsx
WARNING: Download: financial.xlsx
WARNING: Download: theories.xls
WARNING: Download: food.xls
WARNING: Download: whostat2005_mortality.xls
WARNING: Download: whostat2005_demographics.xls

Đọc tệp Excel

Để đọc dữ liệu trực tiếp từ tệp excel, hãy sử dụng

Install-Module -Name ImportExcel -Force

5. Ví dụ: để chỉ nhận dữ liệu tài chính cho tháng 12, hãy thử điều này

# path with excel files
# (assuming you downloaded the sample data as instructed before)
Set-Location -Path "$env:temp\excelsampledata"

Import-Excel -Path .\financial.xlsx | Where-Object 'Month Number' -eq 12 | Out-GridView

Theo mặc định,

Install-Module -Name ImportExcel -Force

5 đọc dữ liệu từ trang tính đầu tiên. Nếu tệp của bạn chứa nhiều hơn một trang tính, hãy sử dụng tham số -WorksheetName để chỉ định tên của nó

Để nhóm các quốc gia cho tháng 12, chỉ cần sử dụng các lệnh ghép ngắn đường ống PowerShell phổ biến

Rõ ràng, bạn cũng có thể làm điều này trực tiếp với excel. Đây là về tự động hóa (trong trường hợp bạn cần thực hiện các loại phân tích này thường xuyên) và dành cho các chàng trai nhà PowerShell, những người có thể không biết cách xoay vòng trong excel nhưng biết các công cụ của họ trong PowerShell

Và đó là về việc học. không có cách nào tốt hơn để tìm hiểu các lệnh ghép ngắn đường ống PowerShell

# path with excel files
# (assuming you downloaded the sample data as instructed before)
Set-Location -Path "$env:temp\excelsampledata"

Import-Excel -Path .\financial.xlsx | Where-Object 'Month Number' -eq 12 | Group-Object -Property Country -NoElement | Sort-Object -Property Count -Descending

Đây là kết quả

Count Name                     
----- ----                     
   21 Germany                  
   21 United States of America 
   21 Canada                   
   21 France                   
   21 Mexico  

Truy cập tệp XLS

Tin xấu là. . không thể truy cập tệp xls. Họ sử dụng định dạng nhị phân độc quyền mà chỉ excel mới có thể đọc được

Tin tốt là. miễn là bạn đã cài đặt excel, việc chuyển đổi là chuyện nhỏ. tập tin xls để. tập tin xlsx. Nếu bạn thực sự vẫn đang sử dụng. xls, bạn nên xem xét việc chuyển đổi này mãi mãi. . xls đã thực sự lỗi thời và không còn được sử dụng nữa

Chuyển đổi XLS sang XLSX

Ở trên tôi đã tải xuống một loạt. xls không thể được xử lý bởi

Install-Module -Name ImportExcel -Force

5. đáng tiếc

Dưới đây là một chức năng

Install-Module -Name ImportExcel -Force

3 tự động chuyển đổi. tập tin xls để. xlsx và. Tuy nhiên, các tệp xlsm. Tập lệnh yêu cầu cài đặt Microsoft Office trên hộp của bạn vì chỉ excel mới biết cách mở định dạng nhị phân được sử dụng trong. tập tin xls

Install-Module -Name ImportExcel -Force

0

Nó nằm ngoài phạm vi của bài viết này để thảo luận chi tiết về chức năng. Mặc dù vậy, tôi muốn chỉ ra rằng mã minh họa các khía cạnh quan trọng khi sử dụng các đối tượng COM trong PowerShell

Khi sử dụng các đối tượng COM như Excel. Ứng dụng, có thể khó giải phóng tất cả các tham chiếu đối tượng ở cuối. Khi bạn làm điều này sai, các tham chiếu sẽ vẫn tồn tại và quá trình excel trong bộ nhớ cũng vậy. Tất nhiên, bạn luôn có thể tắt tiến trình sau khi sử dụng nhưng điều này có thể làm hỏng excel và lần sau khi bạn khởi chạy, nó sẽ bắt đầu ở chế độ khôi phục

Cách tiếp cận tốt hơn là đảm bảo rằng bạn đang lưu trữ từng tham chiếu đối tượng trong một biến chuyên dụng. Tiếp theo, đảm bảo rằng bạn chủ động giải phóng từng tham chiếu sau khi sử dụng bằng cách gọi ReleaseComObject()

Khi bạn đã làm đúng, không có tham chiếu mở nào tồn tại và khi bạn gọi Quit(), excel sẽ bị xóa khỏi danh sách quy trình của bạn

PowerShell có thể đọc từ Excel không?

Bạn có thể lưu một bảng excel dưới dạng. csv và powershell có thể đọc và ghi vào chúng dưới dạng đối tượng , vì vậy rất dễ làm việc với điều đó.

Làm cách nào để đọc dữ liệu từ Excel bằng PNP PowerShell?

Có nhiều cách để chọn giá trị từ một trang tính, như được đưa ra bên dưới. .

$workSheet. Phạm vi ("A1"). Chữ

$workSheet. Phạm vi ("A1. A1"). Chữ

$workSheet. Phạm vi("A1","A1"). Chữ

$workSheet. tế bào. Mục(1, 1). chữ

$workSheet. tế bào. Mục(1, 1). giá trị2

$workSheet. Cột. Mục 1). hàng. Mục 1). Chữ

$workSheet. hàng. Mục 1). Cột. Mục 1). Chữ

Làm cách nào để chuyển đổi XLSX sang CSV trong PowerShell?

Tập lệnh sử dụng hàm để chuyển đổi các tệp Excel có định dạng XLSX sang CSV , tuy nhiên định dạng này có thể được thay đổi trong tập lệnh. Đối với tập lệnh thứ hai, nơi chúng tôi chỉ chuyển đổi một tab của tệp Excel, chỉ cần nhập tên của thư mục và tên của tệp trong tập lệnh bên dưới. Đó là nó.