BETA
Aby się zalogować, najpiew wybierz portal.
Aby się zarejestrować, najpiew wybierz portal.
Podaj słowa kluczowe
Słowa kluczowe muszą mieć co najmniej 3 sąsiadujące znaki alfanumeryczne
Pole zawiera niedozwolone znaki

Baza wiedzy











Automatyzacja Excela 2002/2003 w .NET

30-07-2004 07:58 | ja_raz141

1. Wymagania wstępne

Artykuł dotyczy Microsoft Excela z pakietu Microsoft Office XP lub 2003. Dodatkowo zakłada się obecność polskiej wersji językowej Excela, choć przykładowy program można łatwo dostosować do pozostałych wersji językowych.

W celu podłączenia się do aplikacji pakietu Microsoft Office XP konieczne staje się ściągnięcie i zainstalowanie zestawów międzyoperacyjnych modelu COM (Primary Interop Assemblies) dostępnych na stronie http://www.microsoft.com/downloads/details.aspx?FamilyId=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en (1741 kB). Zestawy te zawierają oficjalny opis najczęściej używanych bibliotek typów pakietu Microsoft Office XP i ułatwiają współdziałanie zarządzanego kodu .NET z bibliotekami typów COM pakietu Office XP. Przy użyciu Microsoft Office 2003 zabieg instalacji tych zestawów jest zbyteczny.

2. Opis aplikacji

Naszym celem będzie stworzenie prostej aplikacji podpinającej się do Excela i tworzącej w nim tabelę prezentującą miesięczne płace pracowników firmy w pewnym okresie czasu. Liczba pracowników firmy oraz liczba miesięcy będzie ustalana losowo. Dodatkowo dla każdego miesiąca policzymy sumę zarobków po wszystkich pracownikach. Zarobki pracowników na przestrzeni czasu zwizualizujemy przy pomocy prostego wykresu słupkowego. Efekt końcowy prezentuje Rysunek1.

Rysunek 1 – Efekt końcowy aplikacji

3. Implementacja

Po utworzeniu nowej solucji należy do projektu dodać referencję do biblioteki typów Excela. W przypadku korzystania z Office XP wykonuje się to wybierając z głównego menu Project/Add Reference… i z zakładki COM wybierając pozycję Microsoft Excel 10.0 Object Library. Po wciśnięciu przycisków Select i OK będziemy mogli w drzewie referencji w Solution Explorerze zaobserwować trzy dodane referencje: do Microsoft.Office.Interop.Excel, Microsoft.Office.Core oraz Microsoft.Vbe.Interop. Następnym krokiem jest deklaracja użycia w aplikacji dodatkowych przestrzeni nazw:

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

Projektowanie aplikacji zacznijmy od umieszczenia na formularzu przycisku, który będzie uruchamiał instancję Excela. Cały kod naszej aplikacji będzie zawarty w procedurze obsługi zdarzenia wciśnięcia tego przycisku.

Przy tworzeniu każdej nowej aplikacji będącej klientem Excela początek współpracy z serwerem za każdym razem wygląda podobnie, a to dlatego że zazwyczaj chcemy uzyskać dostęp do obiektu pojedynczego arkusza i jego komórek:

Excel.Application oXL = new Excel.Application();
oXL.Visible = true;
Excel.Workbooks oWBs = oXL.Workbooks;
Excel._Workbook oWB = (Excel._Workbook)(oWBs.Add(Missing.Value));
Excel._Worksheet oSheet = (Excel._Worksheet)oWB.ActiveSheet;
Excel.Range oRng;

Pierwszym krokiem jest uruchomienie instancji serwera Excela i pobranie z niej obiektu aplikacji. W tym momencie, jeśli chcemy, żeby Excel był już widoczny dla użytkownika, należy ustawić jego właściwość Visible na true. Następnie uzyskujemy dostęp do kolekcji plików obiektu aplikacji, by za chwilę dodać do niej nasz własny plik, na którym będziemy pracować. Powtarzająca się dość często w dalszym ciągu artykułu wartość Missing.Value oznacza wartość opcjonalną. Tutaj występuje ona w miejscu przeznaczonym na podanie szablonu, z którego tworzony będzie plik. Ostatecznie otrzymujemy referencję do pojedynczego arkusza (tj. pojedynczej zakładki) w pliku excelowym definiując go jako aktywny (tj. pierwszy) arkusz w utworzonym właśnie nowym pliku Excela.

Na samym początku warto już również wspomnieć o klasie Range, której obiekty będą reprezentowały dowolny obszar składający się z komórek w arkuszu excelowym. Pojęcie obszaru jest tutaj równoznaczne z pojęciem obszaru wybieranego przez użytkownika Excela, gdy ten wciska lewy klawisz myszy na pewnej komórce i przy wciśniętym lewym klawiszu myszy przesuwa prostokątne zaznaczenie obejmując nim sąsiednie komórki.

Tekst do pojedynczych komórek możemy wpisywać posługując się indeksowaną właściwością Cells arkusza. Tutaj wykorzystamy ten sposób do wprowadzenia nazw nagłówków dwóch pierwszych kolumn. Zwróćmy uwagę, że numeracja indeksów komórek w pionie i w poziomie rozpoczyna się od jedynek:

oSheet.Cells[1, 1] = "Nazwisko";
oSheet.Cells[1, 2] = "Imię";

Jako, że ilość wierszy (tj. pracowników) w zestawieniu płac będzie wyznaczana losowo, zdefiniujmy dwie jednowymiarowe tablice przechowujące imiona i nazwiska i wypełnijmy wylosowanymi z nich łańcuchami dwie pierwsze kolumny zestawienia, po raz kolejny korzystając z właściwości Cells:

string[] lastNames = new string[]
     { "Brzdąc", "Kowal", "Płyta", "Patyk", "Piknik" };
string[] firstNames = new string[]
     { "Janina", "Zdzisław", "Karol", "Ewaryst", "Genowefa" };
Random rand = new Random();
int rowsCount = rand.Next(1, 6);
for(int i = 2; i < rowsCount + 2; i++)
{
     int randLast = rand.Next(0,5);
     int randFirst = rand.Next(0,5);
     oSheet.Cells[i, 1] = lastNames[randLast];
     oSheet.Cells[i, 2] = firstNames[randFirst];
}

Również losowo zdefiniujmy ilość kolumn (tj. miesięcy), dla których wyznaczać będziemy płace pracowników. Będą one zajmować kolumny zestawienia od trzeciej włącznie. Do wygenerowania nazw miesięcy w nagłówkach tych kolumn zastosujemy w sposób programistyczny bardzo przyjazną własność Excela, zwaną autowypełnianiem. Polega ona na automatycznym wypełnianiu zakresu komórek wartościami wywnioskowanymi z przeanalizowania zawartości pierwszej komórki zaznaczenia. Tutaj wykorzystamy tą właściwość w ten sposób, że zdefiniujemy jednowierszowy obszar o losowo wybranej długości, w którego pierwszej komórce umieścimy łańcuch „Styczeń”. Jeśli teraz wywołamy metodę AutoFill(), obszar nagłówków kolumn oznaczających miesiące zostanie automatycznie wypełniony kolejnymi polskojęzycznymi nazwami miesięcy. W poniższym fragmencie kodu instancja rngFrom oznacza pierwszą komórkę z obszaru autowypełniania, zawierającą tekst „Styczeń”, zaś rngTo oznacza cały obszar poddany autowypełnianiu. Stała Excel.XlAutoFillType.xlFillMonths została użyta jako parametr metody AutoFill() w celu zaznaczenia, że kolejne komórki obszaru mają być wypełnione nazwami miesięcy:

int monthsCount = rand.Next(2,13);
oSheet.Cells[1, 3] = "Styczeń";
Excel.Range rngFrom = oSheet.get_Range("C1", Missing.Value);
Excel.Range rngTo = rngFrom.get_Resize(Missing.Value, monthsCount);
rngFrom.AutoFill(rngTo, Excel.XlAutoFillType.xlFillMonths);

Zauważmy, w jaki sposób definiujemy każdy z tych dwóch obszarów: obszar rngFrom zdefiniowaliśmy przy użyciu metody get_Range() obiektu arkusza. Przyjmuje ona jako parametry dwie excelowe współrzędne definiujące naprzeciwległe wierzchołki prostokąta obszaru. Ponieważ w tym przypadku definiujemy obszar jednokomórkowy, drugim parametrem może być wartość opcjonalna. rngTo było natomiast wygodniej zdefiniować poprzez wywołanie metody get_Resize() na zdefiniowanym przed chwilą jednokomórkowym obszarze rngFrom. Parametry tej metody oznaczają, o ile komórek, odpowiednio w pionie i w poziomie, należy zwiększyć powierzchnię obszaru. Ponieważ obszar rngTo rozszerzamy tylko w poziomie, pierwszym parametrem może być wartość opcjonalna.

Czas teraz na odpowiednie sformatowanie nagłówków wszystkich kolumn, tj. kolumn nazwiska i imienia oraz kolumn miesięcy. Po zdefiniowaniu odpowiedniego obszaru ustalamy dla niego pogrubioną czcionkę, centralne wyrównanie w pionie i w poziomie, nachylenie tekstu nagłówka do poziomu pod kątem 50 stopni, jasnozielone tło oraz cienkie linie odgraniczające:

oRng = oSheet.get_Range("A1", Missing.Value).get_Resize(Missing.Value, 2 + monthsCount);
oRng.Font.Bold = true;
oRng.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng.Orientation = 50;
oRng.Interior.ColorIndex = 4;
oRng.Borders.Weight = Excel.XlBorderWeight.xlThin;

Następnie wygenerujmy losowe dane dotyczące płac poszczególnych pracowników we wszystkich miesiącach. W tym celu dla wersji polskojęzycznej Excela skorzystamy z jego funkcji LOS():

oRng = oSheet.get_Range("C2", Missing.Value).get_Resize(rowsCount, monthsCount);
oRng.Formula = "=LOS()*10000";
oRng.NumberFormat = "# ##0,00 zł;-# ##0,00 zł";

Warto też byłoby wiedzieć, ile pieniędzy szef firmy w każdym miesiącu musi przeznaczyć na wypłaty. Do tego celu użyjemy ostatniego z wierszy, definiując w każdej jego miesięcznej kolumnie excelową formułę korzystającą z funkcji SUMA():

oRng = oSheet.get_Range("C" + (1 + rowsCount + 1), Missing.Value).get_Resize(Missing.Value, monthsCount);               
oRng.Formula = "=SUMA(C2:C"+(rowsCount+1)+")";
oRng.Font.Bold = true;
oRng.Font.ColorIndex = 3;

Teraz posortujemy całe zestawienie rosnąco wg nazwisk, a w przypadku jednakowych nazwisk, rosnąco wg imion:

oRng = oSheet.get_Range("A2", Missing.Value).get_Resize(rowsCount, 1 + monthsCount);
oRng.Sort(oRng.Columns[1, Type.Missing],
     Excel.XlSortOrder.xlAscending,
     oRng.Columns[2,Type.Missing],
     Type.Missing,
     Excel.XlSortOrder.xlAscending,
     Type.Missing,
     Excel.XlSortOrder.xlAscending,
     Excel.XlYesNoGuess.xlNo,
     Type.Missing,
     Type.Missing,
     Excel.XlSortOrientation.xlSortColumns,
     Excel.XlSortMethod.xlPinYin,
     Excel.XlSortDataOption.xlSortNormal,
     Excel.XlSortDataOption.xlSortNormal,
     Excel.XlSortDataOption.xlSortNormal);

O sortowaniu rosnącym wg pierwszej kolumny zdecydowaliśmy ustalając dwa pierwsze parametry metody Sort(). W przypadku równości kluczy po pierwszym sortowaniu, kolejne trzy parametry wyznaczają rosnący porządek sortowania po drugiej kolumnie obszaru. Można jeszcze dodefiniować trzecią kolumnę sortowania, w przypadku równości kluczy po drugim sortowaniu, w kolejnych dwóch parametrach.

Dla czytelności przekazu, skorzystamy też z możliwości automatycznego dopasowania szerokości wszystkich kolumn:

oRng = oSheet.get_Range("A1", Missing.Value).get_Resize(Missing.Value, 2 + monthsCount).EntireColumn;
oRng.AutoFit();

Teraz przystąpimy do wygenerowania słupkowego wykresu dla wprowadzonych danych o płacach. W załączonym kodzie jest to zadaniem metody DrawChart(). Pierwszym krokiem jest dodanie nowego wykresu do kolekcji wykresów zawartych w excelowym pliku:

Excel._Chart oChart = (Excel._Chart)oWB.Charts.Add(Missing.Value,
Missing.Value, Missing.Value, Missing.Value);

Podczas tworzenia wykresu w ten sposób możemy podać m. in. obiekty w arkuszu, pomiędzy którymi wykres ten ma być ulokowany, lecz tutaj przekazaliśmy parametry będące wartościami opcjonalnymi.

Następnie należy zdefiniować obszar wizualizowanych danych (dane o wysokości płac dla wszystkich pracowników):

oRng = oWS.get_Range("C2", Missing.Value).get_Resize(rowsCount, monthsCount);

Teraz, przy pomocy Chart Wizarda tworzymy słupkowy wykres 3D dla pobranych przed chwilą danych:

oChart.ChartWizard(oRng, Excel.XlChartType.xl3DColumn, Missing.Value, Excel.XlRowCol.xlColumns, Missing.Value, Missing.Value, Missing.Value,
“Miesięczne płace”, Missing.Value, Missing.Value, Missing.Value);

Przy korzystaniu z Chart Wizarda jako pierwszy parametr podajemy zakres danych źródłowych wykresu, jako drugi parametr - typ wykresu, czwarty parametr wskazuje, że serie danych umieszczone zostały w kolumnach wybranego obszaru (tzn. seriami będą miesiące), a ósmy parametr pozwala na nadanie nazwy wykresowi.

Następnie zatroszczymy się o nadanie wartościom odpowiednich etykiet na osi X. W tym celu pobieramy pierwszą kolekcję serii i przyporządkowywujemy jej właściwości XValues obszar danych zawierający nazwiska pracowników:

Excel.Series oSeries = (Excel.Series)oChart.SeriesCollection(1);
oSeries.XValues = oWS.get_Range("A2", Missing.Value).get_Resize(rowsCount, Missing.Value);

Teraz musimy nadać każdej z serii odpowiednią nazwę – będzie nią polskojęzyczna nazwa kolejnego miesiąca, uzyskana z pomocniczej metody GetMonthName():

for(int i = 1; i <= monthsCount; i++)
{
     String seriesName = "=\"" + GetMonthName(i) + "\"";
     ((Excel.Series)oChart.SeriesCollection(i)).Name = seriesName;
}

Nie pozostaje nam teraz nic innego, jak umieścić gotowy wykres jako obiekt bieżącego arkusza. Umieścimy go tak, żeby nie przysłaniał danych z zestawienia płac, tj. jego lewy górny róg umieścimy w punkcie przecięcia ósmego wiersza z trzecią kolumną. Dostęp do wykresu uzyskamy odwołując się po jego nazwie „Chart 1” w kolekcji Shapes obiektu arkusza:

oChart.Location( Excel.XlChartLocation.xlLocationAsObject, oWS.Name );
oRng = (Excel.Range)oWS.Rows.get_Item(8, Missing.Value);
oWS.Shapes.Item("Chart 1").Top = (float)(double)oRng.Top;
oRng = (Excel.Range)oWS.Columns.get_Item(3, Missing.Value);
oWS.Shapes.Item("Chart 1").Left = (float)(double)oRng.Left;

Na koniec upewniamy się, że aplikacja Excela jest widoczna i pozwalamy użytkownikowi na przejęcie kontroli nad zachowaniem Excela:

oXL.Visible = true;
oXL.UserControl = true;

4. Uruchomienie aplikacji

Po uruchomieniu przykładowego programu i wciśnięciu przycisku umieszczonego na formularzu naszym oczom powinna ukazać się aplikacja Excela prezentująca się podobnie jak Rysunek 1 i czekająca na działania użytkownika. Uwagę Czytelnika może jednak zwrócić umieszczony na formularzu głównym naszej aplikacji nad przyciskiem uruchamiającym Excela, domyślnie zaznaczony przycisk wyboru, o którym nie było wcześniej mowy w artykule. Oto kod, który jest umieszczony bezpośrednio za kodem zaprezentowanym w artykule, a który zostaje uruchomiony tylko w przypadku, kiedy pole wyboru jest zaznaczone:

System.Runtime.InteropServices.Marshal.ReleaseComObject(oWBs);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);

Kod ten jest odpowiedzialny za poinformowanie uruchomionego przez nas serwera COM Excela o tym, że nasza kliencka aplikacja nie jest już nim zainteresowana. Konkretniej, zwalniamy tutaj obiekty naszego klienta odpowiedzialne za komunikację z serwerem, tj. obiekt kolekcji plików pojedynczej aplikacji Excela oraz obiekt samej aplikacji Excela. Jeśli nie zrobilibyśmy tego, proces Excel.exe nie zakończy się przy zamknięciu uruchomionej przez nas aplikacji Excela, a dopiero w momencie zamknięcia naszego programu. Dzieje się tak dlatego, że serwer Excela będzie cały czas „myślał”, że pomimo zamknięcia aplikacji Excela, ciągle są podłączeni do niego jacyś klienci. Zachowanie takie może doprowadzić do niepożądanych sytuacji, np. przy uruchomieniu z naszego programu dwóch aplikacji Excela (dwóch procesów Excel.exe), po zamknięciu obydwu aplikacji i naszego programu zostanie zamknięty tylko jeden proces Excel.exe, a drugi proces pozostanie uruchomiony w systemie! Można to łatwo sprawdzić odznaczając przycisk wyboru, uruchamiając dwie aplikacje Excela dwukrotnie wciskając przycisk i śledząc uruchomione w systemie procesy z poziomu Menedżera zadań Windows.

5. Podsumowanie

Microsoft dostarczając Primary Interop Assemblies sprawił, że dostęp do niezarządzanych serwerów COM pakietu Office z poziomu kodu zarządzanego stał się bajecznie prosty. Programując klientów COM należy jednak cały czas pamiętać o specyficznych własnościach architektury COM, tj. o konieczności zwalniania na czas obiektów klienckich.

Jak pokazuje artykuł, dostając się do serwera COM w sposób programowy możemy korzystać z wszystkich funkcjonalności Excela, które są normalnie dostępne dla excelowego użytkownika. Warto samemu poeksperymentować z metodami i właściwościami obiektu klasy _Worksheet i zobaczyć, jak się one przekładają na równoważne działania użytkownika.

6. Materiały źródłowe

Załączniki:

Podobne artykuły

Komentarze 10

User 131335
User 131335
0 pkt.
Nowicjusz
21-01-2010
oceń pozytywnie 0
Bardzo ciekawy i funkcjonalny tutorial. Przy zajmowaniu sie aplikacjami biznesowymi predzej czy pozniej zachodzi koniecznosc migracji danych miedzy aplikacja a Excelem, a ten tekst stanowi wysmienity przyklad, jak mozna to zrobic. Duzy plus za wskazanie interesujacych linkow i ciekawe zrodla.
Paczeek
Paczeek
253 pkt.
Junior
21-01-2010
oceń pozytywnie 0
Bardzo dobry artykuł. Poprawny stylistycznie - widać że autor nie ma problemów z językiem polskim :) Technicznie też bardzo dobrze... proponowałbym drugą część o Accesie
uzytkownik usuniety
uzytkownik usuniety
3556 pkt.
Guru
21-01-2010
oceń pozytywnie 0
Artykuł jest bardzo ciekawy, poprawny. Porusza ciekawą tematykę i pokazuje na prostym przykładzie, jak w praktyce wykorzystać przedstawione informacje.
andrzej.ptak7835
andrzej.ptak7835
0 pkt.
Nowicjusz
21-01-2010
oceń pozytywnie 0
Sprawnie opisany przepis na wykorzystanie Excela z poziomu aplikacji .NET. Nie ulega wszak watpliwosci, ze to bardzo przydatna rzecz. Gratulacje!
kruchy3906
kruchy3906
0 pkt.
Nowicjusz
21-01-2010
oceń pozytywnie 0
Ode mnie 7 !!!
batonik
batonik
0 pkt.
Nowicjusz
21-01-2010
oceń pozytywnie 0
Przydatne, oj przydatne... :) Jako drugi (kolejny) artykuł z tej serii, o wykorzystaniu COM i innych "zewnętrznych" obiektów, itp., proponuję porównanie wykorzystania tych technologii ze względu na szybkość przy dużej ilości danych :) - bo korzystanie z Excela np. za pomocą OLE było masakrycznie wolne z tego co pamiętam ;)
DonDoman
DonDoman
0 pkt.
Nowicjusz
21-01-2010
oceń pozytywnie 0
Kolejna porządna, dopracowana rzecz. Do tego otwierająca furtkę nowych możliwości. Przy okazji pokazuje, jak przy minimalnym wysiłku skontruować wykres :)
User 114242
User 114242
1 pkt.
Nowicjusz
21-01-2010
oceń pozytywnie 0

Rozumiem, że te biblioteki bardzo fajnie instalują się na komputerze twórcy programu, a co z użytkownikiem ?? Czy musi on mieć zainstalowanego Excela ?? A jak tak to czy koniecznie XP ?? czy ktoś już coś kombinował w tym zakresie ?

Pozdrawiam Marcin

lkonkel
lkonkel
4 pkt.
Nowicjusz
21-01-2010
oceń pozytywnie 0
Bardzo ciekawy i przydatny artykuł. Walczyłem ostatnio z tym tematem i dla uzupełnienia podaję przydatne dwa adresy: 1. http://support.microsoft.com/kb/302084 2. http://www.tek-tips.com/viewthread.cfm?qid=1245715&page=1
User 84985
User 84985
6 pkt.
Nowicjusz
21-01-2010
oceń pozytywnie 0
No w tym włąśnie jest problem, bo z tego co wiem to biblioteka COM Excela, o którą oparty jest ten artykół jest częścią pakietu Offica i jest chroniona przez prawa autorskie MS. Rozpowrzechnianie jej jako 3rd-part  jest nielegalne chyba, że wraz z licencją na nasz produkt dostarczamy również licencję MS Office. Także opieranie np eksportu do xls w aplikacji niezależnej jest zdziebko ryzykowne (nie wszyscy mają MS office, pozostaje OleDB, ODBC, lub XML). Co innego jeśli nasza aplikacja jest jawnie dedykowana do współpracy z pakietem Offica, wówczas takich problemów nie ma.

Jeśli jestem w błędzie to proszę o sprostowanie.
pkt.

Zaloguj się lub Zarejestruj się aby wykonać tę czynność.