요즘 C#을 통해서 Application을 만드는 중인데, 데이터 처리해야 될 내용이 많아, Excel과 Access를 주로 사용하고 있다. 특히 Excel의 경우에는 직접 COM 개체를 활성화 시켜서 Cell 단위까지 찾아 데이터를 가져오는 방법보다,
마치 DB Access 한 것 처럼 OLEDB로 연결해서 가져오는 방법이 가장 효율적이였던 것 같다.
그런데, 문제는 설치 대상 PC안에 오피스 제품이 무엇이 깔렸는지에 따라 이 OLEDB를 사용할 수 있는 것이 전혀 다르다. 특히 2013 이후에는 x64 전용이 있어, 응용 프로그램이 x64에서 동작하게 되면 OLEDB 32bit 버전은 접근이 불가능했다. 그래서 매번 어떤 오피스가 깔렸는지, 32bit, 64bit 구분을 하기에는 너무 로직이 복잡해졌다.
그래서 구글링으로 찾아 다양한 테스트 후 적용했는데 지금은 큰 문제가 없어 보여서 블로깅을 시작한다.
문제
오피스 데이터를 DB 처럼 접근하기 위해서는 그에 맞는 Connection String을 구성해야 한다.
MS Access 같은 경우에는,
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin; Password=;"라고 쓰고
MS Excel 같은 경우에는
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls; Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";"라고 쓴다.
그런데, 문제는 저 Provider다.
현재 설치되어 있는 오피스 버전에 따라 Microsoft.Jet.OLEDB.4.0 을 쓸수도 있고,
때론 Microsoft.ACE.OLEDB.12.0 라고 쓰기도 하고,
x64 Office 2013의 경우에는 Microsoft.ACE.OLEDB.15.0 라고 해야 동작한다.
그 이유는 설치한 Office 마다 설치되는 OLEDB Provider가 달라서 벌어지는 일이다.
일반적으로 Office 2000, XP, 2003 까지는 Microsoft.Jet.OLEDB.4.0 를,
Office 2007, 2010의 경우에는 Microsoft.ACE.OLEDB.12.0 를
Office 2013의 경우에는 Microsoft.ACE.OLEDB.15.0를 사용하게 된다.
그렇다면 이 Provider가 어떻게 설치되어 있는지 알 수 있을까?
맨 처음, 적용했던 방식은 Registry를 통한 방식이였다.
Registry 내에 설치되어 있는 Provider가 있는지 검색하는 방법인데,
이 방법으로 했을때는 그럭저럭 잘 동작 했다.
그런데, 왠걸... x64로 넘어가자 문제가 발생했다.
분명 해당 Provider는 설치되어 있었지만,
내 프로그램에서 해당 Provider를 찾지 못하는 문제가 발생했다.
즉 x86일 때와 x64일 때 제공되는 OLE DB Provider가 다르다는 사실을 그 때 처음 알았다.
결국 원점으로 돌아왔고, 근본적인 방법을 찾아야 겠다는 생각이 들어 수정하기 시작했다.
해결
구글링을 통해서 다양한 웹페이지들을 들락달락해 본 결과 다음과 같은 예제 소스를 볼 수 있었다.
대게의 경우 앞서 언급한 특정 레지스트릐의 값을 통해 등록되어 있는지 여부를 찾는 것이였는데,
애석하게도 실패를 맞봤기 대문에, 실질적인 방법이 필요했다.그러다가 단서를 하나 발견했다.
http://stackoverflow.com/questions/6570066/c-net-get-the-oledb-provider-version
내용을 대충 훝어 본 후, 적절한 예제로 담긴 MSDN 링크를 따라 들어갔다.
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbenumerator.getelements.aspx
OleDbEnumerator.GetElements 라는 메소드를 사용해보라는 것이였다.
예제의 내용은 아래와 같다.
using System;
using System.Data;
using System.Data.OleDb;
class Program
{
static void Main()
{
OleDbEnumerator enumerator = new OleDbEnumerator();
DataTable table = enumerator.GetElements();
DisplayData(table);
Console.WriteLine("Press any key to continue.");
Console.ReadKey();
}
static void DisplayData(DataTable table)
{
foreach (DataRow row in table.Rows)
{
foreach (DataColumn col in table.Columns)
{
Console.WriteLine(" = ", col.ColumnName, row[col]);
}
Console.WriteLine("==================================");
}
}
}즉 OleDbEnumerator 라는 개체를 만들고 그 메소드 중, GetElements 라는 메소드를 호출하면 현재 프로그램 안에서
사용가능한 모든 Provider를 담은 정보를 DataTable 형식으로 돌려주게 된다.
여기서 착안하여, 실제 Provider들 중, 최신 Provider 이름을 추출하도록 했다.
OleDbEnumerator enumerator = new OleDbEnumerator();
DataTable table = enumerator.GetElements();
List aryProviders = new List();
foreach (DataRow row in table.Rows)
{
aryProviders.Add(row[0].ToString());
}
table.Dispose();
aryProviders.Sort((p, n) => n.CompareTo(p));
string sFoundString = aryProviders.Find(m => m.StartsWith("Microsoft.ACE.OLEDB"));
if (string.IsNullOrEmpty(sFoundString))
sFoundString = aryProviders.Find(m => m.StartsWith("Microsoft.Jet.OLEDB"));
g_sProviderName = sFoundString;
최종적으로 사용되는 값은 g_sProviderName 이라는 값으로, 이 값을 Connection String에 대입하기만 하면 된다.
string sConnectionString = string.Format("Provider=;Data Source=C:\MyExcel.xls; Extended Properties="Excel 8.0; HDR = Yes; IMEX = 1";", g_sProvider);라고 하면 끝. 그러면 해당 Provider로 연결하게 된다.
Provider 검색하는 코드를 간단하게 설명하자면, 아래와 같다.
- 현재 가져올 수 있는 모든 Provider 정보를 DataTable로 가져온다.
- 가져온 Provider에서 Provider 이름만 모두 가져와 string array로 만든다.
- string array를 이름 별로 Sort 한다. - 최신 버전이 위로 갈 수 있도록 -
- 돌면서 최신 버전에 해당하는 것이 있는지 체크하면서 찾으면 해당 이름을 저장한다.
- Connection String을 만들 때, 저장한 Provider 이름을 사용한다.
간단하면서도 잘 알지 못하면, 참 어렵게 프로그램을 짤 번했던 기억이 든다.
참고로 Excel의 경우 "xsl" 파일은 Microsoft.Jet.OLEDB.4.0 만으로도 읽을 수 있지만, "xslx" 파일은 반드시 "Microsoft.ACE.OLEDB.12.0" 이상 버전의 Provider를 사용해야만 읽을 수 있다.
즉 위에서 필터링하는 방식에 따라, xlsx 파일을 읽을 수 있는지 없는지를 체크하는 방편이 될 수 있다.