'Excel'에 해당되는 글 2건

  1. 2013.08.28 OLEDB 개체(Excel, Access) 호환성 높이기 (4)
  2. 2010.03.16 엑셀 자동 채우기 방법

요즘 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("{0} = {1}", 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={0};Data Source=C:\MyExcel.xls;
Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";", g_sProvider);

라고 하면 끝. 그러면 해당 Provider로 연결하게 된다.

Provider 검색하는 코드를 간단하게 설명하자면,  아래와 같다.

  1. 현재 가져올 수 있는 모든 Provider 정보를 DataTable로 가져온다.
  2. 가져온 Provider에서 Provider 이름만 모두 가져와 string array로 만든다.
  3. string array를 이름 별로 Sort 한다. - 최신 버전이 위로 갈 수 있도록 -
  4. 돌면서 최신 버전에 해당하는 것이 있는지 체크하면서 찾으면 해당 이름을 저장한다.
  5. Connection String을 만들 때, 저장한 Provider 이름을 사용한다.

간단하면서도 잘 알지 못하면, 참 어렵게 프로그램을 짤 번했던 기억이 든다.

참고로 Excel의 경우 "xsl" 파일은 Microsoft.Jet.OLEDB.4.0 만으로도 읽을 수 있지만, "xslx" 파일은 반드시 "Microsoft.ACE.OLEDB.12.0" 이상 버전의 Provider를 사용해야만 읽을 수 있다.

즉 위에서 필터링하는 방식에 따라, xlsx 파일을 읽을 수 있는지 없는지를 체크하는 방편이 될 수 있다.

신고
Posted by 하인도

이번에 친구가 사용할 계산기하나 만들려다가, 스스로 좌절할 뻔 했었다.

특히 엑셀의 강점인 자동 채우기 방법의 룰을 잘 몰라서 그런 것이였는데, 혹시나 까먹을까 두려워 적는다.


수식 자동 채우기를 사용해서 아래와 같은 수식을 만들었다고 하자.

( 원금 + 수수료 ) * (이자율+1) * 월차


이 때 원금과 수수료 그리고 이자율은 맨 위의 상단에서 입력받는 형태로 해서

월차에 따라 값을 만들고 싶었다.

그래서 내가 만들려는 형태는 아래와 같이 출력되는 것이다.

excel0003


즉 수식을 B7 위치에다 아래와 같이 만들었다.

 excel0004

원금이 B1, 수수료가 B2, 이자율이 B3 이고, 월차 부분은 A7 부터 쭉이다.

B1, B2, B3는 고정된 위치에서 A7은 다음줄로 내려가면 A8 다시 A9 이런식으로

펼쳐져야 한다. 그래서 자동채우기 기능을 사용하여 아래로 쭉 수식을 펼쳐서

나열해 보았다.

그러자, 아래와 같이 표시된다.

excel0002


숫자가 줄어들더니 0도 나오고, 심지어는 조단위의 숫자까지!

혹시나 하는 마음에 수식을 까보니 아래와 같았다.

excel0001

즉 내가 의도한 바에서 A 부분은 그럭저럭 맞다. 7번째 줄은 A7, 8번째 줄은 A8..

여기는 맞는데, 앞부분의 B1, B2, B3가 갑자기 B2, B3, B4로 바뀐것.

문제는 여기.


자동 채우기를 하게되면 변수에 해당하는 위치 값이 자동으로 바뀌어 발생하는 것이였다.

즉 고정위치에서 값을 가져오고 싶은데 그러하지 못하는 것이였다.


이 부분에 대해 이곳저곳을 찾아보니, 의외로 간단했다.


고정하고 싶은 항목 부분에 “$”를 넣으면 되는 것이였다.


B1 -> $B$1 이 되면 된다.

영문자 부분은 좌우를 나타내게 되는데, 자동채우기가 좌,우로 진행되면 B가 C, D 이런식으로

변경된다. 이게 문제면 B를 $B로 수정한다.

마찬가지로 상하를 나타내는 숫자에서 자동채우기가 상,하로 진행되면 1이 2, 3,4 이런식으로

변경되게 되는데, 여기서는 숫자 앞에 $를 붙여 $1 이런 식으로 해결하면 된다.


그걸 수식으로 표현하면 아래와 같아진다.

excel0005


간단한거 같은데.. 말로 푸니 어렵다.

신고
Posted by 하인도


티스토리 툴바