[C#] Excel 프로그래밍

C# : Excel 프로그래밍 

C#을 이용해 Excel 파일에 데이타를 읽거나 쓸 때, Excel Automation을 이용하거나 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가 남지 않는다. 

예제

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();
        }
    }
}

---

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$] 으로 표시한다. 

예제

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();
}

 

---

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)을 통해 구할 수 있다. 

예제

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);
    }
}

출처 : http://www.csharpstudy.com/Practical/prac-excel.aspx