Hướng dẫn powershell edit excel file - powershell chỉnh sửa tệp excel

Công nghệ cơ sở dữ liệu chứa dữ liệu công ty nhiều nhất là Excel. Mặc dù không phải là quan hệ, định nghĩa về cơ sở dữ liệu là "một bộ sưu tập toàn diện các dữ liệu liên quan được tổ chức để truy cập thuận tiện, thường là trong máy tính." Những người kinh doanh cần đưa ra quyết định làm như vậy dựa trên dữ liệu mà họ thường tìm thấy trong bảng tính Excel.

Liên quan: accel là ứng dụng Killer BI tiếp theo?Is Excel the Next Killer BI App?

Mặc dù tôi thường khuyên bạn nên sử dụng một cái gì đó như Dịch vụ báo cáo SQL Server (SSR) để tạo báo cáo, sau đó xuất báo cáo đó sang Excel, có những nhu cầu khiến phương thức này có vấn đề. Trong trường hợp tôi hiện đang làm việc, một bảng tính loại bảng điều khiển khá tốt cần được cập nhật mỗi ngày với kết quả mới của việc xử lý hàng đêm. Có một vài bảng tính trong tài liệu cần được làm mới với dữ liệu hiện tại và sau đó các tính toán trên bảng điều khiển sẽ điều chỉnh kết quả dựa trên dữ liệu mới.

Bảng tính kiểm tra và thêm bảng tính

Tôi đã thiết lập một bảng tính thử nghiệm và thêm một bảng tính mà tôi đặt tên là "Chơi". Mỗi lần tôi chạy tập lệnh, tôi muốn xóa mọi thứ trên trang, chạy truy vấn dựa trên cơ sở dữ liệu SQL Server của tôi và điền vào trang tính với nội dung của kết quả truy vấn.

Đầu tiên, chúng ta hãy mở tài liệu Excel bằng cách sử dụng đối tượng COM (vì vẫn chưa có bản thân PowerShell bản địa nào cho việc này). Chúng tôi cũng sẽ đặt bộ sưu tập bảng tính từ sổ làm việc đến một màn hình ảnh đối tượng, chỉ chọn cái tên trong đó tên là 'chơi'.

$Path = 'c:\Work\PowerShellPlayTime.xlsx'
 
# Open the Excel document and pull in the 'Play' worksheet
$Excel = New-Object -Com Excel.Application
$Workbook = $Excel.Workbooks.Open($Path) 
$page = 'Play'
$ws = $Workbook.worksheets | where-object {$_.Name -eq $page}

Có rất nhiều cách khác nhau để xóa các hàng hiện có, nhưng trong trường hợp của chúng tôi, chúng tôi chỉ muốn xóa những gì ở đó, vì vậy chúng tôi sẽ sử dụng phương thức rõ ràng (). Tôi đặt đầu ra ra ngoài null, vì phương thức này là trò chuyện và sẽ trả về 'true' khi hoàn thành.

# Delete the current contents of the page
$ws.Cells.Clear() | Out-Null

Bây giờ, tôi sẽ sử dụng ado.net để thực hiện truy vấn của mình và trả về kết quả. Nếu bạn đã tải mô-đun SQLPS, bạn có thể sử dụng Invoke-SQLCMD, nhưng với ADO.NET, tôi không phải đảm bảo mô-đun được tải trước khi chạy truy vấn của tôi. Các công cụ tôi cần đã có ở đó.

# Connect to the target SQL Server and run the query to refresh data
$cstr = "Server=MyServer;Database=Northwind;User ID=sa;[email protected]"
$cn = new-object system.data.SqlClient.SqlConnection($cstr);
$ds = new-object "System.Data.DataSet" "dsProductData"
$q = @"
SELECT TOP 100 [ProductID]
  ,[ProductName]
  ,[SupplierID]
  ,[CategoryID]
  ,[QuantityPerUnit]
  ,[UnitPrice]
  ,[UnitsInStock]
  ,[UnitsOnOrder]
  ,[ReorderLevel]
  ,[Discontinued]
  FROM [dbo].[Products]
"@
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$da.Fill($ds) | Out-Null
 
$dtProduct = $ds.Tables[0]

Biến $ dtProduct là một bộ sưu tập Datarow ADO.NET. Phương thức fill () sẽ trả về số lượng hàng được trả về từ truy vấn và một lần nữa, tôi không cần thông tin đó, vì vậy tôi đặt nó ra ngoài khe với. Nếu tôi đặt biến $ dtProduct để có thành viên, nó sẽ cho tôi thấy các phương thức và thuộc tính, cho phép tôi xem các loại dữ liệu của mỗi cột được trả về.

Để dễ đọc, tôi sẽ đặt một biến để chứa các ô của bảng tính và cũng khởi tạo các biến hàng và cột để điều hướng khi tôi điền vào bảng tính. Sau đó, tôi sẽ viết tên cột vào hàng đầu tiên dưới dạng tiêu đề cột.

# Set variables for the worksheet cells, and for navigation
$cells=$ws.Cells
$row=1
$col=1
 
# Add the headers to the worksheet
$headers = "ProductID","ProductName","SupplierID","CategoryID","QuantityPerUnit","UnitPrice","UnitsInStock","UnitsOnOrder","ReorderLevel","Discontinued"
$headers | foreach {
    $cells.item($row,$col)=$_
    $col++
}

Tiếp theo, chúng tôi sẽ lặp qua các dữ liệu và chèn chúng vào các ô và nếu chúng là các cột số, chúng tôi sẽ đặt các thuộc tính của ô để chúng hiển thị đúng.

# Add the results from the DataTable object to the worksheet
foreach ($prod in $dtProduct) {
    $row++
    $col=1
    $cells.item($Row,$col)=$prod.ProductID
    $col++
    $cells.item($Row,$col)=$prod.ProductName
    $col++
    $cells.item($Row,$col)=$prod.SupplierID
    $col++
    $cells.item($Row,$col)=$prod.CategoryID
    $col++
    $cells.item($Row,$col)=$prod.QuantityPerUnit
    $cells.item($Row,$col).NumberFormat="0.00"
    $col++
    $cells.item($Row,$col)=$prod.UnitPrice
    $cells.item($Row,$col).NumberFormat="0.00"
    $col++
    $cells.item($Row,$col)=$prod.UnitsInStock
    $cells.item($Row,$col).NumberFormat="0"
    $col++
    $cells.item($Row,$col)=$prod.UnitsOnOrder
    $cells.item($Row,$col).NumberFormat="0"
    $col++
    $cells.item($Row,$col)=$prod.ReorderLevel
    $cells.item($Row,$col).NumberFormat="0"
    $col++
    $cells.item($Row,$col)=$prod.Discontinued
}

Khả năng đọc là quan trọng

Khả năng đọc rất quan trọng, vì vậy chúng tôi sẽ đặt tất cả các cột thành tự động dựa trên nội dung cột.

# Set the width of the columns automatically
$ws.columns.item("A:J").EntireColumn.AutoFit() | out-null

Cuối cùng, chúng tôi sẽ đóng bảng tính, lưu các thay đổi của chúng tôi và thoát Excel.

# Close the workbook and exit Excel
$workbook.Close($true)
$excel.quit()

Thay vì sử dụng SQL Server Management Studio (SSMS), chạy truy vấn và dán kết quả vào bảng tính, tôi có thể chạy tập lệnh này và các công thức trong phần còn lại của bảng điều khiển sẽ dựa trên kết quả mới.

Liên quan: Sử dụng PowerShell để tải dữ liệu XML vào SQL ServerUse PowerShell to Load XML Data into SQL Server