Często zdarza się, że potrzebujemy zapisać bądź odczytać dane z pliku Excela (a przynajmniej mi się ostatnio często zdarzało). Niestety „standardowy" sposób wymiany danych, czyli OLE Automation jest koszmarnie wolny. Nie wspominam tu już o problemie, jaki powstaje, gdy na systemie operacyjnym we wersji językowej A zainstalujemy MS Office w wersji językowej B…
Z drugiej strony dane, na których operuję mają dość często strukturę tabeli: wiersz nagłówka a za nim wiersze danych. I dzięki tej strukturze znalazłem remedium na swoje problemy: pliki Excela można obrabiać interfejsem ODBC.
Pierwszym problemem, na jaki się napotykamy, jest definicja łańcucha połączenia. Próbując tego dokonać za pomocą Visual Studio (Data Connections à Add Connection) trzeba utworzyć najpierw plik za pomocą Excela, ale w końcu osiągamy efekt, czyli gotowy łańcuch do skopiowania z Server Explorera:
|
Dsn=Excel-Dateien;dbq=C:\Temp\test.xls;defaultdir=C:\Temp;driverid=790; maxbuffersize=2048;pagetimeout=5 |
Niestety tu powstaje kolejny problem, trudno się bowiem spodziewać, żeby na każdym komputerze z zainstalowanym Excelem zdefiniowane było źródło danych Excel-Dateien. Na szczęście można i ten problem obejść i zamiast DSN-a zdefiniować sterownik ODBC:
|
driver={Microsoft Excel Driver (*.xls)};dsn='';dbq=C:\temp\test.xls; defaultdir=c:\temp;maxbuffersize=2048;pagetimeout=5;readonly=false |
Ten powinien już być ogólnie dostępny. Niestety nie da się powyższego skrócić do parametru driverid, w łańcuchu musi być zdefiniowany parametr driver lub dsn. Istotne jest też to, żeby dodać parametr readonly=false, gdyż domyślnie plik otwierany jest tylko do odczytu (także jeśli jest tworzony!).
Sam zapis do pliku jest bajecznie prosty:
OdbcConnection cnn = new OdbcConnection(cnnString);
cnn.Open();
OdbcCommand cmd = new OdbcCommand("CREATE TABLE [Test] ([id] NUMBER, [v] TEXT)");
cmd.Connection = cnn;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test (id, v) VALUES (1, 'aaa')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Test VALUES (2, 'bbb')";
cmd.ExecuteNonQuery();
Odczyt danych odbywa się analogicznie za pomocą instrukcji SELECT. Można też używać parametrów:
cmd.CommandText = "INSERT INTO Test VALUES (?, ?)";
OdbcParameter p1 = cmd.Parameters.Add("@id", OdbcType.Int);
p1.Value = 3;
OdbcParameter p2 = cmd.Parameters.Add("@value", OdbcType.Text);
p2.Value = "ccc";
cmd.ExecuteNonQuery();
Tutaj mała uwaga: ODBC nie obsługuje parametrów nazwanych. Wszystkie parametry muszą zostać podane w postaci znaków zapytania, a następnie w odpowiedniej kolejności dodane do kolekcji parametrów. Stąd też próba uruchomienia poniższego kodu zakończy się komunikatem, że została wprowadzona niewłaściwa liczba parametrów:
cmd.CommandText = "INSERT INTO Test VALUES (@id, @value)";
cmd.Parameters.Add("@id", OdbcType.Int);
cmd.Parameters.Add("@value", OdbcType.Text);
cmd.Parameters[0].Value = 5;
cmd.Parameters[1].Value = "eee";
cmd.ExecuteNonQuery();