ADO를 통해 엑셀 데이터 다루기

:: 관련링크 : How To Use ADO with Excel Data from Visual Basic or VBA(영문)
http://support.microsoft.com/kb/257819

성질 급한 분들은 위의 링크를 타고 들어가서 영문을 보시도록 하고...
DTS로 데이터를 많이 다루는 유저는 엑셀 파일 또한 DTS의 데이터 원본으로 사용할 수 있다는 것을 알고 있을 것이다. ADO에서는 다음과 같은 연결 문자열Connection String을 사용해서 엑셀 파일에 연결할 수 있다 :

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="c:\temp.xls";Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Provider=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=c:\temp.xls;FirstRowHasNames=1;readonly=false;

전자의 경우 OLEDB, 후자의 경우 ODBC 드라이버를 사용한다. 각각 HDR, FirstRowHasNames는 엑셀 시트의 첫 행이 컬럼 이름임을 표시하는 옵션이며, 후자의 경우 readonly=false라는 옵션에 주목해 볼 필요가 있다.

일반적으로 엑셀 파일을 ADO, DTS 등에서 사용할 경우 단순히 데이터 원본의 용도로만 사용하는 경우가 많은데, 실제로는 엑셀 파일에 새로운 데이터를 추가하는 것도 가능하다. 다만 여기에는 몇 가지 제약이 있는데 :

• ODBC 연결을 사용, readonly=false 옵션을 줘야 한다.
• 레코드를 삭제할 수 없다.
• 레코드를 추가할 경우, 이상한 위치에 레코드가 추가될 수 있다.
• ADO의 RecordSet을 사용해 데이터 추가/업데이트를 할 수 없다. ; 필드에 직접 값을 쓰거나, .AddNew 메서드를 사용할 수 없으며, 오직 INSERT/UPDATE SQL 구문을 사용한 추가/업데이트만이 가능하다.

이러다보니 조금 애매한데 ... 여튼 엑셀에 대한 다음과 같은 쿼리 구문은 유효하다 :

INSERT Into [Sheet1$] ('abc', 1, 2, 3,);
UPDATE [Sheet1$] 부서이름='test' WHERE 부서ID=1
SELECT * FROM [Sheet1$]
DROP TABLE [Sheet1$]

마지막의 DROP TABLE 쿼리는 해당 시트가 삭제되는 것은 아니고, 단지 해당 시트 내의 모든 레코드가 삭제되는 기능을 한다. 왜 이렇게 된건지는 케냘도 잘 모르겠음.

여튼 잘 활용해서 주물러 봅시다.