엔터프라이즈커넥터 엔터프라이즈 커넥터로 'VCL 애플리케이션에서 엑셀 데이터 가져오기&편집하기'
2019.09.27 19:10
엔터프라이즈 커넥터는 파이어댁(FireDAC)을 이용해 데이터베이스를 연동하는 것과 동일한 방법으로 여러 데이터들을 바로 연동할 수 있게 해주는 솔루션입니다.
이번에는 커넥터중 Excel용 CData FireDAC 컴포넌트를 사용하여 Excel에 양방향으로 액세스 할 수 있는 응용 프로그램을 구축 해보도록 하겠습니다.
Step 1) 엑셀 엔터프라이즈 커넥터 평가판 다운로드 & 설치
엔터프라이즈 커넥터 설치 방법은 아래 주소를 참조하여 주시기 바랍니다.
https://tech.devgear.co.kr/delphi_news/456343
Step 2) 엑셀 데이터 준비
ExcelFile을 유효한 Excel 파일 (파일 경로 포함)로 설정해야합니다. 커넥션 컴포넌트는 Excel 2007 이상에서 사용되는 Office Open XML 형식을 지원합니다.
통합 문서를 쿼리 한 후 통합 문서의 스프레드 시트 및 범위에 대해 SELECT, INSERT, UPDATE 및 DELETE 문을 실행할 수 있습니다 이 예제에서는 첨부한 Sample.xlsx 파일을 사용하도록하겠습니다.(다른 파일을 사용하셔도 무방합니다).
Step 3) FireDac 연결정의(Connection Definetion) 설정
영결 정의는 특정 FireDac 드라이버를 사용하여 DBMS에 프로그램을 연결하는 방법을 정의하는 매개변수 집합입니다. 데이터베이스 연결과 동일하게 Data Exploer 및 FireDAC Connection Editor를 사용하여 Connetion Name을 작성할 수 있습니다. 연결 정의 방법에는 지속적(Persitent), Private, 또는 Tempory 연결등이 있습니다. 자세한 정보는 파이어닥 연결 정의를 참조하십시오. 여기 따라하기에서는 데이터 탐색기를 사용하여 애플리케이션 및 프로젝트간에 공유 할 수있는 지속적 연결을 사용하도록 하겠습니다.
- Data Exploer를 클릭하고 FireDAC 노드를 확장합니다.
- CData Excel 데이터 소스 노드에서 오른쪽 마우스 클릭하고 Add New Connection을 클릭합니다.
- Connection Name을 'Excel_Conn'으로 입력합니다.
- FireDAC 연결 편집기가 열리면 아래와 같이 ExcelFile 항목에 영결하고자 하는 엑셀파일을 입력합니다.
아래의 표는 위의 속성 중 테이블,리턴 값 및 기타 데이터 액세스를 제어하기 위한 몇 가지 속성들을 정리한 표입니다.
| 분류 | 속성 | 내용 |
| 스키마검색 | Orientation | 스프레드 시트의 방향과 일치하는 모델 테이블,가로 또는 세로로 설정합니다. |
| DefinetionTables |
표준 Excel 범위 형식을 사용하여 테이블로 액세스하려는 범위를 명시 적으로 지정하십시오. [Table Name] = [Sheet Name]! [Range]. 세미콜론으로 구분 된 목록에 여러 테이블을 지정하십시오. SELECT * FROM Excel_Sheet#A5:F * |
|
| 컬럼검색 | Header | true 인 경우 첫 번째 행에서 컬럼 이름을 인식합니다. 그렇지 않으면 컬럼 이름 A, B, C 등이 사용됩니다. |
| TypeDetectionScheme | 컴포넌트가 컬럼의 데이터 유형을 발견하는 방법을 결정합니다. | |
| RowScanDepth | TypeDetectionScheme를 사용하는 경우 이 특성을 스캔 할 행 수로 설정하여 열 데이터 유형을 판별합니다. | |
| 공식 | AllowFormula | 수식 결과 또는 수식 표현식을 삽입 또는 업데이트할지 여부 |
| Recalculate | 데이터가 변경되면 수식을 다시 계산합니다. | |
| IgnoreCalcError | 수식을 계산할 때 발생한 오류를 무시합니다. | |
| HasCrossSheetReferences | 데이터가 변경되면 모든 워크 시트에서 수식을 다시 계산합니다. 컴포넌트는 교차 시트 참조를 따르지만 성능 비용이 있습니다. |
5. Test 버튼을 눌러 엑셀 파일이 연결되는지 확인 후 OK 버튼을 클릭합니다.
- File > New > VCL Forms Application > Delphi 를 선책하여 프로젝트를 생성합니다.
- TFDPhysExcelDriverLink 및 TFDConnection, TFDGUIxWaitCurso 컴포넌트를 내려 놓습니다.
- TFDConneciton 컴포넌트의 ConnectionDefName 속성을 'Excel_conn'으로 지정합니다
- TFDQuery컴포넌트를 내려 놓고 아래 속성들을 설정하십시오.
- Connection : 이미 지정되어 있습니다.
- SQL: 원하는 SQL 문을 입력합니다. (예:select * from Sheet4)
- Active:true
아래와 같이 코드로 구현하여 연결정의와 쿼리문 구현 할수도 있습니다.
|
FDConnection1.Name := 'SQLTest'; FDConnection1.DriverName := 'CData.Excel'; with FDConnection1.Params as TFDPhysCDataExcelConnectionDefParams do begin ... end; FDQuery1.Connection := FDConnection1; FDConnection1.Connected := true; FDQuery1.Close; FDQuery1.Sql,Text := 'SELECT * FROM Sheet4'; FDQuery1.Open;
|
5. TDataSource, TDBGrid 컴포넌트를 사용하여 엑셀 데이터를 아래와 같이 화면에 표시합니다.
- TDataSource의 DataSet 속성:FDQuery1;
- TDBGrid의 DataSource 속성:DataSource1;
Step 5) 엑셀 데이터 조회및 데이터 입력작업
1. 아래 화면과 같이 TButton, TEdit 컴포넌트들을 내려 놓습니다.
2. '전체 쿼리 검색' 버튼의 onClick 이벤트 핸들러를 다음과 같이 구현합니다.
|
procedure TForm1.Button1Click(Sender: TObject); const SQL_Text = 'select * from sheet4'; begin FDQuery1.Close; FDQuery1.SQL.Text := SQL_Text; FDQuery1.Open; end; |
3. '필터검색' 버튼의 onClick 이벤트 핸들러를 다음과 같이 구현합니다
|
procedure TForm1.Button2Click(Sender: TObject); const SQL_Text = 'select a,b,c from Sheet4 where a >=:pcode'; begin FDQuery1.Close; FDQuery1.SQL.Text := SQL_Text; FDQuery1.Params[0].AsString := Edit1.Text; FDQuery1.Open; end; |
4. 셀범위 지정
범위 기능을 사용하여 스프레드 시트의 특정 부분에서 데이터를 선택할 수 있습니다.
'셀범위지정' 버튼의 onClick 이벤트 핸들러를 다음과 같이 구현합니다
|
procedure TForm1.Button3Click(Sender: TObject); const SQL_Text = 'Select * from Sheet4#A3:B10'; begin FDQuery1.Close; FDQuery1.SQL.Text := SQL_Text; FDQuery1.Open; end; |
5. 행 추가
INSERT 문을 사용하여 스프레드 시트에 새 행을 추가 할 수 있습니다. header 속성을 True로 설정하면 컬럼 헤더를
기준으로 추가할 수 있습니다. Header 속성이 false이면 셀 이름 (A, B, C 등)을 사용하여 행을 추가해야합니다.
행을 추가하면 시트 끝에 추가됩니다. 새로운 행에는 Excel 시트의 위치에 따라 RowId가 할당됩니다.
'행추가' 버튼의 onClick 이벤트 핸들러를 다음과 같이 구현합니다
|
procedure TForm1.Button4Click(Sender: TObject); const SQL_Text = 'insert into Sheet4 (A,B,C) values(:pa, :pb, :pc)'; begin FDQuery1.Close; FDQuery1.SQL.Text := SQL_Text; FDQuery1.Params[0].AsString := Edit1.Text; FDQuery1.Params[1].AsString := Edit2.Text; FDQuery1.Params[2].AsString := Edit3.Text; FDQuery1.Execsql; Button1Click(sender);
end; |
6. 행중간추가
때로는 Excel 시트의 중간에 행을 삽입하려고 할 수 있습니다. 행 번호를 식별하는 RowId와 함께 INSERT 명령을
사용하여 이를 지원합니다.
'행중간삽입' 버튼의 onClick 이벤트 핸들러를 다음과 같이 구현합니다
|
procedure TForm1.Button5Click(Sender: TObject); const SQL_Text = 'insert into Sheet4 (RowId, A, B,C) VALUES (5, :PA, :PB, :PC)'; begin FDQuery1.Close; FDQuery1.SQL.Text := SQL_Text; FDQuery1.Params[0].AsString := Edit2.Text; FDQuery1.Params[1].AsString := Edit3.Text; FDQuery1.Params[2].AsString := Edit4.Text; FDQuery1.Execsql; Button1Click(sender);
end; |
7. 프로그램을 실행하여 각 버튼을 클릭하여 조회및 데이터를 추가하여 보도록하겠습니다.
- 지정된 셀 범위(A3:B10) 데이터를 확인 하실 수 있습니다.
-마지막 행에 추가된 데이터를 확인 하실 수 있습니다.
- 5번행에 추가된 코드값 '4444'를 확인 할 수 있습니다.
** 수식 사용도 가능합니다.**
컴포넌트를 사용하면 셀에 수식을 삽입 할 수 있습니다. AllowFormula 연결 문자열 속성이 true로 설정되면 등호 ( '=')로 시 작하는 삽입은 모두 수식으로 취급됩니다.
예를 들어, 다음은 셀 B1:B5를 합하는 수식을 B 열에 삽입합니다.
INSERT INTO Excel_Sheet (A, B) VALUES ('Bill', '=SUM(B1:B5)')
Step 6) 배포하기
설치 디렉토리에는 배포 용 FireDAC 컴포넌트의 라이센스를 부여하는 지침이 포함 된 'deployment_licensing.txt'파일이 있습니다.
참고 :이 파일은 정식 버전 설정을 실행할 때만 설치됩니다. 베타 또는 평가판 버전 설정을 실행할 때는 설치되지 않습니다.
