Работа с файлами Excel пользователем через интерфейс, на основе SpreadsheetGear

Возникла задача дать пользователю возможность ввести данные в приложении, но на основе шаблона загруженного из Excel файла. Или, иными словами, грузиться и показывается пользователю Excel файл. Файл заранее подготовлен и данные можно вводить только в определенные места (отличающиеся от файла к файлу). Стоит задача показать такой файл, выполнить с ним некоторые операции, ну и считать из него данные по завершению ввода.
данная статья сборник небольших примеров решения такой задачи с использование компонента SpreadsheetGear.

1. Установка и подключение в проект
Здесь все просто. Качаем бесплатную версию здесь. Запускаем инсталятор, далее, далее, далее, конечно же, не забыв поставить галку, что вы согласны с лицензией. Запускаем VS, создаем проект WPF и в Панели инструментов видим:

Все, можно перетаскивать в разметку. Для любителей все делать самому, можно добавить в проект ссылки на две библиотеки (все картинки кликабельны):

Ну и в XAML, подключаем пространство имен и размещаем компонент WorkbookView в нужном нам месте:

<Window x:Class="SpreadsheetGearTestProject.MainWindow"

        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

        xmlns:sg="clr-namespace:SpreadsheetGear.Windows.Controls;assembly=SpreadsheetGear2012.Windows.WPF"

        Title="MainWindow" Height="350" Width="525">

    <Grid>

        <sg:WorkbookView />

    </Grid>

</Window>

Можно запускать.
Во всех остальных примерах, я буду использовать приложение с вот такой разметнкой главной формы:

Ну и все примеры, это обработчики соответствующих кнопок.
2. Загрузка файла XLS в компонент

private void Load_Click(object sender, RoutedEventArgs e)

{

    // Настраиваем культуру, в рамках которой будет читаться файл,

    // можем, например, использовать не установленную в системе, а задать требуемую нам

    SpreadsheetGear.IWorkbookSet workbookSet =

            SpreadsheetGear.Factory.GetWorkbookSet(System.Globalization.CultureInfo.CurrentCulture);

    // Загружаем книгу

    SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Open("DOC1152016_505.xls");

    // Передаем книгу в компонент

    wvExcel.ActiveWorkbook = workbook;

}

Вот так это выглядит для обычной книги:

А вот так, для книги в которой листы защищены (нет сетки и заголовков строк и столбцов):

Т.к. мне представляет интерес работа именно с защищенными листами, то все остальные картинки будут именно с ними.
3. Скрываем листы книги, которые нам не нужны
Перед началом работы с элементами книги, необходимо заблокировать ее, а по окончании работы освободить.  В остальных примерах я на этом уже не буду останавливаться, но везде будут соответствующие блоки.

wvExcel.GetLock();

try

{

    wvExcel.ActiveWorkbook.ActiveSheet.Visible = SpreadsheetGear.SheetVisibility.Hidden;

}

finally

{

    wvExcel.ReleaseLock();

}

После выполнения этого кода, будет скрыт активный лист и активным станет соседний лист:

При попытке скрыть последний лист, возникает исключение.
4. Работа с именованными блоками
Если в книге есть именованные блоки, то мы можем как внести в них данные, так и считать их. Например, я буду вносить по кнопке почтовый индекс. Блок в котором он находится имеет имя "П000020020001".

private void AddPostCode_Click(object sender, RoutedEventArgs e)

{

    wvExcel.GetLock();

    try

    {

        var name = wvExcel.ActiveWorkbook.Names.Cast<IName>().FirstOrDefault(n => n.Name == "П000020020001");

        if (name != null)

        {

            var range = name.RefersToRange;

            range.Value = "248021";

            // Т.к. ячейка может быть не на текущем активном листе,

            // то для удобства проверки, перейдем на него

            wvExcel.ActiveSheet = range.Worksheet;

        }

    }

    finally

    {

        wvExcel.ReleaseLock();

    }

}

Вот так работает:

Со значениями в компонентах для эмуляции Excel, отдельная песня. Value у диапазона или ячейки имеет тип object. У SpreadsheetGear, чтобы определить тип хранящегося значения есть специальное свойство ValueType. Вот так, например, можно проверить какого типа значение храниться в ячейке:

private void CheckType_Click(object sender, RoutedEventArgs e)

{

    wvExcel.GetLock();

    try

    {

        var name = wvExcel.ActiveWorkbook.Names.Cast<IName>().FirstOrDefault(n => n.Name == "П000020020001");

        if (name != null)

        {

            var range = name.RefersToRange;

            if (range.ValueType == SpreadsheetGear.ValueType.Number)

            {

                MessageBox.Show(string.Format("В ячейке числовой тип значение {0}", (double)range.Value));

            }

            else if (range.ValueType == SpreadsheetGear.ValueType.Logical)

            {

                MessageBox.Show(string.Format("В ячейке логический тип значение {0}", (bool)range.Value));

            }

            else if (range.ValueType == SpreadsheetGear.ValueType.Empty)

            {

                MessageBox.Show("Ячейка пустая");

            }

            else

            {

                MessageBox.Show(string.Format("В ячейке строковый тип значение {0}", range.Value));

            }

        }

    }

    finally

    {

        wvExcel.ReleaseLock();

    }

}

Если загрузить данные, добавить индекс и попробовать получить тип, то мы увидим:

Если поправить пример с добавлением вот так:

range.Value = 248021;

То при той же последовательности действий результат будет вот такой:

Т.е. в зависимости от типа присвоенного значения, определяется тип ячейки.
Ну и самый интересный эксперимент. Присвоим ячейке дату:

range.Value = DateTime.Now.Date;

Результат будет следующий:

Обращают на себя внимание два факта. Во-первых, дата показывается правильно, а вот тип ячейки определяется как числовой. Печально...
5. Определение ячейки и блока редактируемых пользователем
Речь идет о редактировании ячейки, т.е. пока ячейка не перешла в состояние редактирования события не вызываются. Ну и для решения этой задачи есть два варианта.
В рамках первого способа, мы можем обрабатывать клики мышкой, для этого подписываемся в компоненте на соответствующее событие:

<sg:WorkbookView Grid.Row="1" x:Name="wvExcel" MouseLeftButtonUp="wvExcel_MouseLeftButtonUp" />

Ну и пишем соответствующий обработчик:

private void wvExcel_MouseLeftButtonUp(object sender, MouseButtonEventArgs e)

{

    wvExcel.GetLock();

    try

    {

        // Перевоим координаты клика мышкой

        // в строку и столбец активного листа

        System.Windows.Point point = e.GetPosition(wvExcel);

        double x = point.X;

        double y = point.Y;

        double row, col;

        wvExcel.LocationToRange(x, y, out row, out col,

            SpreadsheetGear.Windows.Controls.RangeLocationFlags.Headers);

        // Проверяем что кликнули именно в пределах листа и есть столбец и строка

        if (row >= 0.0 && col >= 0.0)

        {

            // Получаем ячейку

            var range = wvExcel.ActiveWorksheet.Cells[(int)row, (int)col];

            // Получаем блок в который входит эта ячейка (если входит)

            var block = range.CurrentRegion; // var block = range.MergeArea; - можно и вот так

            MessageBox.Show(string.Format(

                "Редактируем ячейку ({0}) значение в ней '{1}'.{6}Ячейка входит в блок ({2}) объединяющий строк: {3} и столбцов {4}.{6}Значение в блоке '{5}'.",

                range.Address,

                range.Value,

                block.Address,

                block.RowCount,

                block.ColumnCount,

                ((block.Value as object[,]) ?? new object[,] { { null } })[0, 0],

                Environment.NewLine

                ));

        }

    }

    finally

    {

        wvExcel.ReleaseLock();

    }

}

Загрузив наш лист и кликнув по ячейке с индексом, мы получим:

На что следует обратить внимание:
а) Хотя мы и подписывались на MouseUp событие вызовется только при первом переходе ячейки в редактирование. Выделение ячейки событие не вызывает, также как и повторные клики на редактируемой ячейке.
б) Получить блок в который входит ячейка можно двумя способами. Разницы в них не заметил.
в). Ни один из способов получения ячейки не позволяет понять, что этот Range именованный:

Второй способ, на мой взгляд более правильный, это обрабатывать события связанные с ячейкой. В данном случае, нас интересует CellBeginEdit. В данном случае, обработчик будет значительно проще:

private void wvExcel_CellBeginEdit(object sender, SpreadsheetGear.Windows.Controls.CellBeginEditEventArgs e)

{

    wvExcel.GetLock();

    try

    {

        // Получаем ячейку

        var range = wvExcel.ActiveCell;

        // Получаем блок в который входит эта ячейка (если входит)

        var block = range.CurrentRegion; // var block = range.MergeArea; - можно и вот так

        MessageBox.Show(string.Format(

            "Редактируем ячейку ({0}) значение в ней '{1}'.{6}Ячейка входит в блок ({2}) объединяющий строк: {3} и столбцов {4}.{6}Значение в блоке '{5}'.",

            range.Address,

            range.Value,

            block.Address,

            block.RowCount,

            block.ColumnCount,

            ((block.Value as object[,]) ?? new object[,] { { null } })[0, 0],

            Environment.NewLine

            ));

    }

    finally

    {

        wvExcel.ReleaseLock();

    }

}

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

6. Добавление строк и копирование части листа
В оригинальном листе есть блок:

Я хочу его скопировать и вставить под оригинальный М1. Сделать это можно достаточно просто:

private void CloneM1_Click(object sender, RoutedEventArgs e)

{

    wvExcel.GetLock();

    try

    {

        var name = wvExcel.ActiveWorkbook.Names.Cast<IName>().FirstOrDefault(n => n.Name == "М1");

        if (name != null)

        {

            var range = name.RefersToRange;

            // Снимаем защиту с листа

            range.Worksheet.Unprotect("DOC1152016_505.xls");

            //// Добавляем строки для копирования в них нашего М1 (формат с какой и по какую строки)

            string address = string.Format("{0}:{1}", range.Row + range.RowCount + 1, range.Row + 2 * range.RowCount);

            range.Worksheet.Cells[address].Insert();

            // Устанавливаем высоту строк аналогично высоте строк в M1

            for (int i = 0; i < range.RowCount; i++)

            {

                range.Worksheet.Cells[range.Row + range.RowCount + i, 0].RowHeight =

                    range.Worksheet.Cells[range.Row + i, 0].RowHeight;

            }

            // Копируем в подготовленные блок М1

            range.Copy(range.Worksheet.Cells[range.Row + range.RowCount, range.Column]);

            // Востанавливаем защиту

            range.Worksheet.Protect("DOC1152016_505.xls");

            // Т.к. ячейка может быть не на текущем активном листе,

            // то для удобства проверки, перейдем на него

            wvExcel.ActiveSheet = range.Worksheet;

        }

    }

    finally

    {

        wvExcel.ReleaseLock();

    }

}

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

7. Присвоение имен блокам
Последний на сегодня пример, связанный, кстати, с предыдущим. Блоки подсвеченные синим у меня имен не имеют, я хочу подсвеченным ячейкам внутри М1 эти имена дать, чтобы было удобнее присваивать в них значения.
Вот так выглядит код присвоения имен:

private void NameM1Child_Click(object sender, RoutedEventArgs e)

{

    wvExcel.GetLock();

    try

    {

        var name = wvExcel.ActiveWorkbook.Names.Cast<IName>().FirstOrDefault(n => n.Name == "М1");

        if (name != null)

        {

            var range = name.RefersToRange;

            var worksheet = range.Worksheet;

            // Ищем все дочерние объединения с учетом цвета фона

            var background = SpreadsheetGear.Color.FromArgb(255, 51, 204, 204);

            List<IRange> childRanges = new List<IRange>();

            for (int column = 0; column < range.ColumnCount; column++)

            {

                for (int row = 0; row < range.RowCount; row++)

                {

                    var cell = worksheet.Cells[range.Row + row, range.Column + column];

                    if (cell.Interior.Color == background)

                    {

                        var block = cell.MergeArea;

                        if (!childRanges.Any(r => r.Row == block.Row && r.Column == block.Column))

                        {

                            childRanges.Add(block);

                        }

                    }

                }

            }

            // Даем им имена

            for (int i = 0; i < childRanges.Count; i++)

            {

                var newName = worksheet.Names.Add(string.Format("П0_{0}", i), string.Format("='{0}'!{1}", childRanges[i].Worksheet.Name, childRanges[i].Address), ReferenceStyle.A1);

            }

        }

    }

    finally

    {

        wvExcel.ReleaseLock();

    }

}

Несмотря на то, что мы присваивали имена вида П0_0 и П0_1:

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

private void SetM1Child_Click(object sender, RoutedEventArgs e)

{

    wvExcel.GetLock();

    try

    {

        var name = wvExcel.ActiveWorkbook.Names.Cast<IName>().FirstOrDefault(n => n.Name == "'Раздел 1'!П0_0");

        if (name != null)

        {

            var range = name.RefersToRange;

            // Снимаем защиту с листа

            range.Worksheet.Unprotect("DOC1152016_505.xls");

            range.Value = 100000;

            // Востанавливаем защиту

            range.Worksheet.Protect("DOC1152016_505.xls");

        }

    }

    finally

    {

        wvExcel.ReleaseLock();

    }

}

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

var newName = wvExcel.ActiveWorkbook.Names.Add(string.Format("П0_{0}", i), string.Format("='{0}'!{1}", childRanges[i].Worksheet.Name, childRanges[i].Address), ReferenceStyle.A1);

Тогда поиск ячейки будет иметь вид:

var name = wvExcel.ActiveWorkbook.Names.Cast<IName>().FirstOrDefault(n => n.Name == "П0_0");

И в первом и во втором случае, если начала присвоить имена, а потом по имени присвоить значение, то результат будет одинаковый:

На этом все, мое мнение, все это весьма неоднозначно и связано с неоправданными сложностями. Всякие WPF вкусности типа Binding применить не получится. Для узкой и специфичной задачи возможно применение этого компонента имеет смысл, ну а так...