Leer y escribir datos en un archivo de Excel con PowerShell

En este artículo, mostraremos cómo leer y escribir datos de hojas de cálculo de Excel directamente desde scripts de PowerShell. Puede usar Excel junto con PowerShell para inventariar y generar varios informes sobre computadoras, servidores, infraestructura, Active Directory, etc.

Antes de mostrar cómo acceder a los datos en una celda de Excel, vale la pena comprender la arquitectura de las capas de presentación en el archivo de Excel. La siguiente figura muestra 4 capas de presentación anidadas en el modelo de objetos de Excel:

• Capa de aplicación: se ocupa de la aplicación de Excel en ejecución;

• Capa de libro de trabajo: se pueden abrir varios libros de trabajo (archivos de Excel) al mismo tiempo;

• Capa de hoja de trabajo: cada archivo XLSX puede contener varias hojas;

• Capa de rango: aquí puede acceder a los datos en la celda o rango de celdas específico.

¿Cómo leer datos de una hoja de cálculo de Excel usando PowerShell?

Echemos un vistazo a un ejemplo simple de cómo usar PowerShell para acceder a los datos en un archivo de Excel que contiene una lista de empleados.

Primero, ejecute la aplicación de Excel (capa de aplicación) en su computadora usando el objeto COM:

$ExcelObj = New-Object -comobject Excel.Application

Después de ejecutar el comando, Excel se iniciará en su computadora en segundo plano. Para mostrar la ventana de Excel, cambie la propiedad Visible del objeto COM:

$ExcelObj.visible=$true

$ExcelObj| fl

Luego puede abrir un archivo de Excel (un libro de trabajo):

$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")

Cada archivo de Excel puede contener varias hojas de trabajo. Vamos a mostrar la lista de hojas de cálculo en el libro de Excel actual:

$ExcelWorkBook.Sheets| fl Name, index

Luego puede abrir la hoja que desee (por su nombre o índice):

$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_users")

Puede obtener el nombre de la hoja de cálculo de Excel actual (activa) con este comando:

$ExcelWorkBook.ActiveSheet | fl Name, Index

Luego puede obtener valores de las celdas en la hoja de cálculo de Excel. Puede usar diferentes métodos para obtener los valores de las celdas en la hoja de cálculo actual de Excel: usando un rango, una celda, una columna o una fila. Vea los ejemplos de cómo obtener datos de la misma celda a continuación:

$ExcelWorkSheet.Range("B4").Text
$ExcelWorkSheet.Range("B4:B4").Text
$ExcelWorkSheet.Range("B4","B4").Text
$ExcelWorkSheet.cells.Item(4, 2).text
$ExcelWorkSheet.cells.Item(4, 2).value2
$ExcelWorkSheet.Columns.Item(2).Rows.Item(4).Text
$ExcelWorkSheet.Rows.Item(4).Columns.Item(2).Text

Exportación de información de usuario de Active Directory a una hoja de cálculo de Excel usando PowerShell

Veamos un ejemplo práctico de cómo acceder a datos de Excel desde PowerShell. Supongamos que queremos obtener información de Active Directory para cada usuario en un archivo de Excel. Por ejemplo, su número de teléfono (el atributo phoneNumber), departamento y dirección de correo electrónico.

# Importing Active Directory module into PowerShell session
import-module activedirectory
# Open an Excel workbook first:
$ExcelObj = New-Object -comobject Excel.Application
$ExcelWorkBook = $ExcelObj.Workbooks.Open("C:\PS\corp_ad_users.xlsx")
$ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("CORP_Users")
# Get the number of filled in rows in the XLSX worksheet
$rowcount=$ExcelWorkSheet.UsedRange.Rows.Count
# Loop through all rows in Column 1 starting from Row 2 (these cells contain the domain usernames)
for($i=2;$i -le $rowcount;$i++){
$ADusername=$ExcelWorkSheet.Columns.Item(1).Rows.Item($i).Text
# Get the values of user attributes in AD
$ADuserProp = Get-ADUser $ADusername -properties telephoneNumber,department,mail|select-object name,telephoneNumber,department,mail
# Fill in the cells with the data received from AD
$ExcelWorkSheet.Columns.Item(4).Rows.Item($i) = $ADuserProp.telephoneNumber
$ExcelWorkSheet.Columns.Item(5).Rows.Item($i) = $ADuserProp.department
$ExcelWorkSheet.Columns.Item(6).Rows.Item($i) = $ADuserProp.mail
}
# Save the XLS file and close Excel
$ExcelWorkBook.Save()
$ExcelWorkBook.close($true)

Como resultado, las columnas que contienen información de AD se agregaron para cada usuario en el archivo de Excel.

Consideremos otro ejemplo de elaboración de un informe utilizando PowerShell y Excel. Supongamos que desea generar un informe de Excel sobre el estado del servicio Print Spooler en todos los servidores de dominio.

# Create an Excel object
$ExcelObj = New-Object -comobject Excel.Application
$ExcelObj.Visible = $true
# Add a workbook
$ExcelWorkBook = $ExcelObj.Workbooks.Add()
$ExcelWorkSheet = $ExcelWorkBook.Worksheets.Item(1)
# Rename a worksheet
$ExcelWorkSheet.Name = 'Spooler Service Status'
# Fill in the head of the table
$ExcelWorkSheet.Cells.Item(1,1) = 'Server Name'
$ExcelWorkSheet.Cells.Item(1,2) = 'Service Name'
$ExcelWorkSheet.Cells.Item(1,3) = 'Service Status'
# Make the table head bold, set the font size and the column width
$ExcelWorkSheet.Rows.Item(1).Font.Bold = $true
$ExcelWorkSheet.Rows.Item(1).Font.size=15
$ExcelWorkSheet.Columns.Item(1).ColumnWidth=28
$ExcelWorkSheet.Columns.Item(2).ColumnWidth=28
$ExcelWorkSheet.Columns.Item(3).ColumnWidth=28
# Get the list of all Windows Servers in the domain
$computers = (Get-ADComputer -Filter 'operatingsystem -like "*Windows server*" -and enabled -eq "true"').Name
$counter=2
# Connect to each computer and get the service status
foreach ($computer in $computers) {
$result = Invoke-Command -Computername $computer -ScriptBlock { Get-Service spooler | select Name, status }
# Fill in Excel cells with the data obtained from the server
$ExcelWorkSheet.Columns.Item(1).Rows.Item($counter) = $result.PSComputerName
$ExcelWorkSheet.Columns.Item(2).Rows.Item($counter) = $result.Name
$ExcelWorkSheet.Columns.Item(3).Rows.Item($counter) = $result.Status
$counter++
}
# Save the report and close Excel:
$ExcelWorkBook.SaveAs('C:\ps\Server_report.xlsx')
$ExcelWorkBook.close($true)

Puede usar PowerShell para acceder a Excel en una variedad de escenarios. Por ejemplo, puede crear prácticos informes de Active Directory o crear scripts de PowerShell para actualizar los datos de AD desde Excel.

Set-ADUser