Microsoft.Jet.OLEDB.4.0 를 이용한 Excel 파일 읽기시 주의사항

아래글은 iMoon's Story 블로그(http://blog.imoon.co.kr/9) 에서 퍼온글 입니다. 아래 문제로 인해 큰 문제가 발생하여 알리고자 퍼다가 올립니다. MS 개발환경에서 개발하시면서 Excel 파일을 읽고 쓰는 작업을 하시는 분들은 꼭 필독하셔야 합니다. 안그럼 피 봅니다. 특히 돈문제라면 말이죠. 아래글을 필독하시고 ConnectionString 작성시 참고하시기 바랍니다.

개  요

   - 삼성전자 프로젝트간 Excel 데이타를 읽는 도중 잠시 해매었던 
     부분이었습니다. 그래서 이렇게 정리해서 올립니다.
     Microsoft.Jet.OLEDB 를 이용한 Excel Sheet 읽기 및 HDR, IMEX 값에 대해..

예제 소스

1. // Excel파일 --> DataTable로 읽어 들이기
2. using System.Data.OleDb;
3. public System.Data.DataTable ReadXlsFile(string sheetName)
4. {
5.     string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
      Data Source=" + _filename + ";Extended Properties=
      \"Excel 8.0;HDR=YES;IMEX=1\"";
6.     OleDbConnection conn = new OleDbConnection(connectionString);
7.     OleDbCommand comm = new OleDbCommand();
8.     OleDbDataAdapter adap = new OleDbDataAdapter();
9.     comm.Connection = conn;
10.     comm.CommandType = CommandType.Text;
11.     comm.CommandText = "select * from [" + sheetName + "$]";
12.     adap.SelectCommand = comm;
13.     System.Data.DataTable dtXls = new System.Data.DataTable("");
14.     adap.Fill(dtXls);
15.     return dtXls;
16. }

* 간단히 보기 위해 Exception 처리는 생략 하였습니다.

소스설명 1. ConnectionString의 옵션정보   - HDR : YES를 하게 되면 Excel Sheet의 제일 윗줄은 데이타가 아닌 타이틀이                존재한다는 것을 알립니다.            NO를 하게 되면 첫줄부터 데이타가 시작함을 알립니다.   - IMEX : Excel 데이터를 로드할 경우, Excel의 ISAM 드라이브에서 처음 몇 개의                행을 읽어서 데이터의 유형을 결정하게 됩니다.                이 때, 데이터 유형은 텍스트 형(nvarchar)이거나 숫자 형(Float)형 둘 중의                하나로 결정되어 집니다.                이로인해 연속된 문자타입 중 숫자타입이 나오면 그값은 Null로 읽어 들여                지는 현상이 생기며, 반대의 경우도 마찬가지 입니다.                기본값은 0이며, 1로 셋팅을 하면 숫자가 정상적으로 읽어 들여집니다.                말로 하는 내 자신도 너무 힘드네요...                자세한 내용은 아래 예제를 통해 보여 드리겠습니다.

 실습예제

  1. Excel Sample 준비 

  A컬럼 : 문자로 구성
  B컬럼 : 숫자로 구성
  C컬럼 : 문자 및 숫자로 구성
  D컬럼 : 숫자 및 문자로 구성
  E컬럼 : 숫자형태의 문자로 구성

# 타입이 다른 각 컬럼에 대해 Connection String 옵션에 따라 데이타가 어떻게 읽어 
  들여 지는지 확인 해 볼 것입니다.

  2. 테스트 프로그렘 준비
    - 솔루션 구성

    1) 웹사이트 프로젝트를 새로 만듭니다. (웹프로젝트로 하는건 아~~~~무 이유 
        없습니다..
        윈폼으로 하셔도 괜찮습니다....단지 아래 실습과는 조금 차이가 있겠죠?)
    2) ASP.NET 폴더추가에서 App_Code 를 추가 합니다.
    3) ExcelHelper 라는 클래스를 추가 합니다. 
       (이곳에 Excel파일을 읽어 들이는 메소드를  작성 할 것입니다.)

     - Excel Helper 클래스 관련 메소드 작성

        public System.Data.DataTable ReadXlsFile()
        {
            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
            Data Source=D:\test.xls;Extended Properties=""Excel 8.0;
            HDR=YES;IMEX=1""";
            OleDbConnection conn = new OleDbConnection(connectionString);
            OleDbCommand comm = new OleDbCommand();
            OleDbDataAdapter adap = new OleDbDataAdapter();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select * from [sheet1$]";
            adap.SelectCommand = comm;
            System.Data.DataTable dtXls = new System.Data.DataTable("");
            adap.Fill(dtXls);
            return dtXls;
        }
        위와 다른 점은 테스트를 위해 하늘색으로 표시한 부분은 파일경로와 
        SheetName을 하드코딩으로 적어 두었습니다.   
        워낙 게으르다 보니..........ㅡㅡ;;

     - Default.aspx UI 디자인     

       1) GridView 및 ObjectDataSource 컨트롤을 배치합니다. 

      2) 위와 같이 각각의 바인딩 정보를 셋팅 합니다.
          - GridView의 DataSourceID = "ObjectDataSource1"
          - ObjectDataSource TypeName ="ExcelHelper",
             SelectMethod = "ReadXlsFile"
 
     기본 컨트롤 사용법은 설명 드리지 않겠습니다..........(역쉬 이것도 게을러서...)
     자 이제 준비 작업은 다 마쳤습니다.
     그럼 옵션별 예제 프로그램을 실행시켜 데이타 값을 확인 해 보겠습니다.

  예제 1)  HDR=YES;IMEX=1 일 경우 

   - 보기 편하게 하기 위해 자동서식을 적용하였습니다.
      결과 화면을 보면 엑셀파일의 제일 첫줄을 해더 정보로 보고 문자컬럼,
      숫자컬럼..을 해더 정보로 활용하는 것을 볼수 있습니다.

     이는 실제 DataTable의 Row 0번째 값을 확인 하면 값은 a, 1, a, 1, 6 행이 
    출력되는 것을 확인 할수 있습니다.
     상위 문자컬럼, 숫자컬럼, 문자숫자컬럼, 숫자문자컬럼, 문자컬럼은 
    ColumnName으로 확인 할수 있습니다.

  예제 2)  HDR=NO;IMEX=1 일 경우  

  - 위와 비교해보면 엑셀의 첫행부터 데이터로 인식하기 때문에 임의의 해더 정보를
     만들어 자동 추가 시킨후 아래 데이타를 읽어들이는 것을 볼수 있습니다.

  예제 3)  HDR=YES;IMEX=0 일 경우

  * 엑셀파일

  * 실행결과

  - 비교를 위해 엑셀파일을 한번더 올렸습니다.
    IMEX=0로 하였을 경우 위 결과 화면과 같이 같은 종류의 데이타 타입이 존재할
    경우 아무 이상없이 출력이 되는 것을 확인 할수 있습니다.
    하지만 (숫자,문자) 또는 (문자,숫자)의 조합 컬럼은 데이타가 누락됨을 알수
    있습니다.
    이는 앞에 설명 드렸듯이, ISAM 드라이브에서 처음 몇 개의 행을 읽어서
    데이터의 유형을 결정하게 되므로 일어나는 현상 입니다.

    이상으로.........허접한 포스트 하나 올립니다.....

     예제로 쓰인 소스는 첨부로 올려 두었습니다.

 exceltodatatable.zip

테스트 환경
- OS : WindowsXP Professional
- 개발툴 : VS.NET 2005
- 기타 : Excel 2003