Как легко создать Excel UDF с проектом надстройки VSTO

То, что я пытаюсь сделать, - создать пользовательские функции (UDF) для Excel, используя VSTO С# "Excel 2007 Add-in" - тип проекта (так как я просто хочу сгенерировать некоторые общие UDF). Поскольку я только пытаюсь изучить основы (на этом этапе так или иначе), это то, как выглядит мой код:

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using Microsoft.Office.Tools.Excel.Extensions;
using System.Runtime.InteropServices;

namespace ExcelAddIn1
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {}

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {}

        //My UDF
        public static double HeronicCal(int a, int b, int c)
        {
            //first compute S = (a+b+c)/2
            double S = (a + b + c) / 2;    
            double area = Math.Sqrt(S * (S - a) * (S - b) * (S - c));
            return area;
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }            
        #endregion
    }
}

Он отлично компилируется, и когда я его запускаю, Excel появляется со свежей электронной таблицей, и когда я смотрю на "Add-Ins" -list (в вариантах Excel), я могу видеть свою надпись в списке ( который установлен на "Загрузка при запуске". Но вот моя проблема, когда я пытаюсь вызвать свой UDF из встроенного Excel, Excel не может найти метод!

То, что я представляю, ошибочно, заключается в том, что я должен пометить свой метод как Excel UDF (используя квадратные скобки - как это делается, например, при кодировании веб-сервисов → "[WebService]" ). Но я не смог отследить этот тег (и, поскольку я не уверен, если моя догадка верна), вот почему я решил пойти к вам, прекрасные люди здесь, в SO.

Итак, мой вопрос в основном - от того, где я с моим кодом, есть ли простой способ сделать мой UDF доступным для Excel? Если да, то как?

Я действительно хотел бы остаться в рамках проектов проекта VSTO (надстройка, рабочая книга, шаблон), так как моя общая цель для моего текущего проекта - установить, работает ли выполнение С# UDF с VS2010/Excel2007 на приемлемом скорость. Чтобы проверить это, я работаю над Windows7RC и с VS2010 beta1.

Ответы

Ответ 1

VSTO не поддерживает создание Excel UDF. Надстройки надстройки могут быть созданы в .Net и, похоже, одобрены Microsoft.

Вы должны взглянуть на ExcelDna - http://www.codeplex.com/exceldna. ExcelDna позволяет управляемым сборкам предоставлять пользовательские функции (UDF) и макросы в Excel через собственный .xll-интерфейс. Проект является открытым исходным кодом и свободно разрешает коммерческое использование. И вы обнаружите, что производительность вашего UDF на базе .NET аналогична встроенным надстройкам .xll для Excel. Функции Excel 2007, такие как большой лист, поддерживаются длинные строки Unicode и многопоточный пересчет.

С ExcelDna ваша функция, опубликованная выше, будет открыта для Excel без VSTO - вы можете поместить код в xml-основанный .dna файл или скомпилировать его в .dll.

Файл .dna, отображающий ваш UDF, будет выглядеть так:

<DnaLibrary Language="C#">
   using System;
   using ExcelDna.Integration;

   public class MyFunctions
   {
      [ExcelFunction(Description="Calculate Stuff", Category="Cool Functions")]
      public static double HeronicCal(int a, int b, int c)
      {
         //first compute S = (a+b+c)/2
         double S = (a + b + c) / 2;
         double area = Math.Sqrt(S * (S - a) * (S - b) * (S - c));
         return area;        
      }
   }
</DnaLibrary>

Обновление:. В наши дни самым простым способом начать работу с Excel-DNA является создание нового проекта библиотеки классов в Visual Studio, а затем добавление пакета "ExcelDna.AddIn" из NuGet. Это делает надстройку стартера - просто вставьте свой код и нажмите F5 для запуска.

Ответ 2

Насколько я знаю, вы не можете напрямую создавать UDF в VSTO.

См. статью Пола Стаббса Как создать Excel UDF в управляемом коде VSTO, где он использует надстройку VBA для публикации VBA UDF, которая в свою очередь, вызывать его управляемые UDF, написанные в VSTO.

Вы можете использовать управляемый код для создания UDF, однако, когда не используете VSTO. См. Статью Эрика Картера Написание пользовательских функций для Excel в .NET о том, как это сделать.

Что касается скорости выполнения VSTO, я думаю, вы найдете все в порядке для практически всех задач. Однако, зацикливание через ячейки, которое уже является слабым местом Excel, может быть болезненно медленным, в зависимости от того, что вы делаете. Постарайтесь выполнять все в пакетном режиме, насколько это возможно. Например, вместо того, чтобы перебирать ячейки по одному, возвращаем двухмерный массив значений из области, обрабатываем массив и затем передаем его обратно в диапазон.

Чтобы продемонстрировать, следующее возвращает двухмерный массив значений из области, обрабатывает значения, а затем передает результирующий массив обратно в исходную область за один снимок:

Excel.Range rng = myWorksheet.get_Range("A1:D4", Type.Missing);

//Get a 2D Array of values from the range in one shot:
object[,] myArray = (object[,])rng.get_Value(Type.Missing);

// Process 'myArray' however you want here.
// Note that the Array returned from Excel is base 1, not base 0.
// To be safe, use GetLowerBound() and GetUpperBound:
for (int row = myArray.GetLowerBound(0); row <= myArray.GetUpperBound(0); row++)
{
    for (int column = myArray.GetLowerBound(1); column <= myArray.GetUpperBound(1); column++)
    {
        if (myArray[row, column] is double)
        {
            myArray[row, column] = (double)myArray[row, column] * 2;
        }
    }
}

// Pass back the results in one shot:
rng.set_Value(Type.Missing, myArray);

Надеюсь, это поможет!

Mike

Ответ 4

Что я нашел, что хорошо работает, так это держать UDF в качестве модуля VB, чтобы избежать проблем с объектами COM.

У меня есть тонны кода С#, и когда я готов к выпуску, я делаю следующее:
1. Добавить модуль:
    Developer [вкладка в Excel] | Visual Basic → окно проекта, щелкните правой кнопкой мыши, вставьте модуль
    - просто скопируйте/вставьте код VB здесь
2. Включите соответствующую справочную библиотеку (Инструменты в том же окне VB)
3. Сохраните файл Excel как .xlsm(то есть Macro-Enabled)

Затем вы можете удалить файл .xlsx.

Что я делаю, это zip весь каталог (например, "Release" ) и отправить его нашим пользователям.