Отправка отчетов для пользователей в формате Excel из базы Oracle

Простые отчеты в формате Excel из базы данных Oracle

Эта статья может быть полезна тем, кто должен автоматически создавать множество отчетов из БД в формате Excel-файлов.

Возможные способы формирования

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

Есть несколько вариантов реализации создание отчетов:

1. Можно сформировать текстовый файл в формате csv c помощью sqlplus, например так:



  
set linesize 9999
set pagesize 50000
spool myfile.csv
select x
from
(
select col1||';'||col2||';'||col3||';'||col4||';'||col5||';'||col6||';'||col7||';'||col8||';'||col9||';'||col10||';'||col11||';'||col12||';'||col13||';'||col14||';'||col15||';'||col16||';'||col17||';'||col18||';'||col19||';'||col20||';'||col21||';'||col22||';'||col23||';'||col24||';'||col25||';'||col26||';'||col27||';'||col28||';'||col29||';'||col30 as x
from (  
      
     )
);
spool off


При форматировании файлов на Unix-сервере возможны проблемы с русскими буквами, также иногда не корректно открываются файлы из-за разных настроек разделителей в excel у пользователя.
При формировании файлов на Windows-сервере можно сразу конвертировать созданные csv-файлы в excel-формат с помощью VB-скриптов, наподобие такого:


Private Sub ap_WorkbookOpen(ByVal Wb As Workbook)
    With Wb
    If InStrRev(.Name, ".csv") = Len(.Name) - 3 Then
    .SaveAs Filename:=Left(.FullName, Len(.FullName) - 4) + ".xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    .Close
    End If
    End With
End Sub      




2. Сформировать текстовый файл в формате html c помощью sqlplus



  
SET PAGESIZE 50000
SET FEED OFF MARKUP HTML ON SPOOL ON
SET NUM 24
SPOOL DATA_2.xls

SELECT * FROM xxx;

SPOOL OFF
SET MARKUP HTML OFF SPOOL OFF
       
 


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


Просмотрев предупреждение пользователь должен подтвердить открытие файла. Что-бы пользователь не пугался, добавляем в письмо успокаивающий текст: "При открытии файла xls в программе Excel нажмите ДА"


3. Сформировать реальный Excel-файл. Плюсом является возможность расширенного форматирования и отсутствие проблем с кодировками. Файл можно сформировать с помощью PL/SQL или perl/python - скриптов. 

• C помощью PL/SQL создание excel-файлов выглядит примерно так:

  

    UTL_FILE.PUT_LINE(v_fh,'');
    FOR j in 1..col_cnt
    LOOP
      UTL_FILE.PUT_LINE(v_fh,'');
      UTL_FILE.PUT_LINE(v_fh,''||rec_tab(j).col_name||'');
      UTL_FILE.PUT_LINE(v_fh,'');
    END LOOP;
    UTL_FILE.PUT_LINE(v_fh,'');
       
 



Также есть множество специализированных PL/SQL-пакетов, которые немного упрощают создание excel-файлов. К сожалению мне не удалось найти подходящую для меня библиотеку.

Формирование отчетов с помощью Python

   В Python доступно несколько модулей по работе с Excel: xlrd, xlwt, xlutils, XlsxWriter или openpyxl . Модуль XlsxWriter  идеален для тех кто создает отчеты с нуля, openpyxl удобен тем, что можно открыть существующий файл и добавить в него данные.
 
   Вариант работы с openpyxl  выглядит так:

• в Excel предварительно создаем шаблон отчета, выбираем оформление, заполняем шапку, названия колонок и т.д.;
• создаем конфигурационный файл, с параметрами подключения к базе, с ссылкой на sql-файл, названием отчета  и т.д.
• запускаем Python-скрипт, на вход подаем название конфигурационного файла и название excel-файла, скрипт считывает кофигурационный файл, подключается к указанной БД, выполняет указанный SQL-запрос, и заполняет excel данными из БД
• внутри python-скрипт выполняет запрос к базе данных, открывает существующий  excel-файл и заполняет excel-файл данными;

В коде Python- скрипта это выглядит примерно так:

  
    wb = openpyxl.load_workbook(filename=excel_file)
    try:
        ws = wb[excel_page]  
    except Exception as e:
        logger.error( ... )

    row = first_row
    for tupple_row in cur_sor:
        col = first_col
        for list_item in tupple_row:
            ws.cell(row=row, column=col, value=list_item)
            col = col + 1
        row = row + 1
    wb.save(excel_file)
       
 

Полный вариант скрипта можно скачать в моем репозитории на GitHub:

https://github.com/sbrazgin/SBReporter

Обновление Python

В случае установки последней версии Python и необходимые модули на Linux-сервер, на котором нет доступа к интернету придется вручную скопировать. После установке  необходимо будет дополнительно установить модули cx_Oracle и openpyxl.

Инструкция по установке:

1. Создаем каталог для установки

mkdir /opt/Python
cd /opt/Python



2. Копируем в него и распаковываем дистрибутив Python

gunzip Python-3.6.5.tgz
tar xf Python-3.6.5.tar



3. Компилируем Python

cd Python-3.6.5
./configure
make
make install


4. Устанавливаем расширения


• Копируем пакеты на сервер:
et-xmlfile jdcal openpyxl


• Распаковываем:

tar -xvzf jdcal-1.4.tar.gz
tar -xvzf et_xmlfile-1.0.1.tar.gz

tar -xvzf openpyxl-2.5.3.tar.gz


• Устанавливаем:

cd jdcal-1.4
python setup.py install

cd et_xmlfile-1.0.1
python setup.py install

cd openpyxl-2.5.3
python setup.py install