Centrum skryptów - Microsoft office

W jaki sposób dokonywać zapisu w pliku Excel bez pomocy programu Excel?

Udostępnij na: Facebook

Skrypciarze odpowiadają na Wasze pytania

Cześć Skrypciarze!

Witamy w rubryce TechNet, w której Skrypciarze z firmy Microsoft odpowiadają na częste pytania dotyczące używania skryptów w administracji systemu. Jeśli macie jakieś pytania z tej dziedziny, zachęcamy do wysłania e-maila na adres: scripter@microsoft.com. Nie możemy zagwarantować odpowiedzi na każde otrzymane pytanie, ale staramy się jak możemy.

W jaki sposób dokonywać zapisu w pliku Excel bez pomocy programu Excel?

Cześć Skrypciarze! Pytanie

Cześć, Skrypciarze! Muszę zapisać pewne informacje pliku Excel. Nie chcę instalować systemu Microsoft Office Excel na moim serwerze, jednak arkusze kalkulacyjne stanowią bardzo wygodny sposób przechowywania informacji. Mógłbym przekazywać je do mojego mądralińskiego szefa, który na ich podstawie budowałby tabele przestawne, wykresy czy inne tego typu konstrukcje, których tworzenie stanowi jego główne zajęcie. Mam nadzieję, że jeśli przekażę mu wystarczająco dużo danych, zajmie się sobą i pozwoli mi w spokoju wykonywać moje obowiązki. Czy możecie mi pomóc?

-TW

Cześć Skrypciarze! Pytanie

Cześć, TW,

Czy znasz takie stare powiedzenie odnoszące się do szefów? Jeśli nie zdołasz olśnić ich błyskotliwością, zasyp ich… danymi. Ogromną ilością danych. A zatem chcesz dokonywać zapisu w pliku Excel bez instalowania systemu Excel. To życzenie przypomina pytanie, jakie otrzymaliśmy w zeszłym tygodniu od innego skrypciarza. Z tą różnicą, że on chciał wczytywać pliki Excel. Cóż, mamy dla Was dwie wiadomości: dobrą i lepszą. Dobra wiadomość jest taka, że do dokonywania zapisu w arkuszach kalkulacyjnych Excel możemy użyć tej samej techniki polegającej na wykorzystaniu ADO.NET. A lepsza wiadomość jest taka, że pokażemy Wam, jak sprostać temu wyzwaniu.
Jeśli na maszynie nie został zainstalowany system Excel, możemy wykorzystać klasyczne COM ADO lub ADO.NET. W tym przykładzie użyjemy ADO.NET. Zasadniczo musimy stworzyć dwa obiekty: System. Data.OleDb.OleDbConnection oraz System.Data.OleDb.OleDbCommand. Następnie musimy zbudować kwerendę. Kwerenda wykorzystywać będzie składnię Insert into. Ponadto będziemy musieli określić parametry dla wszystkich właściwości, które chcemy przechowywać, wywołać metodę ExecuteNonQuery obiektu OleDbCommand i zamknąć połączenie. Następujący skrypt realizuje wszystkie wymienione zadania:

$strFileName = "C:\test\shares.xls"

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

$strDataSource = "Data Source ="+$strfilename

$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Insert into [Shares$] (Name,Path,Description,Type) Values (?,?,?,?)"

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

$sqlCommand = new-object System.Data.OleDb.OleDbCommand($strQuery)

$sqlCommand.Connection = $objConn

$NameParam = $sqlCommand.parameters.add("Name","VarChar",80)

$PathParam = $sqlCommand.parameters.add("Path","VarChar",80)

$DescriptionParam = $sqlCommand.parameters.add("Description","VarChar",80)

$TypeParam = $sqlCommand.parameters.add("Type","UnsignedInt",16)

$objConn.open()



$shares = Get-WmiObject -Class Win32_share

ForEach($share in $Shares)

{

$aReturn = $NameParam.value = $share.name

$breturn = $PathParam.value = $share.path

$cReturn = $DescriptionParam.value = $share.Description

$dReturn = $TypeParam.value = $share.Type

$returnValue = $sqlCommand.ExecuteNonQuery()

}

$objConn.close()

Na początku musimy określić ścieżkę do arkusza kalkulacyjnego Excel. W tym skrypcie nie będziemy tworzyć pustego arkusza, ponieważ przyjmujemy założenie, że został on utworzony wcześniej. Arkusz kalkulacyjny Excel będzie służył do przechowywania informacji o udziałach zdefiniowanych na serwerze. Poniższy rysunek ilustruje prostotę pustego arkusza:

Następnie musimy określić nazwę dostawcy. Do komunikowania się z arkuszem Excel użyjemy dostawcy Microsoft.Jet.OLEDB.4.0. Zdefiniujemy źródło danych w postaci ścieżki do arkusza kalkulacyjnego. A do dostarczenia rozszerzonych właściwości ciągu połączenia użyjemy składni Extended Properties = Excel 8.0. Ta część skryptu prezentuje się następująco:

$strFileName = "C:\test\shares.xls"

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

$strDataSource = "Data Source ="+$strfilename

$strExtend = "Extended Properties=Excel 8.0"

Jedno z największych wyzwań towarzyszących opracowywaniu tego skryptu stanowiła składnia Insert into dla kwerendy. Sama instrukcja Insert into jest całkiem oczywista. Ponadto dzięki doświadczeniom zdobytym podczas pisania zeszłotygodniowego skryptu wiedzieliśmy, że nazwę arkusza należy umieścić w nawiasach kwadratowych. To na pozostałą część tej konstrukcji poświęciliśmy godziny prób i błędów. Po określeniu nazwy arkusza musimy podać umieszczoną w nawiasie listę kolumn, w których będziemy dokonywać zapisu. Ich wartości definiować będziemy później, w związku z czym stosujemy znaki zapytania, po jednym dla każdej kolumny. W przyszłości znaki zapytania zostaną zastąpione konkretnymi wartościami podanymi w odpowiedniej kolejności. Składnia kwerendy jest charakterystyczna dla dostawców OleDb. Poniżej z dumą prezentujemy kwerendę Insert into:

$strQuery = "Insert into [Shares$] (Name,Path,Description,Type) Values (?,?,?,?)"

Po rozpracowaniu kwerendy musimy zająć się tworzeniem obiektu połączenia oraz obiektu polecenia. Definiując obiekt OleDbConnection, określamy nazwę dostawcy, źródło danych oraz rozszerzone parametry. Następnie do obiektu polecenia przekazujemy ciąg, który zawiera naszą kwerendę Insert into. Po stworzeniu tych dwóch obiektów przypisujemy obiekt połączenia właściwości Connection obiektu polecenia. Ta sekcja kodu została pokazana poniżej:

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

$sqlCommand = new-object System.Data.OleDb.OleDbCommand($strQuery)

$sqlCommand.Connection = $objConn

Teraz, gdy już stworzyliśmy oba obiekty i powiązaliśmy je ze sobą, musimy zdefiniować parametry. Parametry służą do przekazywania danych do arkusza Excel. Dane dostarczymy później przy pomocy właściwości value parametrów. Należy pamiętać o zachowaniu odpowiedniej kolejności parametrów, ponieważ zastąpią one znaki zapytania, które wykorzystaliśmy we wcześniejszej części kwerendy. Podczas tworzenia obiektu parametru należy określić trzy wartości: nazwę parametru, typ danych oraz rozmiar danych, które mamy zamiar przechowywać w parametrze. Oto kod służący do tworzenia parametrów:

$NameParam = $sqlCommand.parameters.add("Name","VarChar",80)

$PathParam = $sqlCommand.parameters.add("Path","VarChar",80)

$DescriptionParam = $sqlCommand.parameters.add("Description","VarChar",80)

$TypeParam = $sqlCommand.parameters.add("Type","UnsignedInt",16)

Teraz musimy otworzyć połączenie, wykonać naszą kwerendę WMI, przejść w pętli przez dane wynikowe i przypisać je do parametrów. Na końcu każdej sekwencji musimy użyć metody ExecuteNonQuery do zapisania informacji w arkuszu kalkulacyjnym. Kontynuujemy przetwarzanie aż do momentu zakończenia procesu przechodzenia przez dane. Na zakończenie zamykamy połączenie. Oto omówiona sekcja skryptu:

$objConn.open()



$shares = Get-WmiObject -Class Win32_share

ForEach($share in $Shares)

{

$aReturn = $NameParam.value = $share.name

$breturn = $PathParam.value = $share.path

$cReturn = $DescriptionParam.value = $share.Description

$dReturn = $TypeParam.value = $share.Type

$returnValue = $sqlCommand.ExecuteNonQuery()

}



$objConn.close()

Po uruchomieniu skrypt zapisuje informacje o udziałach w arkuszu kalkulacyjnym, jak widać na poniższym rysunku:

I tak oto, TW, odkryliśmy tajemnice dokonywania zapisu w plikach Excel - nawet wtedy gdy system Excel nie jest dostępny. Mamy nadzieję, że ten doskonały skrypt wspomoże Cię w procesie tworzenia ogromnych ilości danych, które zapewnią Twojemu szefowi wielogodzinną rozrywkę. A może warto wypróbować kulkę folii aluminiowej na sznureczku…

Ed Wilson i Craig Liebendorfer, Skrypciarze

 Do początku strony Do początku strony

Centrum skryptów - Microsoft office