C# : Excel 프로그래밍
C#을 이용해 Excel 파일에 데이타를 읽거나 쓸 때, Excel Automation을 이용하거나 OLEDB를 이용할 수 있다.
(1) C#에서 엑셀 오토메이션을 이용하기 위해서는 Excel Interop 을 참조한 후, Office Automation COM API들을 사용하게 된다.
(2) C#에서 엑셀을 OLEDB로 이용할 경우에는 ADO.NET의 OleDb 클래스들을 사용하여 엑셀 데이타를 핸들링하게 된다.
(1) C#에서 엑셀 오토메이션을 이용하기 위해서는 Excel Interop 을 참조한 후, Office Automation COM API들을 사용하게 된다.
(2) C#에서 엑셀을 OLEDB로 이용할 경우에는 ADO.NET의 OleDb 클래스들을 사용하여 엑셀 데이타를 핸들링하게 된다.
C#에서 Excel Automation 사용
엑셀 오토메이션은 COM API들이고, C# 에서 이를 엑세스하기 위해서는 먼저 Excel COM Interop DLL을 참조해야 한다. (주: Excel COM Dll 참조는 버젼에 따라 다른데, (1) Add Reference - COM -Type Libraries 에서 Microsoft Excecl Object Library 를 선택하여 참조하거나 (2) Add Reference - Assembly - Extensions에서 Microsoft.Office.Interop.Excel.dll 을 참조한다.)
DLL이 참조된 후에 using Microsoft.Office.Interop.Excel; 와 같이 Excel 네임스페이스를 참조하여 사용하게 된다.
프로그램 코드에서는 기본적으로 Excel의 구조대로 먼저 Excel Application 객체를 얻고, Workbook 객체 그리고 Worksheet 객체를 차례로 얻은 후, 이 Worksheet의 Cell 혹은 Range를 지정하여 데이타를 핸들링한다.
마지막으로 사용 후에는 Excel 객체들을 Release해 주어야 백그라운드에 Excel.exe가 남지 않는다.
DLL이 참조된 후에 using Microsoft.Office.Interop.Excel; 와 같이 Excel 네임스페이스를 참조하여 사용하게 된다.
프로그램 코드에서는 기본적으로 Excel의 구조대로 먼저 Excel Application 객체를 얻고, Workbook 객체 그리고 Worksheet 객체를 차례로 얻은 후, 이 Worksheet의 Cell 혹은 Range를 지정하여 데이타를 핸들링한다.
마지막으로 사용 후에는 Excel 객체들을 Release해 주어야 백그라운드에 Excel.exe가 남지 않는다.
예제
using System;
using System.Collections.Generic;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
public class ExcelTest
{
public static void RunTest()
{
List<string> testData = new List<string>()
{ "Excel", "Access", "Word", "OneNote" };
Excel.Application excelApp = null;
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
try
{
// Excel 첫번째 워크시트 가져오기
excelApp = new Excel.Application();
wb = excelApp.Workbooks.Add();
ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;
// 데이타 넣기
int r = 1;
foreach (var d in testData)
{
ws.Cells[r, 1] = d;
r++;
}
// 엑셀파일 저장
wb.SaveAs(@"C:\temp\test.xls", Excel.XlFileFormat.xlWorkbookNormal);
wb.Close(true);
excelApp.Quit();
}
finally
{
// Clean up
ReleaseExcelObject(ws);
ReleaseExcelObject(wb);
ReleaseExcelObject(excelApp);
}
}
private static void ReleaseExcelObject(object obj)
{
try
{
if (obj != null)
{
Marshal.ReleaseComObject(obj);
obj = null;
}
}
catch (Exception ex)
{
obj = null;
throw ex;
}
finally
{
GC.Collect();
}
}
}
using System.Collections.Generic;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
public class ExcelTest
{
public static void RunTest()
{
List<string> testData = new List<string>()
{ "Excel", "Access", "Word", "OneNote" };
Excel.Application excelApp = null;
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
try
{
// Excel 첫번째 워크시트 가져오기
excelApp = new Excel.Application();
wb = excelApp.Workbooks.Add();
ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;
// 데이타 넣기
int r = 1;
foreach (var d in testData)
{
ws.Cells[r, 1] = d;
r++;
}
// 엑셀파일 저장
wb.SaveAs(@"C:\temp\test.xls", Excel.XlFileFormat.xlWorkbookNormal);
wb.Close(true);
excelApp.Quit();
}
finally
{
// Clean up
ReleaseExcelObject(ws);
ReleaseExcelObject(wb);
ReleaseExcelObject(excelApp);
}
}
private static void ReleaseExcelObject(object obj)
{
try
{
if (obj != null)
{
Marshal.ReleaseComObject(obj);
obj = null;
}
}
catch (Exception ex)
{
obj = null;
throw ex;
}
finally
{
GC.Collect();
}
}
}
C#에서 Excel OLEDB 사용 : 데이타 읽기, 갱신, 추가 예제
엑셀파일을 OLEDB 를 사용해서 사용하는 경우, Microsoft.Jet.OLEDB.4.0 혹은 Microsoft.ACE.OLEDB.12.0 Data Provider를 사용할 수 있다. 엑셀 97 - 2003 버젼의 .XLS 파일을 사용하는 경우 Microsoft.Jet.OLEDB.4.0 혹은 Microsoft.ACE.OLEDB.12.0을 사용할 수 있으며, 엑셀 2007 이후의 .XLSX 파일에 대해서는 Microsoft.ACE.OLEDB.12.0 을 사용한다.
자신의 컴퓨터에 어떤 Data Provider 가 설치되어 있는지 체크하기 위해서는 아래 그림과 같이 Powershell 명령을 사용한다.
PS> (New-Object system.data.oledb.oledbenumerator).GetElements() | select SOURCES_NAME, SOURCES_DESCRIPTION
만약 Microsoft.ACE.OLEDB.12.0 Provider가 없다면 Microsoft Access Database Engine 2010 Redistributable 을 다운받아 설치한다.
OLDDB Provider가 정상적으로 설치되었으면, ADO.NET의 OleDbConnection을 사용하여 엑셀파일을 엑세스한다. 즉, Excel 파일을 OleDbConnection을 사용해 연결을 한 후에, 다른 데이타 소스들과 같이 ADO.NET의 OleDb* 클래스들을 이용해 데이타를 가져오거나 삽입, 삭제, 갱신등을 하게 된다. 아래 예제는 OLEDB를 이용하여 엑셀 파일로부터 데이타를 읽고, 수정 및 추가를 하는 예이다.
한가지 주의할 점은 엑셀시트명 뒤에 $을 붙이고 시트명을 괄호 [ ] 로 묶는다는 점이다. 즉, 시트명이 Sheet1 인 경우 [Sheet1$] 으로 표시한다.
자신의 컴퓨터에 어떤 Data Provider 가 설치되어 있는지 체크하기 위해서는 아래 그림과 같이 Powershell 명령을 사용한다.
PS> (New-Object system.data.oledb.oledbenumerator).GetElements() | select SOURCES_NAME, SOURCES_DESCRIPTION
만약 Microsoft.ACE.OLEDB.12.0 Provider가 없다면 Microsoft Access Database Engine 2010 Redistributable 을 다운받아 설치한다.
OLDDB Provider가 정상적으로 설치되었으면, ADO.NET의 OleDbConnection을 사용하여 엑셀파일을 엑세스한다. 즉, Excel 파일을 OleDbConnection을 사용해 연결을 한 후에, 다른 데이타 소스들과 같이 ADO.NET의 OleDb* 클래스들을 이용해 데이타를 가져오거나 삽입, 삭제, 갱신등을 하게 된다. 아래 예제는 OLEDB를 이용하여 엑셀 파일로부터 데이타를 읽고, 수정 및 추가를 하는 예이다.
한가지 주의할 점은 엑셀시트명 뒤에 $을 붙이고 시트명을 괄호 [ ] 로 묶는다는 점이다. 즉, 시트명이 Sheet1 인 경우 [Sheet1$] 으로 표시한다.
예제
private void ExcelTest()
{
// OLEDB를 이용한 엑셀 연결
// Excel 97-2003 .xls
// string szConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\x\test.xls;Extended Properties='Excel 8.0;HDR=No'";
// Excel 2007 이후 .xlsx
string szConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\x\test.xlsx;Extended Properties='Excel 8.0;HDR=No'";
OleDbConnection conn = new OleDbConnection(szConn);
conn.Open();
// 엑셀로부터 데이타 읽기
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
adpt.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
string data = string.Format("F1:{0}, F2:{1}, F3:{2}", dr[0], dr[1], dr[2]);
MessageBox.Show(data);
}
// 엑셀 데이타 갱신
cmd = new OleDbCommand("UPDATE [Sheet1$] SET F2='Hello' WHERE F1='a'", conn);
cmd.ExecuteNonQuery();
cmd = new OleDbCommand("UPDATE [Sheet1$A2:C2] SET F2='World'", conn);
cmd.ExecuteNonQuery();
// 데이타 추가
cmd = new OleDbCommand("INSERT INTO [Sheet1$](F1,F2,F3) VALUES ('A3','B3','C3')", conn);
cmd.ExecuteNonQuery();
conn.Close();
}
{
// OLEDB를 이용한 엑셀 연결
// Excel 97-2003 .xls
// string szConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\x\test.xls;Extended Properties='Excel 8.0;HDR=No'";
// Excel 2007 이후 .xlsx
string szConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\x\test.xlsx;Extended Properties='Excel 8.0;HDR=No'";
OleDbConnection conn = new OleDbConnection(szConn);
conn.Open();
// 엑셀로부터 데이타 읽기
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
adpt.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
string data = string.Format("F1:{0}, F2:{1}, F3:{2}", dr[0], dr[1], dr[2]);
MessageBox.Show(data);
}
// 엑셀 데이타 갱신
cmd = new OleDbCommand("UPDATE [Sheet1$] SET F2='Hello' WHERE F1='a'", conn);
cmd.ExecuteNonQuery();
cmd = new OleDbCommand("UPDATE [Sheet1$A2:C2] SET F2='World'", conn);
cmd.ExecuteNonQuery();
// 데이타 추가
cmd = new OleDbCommand("INSERT INTO [Sheet1$](F1,F2,F3) VALUES ('A3','B3','C3')", conn);
cmd.ExecuteNonQuery();
conn.Close();
}
Excel Automation : 대량의 데이타 처리
Excel Automation 을 사용하여 대량의 데이타를 처리하기 위해서는 배열 데이타를 Excel 의 Range에 쓰거나 읽으면서 처리하는 것이 성능면에서 효율적이다. 즉, 루프를 돌며 Cell 에 데이타를 하나씩 넣게 되면, 여러번 COM 메서드를 호출하게 되어 성능이 저하되기 때문에, Range 를 한번 호출하여 대량의 배열 데이타를 한번에 처리하는 것이 효율적이다.
Excel 에서 현재 시트에 있는 데이타의 전체 범위를 선택하기 위해서는 Worksheet 객체로부터 UsedRange 속성을 호출하면 된다. (주: 엑셀에서 한 Cell 에 데이타를 쓰고 바로 지웠다 하더라도 이는 UsedRange 범위에 포함된다.)
전체 Range가 아니라 특정 영역을 지정하기 위해서는 Worksheet객체.Range[시작, 끝] 인덱서를 사용하는데, 사각형 영역의 좌측 상단 Cell을 시작위치로 지정하고, 우측하단 Cell 을 끝위치에 지정한다.
Cell의 위치는 Worksheet객체.Cells(행Index, 열Index) 메서드를 사용하여 구할 수 있는데, 여기서 한가지 주의할 것은 Excel의 행,열 인덱스는 1부터 시작한다는 점이다. 따라서 2번째 행의 첫번째 컬럼은 ws.Cells(2,1) 과 같이 표현된다.
엑셀 Range를 구한 후, 이로부터 배열 데이타를 얻는 방법은 해당 Range의 .Value 속성을 호출하여 2차원 배열에 담으면 된다. 즉, 아래 예제와 같이 object[,] data = rng.Value 는 Range 객체(rng)의 Value 속성을 호출하여 그 값들을 메모리상의 2차원 배열에 할당하는 일을 한다. 데이타가 일단 메모리상의 배열에 할당된 후에는 Excel을 닫아도 상관없다. 여기서 한가지 주의할 점은 데이타를 넘겨 받은 배열 (변수 data)은 배열 인덱스가 0 부터 시작하지 않고, (Excel 처럼) 1부터 시작한다는 점이다.
참고로, 배열의 행(row) 사이즈는 data.GetLength(0) 과 같이 GetLength(배열차원) 메서드를 사용할 수 있으며, 마찬가지로 컬럼 사이즈는 data.GetLength(1)을 통해 구할 수 있다.
Excel 에서 현재 시트에 있는 데이타의 전체 범위를 선택하기 위해서는 Worksheet 객체로부터 UsedRange 속성을 호출하면 된다. (주: 엑셀에서 한 Cell 에 데이타를 쓰고 바로 지웠다 하더라도 이는 UsedRange 범위에 포함된다.)
전체 Range가 아니라 특정 영역을 지정하기 위해서는 Worksheet객체.Range[시작, 끝] 인덱서를 사용하는데, 사각형 영역의 좌측 상단 Cell을 시작위치로 지정하고, 우측하단 Cell 을 끝위치에 지정한다.
Cell의 위치는 Worksheet객체.Cells(행Index, 열Index) 메서드를 사용하여 구할 수 있는데, 여기서 한가지 주의할 것은 Excel의 행,열 인덱스는 1부터 시작한다는 점이다. 따라서 2번째 행의 첫번째 컬럼은 ws.Cells(2,1) 과 같이 표현된다.
엑셀 Range를 구한 후, 이로부터 배열 데이타를 얻는 방법은 해당 Range의 .Value 속성을 호출하여 2차원 배열에 담으면 된다. 즉, 아래 예제와 같이 object[,] data = rng.Value 는 Range 객체(rng)의 Value 속성을 호출하여 그 값들을 메모리상의 2차원 배열에 할당하는 일을 한다. 데이타가 일단 메모리상의 배열에 할당된 후에는 Excel을 닫아도 상관없다. 여기서 한가지 주의할 점은 데이타를 넘겨 받은 배열 (변수 data)은 배열 인덱스가 0 부터 시작하지 않고, (Excel 처럼) 1부터 시작한다는 점이다.
참고로, 배열의 행(row) 사이즈는 data.GetLength(0) 과 같이 GetLength(배열차원) 메서드를 사용할 수 있으며, 마찬가지로 컬럼 사이즈는 data.GetLength(1)을 통해 구할 수 있다.
예제
public void ReadExcelData()
{
Excel.Application excelApp = null;
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
try
{
excelApp = new Excel.Application();
// 엑셀 파일 열기
wb = excelApp.Workbooks.Open(@"C:\Temp\test.xlsx");
// 첫번째 Worksheet
ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;
// 현재 Worksheet에서 사용된 Range 전체를 선택
Excel.Range rng = ws.UsedRange;
// 현재 Worksheet에서 일부 범위만 선택
// Excel.Range rng = ws.Range[ws.Cells[2, 1], ws.Cells[5, 3]];
// Range 데이타를 배열 (One-based array)로
object[,] data = rng.Value;
for (int r = 1; r <= data.GetLength(0); r++)
{
for (int c = 1; c <= data.GetLength(1); c++)
{
Debug.Write(data[r, c].ToString() + " ");
}
Debug.WriteLine("");
}
wb.Close(true);
excelApp.Quit();
}
finally
{
// Clean up
ReleaseExcelObject(ws);
ReleaseExcelObject(wb);
ReleaseExcelObject(excelApp);
}
}
{
Excel.Application excelApp = null;
Excel.Workbook wb = null;
Excel.Worksheet ws = null;
try
{
excelApp = new Excel.Application();
// 엑셀 파일 열기
wb = excelApp.Workbooks.Open(@"C:\Temp\test.xlsx");
// 첫번째 Worksheet
ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;
// 현재 Worksheet에서 사용된 Range 전체를 선택
Excel.Range rng = ws.UsedRange;
// 현재 Worksheet에서 일부 범위만 선택
// Excel.Range rng = ws.Range[ws.Cells[2, 1], ws.Cells[5, 3]];
// Range 데이타를 배열 (One-based array)로
object[,] data = rng.Value;
for (int r = 1; r <= data.GetLength(0); r++)
{
for (int c = 1; c <= data.GetLength(1); c++)
{
Debug.Write(data[r, c].ToString() + " ");
}
Debug.WriteLine("");
}
wb.Close(true);
excelApp.Quit();
}
finally
{
// Clean up
ReleaseExcelObject(ws);
ReleaseExcelObject(wb);
ReleaseExcelObject(excelApp);
}
}