Dziś w ramach ciekawostki zaprezentuję jak można zarządzać danymi w plikach Excelowych z poziomu PowerShella. Do tego celu wykorzystamy klasę Microsoft COM (Component Object Model) Excel.Application.
Zanim jednak przystąpimy do działania stworzymy sobie testowy zbiór danych w excelu, np. dyskografia zespołu Genesis:
| Rok | Album |
| 1969 | From Genesis To Revelation |
| 1970 | Trespass |
| 1971 | Nursery Cryme |
| 1972 | Foxtrot |
| 1973 | Selling England by the Pound |
| 1974 | The Lamb Lies Down on Broadway |
| 1976 | A Trick of the Tail |
| 1976 | Wind & Wuthering |
| 1978 | And Then There Were Three |
| 1980 | Duke |
| 1981 | Abacab |
| 1983 | Genesis |
| 1986 | Invisible Touch |
| 1991 | We Can’t Dance |
Rozpoczynając zabawę z Excelem tworzymy sobie zmienne przechowujące ścieżkę do pliku i nazwę arkusza oraz deklarujemy obiekt typu Excel.Application.
$XLSDoc = "p:\dyskografia.xls" $SheetName = "Arkusz1" $Excel = New-Object -ComObject "Excel.Application"
Teraz może przystąpić do otwierania naszego pliku, a następnie arkusza na którym będziemy pracować.
$Workbook = $Excel.workbooks.open($XLSDoc) $Sheet = $Workbook.Worksheets.Item($SheetName)
Jeśli chcemy mieć podgląd na okno Excela to musimy ustawić parametr visible obiektu $Excel na $true.
$Excel.Visible = $true
Podstawowe dane o Arkuszu możemy przejrzeć wykorzystując dostępne wartości obiektów:
$Workbook | Get-Member $Sheet | Get-Member
Natomiast aby przeglądać odpowiednie komówki wykorzystujemy parametr Cells oraz wskazujemy odpowiednią pozycję metodą Item(wiersz,kolumna)
$Sheet.Cells.Item(1,1).Text
Aby wpisać dane w odpowiednią komórkę powinniśmy najpierw ją zaznaczyć, a następnie wpisać w nią odpowiednią wartość.
$Sheet.Cells.Item(16,1).Activate() $Excel.ActiveCell.Value2 = "1997"
Lub po prostu bez konieczności zmiany aktywnego pola:
$Sheet.Cells.Item(16,2) = "Calling All Stations"
Możliwe jest również masowe wprowadzanie danych dla zaznaczonego obszaru:
$Excel.ActiveSheet.Range("C2:C16").Value2 = "1"
Sprubujmy teraz wyszukać jakieś dane. Do tego celu tworzymy sobie zmienną, której wartość będzie przechowywała szkaną frazę oraz musimy wskazać obszar w jakim będziemy jej wyszukiwać.
$Search = "Genesis"
$Range = $Sheet.Range("A1:B16")
Następnie wywołujemy metodę Find na obiekcie zakresu poszukiwania Range.
$Result = $Range.Find($Search) $Result | Select Row, Text
Jako wynik wyszukiwania dostajemy najbliższą komórkę posiadającą wyszukiwaną frazę. W przypadku większej ilości znalezionych komórek możemy się po nich przemieszczać dzięki metodom FindNext oraz FindPrevious.
$Result = $Range.FindNext($Result) $Result | Select Row, Text $Result = $Range.FindPrevious($Result) $Result | Select Row, Text
Gdy jednak chcemy znaleźć komórkę która dokłądnie zawiera rządaną treść musimy skorzystać z metody Match. Niestety Funkcja ta w przeciwieństwie do Find zwraca tylko numer wiersza.
$Excel.WorksheetFunction.Match($Search,$Range)
Z dodatkowych ciekawostek jeszcze, aby utworzyć nowy arkusz korzystamy z metody Add.
$Excel.Sheets.Add()
Na zakończenie pracy możemy zapisać swoje zmiany poprzez metodę Save albo SaveAs.
$Workbook.Save()
$Workbook.SaveAs("p:\kopia-dyskografia.xls")
Na zakończenie pracy zamykamy proces aplikacji Excel.
$Excel.quit()
Źródła:
http://msdn.microsoft.com/en-us/library/wss56bz7(v=vs.80).aspx
