InfraDev 팀블로그

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

오늘은 .NET/C# 으로 UDF 를 만드는 여러 방법 중 Excel-DNA 라이브러리를 이용하는 방법을 알아보겠습니다.

엑셀 UDF 를 만드는 방법은 여러가지가 있지만, 그 중 Excel-DNA 라이브러리를 이용하는 방법이 아래 정리한 이유들 때문에 가장 마음에 듭니다. 

이렇게 만든 엑셀 UDF를 비주얼 스튜디오에서 디버깅 모드로 디버깅도 할 수 있습니다.

@Excel-DNA 를 이용한 엑셀 UDF 함수 디버깅 하기

개발하는 동안에는 이렇게 개발과 디버깅을 반복하면 됩니다.

개발이 완료 된 후 배포를 해야 하는데, Excel-DNA 라이브러리에서 제공하는 패키징 도구를 사용하면 깔끔하게 패키징 할 수 있습니다.

패키징을 하면 단일 .xll 파일이 하나 생기는데, 이 파일은 이름을 마음대로 변경해도 되며, 다른 .dll 파일은 필요가 없게 됩니다.

단일 .xll 파일 하나에 UDF 를 사용하기 위해 필요한 모든 라이브러리(.dll) 을 포함시킨다고 생각하시면 됩니다.

@배포용 통합 패키지 만들기

이상입니다. 감사합니다.

@서론:

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

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

그래서 엑셀은 사용자가 직접 함수를 만들어서 사용 할 수 있는 기능을 지원하며,
이렇게 사용자가 직접 만든 함수는 "사용자 정의 함수(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 을 활용하는 방식에 대해 정리 해 보겠습니다.

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

단계6: COM Interop 관련 설정.
이제 작성한 클래스를 COM Interop 에 노출시키기 위해 아래와 같이 프로젝트 속성을 설정 해 줍니다.

1. [프로젝트 속성 > 응용프로그램 > 어셈블리 정보 > 어셈블리를 COM에 노출] 옵션 체크

2. [프로젝트 속성 > 빌드 > COM Interop 등록] 옵션 체크

단계7: 빌드
클래스 라이브러리를 빌드하면 자동으로 COM Interop 에 빌드 된 어셈블리가 등록 됩니다.
COM Interop 에 등록 된 어셈블리를 엑셀에서 불러와 사용이 가능 합니다.

단계8: 엑셀에서 실제 사용 해 보기

1. [개발도구 > 추가기능 > 자동화] 버튼을 클릭하면 "자동화 서버" 창이 표시 됩니다.

2. 사용 가능한 자동화 서버 목록에서 방금 만든 라이브러리를 찾아서 선택 후 "확인" 버튼을 누릅니다.

3. 방금 찾은 라이브러리를 체크 해 주고 확인 버튼을 누릅니다.

4. 계산된 값이 입력 될 셀을 선택 하고 "함수 삽입" 버튼을 누릅니다.

5. "범주선택" 메뉴에서 방금 추가 한 라이브러리를 선택 해 줍니다.

6. 함수 목록 중 사용하고자 하는 함수를 선택 후 확인 버튼을 누릅니다.

7. 대상 값을 지정(마우스로 셀 클릭) 해 주고 확인 버튼을 누릅니다.

8. 직접 만든 UDF 로 원하는 값을 얻어냈습니다. ^^

@트러블 슈팅:

1. "올바른 추가 기능이 아닙니다" 오류
빌드까지 마친 후 엑셀에서 추가기능 메뉴를 통해 불러오려 할 때 다음의 메시지가 나오는 경우가 있습니다.

확인 결과 원인은 클래스 이름에 언더바('_') 가 들어가 있어서 인 것으로 확인 되었습니다. 언더바를 제거 해 주면 정상적으로 불러와 집니다.

이상입니다. 감사합니다.