Данная заметка, возможно будет интересна тем, кто в своих проектах использует Oracle Apex. Принцип описанный здесь, можно применить под любые другие задачи, используя не только python, но и любой другой язык.

Oracle Apex — это конструктор веб приложений со своей атмосферой. Для простых приложений его функционала более чем достаточно, но когда приложение активно развивается, то возникают определенные трудности.

Для других популярных языков и фреймворков, есть гигантское количество библиотек выполняющие любые задачи. В случае с Oracle Apex, готовых и современных решений очень мало.

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

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

Возник вопрос о том, что нужно его дорабатывать и расширять его возможности. Данная задача требовала много времени, которого всегда нет. Поэтому решили поискать готовые решения.
Обратившись к могучему гуглу, с поиском готовых решений, для PL\SQL, нашлось много интересного, но в каждом решении чего-то, да не хватало. Таким образом, в продолжении поисков того что нужно, я напоролся на модуль 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-8\n\n"
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. И нам не пришлось тратить кучу времени на доработку своего решения. Возможно, это больше похоже на велосипед, чем на годное решение, но тем не менее на этом велике можно ехать.