воскресенье, 5 апреля 2015 г.

Работа с файлами 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 применить не получится. Для узкой и специфичной задачи возможно применение этого компонента имеет смысл, ну а так... 

2 комментария:

  1. Моя хотеть сравнения с XtraSpreadsheet от DX =)
    Судя по коду:

    // Получаем ячейку
    var range = wvExcel.ActiveWorksheet.Cells[(int)row, (int)col];
    // Получаем блок в который входит эта ячейка (если входит)
    var block = range.CurrentRegion; // var block = range.MergeArea; - можно и вот так

    работа с группированными ячейками чуть проще в SPG или структура аналогичная ?

    ОтветитьУдалить
    Ответы
    1. Проще, там сразу получая Range, если он именновынный, можно получить имя. Ну и работает он побыстрее. В целом, менять часы на трусы. Если будет свободное время, то можно и перейти. Но и XtraSpreadsheet мы в WPF нормально встроили.

      Удалить