블로그 이미지
Every unexpected event is a path to learning for you.

카테고리

분류 전체보기 (2328)N
Unity3D (572)N
Programming (472)
Unreal (4)
Gamebryo (56)
Tip & Tech (185)
협업 (34)
3DS Max (3)
Game (12)
Utility (116)
Etc (92)
Link (31)
Portfolio (19)
Subject (90)
iOS,OSX (38)
Android (13)
Linux (5)
잉여 프로젝트 (2)
게임이야기 (1)
Memories (19)
Interest (37)
Thinking (36)
한글 (26)
PaperCraft (5)
Animation (408)
Wallpaper (2)
재테크 (19)
Exercise (3)
나만의 맛집 (2)
냥이 (9)
육아 (9)
Total1,346,355
Today6
Yesterday155
Statistics Graph

달력

« » 2019.12
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        

공지사항

태그목록


[제작자 황현우님의 파일]

LiAsExcelDB.cs


[개인적으로 필요해서 소스 좀 수정된 파일]

LiAsExcelDB.cs




C# 에서 Excel 로 데이터 기록 및 읽기 [OleDB]

 

OleDB 를 이용한 Excel 로 데이터베이스 처럼이용하기 입니다만...

Excel 을 제어하는 방법으로 사용해도 무방합니다. 저장이.. 1만건 이상일 때부터는 좀 느려지지만

(제 PC 에서는 한 2초 정도 걸리는 군효..) 읽기는 순식간입니다.

대량으로 저장할 때는 다른 방식으로 해야될 듯..

 

[참고] 요기 본문에 나오는 모든 소스는 첨부된 파일에 있으니, 복사해서 쓰지 마시고..

그리고 퍼가시거나 소스를 사용하신다면 댓글이라도 남겨주시는 센스도.. 사실 퍼가면 코딩하는

시간 버는 거니.. 댓글 다시는데 약간의 시간의 사용하셔도 쿨럭..ㅡ.,ㅡ;;

 

흠,.. C# 에서는 좀 사기적인(?) 지원이 많은 관계로.. 엄청 편리하네요.. 속도는 좀 떨어지만서도..

여튼,.. 프로그램을 만들때.. 매번 DB 를 쓰기도 그렇고.. 그렇다고 Access 쓰기도 구차니즘에

손가락이 떨려오신다면.. Excel 파일을 DB 처럼 사용하는 방법을 이용하는 것도 나름 좋은

방법입니다. 일단 내용의 보안이나 등등의 기능까지 하긴 너무 양이 많아 질꺼 같고..

최대한 간단하게 Excel 파일을 제어해서 Database 처럼 사용하는 방법을 살펴보죠..

(검색해보시면 비슷자료가 많이 있으니 다른 사람들의 것도 참고 하시는 것도 좋은 공부방법이죠..)

 

먼저 Excel 의 구조와 C# 의 DataSet 을 간단하게 살펴보면... 왠지 느낌이 팍! 하고 오실껍니다.

 

DataSet : 테이블의 집합

Excel : Sheet 의 집합 -> 즉, 하나의 시트가 DataSet 에 하나의 Table 이라고 생각하면 됩니다.

 

DataTable : 하나의 테이블 객체.. 컬럼과 줄이 존재하지요..

Sheet : 열과 행이 존재하지요.. 테이블로 보자면 컬럼과 줄..

 

그렇습니다.. -_-;; 이넘들이 개념이 같은 넘들이였습니다. (원래도 같은 넘들이였죠.. DOS 시절에..)

 



 

위와 같이 되는 거죠.. sheet 명이 table 명,  첫번째 줄의 데이터가 Field 명이 됩니다.

음.. 개념 잡기는 여기까지만 하고.. 본격적인 내용으로 들어가면...

 

[ Excel 파일 버전 검사 ]

 

Excel 파일이 버전에 따라서.. OleDB Connection String 이 다릅니다.

즉, xls 인지.. xlsx 인지에 따라서 다르게 처리해야 되는데...

기본적으로 확장명으로 비교할 수도 있겠지만.. 프로그램 만드는데 내부에서 쓰는 데이터 떡하니

xls 하기도 머시기 하고.. xxx 면 xls 라고 소스에서 비교할 수도 있겠지만.. 좀 아닌것 같아서

일단 확장명 상관없이 xls 인지 xlsx 인지 알아내는 방법을 보면... 아래처럼 2개가 있습니다.

 

A. Excel 를 연동해서 파일을 로딩.. 파일 정보를 본다.

B. Excel 파일을 열어서 맨 앞의 file header 부분을 본다.

 

A 로 하자니.. 실제로 두번 읽게 되니 -_-;; 글고 좀 느리구요.. B 로 해보겠습니다.

 

        public static int ExcelFileType(string XlsFile)
        { 

            // 요거이 비교할 파일 데이터 입니다.
            byte[,] ExcelHeader = {
                { 0xD0, 0xCF, 0x11, 0xE0, 0xA1 }, // XLS  File Header
                { 0x50, 0x4B, 0x03, 0x04, 0x14 }  // XLSX File Header
            };

            // result -2=error, -1=not excel , 0=xls , 1=xlsx
            int result = -1;

            FileInfo FI = new FileInfo(XlsFile);
            FileStream FS = FI.Open(FileMode.Open);

            try
            {
                byte[] FH = new byte[5];

                FS.Read(FH, 0, 5);

                for (int i = 0; i < 2; i++)
                {
                    for (int j = 0; j < 5; j++)
                    {
                        if (FH[j] != ExcelHeader[i, j]) break;
                        else if (j == 4) result = i;
                    }
                    if (result >= 0) break;
                }
            }
            catch (Exception e)
            {
                result = (-2);
                //throw e;
            }
            finally
            {
                FS.Close();                
            }
            return result;
        }

 

와 같이 되겠습니다. 머.. 단순하죠.. 파일 열어서 5 byte 비교해서.. xls 인지 xlsx 인지..

excel 파일이 아닌지를 알아내는 것입니다.

 

[ OleDB Connection String ]

 

자.. 어느넘이 어느 넘인지 알았으니.. OleDB Connection String 을 만들어 봅시다요..

각 각 다음과 같습니다만.. 중요한 것만 내용에 넣었으니.. 필요한게 더 있으면 추가를...

아래의 형태는 string.Format 함수로 사용할 format 용 서식입니다.

 

        // 확장명 XLS (Excel 97~2003 용)
        private const string ConnectStrFrm_Excel97_2003 =
            "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=\"{0}\";" +
            "Mode=ReadWrite|Share Deny None;" +
            "Extended Properties='Excel 8.0; HDR={1}; IMEX={2}';" +
            "Persist Security Info=False";

 

        // 확장명 XLSX (Excel 2007 이상용)
        private const string ConnectStrFrm_Excel =
            "Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=\"{0}\";" +
            "Mode=ReadWrite|Share Deny None;" +
            "Extended Properties='Excel 12.0; HDR={1}; IMEX={2}';" +
            "Persist Security Info=False";

 

중요한 건 Provider 가 무언지와 Extended Properties (<- 요게 Excel 파일에 대한 옵션) 입니다.

Extended 보면 HDR 과 IMEX 속성이 있는데 이것이 중요합니다.

HDR 은 Excel 의 첫번째 줄의 데이터를 Field 명으로 인식 할 것인지 여부 (YES , NO) 이고..

IMEX 는 데이터 형식을 어떻게 적용할 것인지 옵션인데, 만약 그 줄의 데이터의 표본이 정수라면

필드가 생성될 때 정수형으로 생성됩니다. 일단은 IMEX=1 로 해서 걍 무시하고 무조건 string 으로

하겠습니다. (다음 버전을 만든다면 속성까지 다 하는 방향으로..)

머.. 여기 까지 왔으면 사실 대부분 그냥 만드실 수 있겠지만.. 그래도 노가다를 줄이기 위해..

소스를 올려 놓겠습니다... (-o- ;)a

 

[ Excel 파일을 DataSet 으로.. ]

 

아.. 참고로.. Excel 파일을 DataSet 으로 바꾸는 순간만을 제외하면 Excel 파일을 사용하지

않습니다. 즉.. 메모리에 로딩한 다음 데이터가 바뀐다고 Excel 파일도 같이 수정되지는 않습니다.

읽을 때도.. 읽는 당시만, 저장할 때도 저장하는 당시만 파일에 lock 이 걸리고 그 전,후에는

엑셀 파일과 전혀~ 상관 안합니다.

 

    첨부된 소스의        

    private static DataSet OpenExcel(string FileName, bool UseHeader)

    를 참고해 주세요~

 

[ DataSet 을 Excel 파일로.. ]

 

    첨부된 소스의        

    private static bool SaveExcel(string FileName, DataSet DS, bool ExistDel, bool OldExcel)

    를 참고해 주세요~

 

첨부된 소스를 사용하실 꺼라면..

OpenExcelDB, SaveExcelDB 두개를 사용하세요..

 

나중에 소스 업데이트를 하면 OpenExcel 이랑 SaveExcel 는 내용이 변경될 것인지라..

업데이트된 소스로 엎어 쳤을 때 에러날 수 있습니다.

 

첨부된 Source 사용법은 다운 받은 소스를 Project 에 추가해 주시고...

사용할 소스의 using 절 부분에 아래줄 추가..

 

using LiAsExcelDatabase;

 

사용하는 것은 아래처럼 사용하시믄 됩니다..

 

                    DataSet DS = LiAsExcelDB.OpenExcelDB("C:\\Temp.xlsx");
                    LiAsExcelDB.SaveExcelDB("C:\\Temp_save.dat",DS);

 

질문이 있으시면.. 이 글의 답글로 남겨주시면 고맙겠습니다.



[출처] C# 에서 Excel 로 데이터 기록 및 읽기 [OleDB]|작성자 애쁠


Posted by blueasa

댓글을 달아 주세요

  1. 이전 댓글 더보기
  2. 2014.10.01 03:04 신고 blueasa  댓글주소  수정/삭제  댓글쓰기

    안녕하세요.
    우선 올려진 글만봐서는 파일에 대한 ConnectionString 셋팅이 잘못된건지 체크해봐야 될 것 같습니다.
    엑셀은 버전이 올라가면서 .xls와 .xlsx로 나눠졌는데요.
    두 포멧이 달라서 ConnectionString도 다릅니다.
    우선 저는 Win7 x64/VS2010에서 작업했습니다.
    소스는 대충 아래와 같이 했습니다.
    참조해 보셨으면 합니다.

    String connectionString = "";
    if (dialog.SafeFileName.Contains(".xlsx";))
    {
    connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0", dialog.FileName);
    }
    else if (dialog.SafeFileName.Contains(".xls";))
    {
    connectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", dialog.FileName);
    }

    using (OleDbConnection excelConnection = new OleDbConnection(connectionString))
    {
    DataTable dtSheets = new DataTable();

    try
    {
    excelConnection.Open();
    dtSheets = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

    // ToDo
    }

    dtSheets.Dispose();
    if (excelConnection != null)
    excelConnection.Close();
    }

    혹시나 싶어서 참조할만한 링크도 남깁니다.
    잘 해결 되셨으면 합니다. (_ _)
    링크1 : http://www.devpia.com/MAEUL/Contents/Detail.aspx?BoardID=17&MAEULNo=8&no=145967&ref=145964
    링크2 : http://deeplu.blogspot.kr/2012/11/microsoftjetoledb40.html

  3. 2014.12.22 00:01 ronald03  댓글주소  수정/삭제  댓글쓰기

    계속 실패하고 있었는데 이 소스로 하니까 너무 잘되네요 ㅠㅠ 감사합니다~!!

  4. 2015.01.12 16:25 초보  댓글주소  수정/삭제  댓글쓰기

    친절한 설명 감사드립니다 ^^ 잘 배우고 갑니다

  5. 2015.01.21 11:31 구식  댓글주소  수정/삭제  댓글쓰기

    좋은내용과 친절한 설명 너무 감사드립니다
    자주 구독 하겠습니다^^ 꾸벅

  6. 2015.01.21 20:35 supercrat  댓글주소  수정/삭제  댓글쓰기

    땡큐 감사합니다.
    오랜만에 엑셀파일 쓸려고하니 xlsx file에서 오류가..
    덕분에 쉽게 갑니다.

  7. 2015.01.29 00:33 망고땡  댓글주소  수정/삭제  댓글쓰기

    엑셀 저장하고 불러오고..오랫만이라 잘 안되었는데...
    덕분에 잘 해결 되었습니다
    소스 오픈 감사합니다...

  8. 2015.02.09 12:43 신고 배고픈멍멍이  댓글주소  수정/삭제  댓글쓰기

    좋은 글 감사합니다 ㅎ
    막상 글들 많이 봤다고 생각했는데 여전히 못 본 좋은 글들이 많이 남아있네요!!ㅎ
    매번 좋은 글 감사합니다~ㅎ

  9. 2015.04.23 15:57 서우르  댓글주소  수정/삭제  댓글쓰기

    좋은 글 감사합니다. MFC 하다가 C# 공부 중인데.. 블로그에 좋은 글들이 많아서 큰 도움 되고 있습니다.
    C#엔 참 편리하고 좋은 것들이 많네요 ㅎㅎ 자주 방문하며 도움 얻어가겠습니다.

    • 2015.04.23 19:48 신고 blueasa  댓글주소  수정/삭제

      C#이 코딩을 참 편하게 해주는 것 같아요.
      예전엔 느리다고 말이 많았었는데..
      요즘은 포팅엔진들이 잘나와서 알아서 처리해주니 좋은 세상이네요. :)
      아무튼..
      도움 되셨다니 좋네요.
      좋은 하루 되세요~

  10. 2015.08.06 12:43 초보자2  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 소중한 게시물 잘보고있습니다 다름이 아니라 위의 소스를 사용하지는 않았고 C#프로젝트에서 oledb를 사용하여 엑셀 테이블을 수정하고싶은데 ExecuteNonQuery() 를 할때 Operation must use an updateable query. 오류가 나서 진행이 되지 않습니다ㅠㅠ 혹시나 아시는 내용이면 조언 부탁드립니다ㅠㅠ
    string connection_str = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Mode=ReadWrite;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'", fileName);

    OleDbConnection MyConnection2 = new OleDbConnection(connection_str);
    MyConnection2.Open();
    myCommand.Connection = MyConnection2;
    sql = string.Format("INSERT INTO [{0}$] (RoomId, ItemId) VALUES(1,2);", tableName);
    myCommand.CommandText = sql;
    myCommand.ExecuteNonQuery();

    • 2015.08.07 09:40 신고 blueasa  댓글주소  수정/삭제

      안녕하세요.
      저도 오랜만에 봐서 정확히는 말씀드릴 수 없을 것 같습니다만..

      1) 우선 눈에 띄는건 connection_str이 다른데요.
      본문에 있는걸로 대체해 보시겠어요?
      // 확장명 XLS (Excel 97~2003 용)
      private const string ConnectStrFrm_Excel97_2003 =
      "Provider=Microsoft.Jet.OLEDB.4.0;" +
      "Data Source=\"{0}\";" +
      "Mode=ReadWrite|Share Deny None;" +
      "Extended Properties='Excel 8.0; HDR={1}; IMEX={2}';" +
      "Persist Security Info=False";

      // 확장명 XLSX (Excel 2007 이상용)
      private const string ConnectStrFrm_Excel =
      "Provider=Microsoft.ACE.OLEDB.12.0;" +
      "Data Source=\"{0}\";" +
      "Mode=ReadWrite|Share Deny None;" +
      "Extended Properties='Excel 12.0; HDR={1}; IMEX={2}';" +
      "Persist Security Info=False";


      2) 혹시 엑셀파일이 열려있는건 아닌가요?

      아무튼..가능하면 위 파일 소스를 한 번 보시는 걸 추천 드립니다.
      아래는 ExecuteNonQuery() 함수가 포함된 SaveExcel() 함수입니다.
      도움이 되실지 모르지만 이거라도..

      /// <summary>
      /// DataSet 을 Excel 파일로 저장한다.
      /// </summary>
      /// <param name="FileName">
      /// Excel File 명 PullPath
      /// </param>
      /// <param name="DS">
      /// Excel 로 저장할 대상 DataSet 객체.
      /// </param>
      /// <param name="ExistDel">
      /// 동일한 파일명이 있을 때 삭제 할 것인지 여부, 파일이 있고 false 면 저장안하고 그냥 false 를 리턴.
      /// </param>
      /// <param name="OldExcel">
      /// xls 형태로 저장할 것인지 여부, false 이면 xlsx 형태로 저장함.
      /// </param>
      private static bool SaveExcel(string FileName, DataSet DS, bool ExistDel, bool OldExcel)
      {
      bool result = true;

      if (File.Exists(FileName))
      if (ExistDel) File.Delete(FileName);
      else return result;

      string TempFile = FileName;
      // 파일 확장자가 xls 이나 xlsx 가 아니면 아예 파일을 안만들어서
      // 템프파일로 생성후 지정한 파일명으로 변경..

      OleDbConnection OleDBConn = null;

      try
      {
      OleDbCommand Cmd = null;
      string ConnStr = "";

      if (OldExcel)
      {
      TempFile = TempFile + ".xls";
      ConnStr = string.Format(ConnectStrFrm_Excel97_2003, TempFile, "YES", "0";);
      }
      else
      {
      TempFile = TempFile + ".xlsx";
      ConnStr = string.Format(ConnectStrFrm_Excel, TempFile, "YES", "0";);
      }

      OleDBConn = new OleDbConnection(ConnStr);
      OleDBConn.Open();

      // Create Table(s).. : 테이블 단위 처리
      foreach (DataTable DT in DS.Tables)
      {
      String TableName = DT.TableName;

      StringBuilder FldsInfo = new StringBuilder();
      StringBuilder Flds = new StringBuilder();

      // Create Field(s) String : 현재 테이블의 Field 명 생성
      foreach (DataColumn Column in DT.Columns)
      {
      if (FldsInfo.Length > 0) {
      FldsInfo.Append(",";);
      Flds.Append(",";);
      }

      FldsInfo.Append("[" + Column.ColumnName.Replace("'", "''";) + "] CHAR(255)";);
      Flds.Append( Column.ColumnName.Replace("'", "''";) );
      }

      // Table Create
      Cmd = new OleDbCommand("CREATE TABLE " + TableName + "(" + FldsInfo.ToString() + ";)", OleDBConn);
      Cmd.ExecuteNonQuery();

      // Insert Data
      foreach (DataRow DR in DT.Rows)
      {
      StringBuilder Values = new StringBuilder();
      foreach (DataColumn Column in DT.Columns)
      {
      if (Values.Length > 0) Values.Append(",";);
      Values.Append("'" + DR[Column.ColumnName].ToString().Replace("'", "''";) + "'";);
      }

      Cmd = new OleDbCommand(
      "INSERT INTO [" + TableName + "$]"+
      "(" + Flds.ToString() + ";) "+
      "VALUES (" + Values.ToString() + ";)",
      OleDBConn);
      Cmd.ExecuteNonQuery();
      }
      }
      }
      catch (Exception)
      {
      result = false;
      }
      finally
      {
      if (OleDBConn != null) OleDBConn.Close();
      try
      {
      if (File.Exists(TempFile))
      {
      File.Move(TempFile, FileName);
      }
      }
      catch { }
      }
      return result;
      }

  11. 2015.08.07 10:24 초보자2  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 참고만하고 가져다 쓰진않고 직접해보려 했는데 적어주신 소스로 작동시키니까 쿼리문이 잘 넘어가네요. 뭔가 빼먹은 부분이 있나봅니다ㅠㅠ잘 보고 다시해봐야겠네요 정말 감사드립니다!!ㅠㅠ

    • 2015.08.07 10:39 신고 blueasa  댓글주소  수정/삭제

      직접 해보는 건 좋은 습관이라고 생각합니다.
      그 습관 계속 지켜나가시길 바랄게요. :)
      아무튼 해결되었다니 다행이네요.
      해결후에 차근차근 비교하면서 다시 한 번 체크해 보세요~
      포스트모템 한다는 생각으로.. ^________^

  12. 2015.08.07 21:20 초보자2  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 민폐지만 질문 하나만 더해도 될까요 많은 힘을 얻고있습니다 감사합니다ㅠㅠ
    지금 SaveExcel을 사용하여 Excel파일을 만들어 내는데 성공하였는데,
    OldExcel = false로 주고 파일 확장자를 .xlsx로 생성하였는데도
    "파일 형식 또는 파일 확장명이 잘못되어 열수 없다"는 메세지박스가 뜹니다ㅠㅠ
    확장자를 xls로 변경하면 "파일 형식 및 확장명이 일치하지 않다"라고 나오지만 그래도 여시겠습니까?라는 선택지가 나와서 "예"를 누르면 데이터도 잘 들어가있고 열리긴합니다.

    xlsx확장명으로 잘 저장이 되셨는지 이런 문제가 있으셨는지 궁금해서 질문드렸습니다.
    긴 글 읽어주셔서 감사하고 주말 잘보내세요!!ㅋㅋ

    • 2015.08.10 10:11 신고 blueasa  댓글주소  수정/삭제

      음..
      저는 현재 잘쓰고 있습니다.
      두 확장자 다 되지만 현재는 xlsx만 쓰고 있는데요.
      예상으로는 xls로 만들어진 파일을 xlsx로 저장하려고 해서 생기는 문제는 아닌가..싶긴합니다.
      xlsx파일을 하나 새로 만들어서 테스트 해 보시겠어요?

  13. 2016.03.08 11:04 초보자02  댓글주소  수정/삭제  댓글쓰기

    안녕하세요
    첨부파일 다운 받았습니다..
    좋은 소스 감사합니다.

  14. 2016.03.22 11:41 학생1  댓글주소  수정/삭제  댓글쓰기

    안녕하세요..! 현재 공부하고 있는 부분에 대해서 많은 도움이 되었습니다..!
    몇 일 헤매다가 결국 해결하지 못해서 이곳에 질문을 남기는데요..!
    엑셀 첮 줄을 헤더로 사용하고 싶지 않아서 OpenExcelDB를 FALSE 값을 주면..
    EXPORT 되는 엑셀 맨 윗줄에 F1, F2,F3..해서 첫 줄이 생겨납니다...ㅜㅜ DELETE나 REMOVE를 사용하려고 해도 이 ROW에는 적용이
    안되더라구요..! 저 첫쩨줄을 업앨 수 있는 방법이 있을까요..?

    • 2016.03.22 15:07 신고 blueasa  댓글주소  수정/삭제

      안녕하세요.
      우선 어떻게 사용하려는지 모르겠습니다만, UseHeader 변수는 사용자가(자신)가 셋팅한 임의의 헤더명을 쓸거냐 엑셀 칼럼명(F1~...)을 쓸거냐일 뿐 헤더는 있어야 됩니다.
      헤더가 없으면 .db로 만들어졌을 때 찾을 방법이 없습니다.
      변수명이 있어야 해당 변수에 접근 가능하듯이요.
      헤더가 필요없다면 UseHeader를 false로 하시고, F1등이 나오는 Row는 무시하고 다음 Row부터 읽으시면 될거라 생각합니다.
      잘 해결되시길 바랄게요. :)

  15. 2016.03.23 18:26 학생1  댓글주소  수정/삭제  댓글쓰기

    답변감사합니다...! 음..소스에선 create table을 하면서 excel에 쓰여지게 되있는것 같은데.. 어떤 부분에서 f1줄을 제외하고 읽을 수 있을까요..?ㅜㅜ 몇일째 안풀리네요ㅜㅜ...

    • 2016.03.25 09:30 신고 blueasa  댓글주소  수정/삭제

      안녕하세요.
      읽어들인 엑셀을 저장하시려는 건가요?
      SaveExcel을 하시는건데 첫 줄(Row)을 없애고 싶으신거라면
      소스 상
      private static bool SaveExcel(string FileName, DataSet DS, bool ExistDel, bool OldExcel)
      부분에 보시면
      foreach (DataRow DR in DT.Rows)
      하는 부분이 있습니다.
      여기에 중단점을 걸어서 디버깅 해보시면 아시겠지만 여기서 첫 줄(Row)만 건너띄는 소스를 넣으시면 될거라 생각합니다.
      확인해 보시겠어요?

  16. 2016.03.28 15:54 학생1  댓글주소  수정/삭제  댓글쓰기

    안녕하세요..! 답변 감사드립니다..
    네..SaveExcel을 하는 건데..중단점을 걸어서 디버깅 했을 때 첫번재로 들어오는 row가 f1, f2 등이 나오는 row가 아니라 그 다음 데이터 row가 들어옵니다..ㅜㅜ 그래서 그쪽에서는 처리할 수가 없더라구요.. F1, F2는 CreaeTable을 했을 때의 컬럼으로 들어가는 것 같습니다..ㅜㅜ 해결할수없는 걸까요..흑흑 너무답답하네요ㅜㅜ

    • 2016.03.28 16:41 신고 blueasa  댓글주소  수정/삭제

      음..
      F1 등이 나오는 Row를 없애고 싶으셨던 것 아닌가요?
      SaveExcel 할 때 F1등의 Row를 제외하고 다음 Row부터 읽는다면 원하시는 상태가 아닌건가요?
      뭘 하고 싶으신건지 이해를 못하겠습니다. -ㅅ-;;

  17. 2016.06.27 16:25 초심자  댓글주소  수정/삭제  댓글쓰기

    감사합니다. 참고하여 학습하겠습니다. 블러그 번창하시고 언제나 좋은 자료 감사합니다 ~*

  18. 2016.07.26 15:25 초시자  댓글주소  수정/삭제  댓글쓰기

    좋은 정보를 공유해 주셔서 감사드립니다
    초심자로써 지푸라기라도 잡는 심정으로 인터넷 서핑중 만나게 되어서 기대와 희망을 갖고 열공해 보겠습니다

    • 2016.07.31 19:44 신고 blueasa  댓글주소  수정/삭제

      리플 감사합니다.
      모르는 걸 배우는 게 지겹고 힘들긴 하지만 해냈을 때 뿌듯함으로 살아가는 게 계속 하는 이유이지 않나 싶네요.
      힘들겠지만 즐겁게 개발하시길 바랄게요. :)

  19. 2017.03.02 17:09 두둥  댓글주소  수정/삭제  댓글쓰기

    관련 프로젝트 진행중인데 좋은 정보 감사합니다.

    한가지 궁금한점이 있는데 엑셀파일 버전(2013, 2014, 등등)에 따라서 못 읽어 올 수 있는 경우가 생길수 있나요?

    • 2017.03.02 17:13 신고 blueasa  댓글주소  수정/삭제

      안녕하세요.
      본문에 보시면

      // 확장명 XLS (Excel 97~2003 용)
      // 확장명 XLSX (Excel 2007 이상용)

      위와 같은 주석이 있는데요.
      엑셀은 버전에 따라 xls/xlsx로 구분되는데요. 그에 맞게 만들어져 있다면 상위 호환도 되기때문에 알아서 처리 되는걸로 압니다.

      좋은 하루 되세요. :)

      P.s. 저는 엑셀 2013 버전 씁니다.

  20. 2017.03.16 15:07 애증의 닷넷  댓글주소  수정/삭제  댓글쓰기

    윈폼 개발중 해당 자료를 보게되서 슬적 사용하려 합니다.
    감사합니다 ㅎㅎ

  21. 2017.04.14 18:19 jazz  댓글주소  수정/삭제  댓글쓰기

    안녕하세요, 올려주신 소스를 이용해서 잘 사용하고 있습니다 . 이점에 너무 감사합니다. 문의항것이 있는데요 엑셀파링 Open중에 IE가 다운되더라구요 그래서 봤더니 용량이 적은 파일은 정상 적인데 용량이 큰 엑셀은 않되는같더라구요 해결할 방법이 없을까요?
    엑셀 파일의 용량이 5.1MB이고 엑셀행수는 1만 정도 되구요 컬럼은 'DH' 한 112 컬럼 됩니다. 꼭 좀 부탁 드립니다.

    • 2017.04.18 14:48 신고 blueasa  댓글주소  수정/삭제

      안녕하세요.
      요즘 바빠서 늦게 답변 드리게 되네요. 죄송합니다.
      말씀해주신 내용으로 예상되는 부분은 처리할 데이터 양이 많아서 for(or foreach)문에서 너무 오래 잡혀있어서 (응답없음) 등으로 빠지는 문제가 나오는 게 아닐까 싶은데요.
      이 문제라면 비동기 처리 방식으로 바꿔야 되지 않나 싶긴하네요.
      C#이니 BackgroundWorker를 써보는 게 어떨까 싶습니다. :)

      [BackgroundWorker 참조] http://blueasa.tistory.com/1260

원본 : http://www.connectionstrings.com/excel



Connection strings for Excel

Developers Community

Find solutions and post questions regarding connection string related issues.

Forum for Excel

Microsoft Jet OLE DB 4.0

TYPE OLE DB Provider
USAGE Provider=Microsoft.Jet.OLEDB.4.0
MANUFACTURER Microsoft

Standard

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

Standard alternative

Try this one if the one above is not working. Some reports that Excel 2003 need the exta OLEDB; section in the beginning of the string.

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

Important note!
The quota " in the string needs to be escaped using your language specific escape syntax.
c#, c++   \"
VB6, VBScript   ""
xml (web.config etc)   &quot;
or maybe use a single quota '.

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD "TypeGuessRows". That's the key to not letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Please also note that adding the IMEX=1 option might cause the IMEX feature to set in after just 8 rows. Use IMEX=0 instead to be sure to force the registry TypeGuessRows=0 (scan all rows) to work.

If the Excel workbook is protected by a password, you cannot open it for data access, even by supplying the correct password with your connection string. If you try, you receive the following error message: "Could not decrypt file."

A workaround for the "could not decrypt file" problem

 
 

ACE OLEDB 12.0

TYPE OLE DB Provider
USAGE Provider=Microsoft.ACE.OLEDB.12.0
MANUFACTURER Microsoft

Excel 97-2003 Xls files with ACE OLEDB 12.0

You can use this connection string to use the Office 2007 OLEDB driver (ACE 12.0) to connect to older 97-2003 Excel workbooks.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;
Extended Properties="Excel 8.0;HDR=YES";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

 
 

.NET Framework Data Provider for OLE DB

TYPE .NET Framework Wrapper Class Library
USAGE System.Data.OleDb.OleDbConnection
MANUFACTURER Microsoft

Use an OLE DB provider from .NET

Provider=any oledb provider's name;OledbKey1=someValue;OledbKey2=someValue;

See the respective OLEDB provider's connection strings options. The .net OleDbConnection will just pass on the connection string to the specified OLEDB provider. Read more here.

 
 

Microsoft Excel ODBC Driver

TYPE ODBC Driver
USAGE Driver={Microsoft Excel Driver (*.xls)}
MANUFACTURER Microsoft

Standard

Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;
DefaultDir=c:\mypath;

SQL syntax "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

 
 

Specify ReadOnly

[Microsoft][ODBC Excel Driver] Operation must use an updateable query. Use this connection string to avoid the error.

Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\MyExcel.xls;ReadOnly=0;

ReadOnly = 0 specifies the connection to be updateable.

 
 

Microsoft Excel 2007 ODBC Driver

TYPE ODBC Driver
USAGE Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
MANUFACTURER Microsoft

Standard

Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=C:\MyExcel.xls;
 
 

.NET Framework Data Provider for ODBC

TYPE .NET Framework Wrapper Class Library
USAGE System.Data.Odbc.OdbcConnection
MANUFACTURER Microsoft

Use an ODBC driver from .NET

Driver={any odbc driver's name};OdbcKey1=someValue;OdbcKey2=someValue;

See the respective ODBC driver's connection strings options. The .net OdbcConnection will just pass on the connection string to the specified ODBC driver. Read more here.

 
 

.NET xlReader for Microsoft Excel

TYPE .NET Framework Class Library
USAGE VM.xPort.ExcelClient.ExcelConnection
MANUFACTURER xPortTools

Excel file with header row

Data Source =c:\myExcelFile.xls;HDR=yes;Format=xls;
 
 

Excel file without header row

Data Source =c:\myExcelFile.xls;HDR=no;Format=xls;
 
 


Posted by blueasa
TAG Excel, odbc, OLEDB

댓글을 달아 주세요

  • Excel 2007(xlsx) 파일 연결문자열
  1. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePath;Extended Properties="Excel 12.0;HDR=YES";

 

  • Excel 2003 이전(xls) 파일 연결문자열
  1. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePath;Extended Properties="Excel 8.0;HDR=YES";
  2.  Provider=Microsoft.JET.OLEDB.4.0;Data Source=FilePath;Extended Properties="Excel 8.0;HDR=YES";

 

  • Access 2007(accdb) 파일 연결문자열
  1. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePath;
  2. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePath;Jet OLEDB:Database Password=Password; (패스워드가 존재할 경우)

 

  • Access 2003 이전(mdb) 파일  연결문자열
  1. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePath;
  2. Provider=Microsoft.ACE.OLEDB.12.0;Data Source=FilePath;Jet OLEDB:Database Password=Password; (패스워드가 존재할 경우)
  3. Provider=Microsoft.JET.OLEDB.4.0;Data Source=FilePath;

    Provider=Microsoft.JET.OLEDB.4.0;Data Source=FilePath;Jet OLEDB:Database Password=Password; (패스워드가 존재할 경우)

 

Microsoft.ACE.OLEDB.12.0 관련 오류 해결 방법

 
로컬에서 잘 되던 기능이 서버에서는 안되어서 인터넷을 검색해 보았습니다.

로컬에서는 오피스2007을 설치하면서 관련 컴포넌트가 같이 설치된 것 같습니다.

위와 같은 오류가 발생시 관련 컴포넌트를 설치해 주셔야 합니다.

* 다운로드 URL: http://www.microsoft.com/downloads/details.aspx?displaylang=ko&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891




Posted by blueasa
TAG OLEDB

댓글을 달아 주세요

OleExcelReader

엑셀파일을 읽는 클래스이다. 
앞서 글에서도 언급을 했지만 OleDB를 이용한 방법은 다른 것보다 굉장한 속도를 자랑한다..^^;
단순히 읽거나 쓰거나 하는 거라면...이 방법을 사용하기 바란다.

다른 DB프로바이더와 마찬가지로 연결개체를 만들고(Connection) 만들어진 연결로 열고(Open) 데이터를 조작(Excute~)하고 마지막으로 닫는 (Close) 순서로 진행된다.
하나씩 살펴 보도록 하겠다.

 먼저 Connection을 살펴 보자

  261 private string ExcelConnection()

  262 {

  263   return

  264     @"Provider=Microsoft.Jet.OLEDB.4.0;" +

  265     @"Data Source=" + this.fileName + ";" +

  266     @"Extended Properties=" + Convert.ToChar(34).ToString() +

  267     @"Excel 8.0;" + ExcelConnectionOptions() + Convert.ToChar(34).ToString();

  268 }

기존의 sql 프로바이더와 비슷한 구조이다.
Provider가 Jet.OLEDB 라는거 빼면..비슷한 구조..^^;
Data Source는 출력할 파일의 이름을 선택하면 되겠다.

  247 private string ExcelConnectionOptions()

  248 {

  249   string strOpts = "Imex=" + this.imex + ";";

  250   if (this.hasHeaders)

  251   {

  252     strOpts += "HDR=Yes;";

  253   }

  254   else

  255   {

  256     strOpts += "HDR=No;";

  257   }

  258   return strOpts;

  259 }

추가로 헤더의 유무를 선택 할 수 있으며 IMEX 값을 선택 할 수 있다.
IMEX라는 속성은 중요하다..^^;
예전에 데브피아 팁게시판에 올려 놓은 링크를 보게 되면..^^;

http://www.devpia.com/MAEUL/Contents/Detail.aspx?BoardID=18&MAEULNo=8&no=986&ref=986

MSDN에 의하면 이 설명은 아래와 같다.

NOTE: Setting IMEX=1 tells the driver to use Import! mode. In this state, the registry setting Import!MixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric. 

You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT! mode, so the results may be unpredictable if you try to do appends or updates of data in this mode. 

The possible settings of IMEX are:

<PRE class=FIXEDTEXT>0 is Export mode 1 is Import! mode 2 is Linked mode (full update capabilities)</PRE>

이 클래스는 Reader이므로 imex의 기본값은 1이 되겠다.
만약 Writer라면 ..당연히 0이 될것이다.

그 외의 속성은 첨부된 소스 코드를 참고 하도록 한다.

이제 생성된 ConnectionString으로 connection 개체를 만들고 Open 하도록 한다.

  289 this.con = new OleDbConnection(ExcelConnection());

  290 this.con.Open();

 이제 Select 할 Command 개체를 만들어 보겠다.

  313 private bool SetSheetQuerySelect()

  314 {

  315   try

  316   {

  317     if (this.con == null)

  318     {

  319       throw new Exception("Connection is unassigned or closed.");

  320     }

  321 

  322     if (this.sheetName.Length == 0)

  323     {

  324       throw new Exception("Sheetname was not assigned.");

  325     }

  326 

  327     string cmd = "";

  328     for (int i = 0; i < this.sheetName.Length; i++)

  329     {

  330       cmd += @"SELECT * FROM [" + this.sheetName[i] + "$" + this.SheetRange[i] + "]" + System.Environment.NewLine;

  331     }

  332     this.selectCmd = new OleDbCommand(cmd, con);

  333     return true;

  334   }

  335   catch (Exception ex)

  336   {

  337     throw ex;

  338   }

  339 }

330 라인에 주목하자.
SQL에서 사용하는 쿼리와 같은 형식의 SELECT 문이다.
해당 시트의 영역에서 컬럼들을 SELECT 한다는 의미가 되겠다.
이러한 쿼리로 332라인의 OleCommand 개체를 만들었다.
이제 쿼리를 실행만 하면 될것이다.

  382 //Fill table

  383 OleDbDataAdapter oleAdapter = new OleDbDataAdapter();

  384 this.selectCmd.CommandTimeout = 200;

  385 oleAdapter.SelectCommand = selectCmd;

  386 DataTable dt = new DataTable(strTableName);

  387 oleAdapter.FillSchema(dt, SchemaType.Source);

  388 oleAdapter.Fill(dt);

  389 if (this.hasHeaders == false)  //헤더가 없으면 임의로 컬럼명을 생성한다.

  390 {

  391   if (this.sheetRange[0].IndexOf(":") > 0)

  392   {

  393     string FirstCol = this.sheetRange[0].Substring(0, this.sheetRange[0].IndexOf(":") - 1);

  394     int intCol = this.ColNumber(FirstCol);

  395     for (int intI = 0; intI < dt.Columns.Count; intI++)

  396     {

  397       dt.Columns[intI].Caption = ColName(intCol + intI);

  398     }

  399   }

  400 }

  401 this.SetPrimaryKey(dt);

위 코드는 GetDataTable() 메서드의 일부이다.
OleDbDataAdpater 개체를 만들어서 388 라인처럼 Fill 하게 되면 dt 라는 데이터 테이블에 SELECT 한 데이터가 채워지게 된다.

구조는..간단하지 않은가????

이제 사용 사례를 살펴 보자..

    1 OleExcelReader reader = new OleExcelReader();

    2 reader.Filename = filePath;

    3 reader.HasHeaders = true;

    4 reader.KeepConnectionOpen = false;

    5 reader.PkColIndex = 0;

    6 string[] sheetNames = reader.GetExcelSheetNames();

    7 if (sheetNames.Length < 1)

    8 {

    9   exceptionMessage = "적어도 한개 이상의 Sheet가 있어야 합니다.";

   10   returnDataSet = null;

   11   return false;

   12 }

   13 else

   14 {

   15   reader.SheetName = new string[] { "List""Staphylococcus""E.coli""Salmonella""Shigella" };

   16   returnDataSet = reader.GetDataSet();

   17 

   18   if (returnDataSet.Tables.Count != 5)

   19   {

   20     exceptionMessage = "시트의 수가 맞지 않습니다.";

   21     return false;

   22   }

   23 }

6라인의 GetExcelSheetNames()라는 메서드로 해당 엑셀파일의 시트 이름의 배열을 얻을 수 있다.
이 배열로 해당 엑셀 파일이 몇개의 시트를 가지고 있는지 판단 할 수 있게 된다.

15라인을 보게 되면 SheetName 이라는 속성에 출력할 시트의 이름들의 배열을 할당 하는 것을 볼 수 있다.
그리고 나서 16라인의 GetDataSet() 메서드로 엑셀로 부터 쿼리해온 5개의 시트를 하나의 데이터셋으로 Fill 하는 작업을 할수 있다.

그외의 자잘한 작업이 있긴 하지만..
알고보면 아주..간단한 작업이 되겠다.

제공되는 샘플 소스는..꽤..지저분하다..
내가 처음부터 만든것도 아닐 뿐더러...불필요한 메서드 들도 꽤 있다...ㅡ.ㅡ;;;
혹시나 필요한 부분만 발췌해서 사용하고자 한다면.. 조금만 분석에 시간을 들여 보길..권한다.

다음은 OleExcelWriter에 대해서 간략히 알아 보자.

 

OleExcelWriter

ㅋ..내가 미쳤나 보다..ㅡ.ㅡ;
예제 코드를 만들고야 말았다..

이번 강좌는..이 예제 코드 중심으로 진행을 할 것이다..@.@

그러므로..예제 프로그램을 다운 받아 실행 해보면서 보는게...이해가 빠를 것이다...라고 생각된다.^^;

이 클래스의 사용 순서는 이렇다.

  1. 파일을 연다. (Open)
  2. 컬럼을 생성한다.
  3. 시트를 생성한다.
  4. 각 시트별 데이터를 인서트 한다. -> 인서트 할때 마다 이벤트를 발생시킨다.
  5. 파일을 닫는다.

앞서 살펴 보았던 Reader와 비슷하다.

   18     public delegate void RowCount(object sender, RowCountEventArgs e);

   19     public event RowCount OnRowCount;

하나의 row가 인서트 될때 마다 이벤트를 일으켜 클라이언트에게 알려 주기 위해 델리게이트와 이벤트를 만들었다.

RowCountEventArgs 클래스는 다음과 같이 정의 된다.

   11 public class RowCountEventArgs : System.EventArgs

   12   {

   13     private int rowCount = 0;

   14     private string tableName = "";

   15 

   16     ///

   17     /// Initializes a new instance of the class.

   18     ///

   19     /// The row count.

   20     public RowCountEventArgs(int rowCount)

   21     {

   22       this.rowCount = rowCount;

   23     }

   24 

   25     ///

   26     /// Initializes a new instance of the class.

   27     ///

   28     /// The row count.

   29     /// Name of the table.

   30     public RowCountEventArgs(int rowCount, string tableName)

   31     {

   32       this.rowCount = rowCount;

   33       this.tableName = tableName;

   34     }

   35 

   36     ///

   37     /// Gets the name of the table.

   38     ///

   39     /// The name of the table.

   40     public string TableName

   41     {

   42       get

   43       {

   44         return this.tableName;

   45       }

   46     }

   47 

   48     ///

   49     /// Gets the row count.

   50     ///

   51     /// The row count.

   52     public int RowCount

   53     {

   54       get

   55       {

   56         return this.rowCount;

   57       }

   58     }

   59   }

tableName과 rowCount 라는 필드를 만든 이유는 필요에 따라서 몇번째 테이블의 몇번째 row인지 보여줄 필요가 있겠다 싶어서 였다.
하나의 시트 라면 tableName은 필요 없는...게 되겠다. 생성자를 선택해서 사용하면 될것이다.

ConnectionString은 앞서 설명한 OleExcelReader와 같다. 단지 앞에서 언급한 대로 IMEX 속성의 값만 다를 뿐이다..기본값인 0을 유지 하기 바란다..

  145 public void Open()

  146 {

  147   try

  148   {

  149     if (con != null)

  150     {

  151       if (con.State == ConnectionState.Open)

  152       {

  153         con.Close();

  154       }

  155       con = null;

  156     }

  157 

  158     if (System.IO.File.Exists(this.fileName))

  159     {

  160       System.IO.File.Delete(this.fileName);

  161       //throw new Exception("파일이 이미 존재 합니다.");

  162     }

  163     con = new OleDbConnection(ExcelConnection());

  164     con.Open();

  165   }

  166   catch (Exception ex)

  167   {

  168     throw ex;

  169   }

  170 }

161라인의 파일의 존재 여부는 상황에 따라서..달라질듯 하지만..일단은 기존 파일은 삭제를 하는 방향으로 하였다.
본 예제처럼 SaveFileDialog 컨트롤을 사용하게 된다면..별 상관 없는 문제이지만 이 컨트롤을 사용하지 않거나 웹에서 사용하거나 하는 경우에는..문제가 생길 것이다..
웹에서 사용하는 경우에는 이 때문에..파일명을 시간으로 정해주는 것도 좋다..혹은 유일성을 보장하는 GUID 값이라든지...

두번째로는 컬럼을 생성하는 부분이다.

   41 string[] colType = new string[3];

   42 

   43 colType[0] = "int";

   44 colType[1] = "varchar(16)";

   45 colType[2] = "datetime";

Form1.cs 파일에서 보여지는 위 예처럼 코드로 직접 이렇게 컬럼의 형식을 입력해야 하는 이유는..C#의 형식과 SQL의 형식을 매칭 시킬수가 없기 때문이다.ㅡ.ㅠ;
int형과 datetime 형의 경우는 매칭을 시킬수가 있다고 친다지만..44라인의 varchar(16)은 c#의 string형을 변환 한 것이다..
이 때문에 만약 컬럼수가 많은 엑셀 파일의 경우에는...코드가 좀 많아진다는 단점이 있겠다..ㅡ.ㅠ;
string 형은 여러가지 sql 타입으로 변환 할 수 있다..varchar(사이즈), text 등...
그렇기에 이런식으로 string[] 로 타입을 순서대로..가진 List 형의 ColTypeList 라는 속성이 필요하다.
이 속성에 있는 string[] 은 하나의 시트에 들어 있는 컬럼의 배열을 뜻한다. 
그러므로 시트가 여러개 있는 엑셀파일이라면 그 시트의 수가 곧 ColTypeList.Count 가 되겠다.

세번째로 이제 시트를 생성할 차례이다.
시트는 SQL 쿼리문의 CREATE TABLE 로 생성한다.

  197 public void CreateSheet()

  198 {

  199   for (int i = 0; i < this.curDs.Tables.Count; i++)

  200   {

  201     this.Command("CREATE TABLE [" + this.curDs.Tables[i].TableName + "] (" + this.CreatColName(this.curDs.Tables[i], i) + ")");

  202   }

  203 }

위 구문은 CREATE TABLE ["시트명"] ( '컬럼명', '컬럼명',......) 이라는 쿼리문을 만들어 주어 쿼리(Command메서드)를 하는 메서드이다.
문맥으로 보아 CreateColName 이라는 메서드는 ( '컬럼명', '컬럼명',......) 라는 문자열을 만들어 주는 메서드라는 것을 짐작해 볼 수 있다.

네번째로 각 시트별 데이터를 인서트 할 차례이다.

  250 public void Binding()

  251 {

  252   foreach (System.Data.DataTable dt in this.curDs.Tables)

  253   {

  254     string col = "";

  255     string resultCol = "";

  256     foreach (DataColumn c in dt.Columns)

  257     {

  258       col += "[" + c.ColumnName + "],";

  259     }

  260     if (col.EndsWith(","))

  261     {

  262       resultCol = col.Substring(0, col.Length - 1);

  263     }

  264 

  265     for (int i = 0; i < dt.Rows.Count; i++)

  266     {

  267       this.Command(@"Insert Into [" + dt.TableName + "] (" + resultCol + ") values (" + this.CreateRowForInsert(dt, i) + ")");

  268       this.OnRowCount(thisnew RowCountEventArgs(++this.rowCount));

  269     }

  270   }

  271 }

252 라인의 foreach문을 보면 각각의 시트(DataTable)마다 인서트 하겠다는 것을 알 수 있다.
256~263라인은 인서트 할 컬럼을 만드는 것이다. 
267라인에 보면 INSERT INTO [테이블명] (컬럼명, 컬럼명, ....) VALUES (값, 값, ....) 형태의 인서트 구문을 만들어 쿼리 하는 것을 알 수 있다.
마지막으로 268 라인을 보면 이 강좌의 처음에 보여 주었던 이벤트를 발생 시키는 것을 볼 수 있다. 
이 이벤트의 매개변수로는 ++rowCount를 보낸다. 즉 하나의 라인을 인서트 한 후에 그 인서트한 행의 Count를 매개변수로 던지는 것이다.
그렇게 되면 클라이언트에서는 이 이벤트를 받아서 현재 몇번째의 데이터가 인서트 되어 있는지 알 수 있을 것이다.

자..이제 예제를 살펴 보도록 하겠다.(Form1.cs)
예제에는 실제로 progressbar와 label을 만들어서 현재 인서트 진행 상황을 보여주게끔 만들었다. 
아직 event와 delegate에 익숙하지 않은 분들도 약간의 참고가 될 수 있으리라 생각된다.

   16     private delegate void CurrentRowEventHandler(int rowIndex);

   17     private CurrentRowEventHandler rowCount;

   18     private DataSet ds = new DataSet();

16 라인을 보면 CurrentRowEventHandler 라는 델리게이트를 만들었고 17 라인에 이 델리게이트 형의 rowCount 라는 개체를 만들었다.
이 델리게이트는 OnRowCount 라는 이벤트를 받아서 그 때마다 UI 스레드에게 CallBack 하여 Label 컨트롤과 ProgressBar컨트롤을 작동시키기위해 필요한 존재이다.
그리고 18행은 예제에서 사용될 DataSet이다.

   65     private void Form1_Load(object sender, EventArgs e)

   66     {

   67       this.rowCount = this.CallBackRowCount;

   68       this.MakeDataSet();

   69     }

Form 이 Load가 되면 rowCount 라는 델리게이트는 CallBackRowCount 라는 델리게이트메서드에 연결을 시킨다. 
그리고 DataSet에 데이터를 채운다..
DataSet에는 2개의 테이블이 있으며 각각 int 형, string 형, datetime 형의 컬럼을 가지고 있으며 데이터는 500개씩 이다.
DataSet에 데이터를 채우는 내용은..예제를 참고 하도록 한다..

자 이제 버튼을 클릭하게 되면...스레드를 만들고 스레드를 시작한다.
이 스레드는 MakeExcelFile 이라는 메서드를 호출한다.

이제 이 MakeExcelFile 이라는 메서드를 살펴 보자.

   34 private void MakeExcelFile()

   35 {

   36   OleExcelWriter excel = new OleExcelWriter();

   37   excel.OnRowCount += new OleExcelWriter.RowCount(excel_OnRowCount);

   38 

   39   excel.FileName = this.saveFileDialog.FileName;

   40   excel.HasHeader = true;

   41   List<string[]> colList = new List<string[]>(2);

   42   string[] colType = new string[3];

   43 

   44   colType[0] = "int";

   45   colType[1] = "varchar(16)";

   46   colType[2] = "datetime";

   47 

   48   colList.Add(colType);

   49   colList.Add(colType);

   50 

   51   excel.ColTypeList = colList;

   52   excel.CurDs = this.ds;

   53   excel.KeepConnectionOpen = true;

   54   excel.CreateSheet();

   55   excel.Binding();

   56   excel.Dispose();

   57   excel.Close();

   58 }

36라인에서 OleExcelWriter의 개체를 생성하고 37라인에서 이벤트를 등록 하였다. 
40~51 라인은 컬럼을 생성하여 ColTypeList 속성에 대입시키는 코드이다. 앞에서 설명 했었다.
52 라인은 DataSet을 CurDs 라는 속성에 대입한다. 그리고 54 라인에서 시트를 생성하고 55라인에서 데이터를 인서트 하며, 56,57 라인에서 Close 하게 된다.

excel_OnRowCount 메서드를 살펴 보자면.

   60 void excel_OnRowCount(object sender, RowCountEventArgs e)

   61 {

   62   this.Invoke(this.rowCount, e.RowCount);

   63 }

단순히 Invoke 만 했다. 
Invoke 의 첫번째 매개변수는 rowCount 라는 델리게이트이며, 두번째 인자는 RowCountEventArgs.RowCount 이다. 
이 델리게이트를 호출(Invoke) 하게 되면 Form1_Load 이벤트 메서드에서 설정한 CallBackRowCount 메서드가 실행된다.

   71 private void CallBackRowCount(int rowIndex)

   72 {

   73   this.lblMessage.Text = String.Format("현재 {0}번째의 데이터를 쓰고 있습니다.", rowIndex.ToString());

   74   this.progressBar.PerformStep();

   75   Thread.Sleep(10);

   76 }

매개변수로 받은 rowIndex를 단지 label 컨트롤에 대입하고 progressbar를 performstep 하는 UI 컨트롤에 대한 설정 밖에 없다. 
제2 스레드에서 제1 스레드로 Invoke 하게 된 결과이다 
75라인은...너무 빨라서 진행상태를 보기에 어렵기에..살짝 텀을 준것 뿐이다...

이로서 간략하지만 OLEDB를 이용한 엑셀 핸들링을 마치도록 하겠다 
다음 시간(??)엔 jakarta.POI를 이용한 쓰기 방법을 알아 보자...^^;

 

 

Jakarta.POI 를 이용한 Excel Writing

자 이제 마지막 시간이다..^^;

http://jakarta.apache.org/poi/

위 URL을 참조 하면 jakarta.poi 에 대해서 정보를 얻을 수 있다.
이 어셈블리를 사용하기 위해서는 jakarta.poi.dll이 필요하며 웹의 경우 서버에 J#재배포패키지가 설치되어야 한다.
그리고 JSharplib.dll 파일을 참조 추가 하여야 한다.

자 이제 준비가 되었으니 사용법을 알아 보자.

먼저 Enum.cs 파일을 보게 되면 셀과 폰트에서 사용되는 열거형들이 정의되어 있다.
주석은 없지만...ㅡ.ㅡ;;;

외부에 노출되는 타입은 Enum.cs 에 정의되어 있는 열거형들과 ExcelHandler class, ExcelCellStyle class 이다.
ExcelCellSettingBase class는 internal로 정의되어 내부에서만 사용되는 클래스이다.
ExcelHandler 클래스는 엑셀파일을 생성하기위한 기본 클래스이다. 
ExcelCellStyle 클래스는 선택된 시트의 셀 영역에 적용할 스타일에 대한 클래스가 되겠다. 이르자면 폰트에 관련된 속성같은 것 말이다.

이 어셈블리의 구조를 먼저 설명을 해야 할 것 같다.

ExcelHandler 클래스는 내부적으로 List 형의 리스트를 가진다.  이 리스트는 DataSet형의 컬렉션인데 이 DataSet이 바로 하나의 Sheet이다.
즉 List.Count 는 시트의 갯수와 같다는 의미가 되겠다.
이제 이 DataSet의 구조를 살펴 보자.
이 DataSet에는 최소 1개 이상의 DataTable을 가진다.
이 DataTable은 하나의 데이터영역이다.
이르자면..타이틀영역, 빈공간, 데이터 넣는 곳 등등,,,
하나의 시트에 바인딩되는 데이터의 형태는 반드시 DataTable형태가 되어야 하며 이 DataTable들은 여러가지 형태로 시트에 표현이 될 수 있다.
샘플을 보게 되면 2개의 시트가 있는데 첫번째 시트의 경우에는 DataTable이 3개가 있다.  뒷 부분에서 설명하도록 하겠다.
그리고 시트의 설정에 대한 부분도 포함되어 있다. Split이라든지 Group 같은 기능들이 있다.

ExcelCellStyle 클래스는 데이터와 상관없이 셀의 스타일을 정의한다.
이 클래스는 테두리의 선 및 색상정의, 폰트의 종류,색상,크기등의 정의 등등의 속성이 있다.
이 클래스로 만들어진 스타일 인스턴스는 ExcelHandler.SetCellStyle()메서드로  시트에 임베디드된다.

이제 ExcelCellStyle 클래스부터 살펴 보겠다.

위 클래스 다이어그램을 보는 바와 같이 ExcelCellStyle 클래스는 모두 필드와 속성으로만 정의되어 있으며 내부 클래스인 Font 클래스를 가지고 있다.
이 클래스는 FontStyle이라는 속성으로 사용된다.
Align, Rotation, CellColor, Border, Font를 설정할 수 있다.

이제 핵심 클래스인 ExcelHandler 클래스를 살펴보자.

   11 using org.apache.poi.hssf.util;

   12 using org.apache.poi.hssf.usermodel;

이 어셈블리를 사용하기 위해서는 위처럼 2개의 네임스페이스를 등록하여야 한다.

   48     public delegate void RowCount(object sender, RowCountEventArgs e);

   49     public event RowCount OnRowCount;

앞서 OleExcelWriter에서 설명한 바와 같이 OnRowCount 라는 이벤트와 델리게이트를 생성하였다.
앞서 설명을 하였기 때문에 패스..

public ExcelHandler();

public ExcelHandler(List<string> sheetName);

public ExcelHandler(string fullName);

public ExcelHandler(string fullName, List<string> sheetName);

public ExcelHandler(string path, string fileName);

public ExcelHandler(string path, string fileName, List<string> sheetName);

생성자는 위와 같이 정의 되어 있다.
2개 이상의 시트가 있거나 시트의 이름을 정해주고자 할 때는 sheetName 매개변수를 이용하도록 한다. 주의사항은 한글이름은 지원이되지 않는다..ㅡ.ㅠ;

  789 ///

  790 /// 개체 생성

  791 ///

  792 private void Create()

  793 {

  794   this.wb = new HSSFWorkbook();

  795   foreach (string sheet in this.sheetName)

  796   {

  797     this.wb.createSheet(sheet);

  798   }

  799 }

생성자에서는 위의 Create()메서드를 호출하며 이 메서드에서는 마치 Automation과 마찬가지로 워크북을 생성한다음에  sheetName 리스트에서 할당된 갯수만큼 시트를 만든다.

이제 시트를 만들었다면 Data를 입력해야할 차례이다..다음 메서드를 살펴보자.

  506 public void InsertTable(int sheetIndex, bool hasTitle, DataTable dt)

  507 {

  508   if (this.dsList.Count == sheetIndex)

  509   {

  510     DataSet ds = new DataSet();

  511     this.dsList.Add(ds);

  512   }

  513   this.dsList[sheetIndex].Tables.Add(dt);

  514 

  515   if (this.hasTitleList.Count == sheetIndex)

  516   {

  517     StringCollection sc = new StringCollection();

  518     this.hasTitleList.Add(sc);

  519   }

  520   this.hasTitleList[sheetIndex].Add(hasTitle.ToString());

  521 }

매개변수를 보자면.

  1. sheetIndex - 데이터를 넣을 시트의 인덱스
  2. hasTitle - 데이터테이블의 경우 해당 컬럼의 이름을 데이터의 상단에 렌더 할수 있다. 이 제목의 사용 여부이다.
  3. dt - 시트에 입력될 데이터테이블

처음에 구조를 설명하였듯이...
dsList 라는 제너릭리스트는 하나의 워크북을 의미하고 그 안에 있는 ds 라는 데이터셋은 하나의 시트를 의미하며 그 안에 입력되는 dt는 하나의 데이터영역이다.
hasTitleList는 hasTitle의 bool 값을 저장하고 있는 문자열컬렉션(StringCollection)의 제너릭리스트이다. 
dsList와 hasTitleList의 인덱스는 정확히..매칭된다.

  530 public void InsertBlankRow(int sheetIndex, int rowCount)

  531 {

  532   DataTable dt = new DataTable();

  533   dt.Columns.Add(new DataColumn());

  534   for (int i = 0; i < rowCount; i++)

  535   {

  536     DataRow dr = dt.NewRow();

  537     dr[0] = "";

  538     dt.Rows.Add(dr);

  539   }

  540   this.InsertTable(sheetIndex, false, dt);

  541 }

InsertBlackRow() 메서드는 해당 시트에 빈 공간(row)을 삽입하는 메서드이다.
몇 row의 빈칸을 삽입할지 결정하여 호출 하면 540라인에서 보듯이 InsertTable메서드를 호출하며 해당 DataSet(Sheet)에 값이 없는 (스키마만 있는) DataTable을 삽입하게 된다.

이제 데이터를 바인딩 하기 전에 바딩딩 할때 적용되로 스타일을 세팅 하도록 한다.

public void SetCellStyle(int sheetIndex, short startColumnIndex, short endColumnIndex, int startRowIndex, int endRowIndex, CellType type);

public void SetCellStyle(int sheetIndex, short startColumnIndex, short endColumnIndex, int startRowIndex, int endRowIndex, ExcelCellStyle cellStyle);

public void SetCellStyle(int sheetIndex, short startColumnIndex, short endColumnIndex, int startRowIndex, int endRowIndex, ExcelCellStyle cellStyle, CellType type);

스타일을 세팅하기위한 메서드는 SetCellStyle 메서드인데 3가지로 overload되어 있다.
매개변수를 살펴 보자.

  • sheetIndex - 스타일을 적용할 시트의 인덱스
  • startColumnIndex - 스타일을 적용할 영역의 시작 컬럼 인덱스
  • endColumnIndex - 스타일을 적용할 영역의 끝 컬럼 인덱스
  • startRowIndex - 스타일을 적용할 영역의 시작 로우 인덱스
  • endRowIndex - 스타일을 적용할 영역의 끝 로우 인덱스
  • cellStyle - ExcelCellStyle의 개체참조
  • type - 셀의 타입, 현재 정의된 타입은 string, int, datetime 형 3가지이다.

  301 public void SetCellStyle(int sheetIndex, short startColumnIndex, short endColumnIndex, int startRowIndex, int endRowIndex, ExcelCellStyle cellStyle, CellType type)

  302 {

  303   if (this.wb == null)

  304   {

  305     throw new Exception("워크북이 생성되지 않았습니다.");

  306   }

  307   else

  308   {

  309     if (this.wb.getSheetAt(sheetIndex) == null)

  310     {

  311       throw new Exception(sheetIndex + "인덱스에 시트가 생성되지 않았습니다.");

  312     }

  313     else

  314     {

  315       HSSFCellStyle style = this.wb.createCellStyle();

  316       this.AttachStyle(ref style, cellStyle, type);

  317       this.settingList.Add(new ExcelCellSettingBase(sheetIndex, startColumnIndex, endColumnIndex, startRowIndex, endRowIndex, style));

  318     }

  319   }

  320 }

위 메서드를 살펴 보자.
먼저 HSSFCellStyle 형의 셀의 스타일 개체를 생성한다.
그리고 ExcelCellStyle 개채의 값을 style 개체에 attatch를 하고 ExcelCellSettingBase 형의 개체를 생성하고 settingList 라는 제너릭리스트에 Add 한다.
ExcelCellSettingBase 클래스는 스타일이 정의된 영역에 대한 정보를 가지고 있다. 그러므로 settingList 라는 제너릭 리스트는 이 스타일이 정의된 영역에 대한 정보를 가진 컬렉션이다.

이제 바인딩을 해보자.

  548 public void DataBinding()

  549 {

  550   if (this.dsList.Count == 0)

  551   {

  552     throw new Exception("데이터가 없습니다.");

  553   }

  554   else

  555   {

  556     //하나의 DataSet이 하나의 시트

  557     for (int j = 0; j < dsList.Count; j++)

  558     {

  559       this.sheetIndex = j;

  560       int totalRowCountInSheet = this.SumOfTableRowsCount(j);

  561 

  562       int rowCount = 0;

  563 

  564       //데이터 갯수와 헤더의 갯수(있을거라고 가정)가 65536을 넘을 수 없다.

  565       //헤더는 각각의 테이블당 한개의 row를 차지하므로 한개의 시트에 속하는 테이블의 갯수와 동일하다.

  566       //하나의 시트의 Row가 65535를 넘게 되면 오류...

  567       totalRowCountInSheet += dsList[j].Tables.Count;

  568 

  569       if (totalRowCountInSheet > 65535)

  570       {

  571         throw new ArgumentOutOfRangeException("데이터의 수가 65535건이 넘었습니다.");

  572       }

  573       else

  574       {

  575         for (int i = 0; i < dsList[j].Tables.Count; i++)

  576         {

  577           if (Convert.ToBoolean(this.hasTitleList[j][i]))

  578           {

  579             this.CreateTitle(j, dsList[j].Tables[i].Columns, rowCount);

  580             rowCount++;

  581           }

  582           this.DataBinding(dsList[j].Tables[i], rowCount);

  583           rowCount += dsList[j].Tables[i].Rows.Count;

  584         }

  585       }

  586     }

  587   }

  588 }

지금까지 정의 된 값을 토대로 하여 데이터를 바인딩 한다.
560라인의 SumOfTableRowCount()메서드는 해당 시트에 바인딩 될 총 row의 합계이다.
이를 조사하는 이유는..
엑셀의 경우 하나의 시트에 65536행까지만 허용되기 때문이다.
그러나 Excel 2007의 경우에는 1백만행*65536컬럼 까지 지원한다고 한다..
579라인에 보면 CreateTitle()메서드가 보이는데 제목을 가진 DataTable이라면(577라인의 조건절) 제목을 시트에 쓴다.
그리고 582라인의 DataBinding 메서드에서 실제로 시트에 쓴다.

먼저 CreateTitle()메서드를 살펴보자.

  745 HSSFSheet sheet = this.wb.getSheetAt(sheetIndex);

  746 HSSFRow row = sheet.createRow(rowCount);

  747 for (short i = 0; i < dcc.Count; i++)

  748 {

  749   if (!dcc[i].ColumnName.StartsWith("Column"))

  750   {

  751     HSSFCell cell = row.createCell(i);

  752     cell.setEncoding(1);

  753     this.currentRowIndex = rowCount;

  754     this.currentColumnIndex = i;

  755     ExcelCellSettingBase cellStyle = this.settingList.Find(FindStyle);

  756     if (cellStyle != null)

  757     {

  758       HSSFCellStyle cs = cellStyle.CellStyle;

  759       cell.setCellStyle(cs);

  760     }

  761     cell.setCellValue(dcc[i].ColumnName);

  762   }

  763 }

발췌했다.
745라인에서 sheet개체를 얻고 746라인에서 createRow()메서드로 row를 만들었다.
749라인의 조건절은 자동생성된 컬럼을 걸르기 위함이다. 자동으로 생성된 컬럼은 의미가 없기 때문이다 . 자동생성된 컬럼의 이름패턴은 Column1, Column2..... 처럼 생성된다.
755라인에 주목하자. 
제너릭리스트의 Find()메서드를 사용하였다. 닷넷 2.0에서 새로나온 개념이다..^^; 
이 Find 메서드는 settingList 라는 제너릭 리스트에서 해당 조건에 맞는 첫번째 아이템을 반환한다.

  806 private bool FindStyle(ExcelCellSettingBase setting)

  807 {

  808   if (setting.SheetIndex == this.sheetIndex && setting.StartRowIndex <= this.currentRowIndex && setting.EndRowIndex >= this.currentRowIndex && setting.StartColumnIndex <= this.currentColumnIndex && setting.EndColumnIndex >= this.currentColumnIndex)

  809   {

  810     return true;

  811   }

  812   else

  813   {

  814     return false;

  815   }

  816 }

FindStyle 메서드를 살펴 보면 앞서 정의했던 settingList 제너릭 리스트에서 현재 Cell의 영역이 포함되어 있는 첫번째 ExcelCellSettingBase 개체를 반환한다.
음..한번도 안써보신 분이라면..약간 헤깔릴 수도....

어쨋든 해당되는 부분을 찾았으면 759라인처럼 셀에 스타일을 정의한 후에 761라인 처럼 값을 입력한다.

이제 582라인의 DataBinding 메서드를 살펴 보자.

  620 HSSFCellStyle cs = cellStyle.CellStyle;

  621 c.setCellStyle(cs);

  622 object o = dt.Rows[rowIndexInCurrentTable][currentColumnIndex];

  623 if (o.ToString() != "")

  624 {

  625   switch ((CellType)cs.getDataFormat())

  626   {

  627     case CellType.CELL_TYPE_NUMERIC:

  628       c.setCellValue(Convert.ToDouble(o));

  629       break;

  630     case CellType.CELL_TYPE_DATETIME:

  631       c.setCellValue(new java.util.Date(Convert.ToDateTime(o).ToLongTimeString().Trim()));

  632       break;

  633     case CellType.CELL_TYPE_STRING:

  634       c.setCellValue(o.ToString().Trim());

  635       break;

  636   }

  637 }

  638 else

  639 {

  640   c.setCellValue("");

  641 }

역시 발췌했다.
CreateTitle() 메서드와 마찬가지로 Find 메서드로 해당되는 ExcelCellSettingBase 개체를 반환하여 적용한다.
그리고 추가로 작업을 하나 더 하는데..
바로 셀의 타입을 결정하는 부분이다.
셀의 타입 역시 HSSFCellStyle 개체에 포함되어 있는 부분이다.
그렇기에 셀의 형식에 맞게끔 데이터를 형변환해서 입력해주어야 한다.
그 부분이 switch ~case 구문이다.
631라인의 경우에는 datetime형식을 표현하는 방식이 닷넷과 자바가 달라서..저런 방법을 취할수 밖에 없었다...쿨럭. 그리고 마지막으로 이벤트를 발생 시킨다.

  655 this.OnRowCount(thisnew RowCountEventArgs(rowIndexInCurrentTable + 1, dt.TableName));

지금까지 작업을 하였다면 하나의 워크북에 시트와 데이터가 모두 만들어 졌을 것이다.
이제 약간의 추가 작업을 선택적으로 해주면 되겠다.

추가적인 기능은

public void ColumnWidth(int sheetIndex, short columnIndex, short width);

public void GroupColumn(int sheetIndex, short startColumn, short endColumn);

public void GroupRow(int sheetIndex, int startRow, int endRow);

public void Merge(int sheetIndex, short startColumnIndex, short endColumnIndex, int startRowIndex, int endRowIndex);

public void RowHeight(int sheetIndex, int startRowIndex, int endRowIndex, int height);

public void Split(int sheetIndex, int colSplit, int rowSplit);

위와 같다.

  • ColumnWidth - 컬럼의 폭을 결정한다, 모든 단위는 px 단위이다.
  • GroupColumn - 컬럼을 그룹화한다.
  • GroupRow - 로우를 그룹화한다.
  • Merge - 정의된 셀영역을 합친다.
  • RowHeight - 로우의 높이를 결정한다.
  • Split - 문서를 스플릿한다.

주의 할 것은 이 메서드들은 반드시 바인딩 후에 호출하여야 한다.는 것이다.

이제 마지막 단계이다.
이렇게 워크북을 만들었다면..이제 출력을 해야 할것이다.
출력은 2가지가 지원한다.
하나는 파일 출력이며 하나는 스트림출력이다.

  664 public void OutPut()

  665 {

  666   string fPath = this.fullName;

  667   if (fPath == "")

  668   {

  669     fPath = this.path + this.fileName;

  670   }

  671   using (Stream fileOut = new FileStream(fPath, FileMode.Create))

  672   {

  673     this.wb.write(fileOut);

  674   }

  675 }

OutPut()메서드는 파일 출력이다. 673라인의 write() 메서드로 정의된 파일명으로 파일이 생성된다.
윈도우즈 응용프로그램에서 사용하기 적합하다.

  681 public byte[] OutPutToWeb()

  682 {

  683   Stream fileOut = new MemoryStream();

  684   this.wb.write(fileOut);

  685   byte[] buffer = ((MemoryStream)fileOut).GetBuffer();

  686   fileOut.Close();

  687   return buffer;

  688 }

이 메서드는 byte[] 배열형으로 출력한다.  
웹의 경우 서버에 파일을 만들필요가 없기에 스트림으로 내려 주는 방식이 좋다. 샘플 예제는 웹방식으로 적용해 보았다.

지금까지...어셈블리에 대해서 알아 보았다.
이제 샘플코드를 보고..어떻게 사용하는지 알아보자.

   23 List<string> sheetNames = new List<string>(2);

   24 sheetNames.Add("sheet1");

   25 sheetNames.Add("sheet2");

   26 

   27 ExcelHandler handler = new ExcelHandler(sheetNames);

   28 handler.OnRowCount += new ExcelHandler.RowCount(handler_OnRowCount);

   29 

   30 handler.InsertTable(0, falsethis.MakeTitle());

   31 handler.InsertBlankRow(0, 3);

   32 handler.InsertTable(0, truethis.MakeDataTable("테스트"));

   33 handler.InsertTable(1, falsethis.MakeTitle());

   34 handler.InsertTable(1, truethis.MakeDataTable("테스트"));

   35 

   36 handler.SetCellStyle(0, 0, 0, 0, 0, this.MakeCellStyle1());

   37 handler.SetCellStyle(0, 0, 0, 10, 50, this.MakeCellStyle2(), CellType.CELL_TYPE_NUMERIC);

   38 handler.SetCellStyle(0, 1, 1, 10, 50, this.MakeCellStyle2(), CellType.CELL_TYPE_STRING);

   39 handler.SetCellStyle(0, 2, 2, 10, 50, this.MakeCellStyle2(), CellType.CELL_TYPE_DATETIME);

   40 

   41 handler.DataBinding();

   42 

   43 handler.RowHeight(0, 0, 0, 100);

   44 handler.ColumnWidth(0, 0, 300);

   45 handler.Merge(0, 0, 2, 0, 0);

   46 handler.Split(0, 0, 5);

   47 handler.GroupRow(0, 5, 10);

   48 

   49 //파일 다운로드

   50 this.Response.Clear();

   51 this.Response.AddHeader("Content-Disposition""attachment; filename=exceltest.xls");

   52 this.Response.ContentType = "application/vnd.ms-excel ";

   53 this.Response.BinaryWrite(handler.OutPutToWeb());

   54 this.Response.End();

22~25 라인에서 시트의 이름을 생성했다. 다시 말하지만 한글은 지원하지 않는다.
27라인에서 생성자를 만들고
28라인에서 이벤트를 등록하였다.(웹에서는 사실 이 이벤트가 소용없으나 선언된것이기 때문에 정의하지 않으면 예외를 던진다.)

30~34라인에서 DataTable을 각각의 시트에 입력하였다. 첫번째 시트에는 공란도 입력하였다. 

36~39 라인에서 스타일과 타입을 정의 하였다. 영역이 중복되어도 상관없지만..먼저 정의된것만 적용된다.^^;  테스트를 위하여 각각 숫자형, 문자형, 일시형을 정의해 보았다.

그리고 41라인 처럼 바인딩을 한다.

43~47라인에는 시트의 설정을 변경해 보았다. 어떻게 출력이 될까 하고 예측해보자...

50~54 라인에서는 웹상에서 출력하는 방법을 보여준 예이다.
주목 할 것은 바로 53라인이다.
Response.BinaryWriter() 메서드로 출력하는 것을 확인하기 바란다.

이제 샘플코드를 실행해서 엑셀파일을 다운 받아 보면..아래와 같은 결과가 나오겠다.

그리고 ..

마지막으로 스타일을 정의 하는 부분은 같이 업로드된 파일 처럼 코드스니핏을 이용하면 편하다.
혹시 이용법을 모르는 사람을 위해..
첨부된 excelStyle.snippet 파일을 C:\Documents and Settings\사용자이름\My Documents\Visual Studio 2005\Code Snippets\Visual C#\My Code Snippets 경로에 붙여 넣기를 한다.
그리고 VS2005를 재시동한다.

이제 화면에서 excel이라고 치면 인텔리센스가 뜬다...그게 코드 스니핏이다.



[출처] C#에서 OLEDB를 이용한 엑셀(EXCEL)파일 읽기 및 쓰기|작성자 이카루스


Posted by blueasa
TAG Excel, OLEDB

댓글을 달아 주세요