"Кодекс жути ночной" - это инкунабула в цикле романов Александра Бушкова о приключениях лорда Сварога, в прошлом майора ВДВ, волею судеб, транспространственной магии и собственного незаурядного и неуемного характера овладевшего многими королевствами и многими проблемами, далеко выходящими за пределы майорских забот. В оном кодексе сведены воедино свидетельства очевидцев о всяческих жутких и необъяснимых явлениях, добавивших авторам наблюдений немало седых волос. Таков MS Excel с точки зрения базиста.
Проблема в том, что в базе данных - таблицы. И в MS Excel - тоже таблица. Плюс огромные возможности по визуализации данных, их представлению и прочим творческим действиям. Огромные! Руководитель и аналитик любят MS Excel, поскольку в нем все на виду и со всем этим парой нажатий кнопок можно сделать все, что угодно: обсчитать, график построить. И они требуют от базиста: Excel - в базу! или базу в Excel! Базист сползает под стол в предвкушении тяжелого садо-маза. Oracle напрямую преобразование в MS Excel не поддерживает. И обратно тоже.
База данных и Excel внутренне глубоко противоречивы друг другу, они похожи лишь внешне, но внутри оптимизированы в противоположных направлениях. Это своего рода антиматерии ИТ.
Существуют частные - и зачастую авторские - решения частных задач.
Выгрузка Oracle -> MS Excel
Предположим, у нас есть таблица 3x3 в Oracle. Имитируем ее запросом:
select 1 as id, 'Some text' as text, 'Английский' as lang from dual
union all
select 2, 'Еще текст', 'Русский' from dual
union all
select 3, 'Lisää teksti', 'Финский' from dual
;
1 Some text Английский
2 Еще текст Русский
3 Lisää teksti Финский
Ее надо выгрузить в MS Excel. Рассмотрим три варианта, используем три PL/SQL пакета - ни один из них в собственно Oracle не входит. Поэтому до их применения пакеты надо сначала найти, потом установить, потом разобраться в них. Потом осознать их ограничения.
Выгрузка в CSV - Comma Separated Values, список через запятую
CSV - это стандартный формат "взрослого" обмена данными. Именно данными.
Самый простой способ выгрузить таблицу в формат, понимаемый MS Excel'ем, это записать ее данные в разделенный сепараторами список, что-то типа (сепаратор - запятая)
1,Some text,Английский
2,Еще текст,Русский
3,Lisää teksti,Финский
MS Excel это поймет, но есть беда - запятая часто используется в русских системах в качестве десятичного разделителя, может запросто встретиться в текстовых строках и т.п. Поэтому в качестве разделителя чаще используют точку с запятой (;), а сами элементы списка помещают в двойные кавычки, причем только текстовые значения - а числовые оставляют "голыми"; вероятность перепутать разделитель резко снижается:
1;"Some text";"Английский"
2;"Еще текст";"Русский"
3;"Lisää teksti";"Финский"
Это можно получить простым запросом, в котором, правда, все придется прописать абсолютно явно:
with tab as
(
select 1 as id, 'Some text' as text, 'Английский' as lang from dual
union all
select 2, 'Еще текст', 'Русский' from dual
union all
select 3, 'Lisää teksti', 'Финский' from dual
)
select id || ';"' || text || '";"' || lang || '"' as line from tab
;
1;"Some text";"Английский"
2;"Еще текст";"Русский"
3;"Lisää teksti";"Финский"
А дальше возникнет проблема - как записать это в файловую систему?? Поэтому для генерации даже CSV лучше воспользоваться каким-нибудь авторским PL/SQL пакетом и написать что-то типа:
declare
p_dir varchar2(200) := 'YK_EXPORT_DIR';
p_file_name varchar2(200) := 'my_file.csv';
l_refcursor sys_refcursor;
begin
open l_refcursor for select id as c1, text as c2, lang as c3 from tab;
csv.set_quotes(p_add_quotes => TRUE, p_quote_char => '"');
csv.set_separator(p_sep => ';');
csv.generate_rc(p_dir, p_file_name, l_refcursor);
end;
Tim Hall, 2019, https://oracle-base.com/dba/miscellaneous/cvs.sql
При этом 'YK_EXPORT_DIR' - это объект базы типа directory, указывающий на конкретный каталог файловой системы сервера.
Это - самый простой способ.
Выгрузка в XLSX - "новый" Excel
XLSX - это формат для создания готовых отчетов, которые красиво выглядят и могут быть предъявлены начальству и/или поставлены на полку.
Иногда требуется вывести данные не во вполне понимаемый MS Excel'ем естественный для обмена данными CSV, а в "натуральный Excel". Скажем сразу, реальных способов вывести данные в "старый" формат XLS (95-2003) сейчас не существует, выводить будем в "новый" формат XLSX, представляющий по сути компрессированный XML документ. Т.е. файл бинарный, а структура внутри текстовая и упорядоченная.
В этом случае стоит сразу воспользоваться авторским пакетом, уже другим, и записать что-то типа:
declare
p_dir varchar2(200) := 'YK_EXPORT_DIR';
p_file_name varchar2(200) := 'my_file.xlsx';
v_row number := 0;
begin
as_xlsx.clear_workbook;
as_xlsx.new_sheet;
for c in (select id, text, lang from tab) loop
v_row := v_row + 1;
as_xlsx.cell(p_col => 1, p_row => v_row, p_value => c.id);
as_xlsx.cell(p_col => 2, p_row => v_row, p_value => c.text);
as_xlsx.cell(p_col => 3, p_row => v_row, p_value => c.lang);
end loop;
as_xlsx.save(p_dir, p_file_name);
end;
Anton Scheffer, 2011, http://technology.amis.nl/blog
Выгрузка в XML формата MS Excel
XML - это формат для машинного обмена, но в ряде случаев необходимо выгрузить не в простой и сухой XML от Oracle типа строка-ячейка, а в XML, совместимый с MS Excel.
Вывод в MS Excel XML нужен в тех случаях, когда основным потребителем является машина, но возможен вариант "повышенного интереса" человека, желающего взглянуть на процесс глазами через привычную призму MS Excel. Встречается, в том числе, при пошаговой автоматизации частично ручных процессов.
В этом случае стоит тоже обратиться к специализированному пакету, уже третьему, и записать что-то такого типа:
declare
p_dir varchar2(200) := 'YK_EXPORT_DIR';
p_file_name varchar2(200) := 'my_file.xml';
p_worksheet_name varchar2(200) := 'Book 1';
v_row number := 0;
begin
gen_xl_xml.create_excel(p_dir, p_file_name);
gen_xl_xml.create_worksheet(p_worksheet_name);
for c in (select id, text, lang from tab) loop
v_row := v_row + 1;
gen_xl_xml.write_cell_char(p_row => v_row, p_column => 1,
p_worksheet_name => p_worksheet_name, p_value => c.id, p_style => null);
gen_xl_xml.write_cell_char(p_row => v_row, p_column => 2,
p_worksheet_name => p_worksheet_name, p_value => c.text, p_style => null);
gen_xl_xml.write_cell_char(p_row => v_row, p_column => 3,
p_worksheet_name => p_worksheet_name, p_value => c.lang, p_style => null);
end loop;
end;
Sanjeev Sapre, 2007, http://sanjeev-oracle-world.blogspot.com/2007/06/create-excel-workbook-by-plsql-code.html
Загрузка MS Excel -> Oracle
Можно потребовать преобразовать загружаемый файл в CSV и воспользоваться
- загрузчиком Oracle SQL Developer;
- загрузчиком Oracle APEX (написать его на основе шаблона);
- разобрать CSV самостоятельно написанной PL/SQL процедурой.
Но иногда пользователи могут отказаться это делать и потребовать готовое приложение, которое будет "глотать" родной формат MS Excel или несколько таковых.
Загрузка CSV, XLS, XLSX, XML с использованием плагина Oracle APEX
Плагин Oracle APEX - это дополнительный модуль, который может быть импортирован в приложение Oracle APEX.
Anton Scheffer, https://github.com/antonscheffer/excel2collections
Плагин импортируется в приложение Oracle APEX, на страницу помещается стандартный элемент типа file browse, создается процесс "имени плагина" (он будет в разделе "unsupported"), ему обязательно и статично указывается имя элемента file browse, на странице размещается кнопка с действием submit - и файл MS Excel в коллекции APEX с указанным процессу именем.
Итак, вроде бы мы нашли решения для всех возможных задач. Но!
Во-первых, для четырех задач нам пришлось применить 4 разных решения от трех разных авторов.
Во-вторых, все они "самодельные" в статусе as is.
В-третьих, это лишь обзор, оставивший за скобками значительное количество нюансов.
В-четвертых, там много реальных ограничений; например, конвертер XLS -> MS Excel XML мне пока удалось реализовать лишь в направлении клиент -> сервер, хотя естественнее было бы обратное.
Короче говоря, MS Excel остается "кодексом жути ночной" для базиста.