Принцип использования
Для создания файла Excel на процедурном языке PL/pgSQL создается функция, которая выполняет следующее: определяет документ (pgxls.create), заполняет его (pgxls.add_row, pgxls.put_cell, pgxls.add_sheet), формирует и возвращает файл (pgxls.get_file).
Кол-во столбцов и их ширина определяются при создании документа или добавлении листа. Для каждого столбца можно переопределить формат в зависимости от типа данных c помощью процедур pgxls.set_column_format_[type].
Процедура pgxls.put_cell устанавливает значение ячейки и формат в зависимости от типа значения (настраивается для столбца), имеет необязательные параметры для изменения свойств форматирования.
Процедуры pgxls.format_cell, pgxls.format_row и pgxls.format_all изменяют форматирование.
Для удобства использования определяется текущий столбец, который сбрасывается при добавлении строки и увеличивается при установке значения поля без указания столбца. При установке значения ячейки с указанием столбца текущий столбец переопределяется на указанный. При помощи процедур pgxls.set_column_current и pgxls.next_column можно изменять текущий столбец.
При добавлении строки или листа текущая строка записывается во временную таблицу в виде подготовленной части файла и не может быть изменена. Высота строки вычисляется автоматически, но может быть установлена вручную процедурой pgxls.set_row_height.
Настройка печати позволяет для каждого листа установить формат и ориентацию бумаги, заголовок, повторяющиеся строки ("шапку" таблицы) и отступы. Выполняется процедурами pgxls.set_page_paper, pgxls.set_page_header, pgxls.set_page_rows_repeat и pgxls.set_page_margins соответственно.
При создании больших файлов для заполнения ячеек необходимо использовать процедуры pgxls.set_cell_[type] и получать файл частями (pgxls.get_file_parts_query).
Для создания файла по SQL-запросу создана функция-обертка pgxls.get_file_by_query, а для добавления листа - процедура pgxls.add_sheet_by_query.
Процедуры-обертки pgxls.save_file и pgxls.save_file_by_query сохраняют документ в файл на сервере, выполняются под суперпользователем.
Не разрешается обращение к внутренним полям pgxls.xls и использование процедур и типов, начинающихся с подчеркивания "_"
Безопасность
Хранимые процедуры создаются с правами вызывающего (security invoker), соотвественно, передаваемые через параметры динамические SQL-запросы вызываются с этими правами.
При необходимости доступа разработчика к сохранению файлов на сервере суперпользователь создает процедуру-обертку с правами владельца (security definer) и необходимыми проверками.
Пример:
-- postgres create wrapper for procedure pgxls.save_file with option security definer and access check, -- grant privilege on wrapper create or replace procedure example.save_file_to_share_reports(inout xls pgxls.xls, filename varchar) security definer language plpgsql as $$ begin if filename like '%/%' then raise exception 'File name cannot contain path'; end if; call pgxls.save_file(xls, '/mnt/share/reports/'||filename); end $$; grant execute on procedure example.save_file_to_share_reports to developer_1; -- developer_1 create report and save it using wrapper example.save_file_to_share_reports create or replace procedure example.my_report() language plpgsql as $$ declare xls pgxls.xls; begin xls := pgxls.create(array[10,10]); call pgxls.put_cell_text(xls, 'My'); call pgxls.put_cell_text(xls, 'Report'); call example.save_file_to_share_reports(xls, 'MyReport.xlsx'); end $$; call example.my_report();
pgxls.add_row
Процедура добавляет одну или несколько строк на лист, обнуляет текущий столбец
Параметры:
| xls | pgxls.xls | - | документ | ||
| count | int | - | кол-во строк (необязательный, по умолчанию 1) |
Примеры использования:
call pgxls.add_row(xls); call pgxls.add_row(xls, 5);
pgxls.add_row_texts
Процедура добавляет строку с текстовыми значениями, позволяет менять параметры стиля
Параметры:
| xls | pgxls.xls | - | документ | ||
| texts | text[] | - | значения ячеек | ||
| font_bold | boolean | - | жирный шрифт (необязательный) | ||
| fill_foreground_color | varchar(6) | - | цвет заливки в формате 'RRGGBB' или 'none' (необязательный) | ||
| alignment_horizontal | pgxls.alignment_horizontal | - | выравнивание по горизонтали (необязательный) |
Примеры использования:
call pgxls.add_row_texts(xls, array['ID','Name']); call pgxls.add_row_texts(xls, array['x','√x','x²','md5(x)'], font_bold=>true, fill_foreground_color=>pgxls.color$dark_gray(), alignment_horizontal=>'center');
pgxls.add_sheet
Процедура добавляет новый лист в документ
Параметры:
| xls | pgxls.xls | - | документ | ||
| columns_widths | int[] | - | ширины столбцов | ||
| columns_captions | text[] | - | заголовки столбцов (необязательный) | ||
| name | varchar | - | имя листа (необязательный) |
Пример использования:
call pgxls.add_sheet(xls, array[10,20], array['ID','Name'], 'Sheet2');
pgxls.add_sheet_by_query
Процедура добавляет новый лист в документ
Параметры:
| xls | pgxls.xls | - | документ | ||
| columns_widths | int[] | - | ширины столбцов | ||
| columns_captions | text[] | - | заголовки столбцов (необязательный) | ||
| name | varchar | - | имя листа (необязательный) |
Пример использования:
call pgxls.add_sheet_by_query(xls, 'select * from pg_class', 'Query data');
pgxls.alignment_horizontal
Тип enum, определяет выравнивание по горизонтали
Значения: top,center,bottom,justify,distributed
pgxls.alignment_vertical
Тип enum, определяет выравнивание по вертикали
Значения: top,center,bottom,justify,distributed
pgxls.border_line
Тип enum, определяет внешний вид границы.
Значения: none,thin,thick,dashed,dotted,dashDot,dashDotDot,double
pgxls.clear_file_parts
Процедура удаляет файл из временной таблицы, используется совместно с pgxls.get_file_parts_query
Параметр:
| xls | pgxls.xls | - | документ |
pgxls.color$[name]
Функция возвращает цвет (тип varchar(6)) по названию
Параметр:
| name | - | название: light_red,light_green,light_blue,light_gray,dark_red,dark_green,dark_blue,dark_gray |
Примеры использования:
call pgxls.set_cell_font(xls, color=>pgxls.color$light_red()); call pgxls.set_all_fill(xls, foreground_color=>pgxls.color$dark_gray());
pgxls.create
Функция создает, инициализирует и возращает документ (тип pgxls.xls)
Параметры:
| columns_widths | int[] | - | ширины столбцов | ||
| columns_captions | text[] | - | заголовки столбцов (необязательный) | ||
| sheet_name | varchar | - | имя листа (необязательный) |
Примеры использования:
declare xls pgxls.xls; begin xls := pgxls.create(array[10,20,30], sheet_name=>'My sheet'); xls := pgxls.create(array[15,30,30], array['x','√x','x²'], 'Columns');
pgxls.font_name$[family]
Функция возвращает имя (тип varchar) по семейству шрифта
Параметр:
| family | - | семейство шрифта: sans,sans_serif,monospace |
Примеры использования:
call pgxls.set_column_font(xls, 1, name=>pgxls.font_name$sans_serif(), size=>16); call pgxls.set_cell_value(xls, 123, font_name=>pgxls.font_name$monospace());
pgxls.format_cell
Процедура форматирует ячейку
Параметры:
| xls | pgxls.xls | - | документ | ||
| column_ | int | - | столбец (необязательный, по умолчанию текущий) | ||
| format_code | varchar | - | код формата (необязательный) | ||
| font_name | varchar | - | имя шрифта (необязательный) | ||
| font_size | int | - | размер шрифта (необязательный) | ||
| font_bold | boolean | - | жирный шрифт (необязательный) | ||
| font_italic | boolean | - | шрифт курсивом (необязательный) | ||
| font_underline | boolean | - | подчеркнутый шрифт (необязательный) | ||
| font_strike | boolean | - | зачеркнутый шрифт (необязательный) | ||
| font_color | varchar(6) | - | цвет шрифта в формате 'RRGGBB' или 'auto' (необязательный) | ||
| border_around | pgxls.border_line | - | граница всех сторон (необязательный) | ||
| border_left | pgxls.border_line | - | граница слева (необязательный) | ||
| border_top | pgxls.border_line | - | граница сверху (необязательный) | ||
| border_right | pgxls.border_line | - | граница справа (необязательный) | ||
| border_bottom | pgxls.border_line | - | граница снизу (необязательный) | ||
| fill_foreground_color | varchar(6) | - | цвет заливки в формате 'RRGGBB' или 'none' (необязательный) | ||
| alignment_horizontal | pgxls.alignment_horizontal | - | выравнивание по горизонтали (необязательный) | ||
| alignment_indent | int | - | отступ при выравнивании (необязательный) | ||
| alignment_vertical | pgxls.alignment_vertical | - | выравнивание по вертикали (необязательный) | ||
| alignment_text_wrap | boolean | - | перенос текста при выравнивании (необязательный) |
Примеры использования:
call pgxls.format_cell(xls, 2, format_code=>'dd.mm.yyyy hh:mm'); call pgxls.format_cell(xls, font_size=>10, font_bold=>true, fill_foreground_color=>'AA00EE'); call pgxls.format_cell(xls, border_around=>'thin', alignment_horizontal=>'left', alignment_indent=>2);
pgxls.format_row
Процедура форматирует все ячейки строки
Параметры:
| xls | pgxls.xls | - | документ | ||
| font_name | varchar | - | имя шрифта (необязательный) | ||
| font_size | int | - | размер шрифта (необязательный) | ||
| font_bold | boolean | - | жирный шрифт (необязательный) | ||
| font_color | varchar(6) | - | цвет шрифта в формате 'RRGGBB' или 'auto' (необязательный) | ||
| border | pgxls.border_line | - | граница всех сторон (необязательный) | ||
| fill_foreground_color | varchar(6) | - | цвет заливки в формате 'RRGGBB' или 'none' (необязательный) | ||
| alignment_horizontal | pgxls.alignment_horizontal | - | выравнивание по горизонтали (необязательный) | ||
| alignment_vertical | pgxls.alignment_vertical | - | выравнивание по вертикали (необязательный) | ||
| alignment_text_wrap | boolean | - | перенос текста при выравнивании (необязательный) |
Примеры использования:
call pgxls.format_row(xls, font_size=>10, font_bold=>true, fill_foreground_color=>pgxls.color$light_green()); call pgxls.format_row(xls, border=>'thin', alignment_horizontal=>'center');
pgxls.get_cell_height
Функция возвращает высоту ячейки, обычно используется при явном указании высоты строки при вертикальном объединении ячеек
Параметры:
| line_count | int | - | кол-во строк (необязательный, по умолчанию 1) | ||
| font_size | int | - | размер шрифта (необязательный) |
Пример использования:
call pgxls.set_row_height(xls, pgxls.get_cell_height());
pgxls.get_column_name
Функция возвращает высоту ячейки, обычно используется при явном указании высоты строки при вертикальном объединении ячеек
Параметр:
| column_ | int | - | столбец |
Пример использования:
column_name := pgxls.get_column_name(5);
pgxls.get_file
Функция формирует и возвращает файл (тип bytea)
Параметр:
| xls | pgxls.xls | - | документ |
Пример использования:
return pgxls.get_file(xls);
pgxls.get_file_by_query
Функция создает документ с данными по SQL-запросу, формирует и возвращает файл (тип bytea)
Параметр:
| query | text | - | SQL-запрос |
Пример использования:
select pgxls.get_file_by_query('select * from pg_class');
pgxls.get_file_parts_query
Функция формирует файл и возвращает SQL-запрос (тип varchar) для получения файла по частям.Используется при получении больших файлов, после вычитывания файла необходимо вызвать процедуру pgxls.clear_file_parts
Параметр:
| xls | pgxls.xls | - | документ |
Пример использования:
return query execute pgxls.get_file_parts_query(xls);
pgxls.get_format_code_boolean
Функция формирует логический код формата (тип varchar)
Параметры:
| text_true | varchar | - | текст для истинного значения (необязательный) | ||
| text_false | varchar | - | текст для ложного значения (необязательный) | ||
| text_null | varchar | - | текст для неопределенного значения (необязательный) |
Примеры использования:
format1 := pgxls.get_format_code_boolean('Yes', 'No', 'Null'); format2 := pgxls.get_format_code_boolean(text_true=>'1');
pgxls.get_format_code_numeric
Функция формирует логический код формата (тип varchar)
Параметры:
| decimal_places | int | - | кол-во знаков после запятой | ||
| thousands_separated | boolean | - | разделение тысяч (необязательный) |
Примеры использования:
format1 := pgxls.get_format_code_numeric(3);
format2 := pgxls.get_format_code_numeric(decimal_places=>0, thousands_separated=>true);
pgxls.merge_cells
Процедура объединяет ячейки
Параметры:
| xls | pgxls.xls | - | документ | ||
| column_count | int | - | кол-во столбцов (необязательный, по умолчанию 1) | ||
| row_count | int | - | кол-во строк (необязательный, по умолчанию 1) | ||
| column_ | int | - | столбец, с которого начинается объединение (необязательный, по умолчанию текущий) |
Примеры использования:
call pgxls.merge_cells(xls, 5); call pgxls.merge_cells(xls, row_count=>5); call pgxls.merge_cells(xls, 4, 4, 6);
pgxls.next_column
Процедура увеличивает текущий столбец
Параметр:
| xls | pgxls.xls | - | документ |
Пример использования:
call pgxls.next_column(xls);
pgxls.page_paper_format
Тип enum, определяет формат бумаги.
Значения: A3,A4,A5
pgxls.page_orientation
Тип enum, определяет ориентацию страницы.
Значения: portrait,landscape
pgxls.pgxls_version
Функция возвращает версию инструмента
pgxls.put_cell
Процедура заполняет ячейку: устанавливает значение и позволяет менять параметры стиля, присваивает значение текущего столбца
Параметры:
| xls | pgxls.xls | - | документ | ||
| value | anyelement | - | значение | ||
| column_ | int | - | столбец (необязательный, по умолчанию текущий) | ||
| format_code | varchar | - | код формата (необязательный) | ||
| font_name | varchar | - | имя шрифта (необязательный) | ||
| font_size | int | - | размер шрифта (необязательный) | ||
| font_bold | boolean | - | жирный шрифт (необязательный) | ||
| font_italic | boolean | - | шрифт курсивом (необязательный) | ||
| font_underline | boolean | - | подчеркнутый шрифт (необязательный) | ||
| font_strike | boolean | - | зачеркнутый шрифт (необязательный) | ||
| font_color | varchar(6) | - | цвет шрифта в формате 'RRGGBB' или 'auto' (необязательный) | ||
| border_around | pgxls.border_line | - | граница всех сторон (необязательный) | ||
| border_left | pgxls.border_line | - | граница слева (необязательный) | ||
| border_top | pgxls.border_line | - | граница сверху (необязательный) | ||
| border_right | pgxls.border_line | - | граница справа (необязательный) | ||
| border_bottom | pgxls.border_line | - | граница снизу (необязательный) | ||
| fill_foreground_color | varchar(6) | - | цвет заливки в формате 'RRGGBB' или 'none' (необязательный) | ||
| alignment_horizontal | pgxls.alignment_horizontal | - | выравнивание по горизонтали (необязательный) | ||
| alignment_indent | int | - | отступ при выравнивании (необязательный) | ||
| alignment_vertical | pgxls.alignment_vertical | - | выравнивание по вертикали (необязательный) | ||
| alignment_text_wrap | boolean | - | перенос текста при выравнивании (необязательный) |
Примеры использования:
call pgxls.put_cell(xls, 123); call pgxls.put_cell(xls, now(), 2, format_code=>'dd.mm.yyyy hh:mm'); call pgxls.put_cell(xls, 'Hello'::text, font_size=>10, font_bold=>true, fill_foreground_color=>'AA00AA'); call pgxls.put_cell(xls, true, border_around=>'thin', alignment_horizontal=>'left', alignment_indent=>2);
pgxls.put_cell_[type]
Процедура устанавливет значение ячейки, присваивает значение текущего столбца.
Используется при создании больших документов, т.к. не требуется проверка типа и изменение стиля. В остальных случая рекомендуется использовать pgxls.put_cell
Параметры:
| type | - | тип данных: text,integer,numeric,date,time,timestamp,boolean | |||
| xls | pgxls.xls | - | документ | ||
| value | [type] | - | значение | ||
| column_ | int | - | столбец (необязательный, по умолчанию текущий) |
Примеры использования:
call pgxls.put_cell_integer(xls, 123); call pgxls.put_cell_numeric(xls, 123.4567, 2); call pgxls.put_cell_text(xls, 'Hello'); call pgxls.put_cell_boolean(xls, false, column_=>4);
pgxls.save_file
Процедура сохраняет документ в файл на сервере, выполняется под суперпользователем (необходимы права на выполнение lo_export)
Параметры:
| xls | pgxls.xls | - | документ | ||
| filepath | varchar | - | абсолютный путь к файлу |
Примеры использования:
call pgxls.save_file(xls, '/tmp/myreport.xlsx'); call pgxls.save_file(xls, 'C:\Reports\report1.xlsx');
pgxls.save_file_by_query
Процедура создает документ по SQL-запросу и сохраняет его в файл на сервере, выполняется под суперпользователем (необходимы права на выполнение lo_export)
Параметры:
| filepath | varchar | - | абсолютный путь к файлу | ||
| query | text | - | SQL-запрос |
Примеры использования:
call pgxls.save_file_by_query('/tmp/pg_class.xlsx', 'select * from pg_class'); call pgxls.save_file_by_query('C:\Reports\customers.xlsx', 'select name,code,is_vip,amount from customers order by name');
pgxls.set_all_format
Процедура устанавливает формат ячеек для всех столбцов и всех типов данных
Параметры:
| xls | pgxls.xls | - | документ | ||
| column_ | int | - | столбец (необязательный, по умолчанию текущий) | ||
| font_name | varchar | - | имя шрифта (необязательный) | ||
| font_size | int | - | размер шрифта (необязательный) | ||
| font_bold | boolean | - | жирный шрифт (необязательный) | ||
| font_color | varchar(6) | - | цвет шрифта в формате 'RRGGBB' или 'auto' (необязательный) | ||
| border | pgxls.border_line | - | граница всех сторон (необязательный) | ||
| fill_foreground_color | varchar(6) | - | цвет заливки в формате 'RRGGBB' или 'none' (необязательный) | ||
| alignment_horizontal | pgxls.alignment_horizontal | - | выравнивание по горизонтали (необязательный) | ||
| alignment_vertical | pgxls.alignment_vertical | - | выравнивание по вертикали (необязательный) | ||
| alignment_text_wrap | boolean | - | перенос текста при выравнивании (необязательный) |
Примеры использования:
call pgxls.set_all_format(xls, font_size=>10, font_bold=>true, fill_foreground_color=>pgxls.color$light_green()); call pgxls.set_all_format(xls, border=>'thin', alignment_horizontal=>'center');
pgxls.set_column_current
Процедура устанавливет текущий столбец
Параметры:
| xls | pgxls.xls | - | документ | ||
| column_ | int | - | столбец |
Пример использования:
call pgxls.set_column_current(xls, 5);
pgxls.set_column_format_[type]
Процедура устанавливет формат ячеек столбца в зависимости от типа данных.
Если тип данных не указан (процедура pgxls.set_column_format), то формат устанавливается для всех типов
Параметры:
| type | - | тип данных: text,integer,numeric,date,time,timestamp,boolean | |||
| xls | pgxls.xls | - | документ | ||
| column_ | int | - | столбец (необязательный, по умолчанию текущий) | ||
| format_code | varchar | - | код формата (необязательный, только для процедур с типом) | ||
| font_name | varchar | - | имя шрифта (необязательный) | ||
| font_size | int | - | размер шрифта (необязательный) | ||
| font_bold | boolean | - | жирный шрифт (необязательный) | ||
| font_italic | boolean | - | шрифт курсивом (необязательный) | ||
| font_underline | boolean | - | подчеркнутый шрифт (необязательный) | ||
| font_strike | boolean | - | зачеркнутый шрифт (необязательный) | ||
| font_color | varchar(6) | - | цвет шрифта в формате 'RRGGBB' или 'auto' (необязательный) | ||
| border_around | pgxls.border_line | - | граница всех сторон (необязательный) | ||
| border_left | pgxls.border_line | - | граница слева (необязательный) | ||
| border_top | pgxls.border_line | - | граница сверху (необязательный) | ||
| border_right | pgxls.border_line | - | граница справа (необязательный) | ||
| border_bottom | pgxls.border_line | - | граница снизу (необязательный) | ||
| fill_foreground_color | varchar(6) | - | цвет заливки в формате 'RRGGBB' или 'none' (необязательный) | ||
| alignment_horizontal | pgxls.alignment_horizontal | - | выравнивание по горизонтали (необязательный) | ||
| alignment_indent | int | - | отступ при выравнивании (необязательный) | ||
| alignment_vertical | pgxls.alignment_vertical | - | выравнивание по вертикали (необязательный) | ||
| alignment_text_wrap | boolean | - | перенос текста при выравнивании (необязательный) |
Примеры использования:
call pgxls.set_column_format_numeric(xls, font_size=>10, font_bold=>true, fill_foreground_color=>pgxls.color$light_green()); call pgxls.set_column_format(xls, border_around=>'thin', alignment_horizontal=>'center'); call pgxls.set_column_format_numeric (xls, 10, format_code=>'0.000'); call pgxls.set_column_format_timestamp(xls, 11, format_code=>'dd.mm.yyyy hh:mm'); call pgxls.set_column_format_integer(xls, 10, format_code=>pgxls.get_format_code_numeric(decimal_places=>0, thousands_separated=>true)); call pgxls.set_column_format_boolean(xls, 11, format_code=>pgxls.get_format_code_boolean('Yes', 'No', 'Null'));
pgxls.set_page_paper
Процедура устанавливет параметры бумаги: формат и ориентацию
Параметры:
| xls | pgxls.xls | - | документ | ||
| format | pgxls.page_paper_format | - | формат (необязательный) | ||
| orientation | pgxls.page_orientation | - | ориентация (необязательный) |
Примеры использования:
call pgxls.set_page_paper(xls, 'A3', 'portrait'); call pgxls.set_page_paper(xls, orientation=>'landscape');
pgxls.set_page_header
Процедура устанавливет заголовок страницы
Параметры:
| xls | pgxls.xls | - | документ | ||
| header | text | - | текст заголовка, значение null удаляет заголовок | ||
| alignment | pgxls.alignment_horizontal | - | расположение по горизонтали: left,center,right (необязательный, по умолчанию right) | ||
| font_name | varchar | - | имя шрифта (необязательный, по умолчанию Arial) | ||
| font_size | int | - | размер шрифта (необязательный, по умолчанию 6) |
Примеры использования:
call pgxls.set_page_header(xls, 'MyReport, page &P of &N', alignment=>'center', font_size=>12); call pgxls.set_page_header(xls, null);
pgxls.set_page_margins
Процедура устанавливет отступы страницы
Параметры:
| xls | pgxls.xls | - | документ | ||
| left_ | numeric | - | отступ слева (необязательный) | ||
| top | numeric | - | отступ сверху (необязательный) | ||
| right_ | numeric | - | отступ справа (необязательный) | ||
| bottom | numeric | - | отступ снизу (необязательный) |
Примеры использования:
call pgxls.set_page_margins(xls, 0.15, 0.2, 0.15, 0.4); call pgxls.set_page_margins(xls, bottom=>2);
pgxls.set_page_rows_repeat
Процедура устанавливет строки, повторяющиеся на каждой странице
Параметры:
| xls | pgxls.xls | - | документ | ||
| row_from | int | - | номер начальной строки, значение null отменяет повторение строк | ||
| row_to | int | - | номер конечной строки (необязательный, по умолчанию row_from) |
Примеры использования:
call pgxls.set_page_rows_repeat(xls, 1); call pgxls.set_page_rows_repeat(xls, 3, 5);
pgxls.set_row_height
Процедура устанавливет высоту текущей строки
Параметры:
| xls | pgxls.xls | - | документ | ||
| height | int | - | высота |
Пример использования:
call pgxls.set_row_height(xls, 20);
pgxls.xls
Составной тип, содержит информацию по документу