Данная заметка, возможно будет интересна тем, кто в своих проектах использует Oracle Apex. Принцип описанный здесь, можно применить под любые другие задачи, используя не только python, но и любой другой язык.
Oracle Apex — это конструктор веб приложений со своей атмосферой. Для простых приложений его функционала более чем достаточно, но когда приложение активно развивается, то возникают определенные трудности.
Для других популярных языков и фреймворков, есть гигантское количество библиотек выполняющие любые задачи. В случае с Oracle Apex, готовых и современных решений очень мало.
Мы, в своем проекте столкнулись с проблемой генерации сложных экселевских документов. Под сложными, я имею ввиду документы, в которых используется мощный функционал самого экселя: формулы, макросы, защищенные документы, условное визуальное оформление. и т.д.
Долгое время, нам, вполне хватало самописного пакета, который покрывал все наши кейсы, но в один прекрасный момент, функционала стало не хватать, и что хуже всего стали вылезать ошибки. Из-за этого, итоговый файл, также содержал ошибки, и пользователей нашего сервиса это сильно злило и разочаровывало. Наш пакет для генерации экселевских файлов писался разными людьми в разное время, исправить ошибки в нем крайне сложно, код написан так, словно его прогнали через обфускатор, но все же большинство ошибок было пофиксено.
Возник вопрос о том, что нужно его дорабатывать и расширять его возможности. Данная задача требовала много времени, которого всегда нет. Поэтому решили поискать готовые решения.
Обратившись к могучему гуглу, с поиском готовых решений, для PLSQL, нашлось много интересного, но в каждом решении чего-то, да не хватало. Таким образом, в продолжении поисков того что нужно, я напоролся на модуль XlsWriter написанный на python. Этот модуль приглянулся сразу же, так как у него отличная документация, широкие возможности, и судя по GitHab’у библиотека живет и поддерживается.
Все наше хозяйство работает под северной виндой, а в качестве веб сервера используется Apache. Все что надо было сделать, чтобы запускать питоновские скрипты, это установить сам python, оракловую библиотеку cx_Oracle для коннекта к БД, и сконфигурировать Apache, чтобы можно было запускать скрипты через CGI шлюз.
Итак:
Ставим python2.7. Процесс установки, наверное, было бы неуместно описывать здесь, так как под виндой это обычный инсталлятор, в котором, максимум что можно поменять — это путь установки, но его, мы, конечно, менять не будем.
Далее, устанавливаем библиотеку cx_Oracle, и на наше счастье, тут тоже такой же, простой и удобный инсталлятор.
Если у вас не установлена Oracle Database на том же сервере, где будет запускаться скрипт подключения к БД, то для нормальной работы, вам потребуется установить Oracle Instant Client или же саму Oracle Database. Чтобы не вмешиваться в рабочий конфиг apache, достаточно в каталоге, где будут хранится python скрипты, создать файл .htaccess и добавить туда следующие директивы:
DirectoryIndex index.py
AddHandler cgi-script .py
Options +ExecCGI
И конечно же, устанавливаем XlsxWriter с помощью пакетного менеджера pip, командой
pip install XlsxWriter.
На сервере, в каталоге www (по дефолту у apache обычно htdocs), создаем каталог reports для скриптов, которые будут генерировать нужные нам xlsx документы. В нем создаем каталог storage, в котором буду хранится сгенерированные файлы.
Для примера, в каталоге reports создадим файл emp.py — этот файл будет выгружать сотрудников из БД.
#!C:/Python27/python.exe
# -*- coding: utf8 -*-
##############################################################################
import json
import cgi
import os
import cx_Oracle
import xlsxwriter
# Указываем кодировку
os.environ["NLS_LANG"] = ".UTF8"
# Получаем входные параметры
# Важно! С точки зрения безопасности, в продакшене, все входные параметры обязательно нужно проверять !!!
form = cgi.FieldStorage()
# user_id нужен для того, чтобы складывать все его выгрузки, в его каталог
user_id = form.getvalue('user_id')
workbook_name = 'Сотрудники.xlsx'
# Подключаемся к БД
connection = cx_Oracle.connect('user/password@localhost:1521/myDB')
# Создаем каталог куда будет складываться сформированный документ
os.mkdir('storage/'+`user_id`)
# Создаем книгу по примеру из документации xlsxwriter
workbook = xlsxwriter.Workbook('C:/server/www/reports/storage/'+`user_id`+'/'+workbook_name)
# Создаем жирные стили для заголовка
bold = workbook.add_format({'bold': True})
# Строим заголовок
worksheet = workbook.add_worksheet()
worksheet.write('A1', u'id', bold)
worksheet.write('B1', u'Имя', bold)
worksheet.write('C1', u'Должность', bold)
worksheet.write('D1', u'Дата приема', bold)
# Создаем курсор
employees = connection.cursor()
employees.execute("SELECT empno,ename,name,hiredate FROM emp")
i=1
for (empno,ename,job,hiredate) in employees.fetchall():
worksheet.write(i, 0, empno)
worksheet.write(i, 1, ename.decode('utf-8','ignore'))
worksheet.write(i, 2, job.decode('utf-8','ignore'))
worksheet.write(i, 3, hiredate.decode('utf-8','ignore'))
i+=1
cursor.close()
connection.close()
workbook.close()
download_link='/reports/storage/'+`user_id`+'/'+workbook_name
print "Content-type: application/json; charset=utf-8nn"
print(json.dumps({'download_link':download_link}))
В приложении, создаем кнопку “выгрузить сотрудников”:
Button name: export_emp
Label: Выгрузить сотрудников
Затем создаем DynamicAction
name: export_emp
Selection Type: Button
Button: export_emp
Добавляем сюда Action — Execute JavaScript Code
В поле code пишем следующее:
$.ajax({
url: '/reports/emp.py',
type: "GET",
data: {
user_id: $('#P0_USER_ID').val()
},
success: function(data) {
window.location.href = data.download_link;
}
})
Заходим в настройки созданной ранее кнопки и привязываем к ней DynamicAction
На этом все.
Как это работает? По нажатию на кнопку “Выгрузить сотрудников”, срабатывает созданный нами DyamicAction, в котором запускается ajax запрос к localhost/reports/emp.py. В запросе мы передаем user_id. В этом примере, user_id нужен для того чтобы на сервере создать каталог пользователя, в который будут складываться все генерируемые им документы. Этот файл формирует xlsx документ и возвращает в json формате ссылку по которой можно скачать сгенерированный документ. Далее средствами js осуществляется переход по ней, и собственно происходит скачивание.
В итоге код формирования xlsx документа стал гораздо понятнее и приятнее выглядеть, чем топорный PL|SQL. И нам не пришлось тратить кучу времени на доработку своего решения. Возможно, это больше похоже на велосипед, чем на годное решение, но тем не менее на этом велике можно ехать.
Автор: x07
