C#에서 Microsoft Excel 파일 다루는 방법
본 포스팅에서는 Component Object Model(이하 COM) 객체를 로드하여 C# 프로그램에서 Microsoft Excel 형식의 데이터를 읽거나 기록하는 방법에 대해 설명합니다.
1단계. C# 프로그램에서 Microsoft Excel COM 객체 참조시키기
[참조] - [참조 추가(R)...]를 클릭합니다.
"참조 관리자" 화면이 뜨면 [어셈블리] - [확장] 메뉴를 클릭합니다. 그 다음, "Microsoft.Office.Interop.Excel" 모듈을 체크한 뒤 [확인] 버튼을 눌러 해당 모듈을 C# 프로그램에 참조시킵니다.
2단계. Microsoft Excel Application 로드하기
Excel 파일을 입/출력할 메서드에는 다음과 같이 코딩합니다. Microsoft.Office.Interop.Excel.Application형 인스턴스를 생성하는 것으로써 Microsoft Excel Application이 내부적으로 실행되었습니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
/* ... 생략 ... */
2-1단계. Workbook 새로 만들기
Application을 로드했으면 새 파일(워크북)을 만들어보겠습니다. 새 워크북을 만드는 방법은 다음과 같이 Microsoft.Office.Interop.Excel.Application.Workbooks.Add 메서드를 실행하면 됩니다. Application이 내부적으로 새 워크북을 하나 띄우고 이를 객체 형태로 반환하게 됩니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Add();
/* ... 생략 ... */
2-2단계. 기존의 Workbook 열기
다른 경로에 존재하는 엑셀 파일을 열고자 할 경우에는 다음과 같이 Microsoft.Office.Interop.Excel.Workbooks.Open 메서드를 실행하면 됩니다. Application이 내부적으로 워크북을 열고 이를 객체 형태로 반환하게 됩니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Open(Filename: @"E:\test.xlsx");
/* ... 생략 ... */이 때 메서드로 전달되는 매개변수 중 자주 쓰이는 것은 다음과 같습니다.
- Filename:
- 열 파일의 경로입니다.
- ReadOnly:
- 읽기 전용 여부입니다. 읽기 전용이면
true, 그렇지 않으면false또는 매개변수를 생략합니다. - Password:
- 파일의 열기 암호입니다. 이 매개변수가 생략된 상태에서 암호가 걸린 파일을 열 경우 사용자에게 별도의 암호 입력창이 표시됩니다.
- WriteResPassword:
- 파일의 수정 암호입니다. 이 매개변수가 생략된 상태에서 암호가 걸린 파일을 열 경우 사용자에게 별도의 암호 입력창이 표시됩니다.
- Format:
- 파일의 형식입니다. 이 형식은
Microsoft.Office.Interop.Excel.XlFileFormat에서 열거하는 형식 중 하나이며,.xlsx파일은xlOpenXMLWorkbook이고.xlsm파일은xlOpenXMLWorkbookMacroEnabled입니다. - Origin:
- 위의
Format:매개변수가 텍스트 파일을 지정할 때 사용되는 매개변수입니다. 해당 텍스트 파일이 어떤 플랫폼에서 작성되었는지를 명시하는 매개변수로서 줄바꿈을 표현할 때 CR/LF 또는 LF 단독으로 쓰이는지 여부를 구분하기 위해 사용되는 매개변수입니다.Microsoft.Office.Interop.Excel.XlPlatform에서 열거하는 형식 중 하나입니다. - Delimiter:
- 위의
Format:매개변수가 텍스트 파일을 지정할 때 사용되는 매개변수입니다. 값과 값 사이를 구분해주는 문자를 전달합니다.
2-3단계. Workbook 저장하기
C# 프로그램에서 작업한 내용을 저장할 때는 Microsoft.Office.Interop.Excel.Workbook.Save 메서드 또는 Microsoft.Office.Interop.Excel.Workbook.SaveAs 메서드를 호출합니다. 이 때 사용되는 매개변수는 앞서 적은 매개변수 이름과 같습니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Open(Filename: @"E:\test.xlsx");
/* ... 생략 ... */
workbook.Save(); // 또는
workbook.SaveAs(Filename: @"E:\test2.xlsx");
위 코드의 수행 결과 다음과 같이 1개의 Worksheet를 포함하는 새 Workbook이 생성되었음을 확인할 수 있습니다.
3단계. Worksheet 생성/수정 및 제거하기
3-1단계. Worksheet 생성하기
2단계에서 작업 가능한 Workbook 객체를 얻었으므로 실제 데이터를 Cell에 넣거나 읽어오는 과정만 남았습니다. 1개의 Workbook은 하나 이상의 Worksheet으로 구성되어 있으므로 만일 데이터를 읽거나 쓸 워크시트를 생성하고자 할 때는 다음과 같이 Microsoft.Office.Interop.Excel.Worksheets.Add 메서드를 호출합니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Open(Filename: @"E:\test.xlsx");
Worksheet worksheet = workbook.Worksheets.Add();
/* ... 생략 ... */
Microsoft.Office.Interop.Excel.Worksheets.Add 메서드는 매개변수 없이 호출이 가능한 메서드이지만, 다음과 같은 매개변수를 지정할 수 있습니다.
- Before:
- 이미 존재하는 Worksheet 객체를 지정합니다. 이 객체 이전 순서로 새 Worksheet를 추가합니다.
- After:
- 이미 존재하는 Worksheet 객체를 지정합니다. 이 객체 다음 순서로 새 Worksheet를 추가합니다.
- Count:
- 새로 추가할 Worksheet의 개수를 지정합니다.
- Type:
- Worksheet의 유형을 지정합니다.
Microsoft.Office.Interop.Excel.XlSheetType열거자에서 정의된 유형 중 하나를 전달합니다.
위 메서드의 실행 결과 새 워크시트가 추가되면서 그 객체를 반환하게 됩니다.
3-2 단계. Worksheet 수정하기
Worksheet의 순서 변경은 다음과 같이 수행합니다. Microsoft.Office.Interop.Excel.Worksheet.Move 메서드를 사용하며, 앞서 설명한 Before: 또는 After: 매개변수로 워크시트의 새 위치를 지정합니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Open(Filename: @"E:\test.xlsx");
Worksheet worksheet1 = workbook.Worksheets.Item["Sheet1"]; // Sheet1에 대한 객체 가져오기
Worksheet worksheet2 = workbook.Worksheets.Item["Sheet2"]; // Sheet2에 대한 객체 가져오기
// Sheet1을 Sheet2의 앞쪽에 오도록 순서 변경
worksheet1.Move(After: workseet2);
/* ... 생략 ... */
Worksheet의 이름 바꾸기는 Microsoft.Office.Interop.Excel.Worksheet.Name 속성을 통해 가져오거나 설정할 수 있습니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Open(Filename: @"E:\test.xlsx");
Worksheet worksheet = workbook.Worksheets.Item[1]; // 1번째 Worksheet에 대한 객체 가져오기
worksheet.Name = "Sheet1"; // 0번째 Worksheet의 이름을 Sheet1로 변경
/* ... 생략 ... */
3-3 단계. Worksheet 삭제하기
Worksheet의 삭제는 Microsoft.Office.Interop.Excel.Worksheet.Delete 메서드를 사용하여 수행합니다. Count 속성을 통해 Worksheet의 개수를 구한다음 가장 마지막 Worksheet를 삭제해보겠습니다. 본 포스팅을 참고하면서 주의할 사항은 클래스의 이름이 단수형인지 복수형인지를 구분하는 것입니다. Workbooks와 Workbook은 서로 다른 클래스이며 Workbooks 클래스는 하나 이상의 Workbook 객체를 관리하는 컬렉션의 역할을 수행합니다. Worksheets와 Worksheet 클래스 또한 같습니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Open(Filename: @"E:\test.xlsx");
Worksheet worksheet = workbook.Worksheets.Item[workbook.Worksheets.Count - 1]; // 마지막 Worksheet에 대한 객체 가져오기
worksheet.Delete(); // 마지막 Worksheet를 삭제
/* ... 생략 ... */
4단계. Cell 데이터 가져오기 및 수정하기
4-1단계. 범위 또는 1개의 셀 가져오기
위의 단계를 거쳐 Worksheet 객체를 얻었다면 이 Worksheet에 들어있는 각 셀에 데이터를 기록하거나 불러올 수 있습니다. 하나 이상의 셀을 나타내는 자료형은 Microsoft.Office.Interop.Excel.Range이고 Microsoft.Office.Interop.Excel.Worksheet.Cells 속성을 통해 접근 가능합니다.
예를 들어, A1 셀을 선택하는 코드는 다음과 같습니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Open(Filename: @"E:\test.xlsx");
Worksheet worksheet = workbook.Worksheets.Item[workbook.Worksheets.Count - 1]; // 마지막 Worksheet에 대한 객체 가져오기
Range range = worksheet.Cells[1, 1];
/* ... 생략 ... */
만일 A1:C3와 같이 여러 셀을 선택하여 가져오고자 할 떄는 다음과 같이 Microsoft.Office.Interop.Excel.Worksheet.Range 속성을 사용합니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Open(Filename: @"E:\test.xlsx");
Worksheet worksheet = workbook.Worksheets.Item[workbook.Worksheets.Count - 1]; // 마지막 Worksheet에 대한 객체 가져오기
Range range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[3, 3]]; // top-left 및 bottom-right 셀로 범위 지정
/* ... 생략 ... */
또는 A1:C3, $A$1:$C$3과 같이 엑셀 표현식 그대로 사용하여 범위를 가져올 수 있습니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Open(Filename: @"E:\test.xlsx");
Worksheet worksheet = workbook.Worksheets.Item[workbook.Worksheets.Count - 1]; // 마지막 Worksheet에 대한 객체 가져오기
Range range = worksheet.Range["A1:C3"]; // 엑셀 범위 식을 직접 사용하여 범위 얻기
/* ... 생략 ... */
4-2 단계. 셀에 식 또는 값을 가져오거나 설정하기
셀에 값을 기록하거나 셀에 기록된 값을 가져오는 속성은 Microsoft.Office.Interop.Excel.Range.Value입니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Open(Filename: @"E:\test.xlsx");
Worksheet worksheet = workbook.Worksheets.Item[0];
Range range = worksheet.Cells[1, 1];
range.Value = 1; // A1 셀에 정수 '1'을 기록하기
int temp = range.Value; // A1 셀의 값 가져오기
/* ... 생략 ... */
셀에 수식을 기록하거나 셀에 기록된 수식을 가져오는 속성은 Microsoft.Office.Interop.Excel.Range.Formula입니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Open(Filename: @"E:\test.xlsx");
Worksheet worksheet = workbook.Worksheets.Item[0];
Range range = worksheet.Cells[1, 1];
range.Formula = "=SUM($A$1:$A$100)"; // B1 셀에 "=SUM($A$1:$A$100)" 수식 기록하기
string temp = range.Formula; // B1 셀에 기록된 수식 가져오기
/* ... 생략 ... */
5단계. 테스트하기
이로써 C#과 Microsoft Excel의 기본적인 연동에 대해 살펴보았습니다. 아래 예는 Sheet1!A1:A100에는 정수 3의 배수를 기록하고 Sheet2!A1에는 합, Sheet2!A2에는 평균을 구하는 엑셀파일을 생성하는 예입니다.
/* Program.cs */
using Microsoft.Office.Interop.Excel;
/* ... 생략 ... */
Application application = new Application();
Workbook workbook = application.Workbooks.Add(); // Sheet1이 포함된 빈 워크북 생성
Worksheet worksheet1 = workbook.Worksheets.Item["Sheet1"]; // Sheet1 객체 가져오기
Worksheet worksheet2 = workbook.Worksheets.Add(After: worksheet1); // Sheet1 뒤에 새 워크시트 추가하기
Range range = null;
for (int i = 1; i <= 100; i++)
{
worksheet1.Cells[i, 1] = i * 3; // Sheet1!A1:A100에 3배수 기록
}
range = worksheet2.Cells[1, 1]; // Sheet2!A1
range.Formula = "=SUM(Sheet1!$A$1:$A$100)"; // Sheet2!A1에 수식 기록
range = worksheet2.Cells[2, 1]; // Sheet2!A2
range.Formula = "=AVERAGE(Sheet1!A1:A100)"; // Sheet2!A2에 수식 기록
workbook.SaveAs(Filename: @"E:\test.xlsx"); // 파일 저장
workbook.Close(); // 워크북 닫기
/* ... 생략 ... */
실행 결과
