Часто приходится делать различные выгрузки отчётов в формате XLS из Formspider, используя PL/SQL developer (Oracle 11g2). Так часто, что решил автоматизировать этот процесс на Java.
Язык Java изучаю сам по книгам и видеокурсам уже четвёртый год, поэтому если будут какие-то дополнения или замечания, то можете предложить их здесь через pull request.
Сама выгрузка из PL/SQL делается при помощи пакета as_xlsx.
И так, приступим! Я сделал простенький интерфейс для ввода имён объектов, параметров эксель файла и столбцов:
Интерфейс описан в классе ниже. Для удобства все параметры заполнены по умолчанию. Остаётся добавить информацию для формирования столбцов.
package QueriesCreater;
import java.awt.*;
import java.awt.event.KeyAdapter;
import java.awt.event.KeyEvent;
import javax.swing.*;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.JTableHeader;
public class Gui extends JFrame {
static ImageIcon logo = new ImageIcon(Toolkit.getDefaultToolkit().createImage(Gui.class.getResource("/logo.png")));
static JTable table;
static DefaultTableModel model;
static JTable objectTable;
static DefaultTableModel objectModel;
static int guiWindowHeight = 620;
static int guiWindowWidth = 370;
static int guiWindowX = 650;
static int guiWindowY = 190;
public Gui() {
this.setResizable(false);
setIconImage(logo.getImage());
this.setTitle("PL/SQL: excel export");
this.setFont(new Font("Tahoma", Font.PLAIN, 14));
this.setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
this.setBounds(guiWindowX, guiWindowY, guiWindowWidth, guiWindowHeight);
this.getContentPane().setBackground(new Color(0xFFF9A1));
this.getContentPane().setLayout(null);
// Object name table
final JScrollPane objectNames = new JScrollPane();
objectNames.setBounds(10, 25, 334, 201);
getContentPane().add(objectNames);
String[] objectColumns = new String[]{"Object", "Value"};
objectModel = new DefaultTableModel(new Object[][] {
{"Function name", "get_xls_function"},
{"Procedure name", "get_xls_from_table"},
{"Record type", "t_type_of_record"},
{"Table as record", "t_type_of_record_tbl"},
{"Table or view", "table_or_view"},
{"Headers background", "FFCC66"},
{"Headers font size", 13},
{"Rows font size", 12},
{"Rows height", 25},
{"Rows bold", false},
{"Rows italic", false},
{"Headers font", "Times New Roman"},
{"Rows font", "Times New Roman"},
{"Horizontal alignment", "center"},
{"Vertical alignment", "center"},
{"Wrap text", true},
},objectColumns) {
final boolean[] columnEditables = new boolean[]{
false, true
};
public boolean isCellEditable(int row, int column) {
return this.columnEditables[column];
}
};
objectTable = new JTable(objectModel);
objectTable.setDefaultRenderer(Object.class, new TableInfoRenderer());
// cell border color
objectTable.setGridColor(new Color(58, 79, 79));
objectTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
// table background color
objectTable.setFillsViewportHeight(true);
objectTable.setBackground(new Color(250, 252, 255));
// headers settings
JTableHeader objectHeader = objectTable.getTableHeader();
objectHeader.setFont(new Font("Tahoma", Font.BOLD, 13));
//cell alignment
TableInfoRenderer objectRenderer = new TableInfoRenderer();
objectRenderer.setHorizontalAlignment(JLabel.LEADING);
objectTable.getColumnModel().getColumn(0).setCellRenderer(objectRenderer);
objectTable.getColumnModel().getColumn(1).setCellRenderer(objectRenderer);
objectTable.setRowHeight(20);
objectTable.setColumnSelectionAllowed(true);
objectTable.setCellSelectionEnabled(true);
objectTable.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
objectTable.setFont(new Font("SansSerif", Font.PLAIN, 13));
objectTable.getColumnModel().getColumn(0).setPreferredWidth(158);
objectTable.getColumnModel().getColumn(1).setPreferredWidth(158);
//colors
objectTable.setSelectionBackground(new Color(254, 204, 204));
objectNames.setViewportView(objectTable);
// удаление содержимого ячейки кнопкой Delete
objectTable.addKeyListener(new KeyAdapter() {
public void keyPressed(KeyEvent e) {
if (e.getKeyCode()==127){
objectTable.setValueAt("", objectTable.getSelectedRow(), objectTable.getSelectedColumn());
}
}
});
// Columns table
final JScrollPane scrollPane = new JScrollPane();
scrollPane.setBounds(10, 255, 336, 283);
this.getContentPane().add(scrollPane);
String[] columns = new String[]{"Header", "Width", "Column", "Type"};
model = new DefaultTableModel(new Object[69][], columns) {
final boolean[] columnEditables = new boolean[]{
true, true, true, true
};
public boolean isCellEditable(int row, int column) {
return this.columnEditables[column];
}
};
table = new JTable(model);
// cell border color
table.setGridColor(new Color(58, 79, 79));
table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
// table background color
table.setFillsViewportHeight(true);
table.setBackground(new Color(250, 252, 255));
// headers settings
JTableHeader header = table.getTableHeader();
header.setFont(new Font("Tahoma", Font.BOLD, 13));
//cell alignment
DefaultTableCellRenderer Renderer = new DefaultTableCellRenderer();
Renderer.setHorizontalAlignment(JLabel.CENTER);
table.getColumnModel().getColumn(0).setCellRenderer(Renderer);
table.getColumnModel().getColumn(1).setCellRenderer(Renderer);
table.getColumnModel().getColumn(2).setCellRenderer(Renderer);
table.getColumnModel().getColumn(3).setCellRenderer(Renderer);
table.setRowHeight(20);
table.setColumnSelectionAllowed(true);
table.setCellSelectionEnabled(true);
table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
table.setFont(new Font("SansSerif", Font.PLAIN, 13));
table.getColumnModel().getColumn(0).setPreferredWidth(90);
table.getColumnModel().getColumn(1).setPreferredWidth(48);
table.getColumnModel().getColumn(2).setPreferredWidth(90);
table.getColumnModel().getColumn(3).setPreferredWidth(90);
//colors
table.setSelectionBackground(new Color(254, 204, 204));
scrollPane.setViewportView(table);
// удаление содержимого ячейки кнопкой Delete
table.addKeyListener(new KeyAdapter() {
public void keyPressed(KeyEvent e) {
if (e.getKeyCode()==127){
table.setValueAt("", table.getSelectedRow(), table.getSelectedColumn());
}
}
});
// Create objects button
JButton setColumnsBtn = new JButton("Create objects");
setColumnsBtn.setBounds(224, 547, 120, 22);
setColumnsBtn.setBackground(new Color(192, 225, 255));
setColumnsBtn.setFont(new Font("Tahoma", Font.BOLD, 11));
setColumnsBtn.setContentAreaFilled(true);
setColumnsBtn.setBorderPainted(true);
setColumnsBtn.setFocusable(false);
getContentPane().add(setColumnsBtn);
setColumnsBtn.addActionListener((e) -> getValues());
// Clear table
JButton clearTableBtn = new JButton("Clear");
clearTableBtn.setBounds(10, 547, 120, 22);
clearTableBtn.setBackground(new Color(251, 203, 203));
clearTableBtn.setFont(new Font("Tahoma", Font.BOLD, 11));
clearTableBtn.setContentAreaFilled(true);
clearTableBtn.setBorderPainted(true);
clearTableBtn.setFocusable(false);
getContentPane().add(clearTableBtn);
JLabel objectNameLbl = new JLabel("Parameters");
objectNameLbl.setFont(new Font("Tahoma", Font.BOLD, 13));
objectNameLbl.setBounds(10, 5, 334, 18);
getContentPane().add(objectNameLbl);
JLabel columnLbl = new JLabel("Columns");
columnLbl.setFont(new Font("Tahoma", Font.BOLD, 13));
columnLbl.setBounds(10, 235, 334, 18);
getContentPane().add(columnLbl);
clearTableBtn.addActionListener((e) -> {
for (int i = 0; i < table.getRowCount(); i++)
for(int j = 0; j < table.getColumnCount(); j++) {
table.setValueAt("", i, j);
}
});
this.setVisible(true);
}
public static void getValues() {
// определяем количество строк для создания массива (количество столбцов статическое - 4)
int rowCount = 0;
int columnCount = table.getColumnCount();
for (int i = 0; i < columnCount; i++) {
if (table.getValueAt(i, 0) != null) {
rowCount++;
}
}
// параметры
Object[][] objectParameters = new Object[16][2];
for (int i = 0; i < 16; i++) {
for (int j = 0; j < 2; j++) {
if (objectTable.getValueAt(i, j) != null) {
objectParameters [i][j] = objectTable.getValueAt(i, j);
}
}
}
// столбцы
Object[][] columns = new Object[rowCount][4];
for (int i = 0; i < rowCount; i++) {
for (int j = 0; j < columnCount; j++) {
if (table.getValueAt(i, j) != null) {
columns [i][j] = table.getValueAt(i, j);
}
}
}
// выгрузка запросов в текстовый файл
if (columns.length > 0) {
WriteToFile wr = new WriteToFile();
wr.write(new GetSqlQuery().getQueries(objectParameters, columns));
//System.out.println(sql.getQueries(columns));
}
}
public static class TableInfoRenderer extends DefaultTableCellRenderer {
@Override
public Component getTableCellRendererComponent(JTable table, Object value,
boolean isSelected, boolean hasFocus, int row, int column) {
JLabel c = (JLabel) super.getTableCellRendererComponent(table, value, isSelected, false, row, column);
if(column == 0) {
if (isSelected) {
super.setForeground(new Color(99, 9, 9));
} else {
super.setForeground(Color.BLACK);
}
c.setBackground(new Color(219, 234, 201));
c.setHorizontalAlignment(LEFT);
c.setFont(new Font("Tahoma", Font.BOLD,13));
}
else {
c.setBackground(Color.WHITE);
c.setHorizontalAlignment(LEFT);
if (isSelected) {
super.setBackground(new Color(254, 204, 204));
}
}
return c;
}
}
}В классе GetSqlQuery производится формирование команд для создания объектов PL/SQL на основе ранее введённых данных.
Record создаётся на выбор в 2 вариантах:
1) на основе %TYPE view или таблицы (не %ROWTYPE т.к. чаще нужны не все поля БД для выгрузки);
2) на основе введённых типов в columns.
Пытался сделать красиво, но не обошлось без множества append.. опять же - можно лучше - покажите как надо через pull request на gitHub
package QueriesCreater;
public class GetSqlQuery {
//формирование функции
String getQueries(Object [][] pParameters, Object [][] pColumns) {
Object functionName = pParameters[0][1];
Object procedureName = pParameters[1][1];
Object recordName = pParameters[2][1];
Object typeTableName = pParameters[3][1];
Object tableOrViewName = pParameters[4][1];
Object headersBackground = pParameters[5][1];
Object headersFontSize = pParameters[6][1];
Object rowsFontSize = pParameters[7][1];
Object rowHeight = pParameters[8][1];
Object rowsBold = pParameters[9][1];
Object rowsItalic = pParameters[10][1];
Object xlsFont = pParameters[11][1];
Object rowsFont = pParameters[12][1];
Object horizontalAlignment = pParameters[13][1];
Object verticalAlignment = pParameters[14][1];
Object wrapText = pParameters[15][1];
StringBuilder xlsHeaders = new StringBuilder("-- Package specification\n");
// создаём запись (1 вариант)
xlsHeaders.append("-- version 1\n");
xlsHeaders.append("type ").append(recordName).append(" is record (\n");
for (Object[] column : pColumns) {
xlsHeaders.append("\t").append(column[2]).append(" ").append(tableOrViewName).append(".").append(column[2]).append("%TYPE,\n");
}
// удаляем крайнюю запятую
xlsHeaders.deleteCharAt(xlsHeaders.length() - 2);
xlsHeaders.append(");\n\n");
// создаём запись (2 вариант)
xlsHeaders.append("-- version 2\n");
xlsHeaders.append("type ").append(recordName).append(" is record (\n");
for (Object[] column : pColumns) {
xlsHeaders.append("\t").append(column[2]).append(" ").append(column[3]).append(",\n");
}
// удаляем крайнюю запятую
xlsHeaders.deleteCharAt(xlsHeaders.length() - 2);
xlsHeaders.append(");\n\n");
//создаём тип таблицы на основе записи
xlsHeaders.append("type ").append(typeTableName).append(" is table of ").append(recordName).append(";\n\n");
// создаём функцию на основе типа таблицы
xlsHeaders.append("function ").append(functionName).append("(p_type in ").append(typeTableName).append(") return blob;\n\n");
// тело пакета
xlsHeaders.append("-- Package body\n");
// создание функции
xlsHeaders.append("-- function\n");
xlsHeaders.append("function ").append(functionName).append("(p_type in ").append(typeTableName).append(") return blob is\n")
.append(" row_num number := 0;\n")
.append(" v_file blob;\n")
.append("begin\n")
.append(" as_xlsx.clear_workbook;\n")
.append(" as_xlsx.new_sheet('tab1');\n")
.append(" row_num := row_num + 1;\n")
.append(" as_xlsx.set_row(row_num, p_fontId => as_xlsx.get_font('")
.append(xlsFont)
.append("', p_fontsize => ")
.append(headersFontSize)
.append(", p_bold => true),\n")
.append(" p_fillId => as_xlsx.get_fill('solid', '")
.append(headersBackground)
.append("'), p_borderId => as_xlsx.get_border);\n")
.append("\t-- headers\n")
.append("\tas_xlsx.set_row_height(1, ")
.append(rowHeight)
.append(");\n");
// headers
for (int i = 0; i < pColumns.length; i++) {
int x = i + 1;
xlsHeaders.append("\tas_xlsx.set_column_width(")
.append(x).append(", ").append(pColumns[i][1]).append("); ")
.append("as_xlsx.cell(").append(x).append(", 1, '").append(pColumns[i][0]).append("', ")
.append("p_alignment => as_xlsx.get_alignment (")
.append("p_horizontal => '").append(horizontalAlignment).append("', ")
.append("p_vertical => '").append(verticalAlignment).append("', ")
.append("p_wraptext => ").append(wrapText)
.append("));\n");
}
xlsHeaders.append(" \nFOR i IN 1..p_type.count\n" + " loop\n"
+ " row_num := row_num + 1;\n"
+ " as_xlsx.set_row(row_num, p_fontId => as_xlsx.get_font('")
.append(xlsFont)
.append("', p_fontsize => ").append(rowsFontSize)
.append("), p_borderId => as_xlsx.get_border);\n")
.append(" as_xlsx.set_row_height(row_num, ")
.append(rowHeight)
.append(");\n --\n");
// rows
for (int i = 0; i < pColumns.length; i++) {
int x = i + 1;
xlsHeaders.append(" as_xlsx.cell(")
.append(x).append(", i + 1, coalesce(p_type(i).").append(pColumns[i][2]).append(", '-'), ")
.append("p_alignment => as_xlsx.get_alignment(\n")
.append("p_horizontal => '").append(horizontalAlignment).append("', ")
.append("p_vertical => '").append(verticalAlignment).append("', ")
.append("p_wraptext => ").append(wrapText)
.append("), p_fontId => as_xlsx.get_font('")
.append(rowsFont)
.append("', p_fontsize => ")
.append(rowsFontSize)
.append(", p_bold => ")
.append(rowsBold)
.append(", p_italic => ")
.append(rowsItalic)
.append("));\n");
}
xlsHeaders.append(" end loop;\n"
+ " v_file := as_xlsx.finish;\n"
+ " RETURN v_file;\n"
+ " exception\n"
+ " when others then\n"
+ " sb_util.write_log('package.")
.append(functionName)
.append(" ошибка: ' ||sqlerrm||chr(13)||dbms_utility.format_error_backtrace, 'info'); \n")
.append("END;\n\n");
// Формирование процедуры для скачивания файла
xlsHeaders.append("-- procedure\n");
xlsHeaders.append("procedure ").append(procedureName).append(" is\n")
.append(" l_file BLOB;\n")
.append(" l_type ").append(typeTableName).append(";\n")
.append("begin\n")
.append(" select ");
for (Object[] column : pColumns) {
xlsHeaders.append(column[2]).append(", ");
}
xlsHeaders.deleteCharAt(xlsHeaders.length() - 2).append("\n");
xlsHeaders.append(" bulk collect into l_type\n from ")
.append(tableOrViewName).append("\n");
xlsHeaders.append(" where column_name between sysdate and sysdate + 1;\n" + "\n" + " l_file := ")
.append(functionName).append("(l_type);\n").append("\n")
.append(" if lengthb(l_file) > 0 then\n")
.append(" api_datasource.setColumnValue('BLOB_DUAL_ds.blob', l_file);\n")
.append(" api_datasource.download('BLOB_DUAL_ds.blob', file_name||'.xlsx');\n")
.append(" end if;\n\n")
.append("exception\n" + " when others then\n" + " sb_util.write_log('package.")
.append(procedureName)
.append(" ошибка: ' || sqlerrm ||chr(13)||dbms_utility.format_error_backtrace, 'info');\n")
.append("end;");
return xlsHeaders.toString();
}
}Класс WriteToFile выгружает запросы в формате txt в указанное вами место (по умолчанию - на Рабочий стол):
package QueriesCreater;
import javax.swing.*;
import javax.swing.filechooser.FileNameExtensionFilter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;
public class WriteToFile {
void write(String pSql) {
//Save file to
FileNameExtensionFilter filter = new FileNameExtensionFilter("*.txt", "*.txt", "*.TXT", "*.*");
JFileChooser save_to = new JFileChooser();
save_to.setFileFilter(filter);
save_to.setCurrentDirectory(new File
(System.getProperty("user.home") + System.getProperty("file.separator") + "Desktop"));
int ret = save_to.showDialog(null, "Save");
if (ret == JFileChooser.APPROVE_OPTION) {
try (OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(save_to.getSelectedFile() + ".txt"), StandardCharsets.UTF_8)) {
writer.write(pSql);
writer.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}Завершает парад классов - данный код:
package QueriesCreater;
public class Main {
public static void main(String[] args) {
new Gui();
}
}В итоге после ввода нужных нам столбцов
и нажатия кнопки "Create objects" - получаем следующие запросы:
-- Package specification
-- version 1
type t_type_of_record is record (
DB_ID table_or_view.DB_ID%TYPE,
DB_NAME table_or_view.DB_NAME%TYPE,
DB_DATE table_or_view.DB_DATE%TYPE
);
-- version 2
type t_type_of_record is record (
DB_ID NUMBER,
DB_NAME VARCHAR2(69),
DB_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
-- 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, 20); 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, 50); 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, 40); 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).DB_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).DB_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(p_type(i).DB_DATE, '-'), 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;
-- procedure
procedure get_xls_from_table is
l_file BLOB;
l_type t_type_of_record_tbl;
begin
select DB_ID, DB_NAME, DB_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', 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;Итоговый эксель-файл выглядит так:
Мне такая утилита пару лет назад очень бы помогла. Надеюсь Я кому-то помогу этим постом.
Спасибо за внимание!
