Simple Excel Provider Using OLEDB

Introduction

일반적으로 엑셀 파일을 조작하기 위해서 Microsoft.Office.Interop.Excel 를 이용하지만, 이를 이용하기 위해서는 반드시 사용자의 컴퓨터에 엑셀이 설치되어 있어야 하며, 속도가 매우 느리다는 단점이 있습니다. 이번 강좌에서 소개할 내용은 OLEDB를 이용해 간단하게 엑셀파일을 읽고 쓰는 방법에 대해서 소개합니다.

Excel Interop VS Excel OLEDB

본격적인 설명에 앞서 Interop을 이용한 엑셀 파일 조작과, OLEDB를 이용한 엑셀파일 조작의 차이에 대해 알아 보겠습니다.

Excel Interop

Excel Interop은 COM Interop을 이용해 엑셀 프로그램을 외부에서 제어하는 방식으로 동작합니다. Excel Introp 방식은 외부에서 직접 엑셀 프로그램을 제어하기 때문에 엑셀 프로그램에서 제공하는 대부분의 기능(스타일 지정 등)을 제어 할 수 있다는 장점이 있지만, 사용자 컴퓨터에 엑셀 프로그램이 설치되어 있어야 하며 속도가 느리다는 단점이 있습니다.

Excel OLEDB

Excel OLEDB는 OLEDB(Object Linking and Embedding Database)를 이용해 엑셀 파일에 접근하는 방식으로 SQL데이터 베이스를 조작하는 방법과 유사하게 동작합니다. Excel OLEDB는 Microsoft.Jet.OLEDB에 포함되어 있으며 이는 운영체제 설치시 자동으로 설치 됩니다. Excel OLEDB는 빠른속도로 엑셀에 저장된 내용을 읽거나 쓸 수 있지만, 단순히 데이터를 읽고 쓰는 기능만을 제공하기 때문에 셀의 스타일을 지정할 수는 없습니다.
이처럼 복잡한 컨텐츠 조작이 필요한 경우라면 Excel Interop을 이용해햐 하지만, 단순히 내용을 읽거나, 쓰는 용도만 필요할 경우 Excel OLEDB를 이용하면 간단하게 해결할 수 있습니다.

Simple Excel Provider

Excel OLEDB를 이용하기 위해서는 가장먼저 엑셀 파일에 대한 Connection을 생성해 줘야 하는데 OleDbConnection를 이용해서 Connection을 생성할 수 있습니다. 아래는 OleDbConnection을 생성하기 위한 코드입니다.
private static OleDbConnection CreateConnection(string ExcelPath) { OleDbConnectionStringBuilder ConnectionBuilder = new OleDbConnectionStringBuilder(); ConnectionBuilder.Provider = "Microsoft.Jet.OLEDB.4.0"; ConnectionBuilder.DataSource = ExcelPath; ConnectionBuilder.Add("Extended Properties", "Excel 8.0"); return new OleDbConnection(ConnectionBuilder.ToString()); }
공급자로 Microsoft.Jet.OLEDB.4.0을 지정하고, 데이터소스를 입력받은 엑셀파일의 경로로 지정합니다. 그리고 데이터소스를 인식할 수 있도록 Extend Properties에 Excel의 버전 정보를 입력합니다. 이렇게 엑셀 파일에 연결 되는 Connection이 생성되면 OleDbCommand과 SQL문법을 이용해 엑셀 파일을 조작할 수 있습니다. 아래는 엑셀 파일에 저장된 특정 시트의 모든 내용을 DataTable로 가져오는 코드입니다.
public static DataTable Import(string ExcelPath, string SheetName) { DataTable Result = new DataTable(); using (OleDbConnection Connection = CreateConnection(ExcelPath)) { Connection.Open(); OleDbCommand SelectCommand = Connection.CreateCommand(); SelectCommand.CommandText = "SELECT * FROM [" + SheetName + "$]"; Result.Load(SelectCommand.ExecuteReader()); Connection.Close(); } return Result; }
여기서 주의 할점은 Excel OLEDB를 이용하게 되면 SQL의 테이블은 시트가 되고 각 시트의 첫번째 Row는 Column정보로 사용됩니다. 그리고 테이블명을 지정해 줄때 위 코드와 같이 "[시트명 + $(범위)]" 형식으로 이름을 지정해 줘야 합니다. 그리고 아래는 DataTable에 있는 내용을 엑셀 파일로 저장하는 코드입니다.public static bool Export(DataTable Table, string ExcelPath, string SheetName) { DataTable Result = new DataTable(); using (OleDbConnection Connection = CreateConnection(ExcelPath)) { Connection.Open(); OleDbCommand TempCmd = Connection.CreateCommand(); TempCmd.CommandText = CreateTableQury(Table, SheetName); TempCmd.ExecuteNonQuery(); TempCmd.CommandText = CreateInsertQuery(Table, TempCmd.Parameters, SheetName); foreach (DataRow Row in Table.Rows) { for (int i = 0; i < TempCmd.Parameters.Count; i++) TempCmd.Parameters[i].Value = Row[i]; TempCmd.ExecuteScalar(); } Connection.Close(); } return true; }
위 코드에서 사용된 CreateTableQuery 메서드는 입력받은 DataTable의 구조와 동일한 테이블을 생성하는 쿼리를 생성하는 메서드고, CreateInsertQuery 메서드는 DataTable에 저장된 내용을 엑셀 파일에 Insert하기 위한 쿼리를 생성하는 메서드입니다. 두 메서드는 첨부된 소스코드를 확인하시기 바랍니다.

샘플 코드에는 엑셀로 저장된 우편번호 데이터를 불러오고 이를 다시 엑셀파일로 저장하는 기능이 구현되어 있습니다.

WIT.Sample.SimpleExcelProvider.zip