Часто приходится делать различные выгрузки отчётов в формате XLS из Formspider (на самом деле не важно из какого UI выгружать BLOB), используя PL/SQL developer (Oracle 11g2). Так часто, что решил автоматизировать этот процесс на Java. Язык Java изучаю сам по книгам и видеокурсам уже четвёртый год, поэтому если будут какие-то дополнения или замечания по коду, то опишите их здесь.
Выгрузка из PL/SQL делается при помощи библиотеки AS_XLSX.
Gui создан для удобства. Скачал jar, открыл его (должна быть установлена Java на компьютере), указал параметры и столбцы, нажал кнопку, получил готовый код для вставки в пакет PL/SQL (сэкономил нервы и время).
Возможности программы:
Формирование кода объектов PL/SQL производится после ввода параметров и столбцов при нажатии на кнопку "Create objects";
-
Столбцы можно редактировать вручную или загружать их по кнопке "Open file" из файлов формата CSV или TXT.
Пример содержимого файла CSV Record создаётся на выбор в 2 вариантах:
- на основе %TYPE view или таблицы (не %ROWTYPE т.к. чаще нужны не все поля для выгрузки);
- на основе введённых типов в таблице Columns.Возможность очистки списка параметров и столбцов;
Добавлены комбобоксы со шрифтами, типами данных, boolean переменными и выравниванием в ячейках;
Цвет фона заголовков можно увидеть сразу в параметрах. При указании другого кода цвета, фон ячейки поменяет цвет на указанный;
Столбцы с типом данных DATE будут приведены к типу 'DD.MM.YYYY'.
Код, получаемый после ввода параметров и 3 столбцов:
-- Package specification
-- version 1
type t_type_of_record is record (
BD_ID table_or_view.BD_ID%TYPE,
BD_NAME table_or_view.BD_NAME%TYPE,
BD_DATE table_or_view.BD_DATE%TYPE
);
-- version 2
type t_type_of_record is record (
BD_ID NUMBER,
BD_NAME VARCHAR2(4000),
BD_DATE DATE
);
type t_type_of_record_tbl is table of t_type_of_record;
function get_xls_function(p_type in t_type_of_record_tbl) return blob;
-- Package body
-- procedure
procedure get_xls_from_table is
l_file BLOB;
l_file_name varchar2(30) := 'file_name';
l_type t_type_of_record_tbl;
begin
select BD_ID, BD_NAME, BD_DATE
bulk collect into l_type
from table_or_view
where column_name between sysdate and sysdate + 1;
l_file := get_xls_function(l_type);
if lengthb(l_file) > 0 then
api_datasource.setColumnValue('BLOB_DUAL_ds.blob', l_file);
api_datasource.download('BLOB_DUAL_ds.blob', l_file_name||'.xlsx');
end if;
exception
when others then
sb_util.write_log('package.get_xls_from_table ошибка: ' || sqlerrm ||chr(13)||
dbms_utility.format_error_backtrace, 'info');
end;
-- function
function get_xls_function(p_type in t_type_of_record_tbl) return blob is
row_num number := 0;
v_file blob;
begin
as_xlsx.clear_workbook;
as_xlsx.new_sheet('tab1');
row_num := row_num + 1;
as_xlsx.set_row(row_num, p_fontId => as_xlsx.get_font('Times New Roman', p_fontsize => 13, p_bold => true),
p_fillId => as_xlsx.get_fill('solid', 'FFCC66'), p_borderId => as_xlsx.get_border);
-- headers
as_xlsx.set_row_height(1, 25);
as_xlsx.set_column_width(1, 10); as_xlsx.cell(1, 1, 'id', p_alignment => as_xlsx.get_alignment
(p_horizontal => 'center', p_vertical => 'center', p_wraptext => true));
as_xlsx.set_column_width(2, 20); as_xlsx.cell(2, 1, 'name', p_alignment => as_xlsx.get_alignment
(p_horizontal => 'center', p_vertical => 'center', p_wraptext => true));
as_xlsx.set_column_width(3, 18); as_xlsx.cell(3, 1, 'date', p_alignment => as_xlsx.get_alignment
(p_horizontal => 'center', p_vertical => 'center', p_wraptext => true));
FOR i IN 1..p_type.count
loop
row_num := row_num + 1;
as_xlsx.set_row(row_num, p_fontId => as_xlsx.get_font('Times New Roman', p_fontsize => 12),
p_borderId => as_xlsx.get_border);
as_xlsx.set_row_height(row_num, 25);
--
as_xlsx.cell(1, i + 1, coalesce(p_type(i).BD_ID, '-'), p_alignment => as_xlsx.get_alignment
(p_horizontal => 'center', p_vertical => 'center', p_wraptext => true), p_fontId =>
as_xlsx.get_font('Times New Roman', p_fontsize => 12, p_bold => false, p_italic => false));
as_xlsx.cell(2, i + 1, coalesce(p_type(i).BD_NAME, '-'), p_alignment => as_xlsx.get_alignment
(p_horizontal => 'center', p_vertical => 'center', p_wraptext => true), p_fontId =>
as_xlsx.get_font('Times New Roman', p_fontsize => 12, p_bold => false, p_italic => false));
as_xlsx.cell(3, i + 1, coalesce(to_char(p_type(i).BD_DATE, 'dd.mm.yyyy'), '-'), p_alignment => as_xlsx.get_alignment
(p_horizontal => 'center', p_vertical => 'center', p_wraptext => true), p_fontId =>
as_xlsx.get_font('Times New Roman', p_fontsize => 12, p_bold => false, p_italic => false));
end loop;
v_file := as_xlsx.finish;
RETURN v_file;
exception
when others then
sb_util.write_log('package.get_xls_function ошибка: ' ||sqlerrm||chr(13)||
dbms_utility.format_error_backtrace, 'info');
END;Итоговый эксель-файл выглядит так:
Комментарии (21)
puyol_dev2
16.07.2021 08:29Не очень понятно зачем нужен гуи. Не очень понятно почему запрос формирования файла Excel оказался в коде java. Не самый лучший шаблон программирования, мягко говоря
mrprogre Автор
16.07.2021 09:02GUI просто для формирования запросов, которые создают объекты PL/SQL, чтобы я не делал их руками, а просто ввёл параметры и столбцы и получил готовый код для вставки в PL/SQL. Тут любой язык бы подошёл, но я изучаю Java. Я просто упростил себе работу. Скорость создания выгрузки из Оракла выросла в 10 раз минимум.
puyol_dev2
16.07.2021 09:49Как вариант параметры в тестовом файле хранить (json, xml). Если изучаете java, то практичнее с этими форматами поработать
Лично я бы сделал решение обычным бат файлом, который вызывает java класс, который считывает настройки из текстового файла
mrprogre Автор
16.07.2021 10:13Можно и так как вариант, но мне с gui кажется проще работать. Ввёл параметры - получил готовые запросы.
a1ex322
16.07.2021 15:06Json можно как промежуточный слой использовать. С точки зрения вашей программы это будет импорт экспорт того, что вы вводите. Полезно в том плане, что не надо в следующий вспоминать что надо вводить. Я такие json-ы в гит трекаю, очень удобно
Siemargl
16.07.2021 09:20А где, простите, отчет?
Ну это обычно такая красивая табличка с заголовками, рамочками, разбиением на страницы, подитогами итпmrprogre Автор
16.07.2021 10:16А что за отчёт, простите?
А чтобы в PL/SQL сделать красивую табличку надо написать много кода. Моя программка ускоряет этот процесс. Я не пойму почему столько негатива. Мне эта программа уже сейчас помогла. Я делал 4 разных выгрузки. Раньше бы я потратил на это около дня ,с учётом переделывания и т.д. Сейчас я сделал их за пол часа. Разве не результат? Помогло мне - поможет и другим.
ScarferNV
18.07.2021 23:58А разве не проще работать с xml в PL/SQL?
mrprogre Автор
18.07.2021 23:59Кому как удобно. Я выбрал способ формирования выгрузки через библиотеку AS_XLSX. Может пришлёте ссылку на статейку?
ScarferNV
19.07.2021 13:32Можете покапать в этом направлении, я например в xml выгружаю что-то вроде этого
select "XMLresult" into vClob from ( select 1 as "id", CONVERT(xmlelement("Report", xmlattributes('Report' as "Report"), xmlagg( xmlelement("row", xmlforest( "..." "...", ... )) ) ).getclobval(), 'UTF8') as "XMLresult" from ( ... );mrprogre Автор
19.07.2021 14:24попробую, интересно! в принципе, если пришлёте целую функцию от и до, то я могу добавить в программу формирование кода и в таком виде.. можно будет выбрать галочкой, к примеру.. похоже тут тоже много кода получится :)
mrprogre Автор
01.08.2021 19:10С учётом комментариев и личных сообщений:
доработан исходный код и интерфейс;
добавлена возможность загрузки столбцов из csv или txt файлов;
добавлены комбобоксы со шрифтами, типами данных, boolean переменными и выравниванием в ячейках;
цвет фона заголовков теперь можно увидеть сразу в параметрах. При указании другого кода цвета, фон ячейки поменяет цвет на указанный;
добавлена кнопка очистки параметров.
Изменения можно увидеть здесь. Там же можно скачать jar-файл приложения.
AntonioXXX
Архитектурно ничего не понятно: что в PL/SQL, что в Java, что делает библиотека, что вы писали?
Можно просто в несколько строчек по пунктам сценарий? Ну, там генерируется то-то по кнопке из GUI, сохраняется туда-то, данные берутся так-то, подставляются туда-то, библиотека делает с данными то-то, XLS генерится так-то; тут GUI, тут скрипт, тут готовое, тут самописное и т.д.
Ну и с базой понятно, а Java-то где крутится и что вызывается, точка входа какая, точка выхода (куда файл кладется)? И если есть Java, то не лучше ли у неё с генерацией XLS?
mrprogre Автор
Такое ощущение, что Вы не читали пост. В таком порядке всё и написал. Библиотека AS_XLSX в PL/SQL используется для формирования выгрузки в эксель. Чтобы эту выгрузку произвести надо написать немало кода. Моя программка из введённых параметров и столбцов формирует txt со всеми запросами для создания объектов в пакете PL/SQL. Кто в PL/SQL работает, тот знает какая это нудятина набирать всё заново для новой выгрузки. И по-моему у меня такие простые классы, что какие могут быть вопросы? Файл кладётся куда укажете. Берёте из него готовые запросы, копируете в PL/SQL. Куда уж проще?
Я ждал более конструктивной критики в части программирования, а не то какой я писатель плохой, сюжет запутал. Но учту, конечно же, в следующий раз напишу проще. Спасибо!
mrprogre Автор
Три человека уже в закладки добавили, значит понимают о чём речь..поддержите, товарищи!
AntonioXXX
Я не читал пост (кроме вводных абзацев и пробежки по тексту в попытке понять суть). Возможно 3 человека тоже не читали и ждут, когда вы на мои вопросы ответите )
Я не оценивал вас как писателя, но я действительно оценил текст и написал чего мне не хватает. Сложно понять зачем читать, если не понятна ни проблематика ни суть решения, при таком совсем неразумно пытаться код читать.
Вы дали пояснения, но многое не стало понятнее: Java только для gui и формирования пакета? В чем проблема библиотеки, почему для неё что-то нужно дописывать? Файл формируется на сервере БД и там же выкладывается?
Мой совет по структуре статей (можете ему не следовать, но возможно не мне одному так удобно, может и не 3 закладки будет) : проблематика, суть решения (архитектура, сценарий), описание решения (код), пример.
mrprogre Автор
"Не читал пост", но пост плохой. Прекрасный ответ.
Я понял, надо вводную часть описывать более просто и подробно. Учту.
Java только для формирования готовых запросов для PL/SQL. Файл формируется в файловой системе у Вас на компьютере. Вы его открываете копируете из него готовые запросы в PL/SQL. Пакет AS_XLSX (перешёл по ссылке на пакет, там объяснения зачем он) - "Это пакет PL/SQL, написанный Антоном Шеффером, который позволяет нам экспортировать файлы Excel XLSX из базы данных Oracle.". Проблем нет, своим приложением я упрощаю создание запросов. Раньше я делал каждый столбец руками, копировал, писал номер столбца, следил за тем, чтобы имена типов и объектов совпадали. Много нюансов. А тут я просто ввёл параметры и столбцы и получил готовый код.
По библиотекам. Библиотека Swing для формирования Gui. Пишем код чтобы был GUI.Библиотека AS_XLSX для формирования экселя. Везде надо писать код. Библиотека в помощь. Зачем её дописывать, я просто её использую.
Учту.
BitLord
Простите, я, может, тоже не понял идею, но прочитал пост полноценно 2 раза и вопросы также остались:
В общем, у меня сложилось ощущение, что всё делалось именно для практики Java-разработки и получения фидбека от сообщества именно по ней. А мы решили понять верхнеуровневую проблематику, зачем это всё затевалось, и почему было сделано именно в такой архитектуре, вместо критики недочётов стиля вроде «кучи append»…
mrprogre Автор
Благодарю за такой объективный комментарий!
В принципе да, PL/SQL это позволяет. И я уже сделал ранее динамическое создание эксель-файла нужного формата, в плане оформления, из любого селекта. Однако при динамическом создании нельзя указать для отдельных столбцов отличные от других параметры. И когда ты знаешь сколько у тебя будет столбцов, то динамически делать некорректно, тем более, что в некоторых столбцах нужен жирный шрифт и т.д. Параметры надо задавать сразу и каждому столбцу. Вот поэтому для каждой выгрузки свои объекты и много кода, который каждый раз писать надоело, т.к. тратится много времени и нервов, а заказчик постоянно просит сделать новые выгрузки, а потом поменять столбцы местами и т.д.
Gui просто для удобства. Скачал, открыл (нужна только java на компьютере), вбил параметры и столбцы, получил готовый код для вставки в пакет PL/SQL. Всё! Это ли не счастье? :) Библиотека AS_XLSX - просто библиотека, которую я использую для выгрузки (есть и другие, само собой). Каждый делает как ему удобно.
Основной посыл поста такой: ранее я читал статью на Хабре как делать выгрузку из PL/SQL в xls (ссылку найти не могу, возможно удалили) и просто хотел поделиться с людьми, которые тоже используют библиотеку AS_XLSX, своей утилитой, которая сэкономит им море времени и нервов. Кому надо, тот поисковиком найдёт эту статью и легко применит это в работе. Также делал утилиту для PL/SQL, рекомендую ознакомиться :) https://github.com/mrprogre/PL-SQL-Helper
Второй момент да, я думал мне подскажут, как грамотнее в Java это всё описать, т.к. я её изучаю (и да, в личку прислали несколько интересных идей).
Похоже на всё ответил.
Моя ошибка, что пост написан неграмотно для Хабра (один умный человек посоветовал посты выкладывать в пятницу вечером, чтобы поменьше людей "не в теме" читали и поливали пост своей любовью). А тот кому нужен plsql, formspider, as_xlsx, java - его найдёт и оценит по достоинству (9 закладок о чём-то то и говорит)!
mrprogre Автор
Привет! Переделал статью с учётом всех пожеланий и замечаний. Норм?)