1. Вступление
Привет всем!
Еще в начале 80-х годов прошлого века компания Microsoft выпустила на рынок свою первую электронную таблицу. И, нет, это был еще даже не Excel, а Multiplan, предназначенный для операционной системы CP/M. И почти сразу же эта электронная таблица стала очень популярной. Прошло уже достаточно много времени, но и по сей день нет более универсальной и доступной системы для создания отчетов, чем электронные таблицы. Наверное, просто не существует такого программного продукта, работающего с набором данных под Windows, который не имел бы возможности импорта своих данных в Microsoft Excel.
Да, Excel замечательная программа, но набор её функций и их возможностей иногда очень разочаровывает. И порой даже несложная задача, например, такая как разбивка ФИО (фамилия, имя, отчество) по разным ячейкам, заставляет создавать некую монстро-подобную функцию-химеру, где одна функция со всеми своими параметрами оказывается в другой функции, сама при этом являясь параметром, всё это очень обильно вперемешку заполнено различными координатами ячеек, диапазонами координат и порою даже ссылаясь на другие страницы или даже книги... И когда таких формул на странице десятки, а то и сотни, и все они друг на дружку ссылаются, то это порой способно вызвать долговременную головную боль даже у самого опытного системного администратора.
Но какой же при этом выход? А выход довольно простой. Ну, относительно конечно. Если Вы неплохо знаете Basic, то, как говорится, встроенный редактор макросов Вам в помощь. Но лично я с Basic-ом как-то дружу не очень и поэтому мне нужен был какой-то принципиально другой подход.
К счастью существует бесплатная библиотека для создания расширения для Microsoft Excel под названием Excel-DNA. Эту библиотеку можно установить через пакетный менеджер NuGet в интегрированную среду разработки программного обеспечения Visual Studio, и которая позволяет создавать расширения в виде кнопки и различных контролов на ленте пользовательского интерфейса Excel, вроде поля ввода, выпадающего списка, чекбокса и различных других. А так же позволяет просто добавить свои дополнительные функции, которые используются в строке формул. Для системных администраторов, которым часто приходиться работать с Excel, анализировать и корректировать множество различных данных, эта библиотека будет просто незаменимой находкой. Возможности, которые она предоставляет, почти безграничны. Правда, если они ещё дополнительно обладают и навыками программистов, в противном случае, конечно, придётся обращаться к программистам за помощью. (В конце этой статьи я оставлю ссылку на одно готовое полезное расширения)
Но, для того, чтобы этими возможностями воспользоваться, для начала придётся изучить объектную модель Excel и его API, хотя я не сказал бы, чтобы оно было прямо такое уж сложное. Для того чтобы создать более менее полезное расширение, достаточно запомнить с десяток различных конструкций, вроде таких как получить доступ к приложению Excel, взять в нём рабочую страницу, получить диапазон ячеек, в этом диапазоне выбрать нужную ячейку, получить значение этой ячейки, ну и тому подобное. Большинство действий наверняка будут довольно однотипными. Очень неплохую помощь по этим вопросам может оказать встроенный редактор макросов. Хотя он и использует Visual Basic, но API то все равно один и тот же, поэтому можно записать какие-либо действия в макрос, потом открыть его во встроенной среде разработки, и посмотреть какой код был сгенерирован, и какие там присутствуют функции.
2. Создание надстройки на ленту пользовательского интерфейса
Ну а теперь для примера я хочу представить одно из небольших расширений, которое мне как-то понадобилось сделать.
Специалистам часто присылали Excel-евские файлы со списками кадастровых номеров, и эти списки иногда были довольно большие, не одну сотню строк. А специалистам требовалось эти кадастровые номера преобразовывать в одну строку через точку с запятой, для дальнейшего получения информации по этим номерам, при этом учитывая, что в строке должно быть не больше ста кадастровых номеров. Да ещё и надо было обязательно проверять корректность этих кадастровых номеров, иначе запрос не проходил, и впустую тратилось время. Вручную соединять эти номера, это конечно очень сложно и долго, и возникало довольно много ошибок. Потом всё-таки была сделана формула, вернее это даже была целая цепочка формул, которая, в некой подобии рекурсии, сцепляла значения ячеек через точку с запятой. Но это все равно было очень неудобно, и возникало масса ошибок. Поэтому, в конечном счёте, пришлось сделать следующее расширение для Excel на ленту пользовательского интерфейса:
using ExcelDna.Integration;
using ExcelDna.Integration.CustomUI;
using Excel = Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Runtime.InteropServices;
namespace ExcelExtensionJoinCadNum
{
/// <summary>
/// Дополнение объединение кадастровых номеров в одну строку через точку с запятой
/// </summary>
[ComVisible(true)]
public class RibbonController : ExcelRibbon
{
IRibbonUI ribbon;
string textValue;
bool isValidate;
/// <summary>
/// Свойство значения длины списка
/// </summary>
int ListLength {
get {
textValue = textValue.Trim();
// Если значение длины списка пустое,
if (textValue == "")
// то вернём длину ноль.
return 0;
else {
try {
// Иначе попытаемся преобразовать значение длины списка в целое число.
return int.Parse(textValue);
} catch (FormatException) {
// Если это не удастся, то вернем исключение с текстом ошибки.
throw new RibbonControllerException($"#НЕДОПУСТИМОЕ_ЗНАЧЕНИЕ_ДЛИНЫ_СПИСКА ({textValue})");
}
}
}
}
/// <summary>
/// Добавляет новую вкладку с контролами на ленте пользовательского интерфейса
/// </summary>
public override string GetCustomUI(string RibbonID) {
return @"
<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui' loadImage='LoadImage' onLoad='OnRibbonLoad'>
<ribbon>
<tabs>
<tab id='tab1' label='Дополнения'>
<group id='group1' label='Список кадастровых номеров'>
<button id='button1' image='cad_num_icon' label='Объединить список' onAction='OnCadNumButtonPressed'/>
<editBox id='listLength' label='Длинна списка' onChange='OnListLengthChange' getText='OnListLengthGetText' />
<checkBox id='сhbValidCadNum' label='Проверять корректность' onAction ='OnValidCadNumAction' getPressed='OnValidCadNumGetPressed' />
</group >
</tab>
</tabs>
</ribbon>
</customUI>";
}
/// <summary>
/// Событие при загрузке ленты
/// </summary>
/// <param name="ribbonUi"></param>
public void OnRibbonLoad(IRibbonUI ribbonUi) {
ribbon = ribbonUi;
textValue = "";
isValidate = false;
}
/// <summary>
/// Событие возврата текста из поля длины списка
/// </summary>
/// <param name="control"></param>
/// <returns></returns>
public string OnListLengthGetText(IRibbonControl control) {
return textValue;
}
/// <summary>
/// Событие изменения текста в поле длины списка
/// </summary>
/// <param name="control"></param>
/// <param name="text"></param>
public void OnListLengthChange(IRibbonControl control, string text) {
textValue = text;
ribbon.InvalidateControl(control.Id);
}
/// <summary>
/// Событие возврата требуется ли валидация кадастровых номеров
/// </summary>
/// <param name="control"></param>
/// <returns></returns>
public bool OnValidCadNUmGetPressed(IRibbonControl control) {
return isValidate;
}
/// <summary>
/// Событие изменения чекбокса валидации кадастровых номеров
/// </summary>
/// <param name="control"></param>
/// <param name="isChecked"></param>
public void OnValidCadNumAction(IRibbonControl control, bool isChecked) {
isValidate = isChecked;
ribbon.InvalidateControl(control.Id);
}
/// <summary>
/// Событие нажатия кнопки
/// </summary>
/// <param name="control"></param>
public void OnCadNumButtonPressed(IRibbonControl control) {
Excel.Application xlApp = (Excel.Application)ExcelDnaUtil.Application;
Excel.Worksheet ws = xlApp.ActiveSheet;
// Получим диапазон актичныз ячеек.
Excel.Range activeCell = xlApp.Selection;
// Отобразим диалоговое окно выбора диапазона ячеек.
Excel.Range cadListRange = xlApp.Application.InputBox("Выберите ячейки с кадастровыми номерами", "Выбор диапазона",
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 8);
try {
// Получим список списков кадастровых номеров,
List<string> listCads = GetListCads(cadListRange, ListLength, isValidate);
// и пройдясь по этому списку,
for (int i = 0; i < listCads.Count; i++) {
// каждый отобразим с переходом на следующую ячейку вниз.
activeCell[i + 1, 1].Value = listCads[i];
}
} catch (RibbonControllerException ex) {
System.Windows.Forms.MessageBox.Show($"Ошибка: {ex.Message}.");
return;
}
}
/// <summary>
/// Возвращает список списков кадастровых номеров через точку с запятой
/// </summary>
/// <param name="range"></param>
/// <param name="listLength"></param>
/// <returns></returns>
List<string> GetListCads(Excel.Range range, int listLength, bool isValidate) {
string listCad = "";
List<string> listCads = new List<string>();
Regex regexCadNum = new Regex(@"\d{2}:\d{2}:\d{6,7}:\d+");
// Пробежимся по списку кадастровых номеров.
for (int i = 0, j = 0; i < range.Count; i++) {
// Если эта ячейка пустая,
if (range[i + 1].Value == null) {
// то перейдём к следущей ячейке.
continue;
}
// Иначе возьмём очередной кадастровый номер.
string cadNum = range[i + 1].Value.ToString().Trim();
// Если указано, что должна быть валидация, и очередной номер валидацию не прошёл,
if (isValidate && !regexCadNum.IsMatch(cadNum)) {
// то вернём исключение с ошибкой.
throw new RibbonControllerException($"#НЕВЕРНЫЙ_КАДСТРОВЫЙ_НОМЕР ({cadNum})");
}
// Иначе добавим этот кад.ном. в текстовый список и добавим точку с запятой.
listCad += range[i + 1].Value + ";";
// Проверим, если указана длинна текстового списка (не ноль) и длинна этого списка стала больше указанной,
if (listLength > 0 && j > listLength - 2) {
// то добавим текстовый список в список списоков кадастровых номеров.
listCads.Add(listCad.Substring(0, listCad.Length - 1));
// Очистим текстовый список,
listCad = "";
// и сбросим счётчик текстового списка кад.номеров.
j = -1;
}
// Увеличим счётчик текстового списка кад.номеров.
j++;
}
// В конце проверим, если есть непустой тектовый список кадастровых номеров,
if (listCad.Length > 0)
// то нужно добавить его в список списков.
listCads.Add(listCad.Substring(0, listCad.Length - 1));
return listCads;
}
/// <summary>
/// ExcelDna предоставляет возможность добавить задание на выполнение в основном потоке,
/// для этого предназначен метод ExcelAsyncUtil.QueueAsMacro, пример использования:
/// </summary>
private void test() {
ExcelAsyncUtil.QueueAsMacro(() => {
Excel.Application xlApp = (Excel.Application)ExcelDnaUtil.Application;
xlApp.StatusBar = "Sending request...";
});
}
}
/// <summary>
/// Класс исключения для текущего класса
/// </summary>
class RibbonControllerException : Exception
{
public RibbonControllerException(string message) : base(message) {
}
}
}Ну а теперь немного о том, как это делается.
Для начала в Visual Studio надо добавить новый проект в виде новой библиотеки, после чего, через пакетный менеджер NuGet, добавить к нему библиотеки ExcelDna.AddIn и ExcelDna.Integration. Далее, для того чтобы работать с объектной моделью Excel, надо подключить ссылку на COM-библиотеку Microsoft Excel 14.0 Object Library, и не забыть добавить доступ к функциям этой библиотеки в своём программном коде, у меня это выглядит вот так:
using Excel = Microsoft.Office.Interop.Excel;Для того чтобы на ленте пользовательского интерфейса появилась новая вкладка, надо создать класс унаследованный от класса ExcelRibbon, и указать ему атрибут ComVisible(true). Сам вид вкладки формируется в виде xml и возвращается через переопределённый метод string GetCustomUI(string RibbonID):
/// <summary>
/// Добавляет новую вкладку с контролами на ленте пользовательского интерфейса
/// </summary>
public override string GetCustomUI(string RibbonID) {
return @"
<customUI xmlns='http://schemas.microsoft.com/office/2006/01/customui' loadImage='LoadImage' onLoad='OnRibbonLoad'>
<ribbon>
<tabs>
<tab id='tab1' label='Дополнения'>
<group id='group1' label='Список кадастровых номеров'>
<button id='button1' image='cad_num_icon' label='Объединить список' onAction='OnCadNumButtonPressed'/>
<editBox id='listLength' label='Длинна списка' onChange='OnListLengthChange' getText='OnListLengthGetText' />
<checkBox id='сhbValidCadNum' label='Проверять корректность' onAction ='OnValidCadNumAction' getPressed='OnValidCadNumGetPressed' />
</group >
</tab>
</tabs>
</ribbon>
</customUI>";
}Выглядит это вкладка на ленте вот так:
В формировании XML разметки Вам может помочь какой-нибудь ribbon xml editor.
Видно, что на вкладке присутствует поле ввода и чекбокс. Но есть одна проблема, похоже, что напрямую обратиться к этим элементам не получиться, прямого доступа к ленте Ribbon нет, вернее он есть, но очень ограниченный. Зато есть вот такая конструкция, которая позволяет получить значение поля:
/// <summary>
/// Событие при загрузке ленты
/// </summary>
/// <param name="ribbonUi"></param>
public void OnRibbonLoad(IRibbonUI ribbonUi) {
ribbon = ribbonUi;
textValue = "";
}
/// <summary>
/// Событие возврата текста из поля длины списка
/// </summary>
/// <param name="control"></param>
/// <returns></returns>
public string OnListLengthGetText(IRibbonControl control) {
return textValue;
}
/// <summary>
/// Событие изменения текста в поле длины списка
/// </summary>
/// <param name="control"></param>
/// <param name="text"></param>
public void OnListLengthChange(IRibbonControl control, string text) {
textValue = text;
ribbon.InvalidateControl(control.Id);
}OnRibbonLoad(IRibbonUI ribbonUi) это конструктор класса, в котором определяется переменная ссылка на объект Ribbon, а так же в нём определяется начальное значение переменной textValue, которая будет отображать значение текстового поля. Методы события string OnListLengthGetText(IRibbonControl control) и OnListLengthChange(IRibbonControl control, string text) устанавливают и возвращают содержимое текстового поля, и обратите внимание, что в xml разметке именно на эти методы ссылается текстовое поле.
Аналогично этой конструкции создана и конструкция для получению и изменению значения чекбокса.
В принципе, остальной код более менее понятен и отмечен комментариями, единственно хотелось бы заострить Ваше внимание на функции InputBox, которая открывает диалоговое окно для выбора диапазона ячеек. В качестве последнего параметра указана константа 8, что означает, что диалоговое окно должно возвратить именно значение в виде типа Range - диапазон ячеек.
// Отобразим диалоговое окно выбора диапазона ячеек.
Excel.Range cadListRange = xlApp.Application.InputBox("Выберите ячейки с кадастровыми номерами", "Выбор диапазона",
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 8);В разметке к кнопке добавлено изображение image='cad_num_icon'. Чтобы это изображение появилось, файл изображения надо добавить как ресурс, установить ему параметр "Действие при сборке" как "Внедрённый ресурс" и параметр "Копировать в выходной каталог" как "Всегда копировать". Далее надо прописать его в конфигурационном файле ProjectName-AddIn.dna в разделе Image:
<?xml version="1.0" encoding="utf-8"?>
<DnaLibrary Name="ExcelExtensionJoinCadNum Add-In" RuntimeVersion="v4.0" xmlns="http://schemas.excel-dna.net/addin/2018/05/dnalibrary">
<ExternalLibrary Path="ExcelExtensionJoinCadNum.dll" ExplicitExports="false" LoadFromBytes="true" Pack="true" IncludePdb="false" />
<Image Name="cad_num_icon" Path="Resources\cad_num_icon.png" Pack="true" />
</DnaLibrary>3. Установка надстройки
После того как класс расширения готов, проект надо скомпилировать, и в выходном каталоге, среди рабочих файлов, появятся четыре XLL файла: ProjectName-AddIn, ProjectName-AddIn64, ProjectName-AddIn-packed и ProjectName-AddIn64-packed. Именно эти файлы и являются файлами расширения Excel. Понятно, что 64, в наименовании файла, означает 64-рёх битный Excel, а вот packed означает, что в этот файл упакованы какие-либо внешние файлы, например файл изображение cad_num_icon.png. Лучше, наверное, использовать именно эти файлы. И использовать их надо следующим образом. В параметрах Excel надо выбрать вкладку Надстройки, и в низу окна параметров Excel, при выбранном значении поля "Надстройки Excel", нажать кнопку "Перейти...".
Появится небольшое окно со списком всех возможных надстроек в Excel.
Далее надо выбрать любую из надстроек, и нажать кнопку "Обзор...", при этом совсем не обязательно эту надстройку подключать.
Откроется диалоговое окно, в котором можно увидеть путь, где должны располагаться надстройки. Этот путь нужно запомнить, а лучше скопировать его в буфер.
Далее надо закрыть все диалоговые окна Excel, да и сам Excel тоже следует закрыть.
Теперь надо открыть любую папку и по запомненному пути выйти в директорий, в котором хранятся расширения Excel, хотя, когда Вы этот директорий откроете, то он, скорее всего, будет ещё пустым. В этот директорий и надо скопировать созданное расширение.
После того как расширение окажется в папке, надо снова открыть "Параметры Excel" и во вкладке "Надстройки" кнопкой "Перейти..." открыть окно со списком надстроек. На этот раз в списке надстроек уже можно будет увидеть добавленное расширение, которое надо активировать, поставив напротив него галочку. Ну, вот и всё, расширение должно заработать, и на ленте пользовательского интерфейса должна появиться новая вкладка "Дополнения", на которой и будут сгруппированы кнопка, текстовое поле и чекбокс нового расширения.
Для создания надстроек для приложений пакета Microsoft Office в качестве альтернативы библиотеки Excel-DNA существует так же более универсальное решение - VSTO Add-ins.
4. Создание дополнительной функции в Excel
Но, кроме расширений в виде новой кнопки на панели интерфейса, в Excel можно просто добавить свою функцию. Например, в Excel есть стандартная функция "СЦЕПИТЬ", которая позволяет объединять в единую строку указанный текст или значения указанных ячеек. Но, одно дело сцепить две три ячейки, допустим через пробел, а другое дело, десять ячеек. Формула получится весьма длинная и не очень информативная. Поэтому мне всегда хотелось, чтобы в Excel была функция, позволяющая легко объединять содержимое любого количества ячеек через указанный разделитель. И теперь, с помощью библиотеки ExcelDna, это стало возможным. Вот это код, позволяющий добавить такую функцию:
using ExcelDna.Integration;
using System;
public class FunctionJoinText
{
/// <summary>
/// Объединение текста через знак разделитель
/// </summary>
/// <param name="range"></param>
/// <param name="separator"></param>
/// <returns></returns>
[ExcelFunction(Description = "Объединение текста через знак разделитель", Name = "СцепТекстПоРазд")]
public static string JoinText([ExcelArgument(Description = "Диапазон ячеек для объединения", Name = "Текст")] object[] range,
[ExcelArgument(Description = "Текст разделитель списка", Name = "разделитель")] string separator) {
string result = "";
// Пробежимся по переданным объектам,
for (int i=0; i< range.Length; i++) {
// и если этот объект не пустой,
if (range[i] != ExcelEmpty.Value)
// то добавим его в общую строку с переданным разделителем.
result += $"{range[i]}{separator}";
}
// Если строка была сформированна, то венем ее без послднего разделитея. А если нет, то и вернем пустую строку.
return result.Length > separator.Length ? result.Substring(0, result.Length - separator.Length) : "";
}
}И так, что мы тут видим. Для создания новой функции в Excel надо опять же создать новый проект в виде новой библиотеки и добавить к нему библиотеку ExcelDna. Но теперь уже достаточно добавить обычный новый класс, в нашем случае это FunctionJoinText, в который уже и надо добавлять методы, которые станут функциями в Excel. Но, функциями в Excel станут только те методы, которым добавлен атрибут ExcelFunction, и как минимум у этого атрибута должно быть указано свойство Name, которое означает наименование функции, как оно будет отображаться в строке формул Excel.
[ExcelFunction(Description = "Объединение текста через знак разделитель", Name = "СцепТекстПоРазд")]После имени функции нужно указать список аргументов, также предваряя их атрибутами, в которых нужно указать свойство Name, чтобы определить, как этот аргумент должен быть отображен в Excel. То значение, которое будет передано в параметр функции Excel, будет передано переменной аргумента функции C#.
[ExcelArgument(Description = "Текст разделитель списка", Name = "разделитель")] string separatorНу и, разумеется, функция должна возвращать какой-то результат. Хотя может это и не обязательно, если вы собираетесь возвращать значения через объектную модель Excel, то есть сразу передавать результат в какие-нибудь ячейки.
Таких функций в один класс можно добавить сколько угодно, и подключается потом такое расширение, так же как и предыдущее.
5. Использование дополнительных функций Excel
Чтобы увидеть новые функции надо зайти в категории функций, например через fx, и выбрать добавленное расширение,
после чего появятся функции этого расширения.
6. Надстройка дополнительных функций использования регулярных выражений
Ну а в конце я хотел бы добавить ссылку на загрузку одного своего расширение, добавляющего в Excel возможность проверки, поиска и замены по регулярному выражению:
GitHubНаверное, нет такого программиста, который бы не использовал для своей работы один из мощнейших инструментов для анализа и обработки текста - регулярные выражения. Теперь им можно воспользоваться и в Excel.
Используя всего три эти дополнительные функции (ПроверкаПоРегВыр, ПоискПоРегВыр, ДелТекстПоРегВыр), Ваши возможности по обработке данных в Excel возрастут в несколько раз. Единственное, для этого, конечно же, надо знать язык шаблона регулярных выражений, поэтому, если Вы его до сих пор ещё не знаете, то обязательно изучите, тем более, что не такой уж он и сложный. Вот ссылка на краткий справочник по регулярным выражениям: