XlsxWriter

Модераторы: Лабутин Сергей Евгеньевич, Алек(андр

XlsxWriter

Сообщение Rinat74 » 14 дек 2016 17:26

Причиной создания этой темы послужил заданный Алек(андру вопрос по поводу одной фразы из переписки с ним двухлетней давности. Вопрос тут же переадресовали мне, а я решил ответить развернуто. О вопросе и ответе на него чуть позже, для начала о чем, собственно, идет речь.

Вводная часть

Началось с того, что понадобился модуль для создания отчетов К3 в формате Excel на Python, чтобы сократить зоопарк используемых в работе языков и не пользоваться больше VBScript, на котором раньше приходилось писать отчеты. Предполагалось, что модуль будет уметь создавать документы в формате xlsx и иметь минимально возможное количество зависимостей. Одно дело, когда используется некое настроенное рабочее окружение у себя на машине, другое, когда потом приходится переносить это библиотечное хозяйство клиенту, тогда бывают серьезные проблемы. Манипуляции с пользовательскими компьютерами нужно сводить к минимуму, особенно, если установка удаленная, и тем более, если это будет делать сам пользователь.

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

Недостатки у него есть, но для наших целей они условны. Например, создаются только новые файлы, нельзя открыть ранее созданный файл и изменить, что при создании отчетов вряд ли недостаток. Еще нельзя из программы получить доступ к значению ячейки, как пример, my_var = worksheet.read('A1') не работает. Что, впрочем, не мешает, как и при штатной работе с Excel, неявно использовать значения ячеек в формулах, при валидации данных или условном форматировании. Думаю, не так много среднестатистических пользователей Excel явно обращаются к значениям ячеек и используют в работе функцию ДВВСЫЛ. Повторюсь, для формирования отчетов эти недостатки малосущественны. Но если WlsxWriter покажется недостаточно умелым, стоит обратиться к альтернативным вариантам, openpyxl, например.

Установка XlsxWriter на клиентские машины с К3 несложная. Извлекаем из установочного архива с гитхаба (зеленая кнопка "Clone or download", выбрать "Download ZIP") папку xlsxwriter, удаляем из нее папку test, не нужна для конечного пользователя, и размещаем в подходящем месте, например, в папке Proto. Далее нужно как-то "прописать" библиотеку. Например, можно прописать путь к библиотеке в PYTHONPATH, но это опять лишние и ненужные телодвижения на чужой машине, поэтому предпочитаю другой способ. Предположим, что XlsxWriter поместили в папку Proto\other_libs к другим сторонним библиотекам, чтобы не замусоривать Proto и упростить жизнь при переносе на другие машины. Чтобы получить доступ к XlsxWriter, добавляем в программу такие строки:

Код: Выделить всё
import sys
import k3

protopath = k3.GlobalVar('protopath').value
my_libs = protopath + 'other_libs'
sys.path.append(my_libs)

import xlsxwriter

# Что-то делаем

sys.path.remove(my_libs)


Получилось переносимо, без привязок к конкретной машине и без прописывания путей в переменных среды, которые могут легко пропасть, например, при переустановке операционной системы. И никто, никакой пользователь не вспомнит потом об этих путях, даже если в документации написано аршинными буквами красного цвета. У такого безустановочного способа один недостаток: не будет работать автодополнение, если пользоваться нормальной средой разработчика, а не условным Блокнотом. Но это способ для установки на клиентские машины, для использования конечным пользователем уже готовой программы формирования отчета. На машине разработчика такие ухищрения не нужны, там нормальный Python, помимо замурованного в К3, там настроенные виртуальные окружения, там вольготная жизнь с поняшками и никакого аскетизма.

Задача и попытки решения

Возвращаемся к упомянутому вначале вопросу, который звучал так: "Можно ли выяснить у Рината каким способом он после линка vbaProject.bin делает вызов своей функции(макроса)?" Вопрос, думаю, возник после прочтения этого поста Алек(андра. Тут опять нужны пояснения.

Появилась задача написать программу генерации отчетов с включением чертежей криволинейных деталей. Модуль Алек(андра пришлось допилить для пакетного создания чертежей, но это обычная работа, а вот с чертежами возникла проблема. Не было проблем создать, не было проблем вставить в листы Excel, в XlsxWriter доступны форматы png и jpeg, но стояла проблема с масштабированием. Чертеж может получиться небольшим, с частично неразборчивой информацией, а может наоборот слишком большим для стандартного листа А4. Настройками всего не исправить, ведь речь идет о штатной работе конструктора, когда "шлеп-шлеп и в производство". Логично было бы масштабировать в ту или иную сторону, но чертежи в растровых форматах хорошо выглядят при масштабе 100% и плохо при других масштабах. Можно было бы задать коэффициент масштабирования будущего чертежа перед созданием, но как заранее угадать какими получатся его размеры, каким должен быть этот коэффициент?

Возникла идея создавать чертежи в векторном формате, благо в К3 один такой доступен, wmf, эдакий анахронизм Microsoft. В ссылке говорится, что формат поддерживается "многими мощными приложениями", это не совсем так. В ответах Google на вопросы о wmf две основные темы — чем это открыть и как переконвертировать.

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

Переделка чертежного модуля под новый формат много времени не заняла, но теперь встала проблема с вставкой чертежа на лист отчета. Не потому, что XlsxWriter не понимает такой формат, понимает, хотя об этом и не заявлено, но при этом растрирует вставляемый файл wmf, снова вернулись к тому, от чего ушли. Далее пробовались другие библиотеки, некоторые даже с поддержкой wmf, которых объединяло одно общее свойство — растрирование wmf. Это к вопросу о поддержке формата, наверное, они оказались недостаточно "мощными".

В векторном виде чертежи вставлялись только в двух случаях: вручную при обычной работе в Excel, что никак не вариант, и с помощью pywin32, что неудивительно, кому бы еще корректно работать с этим форматом, как не самой Microsoft, поскольку pywin32, точнее его часть win32com, — модуль, предоставляющий доступ к компонентной объектной модели COM, технологии Microsoft, используемой и в VBScript. Можно было бы писать отчеты тем же привычным способом, только на Python.

Увы, этот вариант тоже не подошел. Проблема оказалось в установке и даже не одна проблема, там легион потенциальных проблем, поджидающих в засаде появления беспечных программистов. Установщик pywin32 доступен в двух видах: в виде exe файла, что могло бы подойти и для клиентов, если бы инсталлятор не требовал установленного в систему Python, наличие которого ищет по записям в реестре Windows, и в виде архива whl, для установки которого опять-таки нужен Python. Чтобы получить возможность корректно вставлять чертеж на лист, пришлось бы устанавливать Python и pywin32 на каждый конструкторский компьютер. Плохо соотносится с идеей минимального общения с клиентскими машинами. Отвратительно то, что даже при выполнении всех условий, отсутствие потом проблем не гарантируется, просто они будут другими. Информация достоверная, проверенная электроникой. (Пока писал, пришла в голову мысль — а получилось бы из консоли К3-Мебель сперва накатить wheel, а потом pywin32-*.whl? Сомнительно, но пробовать не буду, на шизофрению похоже.)

И трюк, как с ручной установкой XlsxWriter, с pywin32 не проходит. Совсем. Много зависимостей, много привязок. Можно было бы победить и эти проблемы, но так надоело бороться с тем, что ни клиенту не объяснить, который давно рыл копытом землю и мечтал создавать отчеты, ни самому понять.

Итоговое решение

По счастью к тому времени XlsxWriter обновился и научился работать с VBA макросами. VBA (Visual Basic для приложений) — очередной Basic Microsoft, призванный "расширять функциональность приложений". Использовать VBA макросы из XlsxWriter несложно, сложнее их писать в корявом редакторе, вызывающем страдания и боль, на чудаковатом языке со списком ограничений. Но задачу в конце концов удалось решить, на этом примере и покажу последовательность действий. Хотя, в принципе, все расписано в документации.

Открываем Excel с новой книгой, жмем Alt + F11, в открывшемся редакторе вставляем модуль (Insert – Module), в котором и создаются пользовательские функции. Урезанный код функции:

Код: Выделить всё
Function InsertDrawing(path, num_sheet)
    Set Shape = Worksheets(num_sheet).Shapes.AddPicture(path, msoFalse, msoTrue, 1, 1, -1, -1)
    With Worksheets(num_sheet)
        BaseShift = .Cells(6, 1).Top
        .Shapes(1).Top = BaseShift
        ' Клиентский код удален
     End With
    InsertDrawing = ""
End Function


Параметры функции: полный путь к чертежу и номер листа. Один лист — один чертеж, плюс шапка, поэтому добавленный чертеж смещаем по вертикали. Последняя строка (InsertDrawing = "") вызвана тем, что если этой строки не будет, то в ячейке, в которую будем вставлять вызов функции, будет стоять 0 и вызывать ненужные вопросы, возвращая пустую строку этого избегаем. Наверное, вместо функции можно и нужно было использовать процедуру Sub, но получилось так, как получилось.

Далее сохраняем рабочую книгу в формате xlsm, так как макросы в формате xlsx не сохраняются, а больше от нее ничего и не требуется. В папке examples установочного архива лежит утилита vba_extract.py, которая помогает их извлечь. Запускаем утилиту с именем сохраненной рабочей книги как параметр и получаем искомый файл vbaProject.bin с макросами. Может так случиться, что на время отладки делать это придется много раз.

Итоговый vbaProject.bin готов к употреблению. Сначала создается рабочая книга, опять-таки в формате xlsm, а к ней прицепляется файл vbaProject.bin. Пути в примере абстрактные.

Код: Выделить всё
report_name = 'some_report_path\\my_report.xlsm'
wb = xlsxwriter.Workbook(report_name)
#..............................................................
vba_project = 'some_custom_func_path\\vbaProject.bin'
wb.add_vba_project(vba_project)


Теперь пользовательская функция доступна из текста программы на Python.

Код: Выделить всё
# Цикл по списку созданных чертежей
ws = wb.add_worksheet('Эскиз ' + num_drawing)
current_drawing = drawings_path + num_drawing + '.wmf'
#..............................................................
params = "=InsertDrawing(" + chr(34) + current_drawing + chr(34) + num_sheet + ")"
ws.write_formula('B4', params)


В цикле создаются рабочие листы и в ячейку B4, выбрана неиспользуемая ячейка в шапке, каждого листа вставляется вызов пользовательской функции. На листах с чертежами эта ячейка будет выглядеть пустой, но если выделить ее, то в строке формул будет строка, подобная этой: =InsertDrawing("C:\PKMPROJECTS73\13\Reports\Some_Report\3.wmf";4)
Rinat74
 
Сообщения: 39
Зарегистрирован: 16 сен 2011 09:29

Re: XlsxWriter

Сообщение Алек(андр » 14 дек 2016 18:11

Вот это опус!
Ринат, спасибо за интересное чтиво в конце дня! Для себя замечу, что прочитаю еще пару раз ибо с первого захода не все усвоил.
Увидел сразу что мне хватило бы и
Код: Выделить всё
#..............................................................
params = "=InsertDrawing(" + chr(34) + current_drawing + chr(34) + num_sheet + ")"
ws.write_formula('B4', params)

Что то сам не заметил в документации метод write_formula

Я думаю не все поймут, а нахрена все эти танцы с питоном. Тут получилось так, что у питона есть то чего нет у VBS. Весь вкус этой разницы можно понять только на сложных проектах.
Там, где есть сложные связи и ветки зависимостей, где требуется наследование или передача функции в качестве параметра.

Кроме того питон имеет доступные нормальные редакторы и отладчики кода .

Обобщу что понял я .

XLSXWRITER позволяет помещать в новую созданную книгу пользовательские функции на нормальном VBA, а не урезанномVBScript. Макрос на VBA можно отладить в том же VBA редакторе в Excel, а с VBS все сложнее. Тут только K3Talk,(который у меня работает через пень-колоду, а дорабатывать его никто не собирается ибо все всем довольны и похоже один я его использую. И не будем показывать пальцем, кто не дает Тар...сову убрать глюки из этого замечательного инструмента. Было бы не плохо пришить к нему отладчик питона PDB и вдохнуть новую жизнь так сказать. Ну, да ладно!) . И далее при помощи метода write_formula мы можем вызвать эту функцию в новой книге, передав ей еще и параметры. Это даже чем то лучше, чем win32com ! Поскольку там я сразу не могу написать код замещающий VBA. Приходится подглядывать нужный функционал в редакторе VBA в Excel.


Еще раз спасибо Ринат!
p.s.
Может есть что то по сторонним библиотекам питон. Особенно по работе с базами данных! Многих этот вопрос интересует.
Аватара пользователя
Алек(андр
 
Сообщения: 1153
Зарегистрирован: 17 ноя 2008 10:16
Откуда: Московская область

Re: XlsxWriter

Сообщение Rinat74 » 14 дек 2016 18:39

Алек(андр писал(а):Что то сам не заметил в документации метод write_formula


Да есть он там, не заметил просто (1, 2).

Про танцы с питоном... Действительно, даже в голову не пришло, что нужно рассказывать о разнице между К3 Макро, VBS и Python. Да это и не объяснить сходу, кто писал на этих языках, тому и не надо объяснять, а кто не писал, тому незачем.

Про перспективность описанной работы с VBA не могу ничего сказать, понадобилось один раз и очень хотелось бы, чтобы больше не понадобилось никогда. Сравнительный анализ бейсиков от Microsoft не вызывает у меня даже намека на интерес, гори они все синим пламенем, все эти GW, Q, Quick, VB, VBS, VBA, кого я там еще забыл. Нет, я понимаю, что кому-то в Редмонде этот язык дорог как память, не будем говорить кому, хотя это старый Гейтс, давно влюбленный в свою юность. Но если есть возможность забыть про них, это нужно сделать. Я, правда, в свое время и на Python фыркал, признаю свою ошибку, был неправ, каюсь.

Про базы данных. Есть одна библиотечка, очень нравится, приятная во всех отношениях, своей неприхотливостью и лаконичностью похожа на XlsxWriter. На днях расскажу про нее.
Rinat74
 
Сообщения: 39
Зарегистрирован: 16 сен 2011 09:29


Вернуться в К3-Мебель. Программа для производства и продажи мебели.

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 1

cron