Формирование объектов PL/SQL для выгрузки отчётов в формате XLS из Formspider с помощью Java

Часто приходится делать различные выгрузки отчётов в формате 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;

Итоговый эксель-файл выглядит так:

Мне такая утилита пару лет назад очень бы помогла. Надеюсь Я кому-то помогу этим постом.

Спасибо за внимание!