C# 으로 엑셀 사용자 정의 함수(UDF) 만들기(COM Interop)

.NET Framework/C#2012. 8. 3. 17:15

C# 으로 엑셀 사용자 정의 함수(UDF) 만들기(COM Interop)

@서론:

안녕하세요. 엉스데브 입니다.

엑셀을 사용하다 보면 가끔 엑셀의 기본 함수가 다소 아쉽게 느껴지는 경우가 있습니다.
이러이러한 함수가 있으면 참 좋을텐데.. 싶을 때가 간혹 있지요.

그래서 엑셀은 사용자가 직접 함수를 만들어서 사용 할 수 있는 기능을 지원하며,
이렇게 사용자가 직접 만든 함수는 "사용자 정의 함수(UDF, User-Defined Function)" 라고 불립니다.

사용자 정의 함수는 여러가지 다양한 방법으로 정의 할 수 있는데,
그 중 제가 가장 익숙한 .NET/C# 으로 함수를 직접 만들어 사용하는 방법을 찾아 봤습니다.

.NET/C# 으로 UDF 를 만드는 방법은 크게 3가지 정도가 있는데,

1. COM Interop 을 이용하는 방법,
2. Sharepoint Server 제품에 포함되어 제공되는 Microsoft.office.Excel.Server.Udf.dll 을 이용하는 방법,
3. Excel-DNA 라이브러리를 이용하는 방법이 있습니다.

이 중 오늘은 COM Interop 을 활용하는 방식에 대해 정리 해 보겠습니다.

@COM Interop 이란?
http://ko.wikipedia.org/wiki/COM_Interop

@COM Interop 을 이용해 엑셀 UDF 만들기 관련 링크:
http://msdn.microsoft.com/ko-kr/library/ms173189(v=vs.80).aspx
http://msdn.microsoft.com/en-us/library/ms499792.aspx

@C#.NET 으로 엑셀 UDF 만들기 (COM Interop):

* OS 환경:
Windows 7 Enterprise K (Service Pack 1) 64bit

* 사용도구:
Visual Studio 2010 Premium
Microsoft Excel 2010

단계1: 클래스 라이브러리 형식의 프로젝트 생성
비주얼 스튜디오를 켜고, 클래스 라이브러리 형식의 새 프로젝트를 만듭니다.

단계2: 엑셀 UDF 용 클래스 생성
엑셀 UDF 용으로 클래스를 만들어 줍니다.
클래스 이름을 적당히 지어주고, 엑셀에서 함수로 사용 될 메소드를 만들어 줍니다.
이번에 필요한 기능은 지정된 도메인의 연결 IP 를 가져오는 기능이기에, 다음과 같이 소스 코드를 작성 했습니다.

using System;
using System.Linq;
namespace InfradevExcelUDF
{
    public class DNSUtil
    {
        public string GetIPAddress(string domain)
        {
            if (string.IsNullOrEmpty(domain) == true)
            {
                return string.Empty;
            }

            try
            {
                if (System.Net.Dns.GetHostAddresses(domain).Length > 0)
                {
                    return System.Net.Dns.GetHostAddresses(domain).First().ToString();
                }
                else
                {
                    return string.Empty;
                }
            }
            catch (Exception)
            {
                return "#ERROR: 예상하지 못한 오류 입니다.";
            }
        }

    }
}

단계3: System.Runtime.InteropService 네임스페이스 추가(using)

using System;
using System.Linq;
using System.Runtime.InteropServices;

namespace InfradevExcelUDF
{
    public class DNSUtil
    {
        public string GetIPAddress(string domain)
        {
            if (string.IsNullOrEmpty(domain) == true)
            {
                return string.Empty;
            }

            try
            {
                if (System.Net.Dns.GetHostAddresses(domain).Length > 0)
                {
                    return System.Net.Dns.GetHostAddresses(domain).First().ToString();
                }
                else
                {
                    return string.Empty;
                }
            }
            catch (Exception)
            {
                return "#ERROR: 예상하지 못한 오류 입니다.";
            }
        }

    }
}

단계4: 클래스에 ClassInterface 속성 설정
클래스가 COM 에 노출 될 때 사용 될 인터페이스 속성을 설정 해 줍니다.

using System;
using System.Linq;
using System.Runtime.InteropServices;
namespace InfradevExcelUDF
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class DNSUtil
    {
        public string GetIPAddress(string domain)
        {
            if (string.IsNullOrEmpty(domain) == true)
            {
                return string.Empty;
            }

            try
            {
                if (System.Net.Dns.GetHostAddresses(domain).Length > 0)
                {
                    return System.Net.Dns.GetHostAddresses(domain).First().ToString();
                }
                else
                {
                    return string.Empty;
                }
            }
            catch (Exception)
            {
                return "#ERROR: 예상하지 못한 오류 입니다.";
            }
        }

    }
}

단계5: COM 자동 등록/삭제 용 메소드 추가
COM 에 자동 등록/삭제 될 때 사용될 메소드를 추가 해 줍니다.

using System;
using System.Linq;
using System.Runtime.InteropServices;
using Microsoft.Win32;

namespace InfradevExcelUDF
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class DNSUtil
    {
        public string GetIPAddress(string domain)
        {
            if (string.IsNullOrEmpty(domain) == true)
            {
                return string.Empty;
            }

            try
            {
                if (System.Net.Dns.GetHostAddresses(domain).Length > 0)
                {
                    return System.Net.Dns.GetHostAddresses(domain).First().ToString();
                }
                else
                {
                    return string.Empty;
                }
            }
            catch (Exception)
            {
                return "#ERROR: 예상하지 못한 오류 입니다.";
            }
        }

        [ComRegisterFunctionAttribute]
        public static void RegisterFunction(System.Type type)
        {
            Registry.ClassesRoot.CreateSubKey(
                GetSubKeyName(type, "Programmable")
            );

            RegistryKey key = Registry.ClassesRoot.OpenSubKey(
              GetSubKeyName(type, "InprocServer32"),
              true
            );

            key.SetValue(
                "",
                System.Environment.SystemDirectory + @"\mscoree.dll",
                RegistryValueKind.String
            );
        }

        [ComUnregisterFunctionAttribute]
        public static void UnregisterFunction(System.Type type)
        {
            Registry.ClassesRoot.DeleteSubKey(
                GetSubKeyName(type, "Programmable"),
                false
            );
        }

        private static string GetSubKeyName(System.Type type, string subKeyName)
        {
            System.Text.StringBuilder s = new System.Text.StringBuilder();

            s.Append(@"CLSID\{");
            s.Append(type.GUID.ToString().ToUpper());
            s.Append(@"}\");
            s.Append(subKeyName);

            return s.ToString();
        }
    }
}