Jak czytać z pliku Excel bez pomocy programu Excel?

Skrypciarze odpowiadają na Wasze pytania

Jak czytać z pliku Excel bez pomocy programu Excel?

Mam arkusz kalkulacyjny Microsoft Office Excel, który zawiera nazwy serwerów i chciałbym użyć go w skrypcie Windows PowerShell. Problem polega na tym, że na serwerze nie mam systemu Microsoft Office ani Excel. W jaki sposób mogę skorzystać z tego arkusza bez instalowania pakietu Office na serwerze (co o ile dobrze rozumiem i tak nie jest wspierane)?

-FE

Cześć, FE,

A zatem chcesz korzystać z systemu Excel, ale nie chcesz go instalować? Przypomina mi to zagadki, które zadawano mi w dzieciństwie. Te w stylu: " Jak podzielić cztery jabłka między pięć osób? Ugotować kompot" albo "Co to jest: im zimniej tym grubszy, im cieplej tym chudszy?" Lód. Chodzi oczywiście o lód, czyli wodę w stanie stałym, a nie lud. A tak na marginesie homofony bywają nieco mylące. Jak w tej bajce o jaju i jeżu: "Ja jem, ty jesz, a jemu ślinka cieknie". Słyszałem, że pewien pasjonat zgromadził 441 homofonów języka angielskiego (niewątpliwą zaletą tego typu kolekcji jest fakt, że nie zajmuje ona tyle miejsca co kolekcja książek czy samochodów).

Często gdy w kodzie języka skryptowego używamy plików Excel, decydujemy się na zastosowanie modelu obiektowego programu Excel. Wykorzystanie modelu obiektowego programu Excel generalnie wiąże się ze stworzeniem instancji obiektu Excel.Application, co ilustruje poniższy skrypt:

$strPath="c:\data\ScriptingGuys\Servers.xls"

$objExcel=New-Object -ComObject Excel.Application

$objExcel.Visible=$false

$WorkBook=$objExcel.Workbooks.Open($strPath)

$worksheet = $workbook.sheets.item("ServerList")

$intRowMax =  ($worksheet.UsedRange.Rows).count

$Columnnumber = 1



for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)

{

$name = $worksheet.cells.item($intRow,$ColumnNumber).value2

"Querying $name ..."

Get-WmiObject -Class win32_bios -computername $name

}

$objexcel.quit()

Jednak skoro FE wyraźnie zaznaczył, że nie chce przetwarzać arkusza kalkulacyjnego przy pomocy modelu obiektowego programu Excel, pozostaje nam zastosowanie obiektów Active X Data Objects (ADO). Występują dwie odmiany technologii ADO: klasyczne COM ADO, które można było wykorzystywać w VBScript oraz ADO.NET. Zasadniczo ADO.NET jest szybsze i oferuje więcej możliwości. Obie wersje COM ADO oraz ADO.NET wymagają określenia dostawcy. W niektórych przypadkach nazwy dostawców będą jednakowe, w innych znaleźć można dostawców dedykowanych specjalnie dla określonej wersji ADO.

W tym skrypcie stworzymy dwa obiekty: System.Data.OleDb.OleDbConnection oraz System.Data.OleDb.OleDbCommand. Jak łatwo zauważyć, oba te obiekty znajdują się w przestrzeni nazw System.Data.OleDb, zmianie ulega jedynie ostatni człon nazwy. Są to odpowiedniki obiektów połączenia i polecenia wykorzystywanych w klasycznym COM ADO. Po stworzeniu dwóch obiektów powiązujemy obiekt połączenia z obiektem polecenia, otwieramy połączenie i wywołujemy metodę ExecuteReader obiektu polecenia. Następnie przy pomocy obiektu DataReader przechodzimy przez otrzymany zbiór danych. Wybieramy kolumnę name, przekazujemy ją do WMI i zamykamy zarówno obiekt DataReader, jak i połączenie. Omówiony skrypt wygląda następująco:

$strFileName = "C:\Data\scriptingGuys\Servers.xls"

$strSheetName = 'ServerList$'

$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"

$strDataSource = "Data Source = $strFileName"

$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Select * from [$strSheetName]"



$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")

$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)

$sqlCommand.Connection = $objConn

$objConn.open()

$DataReader = $sqlCommand.ExecuteReader()



While($DataReader.read())

{

$ComputerName = $DataReader[0].Tostring()

"Querying $computerName ..."

Get-WmiObject -Class Win32_Bios -computername $ComputerName

}

$dataReader.close()

$objConn.close()

A teraz przyjrzyjmy się skryptowi w sposób nieco bardziej szczegółowy. Skrypt rozpoczyna się od zainicjalizowania szeregu zmiennych. Pierwsza zmienna stanowi ścieżkę do arkusza kalkulacyjnego Excel (w celu uproszczenia skryptu pominęliśmy funkcję sprawdzającą poprawność ścieżki, jednak w prawdziwym skrypcie z pewnością warto zweryfikować istnienie pliku przed dokonaniem próby odczytu - w powłoce PowerShell służy do tego cmdlet Test-Path). Następnie podajemy nazwę arkusza. Jak można zauważyć na podstawie załączonego rysunku, Skrypciarze mają w zwyczaju zmieniać nazwy arkuszy kalkulacyjnych Excel (a także usuwać dwa dodatkowe, często niewykorzystywane arkusze):

Następnie musimy określić nazwę, źródło danych oraz rozszerzone parametry połączenia. Są to te same informacje, które wykorzystywaliśmy w klasycznym COM ADO. Na zakończenie definiujemy kwerendę, która przyjmuje postać "Select * from [nazwaarkusza]". Należy zauważyć, że nazwa arkusza kalkulacyjnego musi zostać umieszczona w nawiasach kwadratowych. Ta sekcja skryptu została zaprezentowana poniżej:

$strFileName = "C:\Data\scriptingGuys\Servers.xls"

$strSheetName = 'ServerList$'

$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"

$strDataSource = "Data Source = $strFileName"

$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Select * from [$strSheetName]"

Kolejnym celem jest stworzenie dwóch potrzebnych nam obiektów. Aby utworzyć obiekt OleDbConnection, określamy dostawcę, źródło danych i rozszerzone parametry w poleceniu New-Object (strukturę tę nazywany konstruktorem, ponieważ służy ona do konstruowania obiektu połączenia). Później tworzymy obiekt polecenia OleDbCommand, który akceptuje w swoim konstruktorze kwerendę. Następnie musimy powiązać obiekt połączenia z obiektem polecenia, otworzyć połączenie i wykorzystać metodę ExecuteReader obiektu OleDbCommand, która zwraca potrzebne nam informacje. Za realizację tych zadań odpowiada następujący fragment kodu:

$objConn = New-Object System.Data.OleDb.OleDbConnection ` ("$strProvider;$strDataSource;$strExtend")

$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)

$sqlCommand.Connection = $objConn

$objConn.open()

$DataReader = $sqlCommand.ExecuteReader()

Obiekt zwracany przez metodę ExecuteReader nosi nazwę DataReader. Do pracy z obiektem DataReader wykorzystujemy metodę Read. Stosujemy także instrukcję While, zgodnie z którą ponawiamy odczyt tak długo, jak długo obiekt DataReader zwraca dane:

While($DataReader.read())

A co będziemy robić z danymi zwracanymi przez obiekt DataReader? Będziemy pobierać pierwszy fragment danych i przekształcać go w typ String. Uzyskany w ten sposób ciąg będzie zawierać pobraną z arkusza nazwę komputera, którą następnie wykorzystamy w podstawowej kwerendzie WMI. Do zbudowania kwerendy WMI użyjemy cmdletu Get-WmiObject, podając odpowiednią klasę WMI (Win32_Bios) oraz nazwę komputera (tę pobraną z arkusza kalkulacyjnego), co pokazano poniżej:

$ComputerName = $DataReader[0].Tostring()

"Querying $computerName ..."

Get-WmiObject -Class Win32_Bios -computername $ComputerName

Grzecznie zamykamy obiekt DataReader oraz połączenie z arkuszem kalkulacyjnym Excel. To łatwe, wystarczy użyć metody close w następujący sposób:

$dataReader.close()

$objConn.close()

Wynik wykonania skryptu jest imponujący ze względu na minimalistyczne podejście do zwracanych danych. Mamy nadzieję, że wybrana przez nas kompozycja urzeknie Was prawdziwą głębią niebieskości uwydatnioną przez akcenty świetlistej bieli (to z myślą o tych, w których drzemie artystyczna dusza). Efekty widać na następującym rysunku:

I co FE, całkiem nieźle prawda? A to jeszcze nie wszystko. Jeśli przypadkiem mamy pod ręką arkusz Excel, możemy wykorzystać tę metodę do jego odczytania. Natomiast gdy nie mamy pod ręką żadnego arkusza kalkulacyjnego, musimy go zapisać i wypełnić danymi. Jednak tym zajmiemy się dopiero w przyszłym tygodniu.

Ed Wilson i Craig Liebendorfer, Skrypciarze

Do początku strony