programming excel

Cài đặt mô-đun excel powershell

Trước đây tôi cần thực hiện một số thao tác trên bảng tính Excel nhưng chưa cài đặt Office trên máy chủ. Đối với trường hợp cụ thể, tôi đã tìm cách hoàn thành nhiệm vụ của mình với PowerShell. Xem nhanh kết quả tìm kiếm của Google và…

Mặc dù tên không được chỉ định, cả hai bài viết đều nói về mô-đun ImportExcel [nếu bạn cuộn xuống một chút kết quả, bạn sẽ thấy liên kết này]

Nếu bạn thích chủ đề này, vui lòng xem loạt tình huống sử dụng khác nhau

Loạt PowerShell và Excel mùa đông

ImportExcel là gì?

Tôi sẽ sử dụng mô tả của Doug từ kho lưu trữ GitHub

Mô-đun PowerShell này cho phép bạn đọc và ghi các tệp Excel mà không cần cài đặt Microsoft Excel trên hệ thống của mình. Không cần bận tâm đến đối tượng COM Excel cồng kềnh. Việc tạo Bảng, Bảng Pivot, Biểu đồ và nhiều thứ khác đã trở nên dễ dàng hơn rất nhiều

Đúng rồi. Không cần cài đặt Microsoft Excel

Gần đây tôi đã thử nghiệm 7. 1. 0 và nó chứa khoảng 60 chức năng. Một số chức năng là

Định dạng bổ sung có điều kiện Thêm bảng tính so sánh bảng Excel Chuyển đổi từ-ExcelToSQLChèn Xuất-Excel Nhập-Excel Tham gia-Bảng tính PieChart Pivot

ImportExcel – bắt đầu nhanh

Đầu tiên, để cài đặt mô-đun trên máy của mình, tôi đã chạy tập lệnh ngắn này

# set the execution policy for the current process
Set-ExecutionPolicy Bypass -Scope Process
# install and import module
Install-Module -Name ImportExcel
Import-Module -Name ImportExcel
# confirm the module is installed
Get-Module -Name ImportExcel

Thứ hai, hãy xem công cụ này có thể làm gì. Tôi muốn xem tất cả các lệnh từ mô-đun và xuất tên của chúng sang bảng tính Excel

# get all the functions from the module and export as a spreadsheet
Get-Command -Module ImportExcel | SELECT Name | Export-Excel

Do đó, tôi đang mở các cửa sổ Excel mới với tên tạm thời là tmpF12D nằm trong $env. TEMP với đầu ra được định dạng dưới dạng bảng bảng tính Table1

Điều đó thật dễ dàng, nhưng tôi muốn xem thêm

ImportExcel – cách thực hiện?

Tôi đã tạo một bảng tính Excel từ PowerShell, nhưng tôi có thể làm gì khác?

ImportExcel – cách làm việc với trang tính?

Trên thực tế, mô-đun ImportExcel cho phép thao tác với các trang tính

# go to the temporary location
Set-Location $env:TEMP
# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename
# add an extra sheet to the end
Add-Worksheet -ExcelPackage $excel -WorksheetName Mikey -MoveToEnd
# and save it back to the file and show the file
Close-ExcelPackage -ExcelPackage $excel -Show

Và lý do cho điều đó rất đơn giản - tôi đã mở tệp. Vì vậy, tôi sẽ đóng nó và lặp lại kịch bản trên

# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename
# close the open file
[[Runtime.InteropServices.Marshal]::GetActiveObject['Excel.Application'].workbooks | Where-Object {$_.FullNameURLEncoded -eq $excel.File }].Close[$false]
# similar effect would be using the KillExcel switch
# close WHOLE Excel app [not just the specific file] and load to variable
$excel = Open-ExcelPackage -Path $filename -KillExcel
# add an extra sheet to the end
Add-Worksheet -ExcelPackage $excel -WorksheetName Mikey -MoveToEnd
# and save it back to the file and show the file
Close-ExcelPackage -ExcelPackage $excel -Show

Như đã trình bày ở trên, việc thêm một trang tính trống thật dễ dàng [và tôi đã học được một điều mới về cách đóng tệp]. Làm thế nào về việc phổ biến nó với dữ liệu?

# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename
[[Runtime.InteropServices.Marshal]::GetActiveObject['Excel.Application'].workbooks | Where-Object {$_.FullNameURLEncoded -eq $excel.File }].Close[$false]
# add new worksheet at the beginning
Add-Worksheet -ExcelPackage $excel -WorksheetName Process -MoveToStart
# get the data into a new sheet as a named table
# freeze the first row and column with an auto-sized length
Get-Process | Export-Excel -ExcelPackage $excel -WorksheetName Process -TableName Process -Show -AutoSize -FreezeTopRow -FreezeFirstColumn

Hoàn hảo, chúng tôi có một số dữ liệu. Trục?

ImportExcel – cách tạo bảng tổng hợp?

Ví dụ: sử dụng Add-PivotTable rất đơn giản [và từ bây giờ tôi sẽ đóng toàn bộ Excel]. Ngoài ra, hãy lưu ý công tắc Kích hoạt ở cuối. Khi bạn mở tệp Excel, bảng tính được sử dụng trong lệnh sẽ hiển thị đầu tiên

# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename -KillExcel
# add a simple pivot table
Add-PivotTable -ExcelPackage $excel -PivotRows Name -PivotColumns PriorityClass -PivotData @{'CPU' = 'sum'} -SourceWorkSheet process -PivotTableName 'Pivot' -Activate
# save it to the file and display
Close-ExcelPackage -ExcelPackage $excel -Show

Ngoài ra, nếu có sự cố xảy ra với dữ liệu của chúng tôi và chúng tôi cần tạo lại bảng tổng hợp. Không vấn đề gì. Thả trang tính và tạo bảng tổng hợp mới

# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename -KillExcel
# drop the pivot table worksheet [using the filename, not the package]
Remove-Worksheet -FullName $filename -WorksheetName Pivot 
# add some more stuff to the pivot table
Add-PivotTable -ExcelPackage $excel -PivotRows Name -PivotColumns PriorityClass -PivotData @{'CPU' = 'sum'; 'Company' = 'count'} -SourceWorkSheet process -PivotTableName 'Pivot' -Activate -NoTotalsInPivot -PivotTableStyle Dark1
# save it to the file and display
Close-ExcelPackage -ExcelPackage $excel -Show

Kết quả của cả hai hoạt động, chúng tôi nhận được hai bảng;

ImportExcel – cách tạo biểu đồ bảng tổng hợp?

Thông thường có một biểu đồ bảng tổng hợp bên cạnh dữ liệu. Trên thực tế, mô-đun ImportExcel cũng hỗ trợ điều đó

# load the spreadsheet
$filename = 'tmpF12D.xlsx'
$excel = Open-ExcelPackage -Path $filename -KillExcel
# setup definition for the first pivot table; note the IncludePivotChart
$PTDef =  New-PivotTableDefinition -PivotTableName "P1" -SourceWorkSheet "Process" -PivotRows "Company" -PivotData @{'Cpu' = 'average'} -IncludePivotChart -ChartType BarClustered3D
# join the definition for the second pivot table
$PTDef += New-PivotTableDefinition -PivotTableName "P2" -SourceWorkSheet "Process" -PivotRows "Company" -PivotData @{'Cpu' = 'average'} -IncludePivotChart -ChartType Pie3D
# Export with the PivotTableDefinition
Export-Excel -ExcelPackage $excel -PivotTableDefinition $PTDef -Show -Activate

Điều đó không giới hạn người dùng tạo biểu đồ bảng tổng hợp trên một trang tính mới. Khá đơn giản để thêm nhiều biểu đồ trên trang tính. Chỉ để tránh chồng chéo các biểu đồ và bảng, hãy đặt Địa chỉ [để đặt bảng [A]] và ChartRow / ChartColumn [để đặt vị trí cho biểu đồ [B]]

________số 8

Kinh ngạc. Trên thực tế, sẽ mất cả ngày để hiển thị tất cả các tình huống chỉ dành cho bảng tổng hợp và biểu đồ

Tóm lược

Tóm lại, mô-đun ImportExcel PowerShell có nhiều tính năng và đây là một nhiệm vụ mạnh mẽ. Hôm nay tôi đã chỉ cho bạn cách thực hiện các thao tác cơ bản trên trang tính và bảng tổng hợp/biểu đồ. Tuần tới sẽ mô tả thêm về biểu đồ thông thường và định dạng có điều kiện

Làm cách nào để cài đặt mô-đun trong PowerShell?

Cài đặt mô-đun PowerShell từ Thư viện PowerShell là cách dễ nhất để cài đặt mô-đun. Để cài đặt gói hoặc mô-đun từ Thư viện, chúng tôi sử dụng lệnh. Cài đặt mô-đun hoặc lệnh ghép ngắn Install-Script , tùy thuộc vào loại gói.

PowerShell có thể hoạt động với Excel không?

Trong bài viết này, chúng tôi sẽ trình bày cách đọc và ghi dữ liệu từ trang tính Excel trực tiếp từ tập lệnh PowerShell. Bạn có thể sử dụng Excel cùng với PowerShell để kiểm kê và tạo các báo cáo khác nhau trên máy tính, máy chủ, cơ sở hạ tầng, Active Directory, v.v.

PowerShell có thể xuất sang Excel không?

Xuất và nhập vào và từ Excel bằng mô-đun PowerShell ImportExcel. ImportExcel là mô-đun PowerShell cho phép bạn nhập hoặc xuất dữ liệu trực tiếp từ bảng tính Excel mà không cần cài đặt Microsoft Excel trên máy tính .