MS Excel 데이터 읽고 쓰기

1. 개요

  마이크로소프트 엑셀(MS Excel)이 설치된 환경에서는 델파이(Delphi)로 MS Excel Automation 기술을 이용해 엑셀파일을 직접 열어서 읽기 / 쓰기 작업을 할 수 있다. 

  델파이로 엑셀파일을 읽고 쓰는 작업은 ADO를 이용하여 "Microsoft.Jet.OLEDB.4.0" 프로바이더(Provider)로 접근해서 일반적인 SELECT 쿼리를 이용하는 방법도 있으나 여기서는 OLE Automation 기술을 이용한 엑셀파일 접근 방법에 대해서 알아 본다.

2. Use절에 "Excel2000" 유닛 추가

  Delphi XE버전 이상을 설치하면 다음 경로에 Excel2000.pas 유닛이 이미 설치 되어 있다.

        "C:\Program Files (x86)\Embarcadero\RAD Studio\10.0{버전별번호}\OCX\Servers"

Uses
   Excel2000;

3. LCID?

  LCID는 엑셀(Excel)에서 "xlListDataTypeCurrency" 에 이정되는 화폐(Currency) 심볼을 가리키며, 엑셀 컬럼타입에 데이터 타입이 지정되지 않았다면 항상 0을 리턴한다.

LCID를 얻는 방법은 다음과 같다.

   // Global 변수로 설정해서 초기화 부분에서 획득해 주면 된다.
   LCID := GetUserDefaultLCID; 

4. Excel문서 구조 및 변수 선언

  엑셀을 OLE Automation Object를 이용해 불러오기 위해서는 다음과 같이 변수를 선언해야 한다

private
    // 용도에 따라서 전역(Global) 또는 지역(Local) 변수로 선언하면 된다.
    ExcelApp: TExcelApplication;
    myxlBook: TExcelWorkbook;
    myxlSheetLegacy: TExcelWorksheet; //기존 시트 읽기용
    myxlSheetNew: TExcelWorksheet;     //신규 시트 추가용

5. Excel 연결을 위한 Application 객체 생성

    myxlApp := TExcelApplication.Create(Nil);
    myxlApp.Connect;
    myxlApp.Visible[LCID] := True; // 엑셀을 연결하고 화면에 노출되도록 한다

  

6. 엑셀 연결 종료 및 해제

    myxlApp.Disconnect;
    myxlApp.Quit;
    FreeAndNil(myxlApp); 

  

7. WorkBook 추가/연결

     //엑셀에 새로운 WorkBook을 추가하고 활성화된 워크북을 연결한다.
    myxlApp.Workbooks.Add(EmptyParam, LCID);
    myxlBook := TExcelWorkbook.Create(myxlApp);
    myxlBook.ConnectTo(myxlApp.ActiveWorkbook); 

  

8. WorkBook 연결끊기

    //엑셀문서를 닫기  전에 반드시 WorkBook의 연결을 끊어야 한다.
    //연결을 끊기 전에 편집된 문서를 저장한다.
    myxlBook.Close(True,'C:\ExcelTest\ExcelTest1.xlsx'); 
    myxlBook.Disconnect;
    FreeAndNil(myxlBook);

  

9. WorkSheet 추가 및 연결

    myxlSheetLegacy := TExcelWorksheet.Create(myxlBook);
    myxlSheetLegacy.ConnectTo(myxlBook.ActiveSheet as _worksheet); //현재 활성화된 워크시트 연결
    myxlSheetLegacy.Name := 'LegacySheet1'; //컴포넌트 이름을 변경해준다, 컴포넌트 이름이 중복되면 안되기 때문

  

10. 워크시트(WrokSheet) 연결끊기

    myxlSheetLegacy.Disconnect;
    FreeAndNil(myxlSheetLegacy);

  

11. 워크북(WorkBook)에 새 시트(WorkSheet) 추가하기

    myxlBook.Worksheets.Add(EmptyParam, EmptyParam, EmptyParam, EmptyParam, LCID);
    myxlSheetNew := TExcelWorksheet.Create(myxlBook);
    myxlSheetNew.ConnectTo(myxlBook.ActiveSheet as _worksheet);
    myxlSheetNew.Name := 'NewSheet1'; 

  

12. 시트이름 또는 인덱스로 시트(WorkSheet) 접근하기

   (myxlApp.Worksheets[0] as _Worksheet).Activate(LCID);
   // Or
   (myxlApp.Worksheets['Sheet1'] as _Worksheet).Activate(LCID);

  

13. Cell과 Range를 이용한 값(Value) 입력

    //열과 컬럼을 이용한 값 할당(현재 Active Sheet에)
    myxlApp.Cells.Item[1,1] := 'Value 1';
    //셀이름을 이용한 범위지정을 통한 값 할당(현재 Active Sheet에)
    myxlApp.Range['A3','A3'].Value := 'value 2'; 
    //열과 컬럼을 이용한 값 할당(지정된 Sheet에)
    myxlSheetLegacy.Cells.Item[1,5] := 'JITENDRA'; 
    //셀이름을 이용한 범위지정을 통한 값 할당(지정된 Sheet에)
    myxlSheetLegacy.Range['E3','E3'].Value := '7834911261'; 

  

13. 범위를 지정해 서식 변경하기

    with myxlSheetLegacy.Range['A1', 'B3'] do
    begin
      Font.Name := 'Verdana';
      Font.Size := 15;
      Font.Bold := True;
      Font.Strikethrough := True;
      Font.Color := clRed;
    end;

  

14. 범위를 지정해 셀의 배경색 변경하기

    with myxlSheetLegacy.Range['A1', 'A1'].Interior.Color := clYellow;
    // 지정된 범위의 셀을 병합하고 컬러를 변경
    myxlSheetLegacy.Range['A5', 'D7'].Merge(False);
    myxlSheetLegacy.Range['A5', 'D7'].Interior.Color := clRed;

  

15. 범위를 지정해 셀 합치기

    //Merge 파라미터가 True인 경우에는 행별로 셀을 병합
    myxlSheetLegacy.Range['A5', 'D7'].Merge(False);
    myxlSheetLegacy.Range['A5', 'D7'].Value := 'Merged data';

  

16. 범위를 지정해 셀의 높이와 길이 변경하기

    myxlSheetLegacy.Range['B5', 'B5'].ColumnWidth := 5; //단일컬럼 폭 변경
    myxlSheetLegacy.Range['J5', 'L8'].ColumnWidth := 15; //다중컬럼 폭 변경
    myxlSheetLegacy.Range['B5', 'B5'].RowHeight := 50; //단일열 높이 변경
    myxlSheetLegacy.Range['J10', 'J15'].RowHeight := 50; //다중열 높이 변경

  

17. 이미 존재하는 워크북(WorkBook) 열기

    myxlApp.Workbooks.Open ( 'C:\ExcelTest\ExcelTest1.xlsx'
    EmptyParam , EmptyParam , EmptyParam , EmptyParam ,
    EmptyParam , EmptyParam , EmptyParam , EmptyParam ,
    EmptyParam , EmptyParam , EmptyParam , EmptyParam , 0 );

  

18. 동일시트(Sheet)내에서 셀의 복사와 붙여넣기

    // #1 
    myxlSheetLegacy.UsedRange[LCID].Copy(myxlSheetLegacy.Range['J10', 'J10']);
    myxlSheetLegacy.Range['A5', 'D7'].Copy(myxlSheetLegacy.Range['J10', 'J10']);

    // #2 
    myxlSheetLegacy.UsedRange[LCID].Copy(EmptyParam);
    myxlSheetLegacy.Range['J10', 'J10'].PasteSpecial(xlPasteAll, xlPasteSpecialOperationNone, EmptyParam, EmptyParam);
 
    myxlSheetLegacy.Range['A5', 'D7'].Copy(EmptyParam);
    myxlSheetLegacy.Range['J10', 'J10'].PasteSpecial(xlPasteAll, xlPasteSpecialOperationNone, EmptyParam, EmptyParam);

  

19. 다른 시트로 복사/붙여넣기

    // #1 
    myxlSheetLegacy.UsedRange[LCID].Copy(myxlSheetNew.Range['J10', 'J10']);
    myxlSheetLegacy.Range['A5', 'D7'].Copy(myxlSheetNew.Range['J10', 'J10']);

    // #2
    myxlSheetLegacy.UsedRange[LCID].Copy(EmptyParam);
    myxlSheetNew.Range['J10', 'J10'].PasteSpecial(xlPasteAll, xlPasteSpecialOperationNone,EmptyParam, EmptyParam);
 
    myxlSheetLegacy.Range['A5', 'D7'].Copy(EmptyParam);
    myxlSheetNew.Range['J10', 'J10'].PasteSpecial(xlPasteAll, xlPasteSpecialOperationNone,EmptyParam, EmptyParam); 

  

20. 새 컬럼 삽입하기

    myxlSheetLegacy.Range['b1', 'b1'].Columns.Insert(xlShiftToRight); // xlShiftToLeft, b1 컬럼 앞으로 삽입//

  

21. 새 열 삽입하기

   myxlSheetLegacy.Range['b2', 'b2'].Rows.Insert(xlShiftDown); // xlShiftUp, b2열 윗쪽으로 삽입

  

22. 선택된 셀(범위) 초기화 하기

    myxlSheetLegacy.Range['b3', 'b10'].ClearContents; //내용을 지우고
    myxlSheetLegacy.Range['b3', 'b10'].ClearFormats;  //서식을 지운다

  

23. 워크시트(WorkSheet) / 워크북(WorkBook) 저장

    myxlSheetLegacy.SaveAs('Filename');
    myxlBook.Save; 

  

24. 워크시트/워크북 인쇄 미리보기 /

    myxlSheetLegacy.PrintPreview;
    myxlSheetLegacy.PrintOut;
    myxlBook.PrintPreview;
    myxlBook.PrintOut; 

  

25. 페이지 설정

    myxlSheetLegacy.PageSetup.
    // header: 
    myxlSheetLegacy.PageSetup.CenterHeader := '' The report shows '' ;
    // footer: 
    myxlSheetLegacy.PageSetup.CenterFooter := '' The & P '' ;
    // 위쪽 헤더 여백을 2cm로 지정: 
    myxlSheetLegacy.PageSetup.HeaderMargin := 2 / 0.035 ;
    // 꼬릿말 여백을 3cm로 지정
    myxlSheetLegacy.PageSetup.HeaderMargin := 3 / 0.035 ;
    // 위쪽 여백을 2cm로 지정: 
    myxlSheetLegacy.PageSetup.TopMargin := 2 / 0.035 ;
    // 하단 경계역역을 2cm로 지정
    myxlSheetLegacy.PageSetup.BottomMargin := 2 / 0.035 ;
    // 좌측여백을 2cm로 지정
    myxlSheetLegacy.PageSetup.LeftMargin := 2 / 0.035 ;
    // 우측 여백을 2cm로 지정
    myxlSheetLegacy.PageSetup.RightMargin := 2 / 0.035 ;
    // 가로 중앙정렬
    myxlSheetLegacy.PageSetup.CenterHorizontally := 2 / 0.035 ;
    // 세로 중앙정렬
    myxlSheetLegacy.PageSetup.CenterVertically := 2 / 0.035 ;
    // 그리드라인 표시
    myxlSheetLegacy.PageSetup.PrintGridLines := True ;

  

26. 지정된 범위 자동 채우기

    myxlSheetLegacy.Range['p1', 'p1'].Value := 1;
    myxlSheetLegacy.Range['p2', 'p2'].Value := 2;
    myxlSheetLegacy.Range['p1', 'p1'].AutoFill(myxlSheetLegacy.Range['p1', 'p10'], xlFillSeries);
    {  Other fill options
       xlFillCopy 
       xlFillDays 
       xlFillDefault 
       xlFillFormats 
       xlFillMonths 
       xlFillSeries 
       xlFillValues 
       xlFillWeekdays 
       xlFillYears 
       xlGrowthTrend 
       xlLinearTrend 
    }

  

27. 지정된 범위의 테두리 변경하기

    myxlSheetLegacy.Range['p3', 'p4'].Borders.Color := clRed;
    myxlSheetLegacy.Range['p3', 'p4'].Borders.LineStyle := xlDouble;
    myxlSheetLegacy.Range['p3', 'p4'].Borders.Item[xlEdgeLeft].Color := clBlue;
    myxlSheetLegacy.Range['p3', 'p4'].Borders.Item[xlEdgeRight].Color := clBlue;
    { Other line styles.
       xlContinuous 
       xlDash 
       xlDashDot 
       xlDashDotDot 
       xlDot 
       xlDouble 
       xlSlantDashDot 
       xlLineStyleNone
    }

  

28. 지정된 범위의 채움 스타일 변경하기

    myxlSheetLegacy.Range['p3', 'p4'].Interior.Pattern := xlPatternCrissCross;
    myxlSheetLegacy.Range['p3', 'p4'].Interior.PatternColor := clBlue; 
    {  Other pattern styles 
        xlPatternAutomatic
        xlPatternChecker 
        xlPatternCrissCross 
        xlPatternDown 
        xlPatternGray16 
        xlPatternGray25
        xlPatternGray50
        xlPatternGray75 
        xlPatternGray8 
        xlPatternGrid
        xlPatternHorizontal 
        xlPatternLightDown 
        xlPatternLightHorizontal 
        xlPatternLightUp 
        xlPatternLightVertical 
        xlPatternNone 
        xlPatternSemiGray75 
        xlPatternSolid 
        xlPatternUp 
        xlPatternVertical
    }

  

29. 지정된 범위 셀들의 집계(SUM/AVG/MAX/COUNT)등 처리

    myxlSheetLegacy.Range['k1', 'k1'].Formula := '=Sum(p3:p8)';
    myxlSheetLegacy.Range['k3', 'k3'].Formula := '=Avg(p3:p8)'; 
    myxlSheetLegacy.Range['k5', 'k5'].Formula := '=Max(p3:p8)';
    myxlSheetLegacy.Range['k7', 'k7'].Formula := '=Count(p3:p8)';

  

이상 유용하게 사용하세요^^