Leniwce.com | blog technologiczny

OleDbConnection – Excel jako baza danych(C#)

Bartosz Lewandowski, 2009-07-13 16:01:00

kategoria: C#

Niedawno klient zażyczył sobie, by tworzona dla niego aplikacja miała możliwość importu danych bezpośrednio z plików Excel. W celu połączenia naszego proagamu z Excelem możemy bezpośrednio wykorzystywać obiekty COM (dla platformy .NET technologia COM Interop). Poprzez taki obiekt możemy tworzyć nowe dokumenty, czy też modyfikować istniejące, łącznie z formatowaniem. Generalnie, możemy wykonać dowolną czynność w aplikacji Excel programowo. Jednak do importu danych jest to zbyteczne, przecież ja chciałem tylko odczytać dane.

Jak napisałem we wstępie, moja aplikacja miała tylko pobierać dane zgromadzone w plikach Excel. W tym wypadku możemy potraktować te pliki jako źródła bazy danych. Dzięki temu, dostęp do danych uzyskamy poprzez standardowe komendy SQL.

Połączenie do pliku/bazy.

Na początek musimy zdefiniować połączenie. W tym wypadku, wykorzystam połączenie OleDbConnection (using System.Data.OleDb). Sama definicja połączenia prezentuje się następująco:

 
//Podłączenie dla Excela 97/2000/XP/2003
OleDbConnection connection = new OleDbConnection(
String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""", xlsFilename));

Inaczej prezentować się będzie połączenie do Excela 2007 (pliki XLSX):

 
//Podłączenie dla Excela 2007
OleDbConnection connection = new OleDbConnection(
String.Format(@"Provider= Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=YES;""", xlsFilename));

W zależności od parametru HDR, definiujemy, czy pierwszy wiersz arkusza Excel jest nagłówkiem, czy też nie.

Po uzyskaniu połączenia do naszego pliku programu Excel, dzięki połączeniu OleDB, możemy już bezpośrednio wykonywać zapytania SQL, wykorzystując obiekt OleDbCommand.

Zapytania do bazy.

Świetnie, połączyliśmy się do naszej bazy danych Excel, czas więc na jej odpytanie. Tu, wystarczy użyć obiektu OleDbCommand, który pozwoli nam na wykonywanie zapytań SQL.

Odczytajmy więc arkusz Faktury z przykładowego skoroszytu (nazwy tabel podajemy w nawiasach kwadratowych, zakończone symbolem dolara):

 OleDbDataReader reader = new OleDbCommand(
"select * from [Faktury$]"),this.connection).ExecuteReader();

Kod powyżej, zwróci obiekt OleDbDataReader, który pozwala na sekwencyjny odczyt danych.

Lista tabel/arkuszy

Każdy arkusz w skoroszycie programu Excel będzie traktowany jako tabela. No dobrze, ale nie zawsze znamy nazwę arkusza. Jak więc pobrać listę tabel?
Tu posłużę się metodą GetOleDbSchemaTable z obiektu OleDbConnection. Metoda ta zwróci szczegółowe informacje o schemacie bazy danych. Możemy odczytać listę tabel i widoków, możemy poznać kolumny tabel, czy też informacje o kluczach. Mi wystarczy lista tabel:

 
DataTable dt = new DataTable();
dt = connection.GetOleDbSchemaTable(
System.Data.OleDb.OleDbSchemaGuid.Tables, 
new Object[] { null, null, null, "TABLE" });

Gdzie connection, to połączenie do bazy przez obiekt OleDbConnection.
Kod ten spowoduje, że obiekt DataTable będzie przechowywać informację o tabelach. Wyświetlmy je:

 
foreach (DataRow row in dt.Rows)
{
  MessageBox.Show(row["TABLE_NAME"].ToString());
}

ExcelManager.XLSReader

Przygotowałem klasę XLSReader, która pozwala na odczyt plików XLS. Dzięki temu, implementacja odczytu danych z Excela we własnym programie będzie prosta i sprowadzi się do kilku linijek.
Klasa jest zamknięta w bibliotece DLL i umieszczona w przestrzeni nazw ExcelManager, użycie jej w programie sprowadza się do dodania odpowiedniej referencji.

Konstruktor:

ExcelManager.XLSReader(string fileName);

Gdzie fileName to ścieżka do pliku XLS.
Metody:

DataTable GetDataTable(string sheetName)

zwraca o obiekt DataTable powiązany z arkuszem (sheetName). Dzieki temu, możemy wyświetlić dane z arkusza w obiekcie DataGridView.

OleDbDataReader ExecuteReader(string sheetName)

zwraca otwarty obiekt OleDbDataReader.

string[] GetTables()

zwraca listę tabel/arkuszy w pliku XLS.

Praktyczne wykorzystanie tej bliblioteki pokazuje załączony program testowy.
Pobierz źródła bliblioteki (64KB) oraz przykładowej aplikacji z niej korzystającej.

Warto tę klasę rozbudować, dodać możlwość manipulowania danymi, czy też odczyt plików XLSX. Zmiany te są proste do wprowadzenia, więc każdy, kto będzie zainteresowany bez problemu zmodyfikuje kod programu.