Buongiorno Mr Script! Formazione in Excel

The Microsoft Scripting Guys

che sia trascorso il mese scorso vivere e l'utilizzo in base ad Australia, questo Scripting Guy è costantemente amazed dal creatività e affidabilità generale dei colleghi Aussie. Pochi notti fa, un amico impiegato la moglie Scripting e mi out per cena. Ha detto, che il ristorante era un breve percorso dal nostro hotel.

40 Minuti di ritardo, è arrivato in una selezione ristorante overlooking Harbor Sydney, con Sydney opera A glistening come un seashell washed alto in un spiaggia multicolore. Il bridge Harbour, ovvero il bridge singolo periodo più lungo nel mondo, impone ordine tenendo il peninsulas due contemporaneamente come un massiccio clamp restraining due aree warped.

Nuovo principale 40 minuti di esame non è un breve stroll per cena. Si tratta di un Allenamento giornaliero preceduto da 15 minuti di warm-ups e adattamento.

Uno dei motivi che questo Scripting Guy è godendo il viaggio verso il basso in è per la prospettiva diversa che sono i colleghi. Sembra un modo univoco di esaminare elementi, spesso invia i preconceived approcci di come operazioni devono essere eseguite.

In script, è facile tutti troppo ottenere in un rut rimanere il corso con le metodologie provato a e a true. Più volte, utilizzando lo stesso approccio per ottenere lo stesso risultato è esattamente quello desiderato.

Dal punto di vista di monitoraggio, ad esempio, utilizzando Windows PowerShell è possibile facilmente ottenere uno snapshot interessante di utilizzo di processo nel computer locale. È possibile utilizzare il cmdlet Get-processo e visualizzata con output interessante, ordinato, come illustrato nella Figura 1 .

Figura 1 ricerca in processi locali utilizzando Get-processo

I risultati del cmdlet Get-Process sono utili in molti casi. Visualizzare il numero di handle aperti, un paio di diverse visualizzazioni di consumo di memoria e uno snapshot di utilizzo della CPU. E quando Windows PowerShell 2.0 insieme, è anche potrà utilizzare Get-processo con un parametro –computername per recuperare questa panoramica da un computer remoto. Con tutti i questo Web perché nel mondo si chiunque mai preoccuparsi analisi di qualsiasi altro?

Il problema è è molto di dettaglio nascosto nelle colonne di dati lunghe. Tutti i dati si tende a maschera i dettagli più importanti. E mentre è grande che in futuro Windows Power­Shell 2.0 verranno supporta il –computername parametro, ora cui assolutamente nulla per un amministratore di rete. Di conseguenza, è solo necessario utilizzare Strumentazione gestione Windows (WMI) e la classe WMI Win32_Process per eseguire il monitoraggio di sistemi remoti e presentare le informazioni in modo da veramente utile. Se è considerata che dell'output del processo di lettura è stata bountiful, ottenere un carico dell'output da Win32_Process, illustrato nella Figura 2 .

Nella figura 2 Using WMI per visualizzare i processi

Qual è un amministratore di rete ol'scarsa da eseguire quando tutti i che desidera è un rapporto di facile lettura della quantità di memoria utilizzata? Si tratta in cui è necessario considerare all'esterno della casella, sfruttare la shell e iniziano in Excel da automazione. Più probabile, che si sia installato nel computer in uso Microsoft Office Excel. Se si è simile a Microsoft, non si è esperti, ma fa parte di Microsoft Office system ed è potrebbe come e utilizzare di esso.

Come disco rigido è per l'automazione di Excel? È effettivamente molto semplice poiché Microsoft ha creato un modello di automazione in modo specifico per l'utilizzo di Excel. L'ID di programma è Excel.Application e si tratta di un oggetto COM. Quando si crea un'istanza dell'oggetto Excel.Application, per impostazione predefinita Excel verrà avviare ed eseguire, ma non sarà visibile. Tuttavia, è possibile utilizzare la proprietà visibile per rendere visibile Excel.

Di seguito viene illustrato come creare l'oggetto Excel.Application, determinare lo stato della proprietà visibile e quindi impostare la proprietà visible su true:

PS C:\> $excel = New-Object -ComObject Excel.Application
PS C:\> $excel.Visible
False
PS C:\> $excel.Visible = $true

Termine è che, visualizzata con una visualizzazione di Excel, che sembra solo della shell l'applicazione Excel piuttosto insolita (vedere la Figura 3 ). Esistono non le cartelle di lavoro, non i fogli di calcolo, ovvero non ma Excel naked.

Nella figura 3 naked Excel, non le cartelle di lavoro o fogli di calcolo

È necessario aggiungere una cartella di lavoro per l'applicazione. A tale scopo, utilizziamo il metodo componente dall'oggetto cartella di lavoro. L'oggetto cartella di lavoro è accessibile dall'oggetto Excel.Application principale, come si vede qui in cui è memorizzare l'oggetto cartella di lavoro in una cartella di lavoro variabile denominata $:

$workbook = $excel.Workbooks.add()

A questo punto è necessario connettersi a un foglio di calcolo specifico. Per impostazione predefinita, quando viene aggiunta una cartella di lavoro di Excel, vengono aggiunti tre fogli di calcolo alla cartella di lavoro. Per risolvere questi fogli di calcolo problema per numero. In questa riga di codice, è possibile connettersi nel primo foglio di calcolo e memorizzare l'oggetto foglio di calcolo restituito in un elenco variabile denominata $:

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

Ora si possono scrivere i dati in tale foglio di calcolo. Le informazioni di fogli di calcolo di Excel vengono memorizzate nelle celle. Poiché le celle risiedono in fogli di calcolo, è possibile utilizzare l'oggetto foglio di calcolo memorizzato nella variabile $ foglio per accedere a una cella specifica. È necessario utilizzando numeri che fanno riferimento a righe e colonne nel foglio di calcolo. Una cosa che è un po'di confusione è che in fogli di calcolo di Excel, le righe sono numeri e le colonne sono le lettere. Quando si utilizza il modello di automazione, tuttavia, righe e colonne sono numeri. Il primo numero è la riga e il secondo numero è la colonna. È possibile scrivere la cella impostando semplicemente un'assegnazione valore a tale cella specifica:

$sheet.cells.item(1,1) = "Test"

Dopo aver aggiunto una cartella di lavoro l'oggetto Excel.Application e dati in una cella nel foglio di calcolo, la cartella di lavoro Excel simile a quello di Nella figura 4 .

Nella figura 4 di aggiunta di un valore per una cella

Tutto questo presente, si realizzare qualcosa di utile. Possibile ottenere un insieme di informazioni sul processo da WMI, scrittura, il consumo di nome e la memoria di ogni processo in un foglio di calcolo di Excel e quindi creare un grafico per evidenziare la memoria utilizzata. È sufficiente WriteProcessInformation­ToExcel.ps1 cosa. Si noterà l'intero script sul sito Web TechNet Magazine.

Iniziamo lo script utilizzando il cmdlet WmiObject Get per recuperare un insieme di informazioni sui processi. È utilizzare la classe WMI Win32_Process per ottenere queste informazioni ed è memorizzarlo in variabile $ processi:

$processes = Get-WmiObject -class Win32_Process

Ora è creare un'istanza dell'oggetto Excel.Application e archivio nella variabile $ excel, quindi rendere visibile l'applicazione e aggiungere una cartella di lavoro. È in genere necessario eseguire questi passaggi per l'automazione qualsiasi Excel che si deciso di eseguire. Di seguito sono le righe di codice:

$excel = new-object -comobject excel.application
$excel.visible = $true
$workbook = $excel.workbooks.add()

Uno dei nostri annoyances con Excel è la modalità di una cartella di lavoro crea sempre tre fogli di calcolo. Microsoft considera per essere wasteful perché poco è possibile utilizzare un foglio di solo calcolo, molto meno di tre. Fortunatamente, con l'automazione, è sufficiente eliminare i fogli di calcolo aggiuntivi utilizza l'insieme di fogli di lavoro per connettersi al terzo foglio di calcolo e chiamando il metodo delete. Lo stesso risultato può essere ottenuto per eliminare il secondo foglio di calcolo:

$workbook.workSheets.item(3).delete()
$workbook.WorkSheets.item(2).delete()

Successivamente, si rinominare il foglio di calcolo rimanente. Questo aspetto è importante perché se si desidera sempre utilizzare gli oggetti dei dati ActiveX (ADO) per eseguire query nel foglio di calcolo di Excel, è possibile utilizzare il nome del foglio di calcolo nella stringa di connessione. Pertanto, affinché il codice leggibile e intuitivo, il foglio di calcolo deve dispongono un nome logico. Per rinominare il foglio di calcolo, assegnare semplicemente un nuovo valore per la proprietà nome del foglio di calcolo specifico. Qui, si rinomina il primo foglio di calcolo "processi":

$workbook.WorkSheets.item(1).Name = "Processes"

A questo punto è necessario connettersi al foglio di calcolo rinominato. Utilizzare il metodo Item dall'oggetto di fogli di lavoro e assegnargli il nome del foglio di calcolo:

$sheet = $workbook.WorkSheets.Item("Processes")

La prima riga del foglio di calcolo di includeranno informazioni di intestazione. Verrà disegnare un bordo e grassetto i nomi delle proprietà. Di conseguenza, i dati inizierà nella riga 2, è assegnare il valore 2 per il contatore variabile $ x:

$x = 2

Successivamente, quattro righe di codice creare quattro tipi di enumerazione. I tipi di enumerazione vengono utilizzati per indicare a Excel i valori consentiti per tipi specifici di opzioni. Ad esempio, enumerazione xlLineStyle viene utilizzata per determinare il tipo di riga per disegnare: doppio, tratteggiate e così via. Questi valori di enumerazione sono disponibili su MSDN.

Per semplificare il codice per la lettura, è possibile creare alias di scelta rapida per ogni dei tipi di enumerazione quattro che si utilizzeranno. È in pratica, si esegue il cast una stringa che rappresenta il nome dell'enumerazione per [tipo]. Questa tecnica è effettivamente un trucco molto interessante:

$lineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
$colorIndex = "microsoft.office.interop.excel.xlColorIndex" -as [type]
$borderWeight = "microsoft.office.interop.excel.xlBorderWeight" -as [type]
$chartType = "microsoft.office.interop.excel.xlChartType" -as [type]

A questo punto è necessario formattare la prima riga. Vengono apportate il tipo di carattere grassetto, definire la riga come xlDashDot colore automaticamente specificare e impostare lo spessore del bordo su spessore medio:

For($b = 1 ; $b -le 2 ; $b++)
{
 $sheet.cells.item(1,$b).font.bold = $true
 $sheet.cells.item(1,$b).borders.LineStyle = $lineStyle::xlDashDot
 $sheet.cells.item(1,$b).borders.ColorIndex = $colorIndex::xlColorIndexAutomatic
 $sheet.cells.item(1,$b).borders.weight = $borderWeight::xlMedium
}

Al termine che è, vengono assegnati valori alla prima riga selezionando la cella con il metodo di elemento e fornendo le coordinate di riga e nella colonna. Successivamente, è eseguire un'assegnazione valore semplici per scrivere le intestazioni di colonna:

$sheet.cells.item(1,1) = "Name of Process"
$sheet.cells.item(1,2) = "Working Set Size"

A questo punto è necessario inserire le informazioni di processo memorizzate nella variabile $ processi creato di conseguenza la query WMI in celle appropriate. È utilizzare l'istruzione foreach per scorrere l'insieme di informazioni sul processo. È definire il variabile $ processo l'enumeratore (segnaposto) per l'insieme e si sceglie il nome e le proprietà workingSetSize per scrivere rispettivamente la colonna della prima e seconda.

Di seguito è dove il simbolo $ x variabile entra in gioco. Abbiamo iniziano con la seconda riga e come è scorrere l'insieme dei processi, è incrementare il valore del $ x variabile in modo che punti sempre alla riga corrente nell'insieme. Questa è la modalità è in grado di ordinare tutti i dati archiviati nell'insieme di informazioni sul processo dei processi $:

Foreach($process in $processes)
{
 $sheet.cells.item($x, 1) = $process.name
 $sheet.cells.item($x,2) = $process.workingSetSize
 $x++
} #end foreach

Dopo che è hanno compilato il foglio di calcolo di Excel, che si desidera modificare le dimensioni di colonne in modo che le celle siano i dati che sono memorizzati sono la stessa dimensione. A tale scopo, è possibile creare un intervallo specificando le coordinate di colonna da utilizzare; è tuttavia possibile utilizzare semplicemente la proprietà usedRange dal foglio di calcolo. Quando è stata creata l'oggetto range, si sceglie la proprietà EntireColumn e utilizzare il metodo di adattamento automatico per ridimensionare le colonne. Poiché questo metodo restituisce sempre i dati, abbiamo pipe i risultati la Out-Null cmdlet. In questo modo da occupare fino alla console con informazioni inutili. Ecco il codice che viene utilizzato:

$range = $sheet.usedRange
$range.EntireColumn.AutoFit() | out-null

A questo punto, è stato possibile interrompere e si avrebbe un foglio di calcolo interessante con il nome e la memoria working set di tutti i nostri processi. Ma opportuno procedere e creare un grafico. È facile. È utilizzare il metodo aggiunta dell'oggetto grafici dalla cartella di lavoro. Poiché questo metodo restituisce anche informazioni non si desidera visualizzare, abbiamo pipeline i risultati la Out-Null cmdlet, come illustrato di seguito:

$workbook.charts.add() | out-null  

Il comando precedente viene aggiunto un grafico a linee. Se si desidera definire un tipo diverso di grafico, è necessario utilizzare uno dei valori di enumerazione di tipo t grafico. A tale scopo, è possibile apportare utilizzare uno dei valori enumerazione microsoft.office.interop.excel.xlChartType, ad esempio il tipo xl3DPieExploded. Il tipo di xl3DPie­Exploded crea non ovviamente un grafico a torta 3D viene esplosa. È assegnare questo tipo di enumerazione chartType proprietà dell'oggetto ActiveChart. Quindi è possibile assegnare l'origine dati per il grafico per l'intervallo definito nella variabile $ intervallo. Il risultato è che viene visualizzato il grafico a linee flash brevemente e quindi il grafico a torta 3D esplosa sullo schermo. Di seguito è riportato il codice:

$workbook.ActiveChart.chartType = $chartType::xl3DPieExploded
$workbook.ActiveChart.SetSourceData($range)

Purché si riproduzione intorno a ruotare seguito grafico a torta. Questo scopo è possibile tramite la proprietà rotazione dell'oggetto ActiveChart. Utilizziamo un per istruzione incrementare il conteggio e 360 per 15. 360 è il numero di gradi in un cerchio, il grafico verrà ruotata un cerchio completo 15 gradi alla volta. Effettivamente sembra molto interessante. In questo campo è il codice per eseguire questo tra:

For($i = 1 ; $i -le 360 ; $i +=15)
{
 $workbook.ActiveChart.rotation = $i
}

L'ultima operazione da eseguire è possibile salvare il foglio di calcolo. A tale scopo, è possibile utilizzare il cmdlet percorso test se il foglio di calcolo è già presente. In caso affermativo, si elimina quella precedente utilizzando il cmdlet elemento Rimuovi e quindi si salva la cartella di lavoro corrente nel percorso memorizzato nella variabile $ strPath. È utilizzare l'oggetto ActiveWorkbook dall'oggetto Excel.Application e il metodo SaveAs di salvare la cartella di lavoro. Se non esiste già una copia del foglio di calcolo salvato, è necessario utilizzare il metodo SaveAs dall'oggetto ActiveWorkbook e salvarlo direttamente:

IF(Test-Path $strPath)
  { 
   Remove-Item $strPath
   $Excel.ActiveWorkbook.SaveAs($strPath)
  }
ELSE
  {
   $Excel.ActiveWorkbook.SaveAs($strPath)
  }

Quando viene eseguito lo script, viene visualizzata con un grafico solo come quello illustrato nella Nella figura 5 .

Nella figura 5 il processi esplosa grafico a torta

Il foglio di calcolo è nella scheda processi. Figura 6 Mostra intestazioni di colonna, lo stile della linea tratto e punto abbiamo selezionate per il bordo e le testine di colonna in grassetto. I nomi dei processi e il proprietà dimensione di lavoro insieme sono le due colonne di dati che si sono visualizzate.

Nella figura 6 il foglio di calcolo completato

Come si può notare, con il modello di automazione Excel.Application sono gli strumenti necessari per consentire elaborare i dati dai server in modo che possono sfruttare l'analisi e grafici strumenti ricca e potente applicazione.

Wilson ed è un consulente senior in Microsoft e un esperto di script noto. È un trainer Certified Microsoft che recapita un workshop di Windows PowerShell comuni clienti Premier Microsoft nel mondo. Ha scritto otto libri, tra cui più in Windows script e ha contribuito a libri quasi una dozzina. Ed contiene più di 20 certificazioni del settore.

Craig Liebendorfer è wordsmith e longtime editor Web di Microsoft. Craig ancora non ritiene c'è un processo che paga gli lavorare con le parole ogni giorno. Uno dei suoi elementi preferiti è irreverent umorismo in modo che deve adattare destra in questa posizione. Ha considerato suo accomplishment maggiore nella vita da sua figlia magnificent.