Простые отчеты в формате Excel из базы данных Oracle
Эта статья может быть полезна тем, кто должен автоматически создавать множество отчетов из БД в формате Excel-файлов.Возможные способы формирования
Иногда возникает задача автоматически отправлять произвольные выгрузки из базы данных обычным пользователям, которые привыкли работать с Excel и WordЕсть несколько вариантов реализации создание отчетов:
- Можно сформировать текстовый файл в формате csv c помощью sqlplus, например так:
- Сформировать текстовый файл в формате html c помощью sqlplus
- Сформировать реальный Excel-файл. Плюсом является возможность расширенного форматирования и отсутствие проблем с кодировками. Файл можно сформировать с помощью PL/SQL или perl/python - скриптов.
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
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
В этом случае проблем с кодировками нет, но пользователь при открытии файла получит сообщение:
- 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,' ');
Формирование отчетов с помощью 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)
https://github.com/sbrazgin/SBReporter
Обновление Python
В случае установки последней версии Python и необходимые модули на Linux-сервер, на котором нет доступа к интернету придется вручную скопировать. После установке необходимо будет дополнительно установить модули cx_Oracle и openpyxl.Инструкция по установке:
- Создаем каталог для установки
- Копируем в него и распаковываем дистрибутив Python
- Компилируем Python
- Устанавливаем расширения
- Копируем пакеты на сервер: et-xmlfile jdcal openpyxl
- Распаковываем:
- Устанавливаем:
mkdir /opt/Python
cd /opt/Python
gunzip Python-3.6.5.tgz
tar xf Python-3.6.5.tar
cd Python-3.6.5
./configure
make
make install
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
