Arkusz danych serwisowych Ca Mi Eolo

• Artykuł
01/31/2023
• Czas czytania: 13 min

Dotyczy:

Excel 2010, Office Excel 2007, Office Excel 2003

Podsumowanie

W tym artykule omówiono wiele metod przesyłania danych do programu Microsoft Excel z aplikacji Microsoft Visual Basic. W tym artykule przedstawiono również zalety i wady każdej metody, dzięki czemu można wybrać rozwiązanie, które działa najlepiej dla Ciebie.

Więcej informacji

Najczęściej używanym podejściem do przesyłania danych do skoroszytu programu Excel jest automatyzacja. Automatyzacja zapewnia największą elastyczność określania lokalizacji danych w skoroszycie, a także możliwość formatowania skoroszytu i tworzenia różnych ustawień w czasie wykonywania. W usłudze Automation można użyć kilku metod przesyłania danych:

• Transferowanie komórki danych według komórki
• Transferowanie danych w tablicy do zakresu komórek
• Transferowanie danych w zestawie rekordów ADO do zakresu komórek przy użyciu metody CopyFromRecordset
• Tworzenie tabeli QueryTable w arkuszu programu Excel zawierającej wynik zapytania w źródle danych ODBC lub OLEDB
• Przesyłanie danych do schowka, a następnie wklej zawartość schowka do arkusza programu Excel

Istnieją również metody, których można użyć do przesyłania danych do programu Excel, które niekoniecznie wymagają automatyzacji. Jeśli korzystasz z serwera aplikacji, może to być dobre podejście do zbiorczego przetwarzania danych od klientów. Do transferu danych bez usługi Automation można użyć następujących metod:

• Przesyłanie danych do pliku tekstowego rozdzielanego przecinkami lub tabulatora, który program Excel może później przeanalizować w komórkach w arkuszu
• Przesyłanie danych do arkusza przy użyciu usługi ADO
• Przesyłanie danych do programu Excel przy użyciu dynamicznego programu Data Exchange (DDE)

Poniższe sekcje zawierają więcej szczegółów dotyczących każdego z tych rozwiązań.

Uwaga W przypadku korzystania z programu Microsoft Office Excel 2007 można użyć nowego formatu pliku skoroszytu programu Excel 2007 (*. xlsx) podczas zapisywania skoroszytów. W tym celu znajdź następujący wiersz kodu w następujących przykładach kodu:

oBook. SaveAs "C:\Book1. xls"

Zastąp ten kod następującym wierszem kodu:

oBook. xlsx"
Ponadto baza danych Northwind nie jest domyślnie uwzględniona w pakiecie Office 2007. Możesz jednak pobrać bazę danych Northwind z usługi Microsoft Office Online. 
Przesyłanie komórek danych według komórki za pomocą usługi Automation
Za pomocą usługi Automation można przesyłać dane do arkusza po jednej komórce naraz:
Dim oExcel As ObjectDim oBook As ObjectDim oSheet As Object'Start a new workbook in ExcelSet oExcel = CreateObject("Excel. Application")Set oBook = oExcel. Workbooks. Add'Add data to cells of the first worksheet in the new workbookSet oSheet = oBook. Worksheets(1)oSheet. Range("A1"). Value = "Last Name"oSheet. Range("B1"). Value = "First Name"oSheet. Range("A1:B1"). Font. Bold = TrueoSheet. Range("A2"). Value = "Doe"oSheet. Range("B2"). Value = "John"'Save the Workbook and Quit ExceloBook. xls"oExcel. Quit
Transferowanie komórek danych przez komórki może być całkowicie akceptowalnym podejściem, jeśli ilość danych jest niewielka. Możesz elastycznie umieszczać dane w dowolnym miejscu w skoroszycie i warunkowo formatować komórki w czasie wykonywania. Jednak takie podejście nie jest zalecane, jeśli masz dużą ilość danych do przesłania do skoroszytu programu Excel. Każdy obiekt zakresu, który uzyskujesz w czasie wykonywania, powoduje żądanie interfejsu, dzięki czemu przesyłanie danych w ten sposób może być powolne. Ponadto systemy Microsoft Windows 95 i Windows 98 mają ograniczenie 64K dotyczące żądań interfejsu. Jeśli osiągniesz lub przekroczysz ten limit 64 tys. żądań interfejsu, serwer usługi Automation (Excel) może przestać odpowiadać lub mogą wystąpić błędy wskazujące małą ilość pamięci. 
Po raz kolejny transferowanie komórki danych przez komórkę jest dopuszczalne tylko w przypadku niewielkich ilości danych. Jeśli musisz przenieść duże zestawy danych do programu Excel, rozważ jedno z rozwiązań przedstawionych później. 
Aby uzyskać więcej przykładowego kodu automatyzowania programu Excel, zobacz Jak zautomatyzować program Microsoft Excel z poziomu programu Visual Basic. 
Przenoszenie tablicy danych do zakresu w arkuszu przy użyciu automatyzacji
Tablicę danych można przesyłać do zakresu wielu komórek jednocześnie:
'Create an array with 3 columns and 100 rowsDim DataArray(1 To 100, 1 To 3) As VariantDim r As IntegerFor r = 1 To 100DataArray(r, 1) = "ORD" & Format(r, "0000")DataArray(r, 2) = Rnd() * 1000DataArray(r, 3) = DataArray(r, 2) * 0. 7Next'Add headers to the worksheet on row 1oSheet. Range("A1:C1"). Value = Array("Order ID", "Amount", "Tax")'Transfer the array to the worksheet starting at cell A2oSheet. Resize(100, 3). Value = DataArray
Jeśli przesyłasz dane przy użyciu tablicy, a nie komórki po komórce, możesz uzyskać ogromny wzrost wydajności dzięki dużej ilości danych. Rozważmy ten wiersz z powyższego kodu, który przesyła dane do 300 komórek w arkuszu:
 oSheet. Value = DataArray
Ten wiersz reprezentuje dwa żądania interfejsu (jedno dla obiektu Zakres zwracanego przez metodę Range, a drugie dla obiektu Range zwracanego przez metodę Resize). Z drugiej strony przeniesienie komórki danych według komórki wymagałoby żądań 300 interfejsów do obiektów zakresu. Jeśli to możliwe, możesz skorzystać z przesyłania danych zbiorczo i zmniejszenia liczby żądań interfejsu. 
Przenoszenie zestawu rekordów ADO do zakresu arkuszy przy użyciu automatyzacji
Program Excel 2000 wprowadził metodę CopyFromRecordset, która umożliwia transfer zestawu rekordów ADO (lub DAO) do zakresu w arkuszu. Poniższy kod ilustruje, jak można zautomatyzować program Excel 2000, Excel 2002 lub Office Excel 2003 i przenieść zawartość tabeli Orders w przykładowej bazie danych Northwind przy użyciu metody CopyFromRecordset. 
'Create a Recordset from all the records in the Orders tableDim sNWind As StringDim conn As New ADODB. ConnectionDim rs As ADODB. RecordsetsNWind = _"C:\Program Files\Microsoft Office\Office\Samples\Northwind. mdb" conn. Open "Provider=Microsoft. Jet. OLEDB. 4. 0;Data Source=" & _ sNWind & ";"conn. CursorLocation = adUseClientSet rs = conn. Execute("Orders",, adCmdTable)'Create a new workbook in ExcelDim oExcel As Object'Transfer the data to ExceloSheet. CopyFromRecordset rs'Close the connectionrs. Closeconn. Close
Uwaga Jeśli używasz wersji pakietu Office 2007 bazy danych Northwind, musisz zastąpić następujący wiersz kodu w przykładzie kodu:
conn. 0;Data Source=" & _ sNWind & ";"
Zastąp ten wiersz kodu następującym wierszem kodu:
conn. ACE. 12. 0;Data Source=" & _ sNWind & ";"
Program Excel 97 udostępnia również metodę CopyFromRecordset, ale można jej używać tylko z zestawem rekordów DAO. CopyFromRecordset z programem Excel 97 nie obsługuje ADO. 
Aby uzyskać więcej informacji na temat korzystania z ADO i metody CopyFromRecordset, zobacz How to transfer data from an ADO recordset to Excel with automation (Jak przesyłać dane z zestawu rekordów ADO do programu Excel za pomocą automatyzacji). 
Tworzenie tabeli Zapytań w arkuszu przy użyciu automatyzacji
Obiekt QueryTable reprezentuje tabelę utworzoną na podstawie danych zwróconych z zewnętrznego źródła danych. Podczas automatyzowania programu Microsoft Excel można utworzyć tabelę Zapytań, podając parametry połączenia z OLEDB lub źródłem danych ODBC wraz z ciągiem SQL. Program Excel ponosi odpowiedzialność za wygenerowanie zestawu rekordów i wstawienie go do arkusza w określonej lokalizacji. Użycie tabeli QueryTables oferuje kilka korzyści w porównaniu z metodą CopyFromRecordset:
Program Excel obsługuje tworzenie zestawu rekordów i jego umieszczanie w arkuszu. 
Zapytanie można zapisać przy użyciu tabeli QueryTable, aby można było je odświeżyć w późniejszym czasie w celu uzyskania zaktualizowanego zestawu rekordów. 
Po dodaniu nowej tabeli QueryTable do arkusza można określić, że dane już istniejące w komórkach arkusza zostaną przesunięte w celu uwzględnienia nowych danych (zobacz właściwość RefreshStyle, aby uzyskać szczegółowe informacje). 
Poniższy kod przedstawia sposób automatyzowania programu Excel 2000, Excel 2002 lub Office Excel 2003 w celu utworzenia nowej tabeli QueryTable w arkuszu programu Excel przy użyciu danych z przykładowej bazy danych Northwind:
'Create a new workbook in Excel'Create the QueryTable"C:\Program Files\Microsoft Office\Office\Samples\Northwind. mdb"Dim oQryTable As ObjectSet oQryTable = oSheet. QueryTables. Add( _"OLEDB;Provider=Microsoft. 0;Data Source=" & _sNWind & ";", oSheet. Range("A1"), "Select * from Orders") oQryTable. RefreshStyle = xlInsertEntireRowsoQryTable. Refresh False
Korzystanie ze schowka
Schowek systemu Windows może być również używany jako mechanizm przesyłania danych do arkusza. Aby wkleić dane do wielu komórek w arkuszu, możesz skopiować ciąg, w którym kolumny są rozdzielane znakami tabulacji, a wiersze są rozdzielane przez zwroty karetki. Poniższy kod ilustruje sposób, w jaki program Visual Basic może używać obiektu Schowka do przesyłania danych do programu Excel:
'Copy a string to the clipboardDim sData As StringsData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _ & "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _& "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"Clipboard. ClearClipboard. SetText sData'Paste the dataoBook. Worksheets(1). SelectoBook. Paste
Tworzenie rozdzielanego pliku tekstowego, który program Excel może analizować w wierszach i kolumnach
Program Excel może otwierać pliki rozdzielane przecinkami lub tabulatorami i poprawnie analizować dane w komórkach. Możesz skorzystać z tej funkcji, gdy chcesz przenieść dużą ilość danych do arkusza, używając niewielkiej, jeśli w ogóle, automatyzacji. Może to być dobre podejście dla aplikacji klient-serwer, ponieważ plik tekstowy może być generowany po stronie serwera. Następnie możesz otworzyć plik tekstowy na kliencie przy użyciu usługi Automation, jeśli jest to odpowiednie. 
Poniższy kod ilustruje sposób tworzenia pliku tekstowego rozdzielanego przecinkami na podstawie zestawu rekordów ADO:
'Save the recordset as a tab-delimited filesData = rs. GetString(adClipString,, vbTab, vbCr, vbNullString)Open "C:\Test. txt" For Output As #1Print #1, sDataClose #1'Open the new text file in ExcelShell "C:\Program Files\Microsoft Office\Office\Excel. exe " & _ Chr(34) & "C:\Test. txt" & Chr(34), vbMaximizedFocus
Uwaga Jeśli używasz wersji pakietu Office 2007 bazy danych Northwind, musisz zastąpić następujący wiersz kodu w przykładzie kodu:
conn. 0;Data Source=" & _sNWind & ";"
conn. 0;Data Source=" & _
Jeśli plik tekstowy ma rozszerzenie. CSV, program Excel otwiera plik bez wyświetlania Kreatora importu tekstu i automatycznie zakłada, że plik jest rozdzielany przecinkami. Podobnie, jeśli plik ma rozszerzenie. TXT, program Excel automatycznie analizuje plik przy użyciu ograniczników tabulatorów. 
W poprzednim przykładzie kodu program Excel został uruchomiony przy użyciu instrukcji Shell, a nazwa pliku była używana jako argument wiersza polecenia. W poprzednim przykładzie nie użyto usługi Automation. Jednak w razie potrzeby można użyć minimalnej ilości automatyzacji, aby otworzyć plik tekstowy i zapisać go w formacie skoroszytu programu Excel:
'Create a new instance of Excel'Open the text fileSet oBook = oExcel. Open("C:\Test. txt")'Save as Excel workbook and Quit ExceloBook. xls", xlWorkbookNormal
Przesyłanie danych do arkusza przy użyciu usługi ADO
Za pomocą dostawcy usługi Microsoft Jet OLE DB możesz dodać rekordy do tabeli w istniejącym skoroszycie programu Excel. "Tabela" w programie Excel to tylko zakres o zdefiniowanej nazwie. Pierwszy wiersz zakresu musi zawierać nagłówki (lub nazwy pól), a wszystkie kolejne wiersze zawierają rekordy. Poniższe kroki ilustrują sposób tworzenia skoroszytu z pustą tabelą o nazwie MyTable. 
Excel 97, Excel 2000 i Excel 2003
Uruchom nowy skoroszyt w programie Excel. 
Dodaj następujące nagłówki do komórek A1:B1 arkusza Sheet1:
A1: FirstName B1: LastName
Sformatuj komórkę B1 jako wyrównaną do prawej. 
Wybierz pozycję A1:B1. 
W menu Wstawianie wybierz pozycję Nazwy, a następnie wybierz pozycję Zdefiniuj. Wprowadź nazwę MyTable i kliknij przycisk OK. 
Zapisz nowy skoroszyt jako C:\Book1. xls i zamknij program Excel. 
Aby dodać rekordy do tabeli MyTable przy użyciu biblioteki ADO, możesz użyć kodu podobnego do następującego:
'Create a new connection object for Book1. xlsconn. 0;" & _"Data Source=C:\Book1. xls;Extended Properties=Excel 8. 0;"conn. Execute "Insert into MyTable (FirstName, LastName)" & _" values ('Bill', 'Brown')"" values ('Joe', 'Thomas')"
Excel 2007
W programie Excel 2007 uruchom nowy skoroszyt. 
Na wstążce kliknij kartę Formuły, a następnie kliknij pozycję Zdefiniuj nazwę. Wpisz nazwę MyTable, a następnie kliknij przycisk OK. xlsx, a następnie zamknij program Excel. 
Aby dodać rekordy do tabeli MyTable przy użyciu biblioteki ADO, użyj kodu podobnego do poniższego przykładu kodu. 
conn. xlsx;Extended Properties=Excel 12. 0;"" values ('Scott', 'Brown')"" values ('Jane', 'Dow')"
Po dodaniu rekordów do tabeli w ten sposób formatowanie w skoroszycie jest zachowywane. W poprzednim przykładzie nowe pola dodane do kolumny B są sformatowane z wyrównaniem do prawej. Każdy rekord dodany do wiersza wypożycza format z wiersza nad nim. 
Należy pamiętać, że gdy rekord jest dodawany do komórki lub komórek w arkuszu, zastępuje wszystkie dane wcześniej w tych komórkach; Innymi słowy, wiersze w arkuszu nie są "wypychane" po dodaniu nowych rekordów. Należy o tym pamiętać podczas projektowania układu danych w arkuszach. 
Uwaga
Metoda aktualizowania danych w arkuszu programu Excel przy użyciu biblioteki ADO lub przy użyciu obiektu DAO nie działa w środowisku programu Visual Basic for Application w programie Access po zainstalowaniu pakietu Office 2003 z dodatkiem Service Pack 2 (SP2) lub po zainstalowaniu aktualizacji programu Access 2002 zawartej w artykule bazy wiedzy Microsoft Knowledge Base 904018. Metoda działa dobrze w środowisku Visual Basic for Application z innych aplikacji pakietu Office, takich jak Word, Excel i Outlook. 
Aby uzyskać więcej informacji, zobacz następujący artykuł:
Nie można zmieniać, dodawać ani usuwać danych w tabelach połączonych ze skoroszytem programu Excel w programie Office Access 2003 lub w programie Access 2002
Aby uzyskać więcej informacji na temat uzyskiwania dostępu do skoroszytu programu Excel przy użyciu usługi ADO, zobacz Jak wykonywać zapytania i aktualizować dane programu Excel przy użyciu usługi ADO z poziomu programu ASP. 
Przesyłanie danych do programu Excel przy użyciu środowiska DDE
DDE jest alternatywą dla usługi Automation jako sposobu komunikowania się z programem Excel i przesyłania danych; Jednak wraz z pojawieniem się usług Automation i COM, DDE nie jest już preferowaną metodą komunikacji z innymi aplikacjami i powinna być używana tylko wtedy, gdy nie ma innego rozwiązania dostępnego dla Ciebie. 
Aby przenieść dane do programu Excel przy użyciu funkcji DDE, możesz użyć metody LinkPoke, aby szturchać dane do określonego zakresu komórek lub użyć metody LinkExecute do wysyłania poleceń, które zostanie wykonane przez program Excel. 
Poniższy przykład kodu ilustruje sposób nawiązywania konwersacji DDE z programem Excel, aby można było szturchać dane do komórek w arkuszu i wykonywać polecenia. Korzystając z tego przykładu, aby konwersacja DDE została pomyślnie ustanowiona w programie LinkTopic Excel|MyBook. xls skoroszyt o nazwie MyBook. xls musi być już otwarty w uruchomionym wystąpieniu programu Excel. 
W przypadku korzystania z programu Excel 2007 można zapisać skoroszyty przy użyciu nowego formatu pliku. xlsx. Upewnij się, że nazwa pliku została zaktualizowana w poniższym przykładzie kodu. W tym przykładzie tekst Text1 reprezentuje kontrolkę Pole tekstowe w formularzu Visual Basic:
'Initiate a DDE communication with ExcelText1. LinkMode = 0Text1. LinkTopic = "Excel|MyBook. xls"Text1. LinkItem = "R1C1:R2C3"Text1. LinkMode = 1'Poke the text in Text1 to the R1C1:R2C3 in MyBook. xlsText1. Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _"four" & vbTab & "five" & vbTab & "six"Text1. LinkPoke'Execute commands to select cell A1 (same as R1C1) and change the font formatText1. LinkExecute "[SELECT(""R1C1"")]"Text1. LinkExecute "[FONT. PROPERTIES(""Times New Roman"", ""Bold"", 10)]"'Terminate the DDE communication
W przypadku korzystania z narzędzia LinkPoke w programie Excel należy określić zakres w notacji wiersz-kolumna (R1C1) dla elementu LinkItem. Jeśli szturchasz dane do wielu komórek, możesz użyć ciągu, w którym kolumny są rozdzielane kartami, a wiersze są rozdzielane przez powroty karetki. 
Jeśli używasz funkcji LinkExecute, aby poprosić program Excel o wykonanie polecenia, musisz podać programowi Excel polecenie w składni języka makr programu Excel (XLM). Dokumentacja xlm nie jest dołączona do programu Excel w wersji 97 lub nowszej. 
DDE nie jest zalecanym rozwiązaniem do komunikacji z programem Excel. Automatyzacja zapewnia największą elastyczność i zapewnia większy dostęp do nowych funkcji oferowanych przez program Excel.