PGXLS: Построитель отчетов и экспорт в Excel из PostgreSQL

Принцип использования

Безопасность

Примеры
Быстрый старт
Форматирование и страница
SQL-запрос и таблица
Большой файл

Документ и листы
create
add_sheet
add_sheet_by_query
get_file
get_file_parts_query
clear_file_parts
get_file_by_query
save_file
save_file_by_query

Строки и ячейки
add_row
add_row_texts
put_cell
put_cell_[type]
merge_cells
next_column
set_column_current

Форматирование
set_all_format
set_column_format_[type]
format_row
set_row_height
format_cell

Страница и печать
set_page_paper
set_page_margins
set_page_header
set_page_rows_repeat

Типы данных
page_paper_format
page_orientation
alignment_horizontal
alignment_vertical
border_line
xls

Вспомогательные функции
get_format_code_boolean
get_format_code_numeric
get_column_name
get_cell_height
font_name$[family]
color$[name]
pgxls_version

Принцип использования

Для создания файла Excel на процедурном языке PL/pgSQL создается функция, которая выполняет следующее: определяет документ (pgxls.create), заполняет его (pgxls.add_row, pgxls.put_cell, pgxls.add_sheet), формирует и возвращает файл (pgxls.get_file).

Кол-во столбцов и их ширина определяются при создании документа или добавлении листа. Для каждого столбца можно переопределить формат в зависимости от типа данных с помощью процедур pgxls.set_column_format_[type]. Процедура pgxls.set_all_format устанавливает форматирование для всех столбцов и всех типов данных. Установленное указанными процедурами форматирование действует только для последующих строк, для текущей и предыдущих строк форматирование не меняется.

Процедура pgxls.put_cell устанавливает значение ячейки и формат в зависимости от типа значения (настраивается для столбца), имеет необязательные параметры для изменения свойств форматирования.

Процедуры pgxls.format_cell и pgxls.format_row изменяют форматирование текущей ячейки и строки соответственно.

Для удобства использования определяется текущий столбец, который сбрасывается при добавлении строки и увеличивается при установке значения поля без указания столбца. При установке значения ячейки с указанием столбца текущий столбец переопределяется на указанный. При помощи процедур 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.put_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 !~ '^[\w]+.xlsx$' then
    raise exception 'Invalid file name';   
  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();

Быстрый старт (простой пример)

-- 1. Создаем и получаем файл(bytea) по SQL-запросу
select pgxls.get_file_by_query('select * from pg_tables');

-- 2. Сохраняем Excel-файл на сервере по SQL-запросу
call pgxls.save_file_by_query('/tmp/top_relations_by_size.xlsx', 'select oid,relname,pg_relation_size(oid) from pg_class order by 3 desc limit 10');

-- 3. Создаем функцию, возвращающую файл(bytea)
create or replace function excel_top_relations_by_size() returns bytea language plpgsql as $$
declare 
  rec record;
  xls pgxls.xls; 
begin
  -- Создаем документ, в параметрах указываем ширины и заголовки столбцов      
  xls := pgxls.create(array[10,80,15], array['oid','Name','Size, bytes']);
  -- В цикле выбираем данные по запросу
  for rec in
    select oid,relname,pg_relation_size(oid) size from pg_class order by 3 desc limit 10    
  loop
    -- Добавляем строку
    call pgxls.add_row(xls);
    -- В ячейки устанавливаем данные из запроса
    call pgxls.put_cell(xls, rec.oid);      
    call pgxls.put_cell(xls, rec.relname);   
    call pgxls.put_cell(xls, rec.size);
  end loop;  
  -- Возвращаем файл(bytea)
  return pgxls.get_file(xls);      
end
$$;
-- Получаем файл
select excel_top_relations_by_size();

Форматирование различными способами, настройка печати

-- Create function that returns file
create or replace function example.excel_format_and_page() returns bytea language plpgsql as $$
declare 
  xls pgxls.xls; 
begin
  -- Create excel document without sheet 
  xls := pgxls.create();
  -- Add the first required sheet named "Styles" with one column of 30 characters wide
  call pgxls.add_sheet(xls, array[30], name=>'Styles');
  -- Set value and style of cell through universal procedure
  call pgxls.put_cell(xls, 'Example full'::text, font_name=>'Times New Roman', font_size=>24, font_color=>'FF0000');
  -- Set value and style of cell through base procedures
  call pgxls.add_row(xls, 2);
  call pgxls.put_cell_timestamp(xls, now()::timestamp);
  call pgxls.format_cell(xls, border_around=>'thin');
  --
  call pgxls.add_row(xls, 2);
  call pgxls.put_cell_integer(xls, 123);  
  call pgxls.format_cell(xls, alignment_horizontal=>'left');
  --
  call pgxls.add_row(xls, 2);
  call pgxls.put_cell_numeric(xls, 1234567.89);  
  call pgxls.format_cell(xls, format_code=>pgxls.get_format_code_numeric(3, true), font_name=>pgxls.font_name$monospace(), font_bold=>true);  
  --------------------------------------------------------------------------------------------------------------------------------
  -- Add sheet named "Columns"
  call pgxls.add_sheet(xls, array[10,15,50], array['x','x/3','md5(x)'], 'Columns');
  -- Set format of column for numeric type 
  call pgxls.set_column_format_numeric(xls, 2, format_code=>'0.0000', font_name=>pgxls.font_name$sans_serif());
  -- Set alignment of column for all types
  call pgxls.set_column_format(xls, 3, alignment_horizontal=>'center');
  -- Set cell values, style defined by column and data type
  for x in 1..10 loop
    call pgxls.add_row(xls);
    call pgxls.put_cell(xls, x);
    call pgxls.put_cell(xls, x/3.0);
    call pgxls.put_cell(xls, md5(x::text));
  end loop;
  --------------------------------------------------------------------------------------------------------------------------------
  -- Add sheet with merged cells
  call pgxls.add_sheet(xls, array_fill(10, array[10]), null, 'Merge cells');
  for row in 1..10 loop
    call pgxls.add_row(xls);
    for col in 1..10 loop      
      call pgxls.put_cell(xls, row||','||col);
      if row=2 and col=2 then call pgxls.merge_cells(xls, 5/*column_count*/); call pgxls.format_cell(xls, fill_foreground_color=>pgxls.color$light_red());   end if;
      if row=4 and col=4 then call pgxls.merge_cells(xls, row_count=>5);      call pgxls.format_cell(xls, fill_foreground_color=>pgxls.color$light_green()); end if;
    end loop;    
    if row=6 then call pgxls.merge_cells(xls, 4, 4, 6); call pgxls.format_cell(xls, fill_foreground_color=>pgxls.color$light_blue(), column_=>6); end if;
  end loop;
  --------------------------------------------------------------------------------------------------------------------------------
  -- Add sheet with wrap text
  call pgxls.add_sheet(xls, array[40,30], name=>'Wrap text');
  call pgxls.set_column_format(xls, 1, alignment_horizontal=>'justify', alignment_text_wrap=>true);
  call pgxls.set_column_format(xls, 2, alignment_text_wrap=>true); 
  -- Text without line feed
  call pgxls.put_cell_text(xls, 'A database management system used to maintain relational databases is a relational database management system (RDBMS)');
  call pgxls.put_cell(xls, 'Row height calculated with assumptions and may be not optimal, usually line count is slightly larger'::text, font_size=>8); 
  -- Text with line feed <LF>
  call pgxls.add_row(xls,2);  
  call pgxls.put_cell_text(xls, 
    'PostgreSQL is a powerful, open source object-relational database system with over 35 years of active<LF>'|| chr(10) ||
    'development  that has earned it a strong reputation for reliability, feature robustness, and performance' 
  );
  --------------------------------------------------------------------------------------------------------------------------------
  -- Add sheet with print setup
  call pgxls.add_sheet(xls, array[10,15,10,60], name=>'Print setup');
  call pgxls.set_page_paper(xls, format=>'A5', orientation=>'landscape');
  call pgxls.set_page_header(xls, 'Example / sheet "Print setup" page &P of &N');
  call pgxls.set_page_rows_repeat(xls, 3); -- table header on each page  
  call pgxls.put_cell(xls, 'Use "Print preview"'::text, font_size=>20, alignment_horizontal=>'center');
  call pgxls.merge_cells(xls, 4);
  call pgxls.add_row(xls);
  call pgxls.set_all_format(xls, border=>'thin');
  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');
  call pgxls.set_all_format(xls, fill_foreground_color=>pgxls.color$light_gray());
  call pgxls.set_column_format_numeric(xls, 2, format_code=>'0.0000');
  call pgxls.set_column_format(xls, 4, alignment_horizontal=>'center');
  for x in 1..100 loop
    call pgxls.add_row(xls);
    call pgxls.put_cell(xls, x);
    call pgxls.put_cell(xls, sqrt(x)::numeric);
    call pgxls.put_cell(xls, x*x);
    call pgxls.put_cell(xls, md5(x::text));
  end loop;
  --------------------------------------------------------------------------------------------------------------------------------
  -- Return file  
  return pgxls.get_file(xls);      
end
$$;

-- Get file
select example.excel_format_and_page();

Вывод таблицы и данных SQL-запроса

-- Create function that returns file
create or replace function example.excel_table_and_query() returns bytea language plpgsql as $$
declare 
  xls pgxls.xls;
  rec record; 
begin
  -- Create excel document
  xls := pgxls.create();
  -- Add sheet by query
  call pgxls.add_sheet_by_query(xls, 'select * from pg_class order by 1', 'pg_class table');
  -- Formatting rows and cells is not possible because the data has already been output
  -- It is only possible to customize the current page.
  call pgxls.set_page_paper(xls, format=>'A5', orientation=>'landscape');  
  --------------------------------------------------------------------------------------------------------------------------------
  -- Add sheet 
  call pgxls.add_sheet(xls, array[10,15,50,15], array['OID','Schema','Table name','Owner'], 'Columns');
  --  Set format for new rows, current row with header not changed
  call pgxls.set_column_format_numeric(xls, 1, format_code=>pgxls.get_format_code_numeric(decimal_places=>0, thousands_separated=>true));
  call pgxls.set_column_format_numeric(xls, 4, font_size=>10);
  for rec in
      select oid,relnamespace::regnamespace as schema,
             relname                        as table_name,
             relowner::regrole::name       as owner
        from pg_class
        where relkind = 'r'
        order by 2,3    
  loop
    -- Add row
    call pgxls.add_row(xls);
    -- Set data from record into cells
    call pgxls.put_cell(xls, rec.oid);      
    call pgxls.put_cell(xls, rec.schema);   
    call pgxls.put_cell(xls, rec.table_name);
    call pgxls.put_cell(xls, rec.owner);
    --
    if rec.owner=current_user then
      call pgxls.format_row(xls, font_size=>12, font_bold=>true);
    end if;
  end loop;  
  -- Returns file (bytea type)
  return pgxls.get_file(xls);      
end
$$;
  
-- Get file
select example.excel_table_and_query();

Создание и получение файла большого размера

-- Create function that returns large file by parts
create or replace function excel_large_file() returns setof bytea language plpgsql as $$
declare 
  xls pgxls.xls; 
  v_value bigint;
begin
  xls := pgxls.create(array[80]);
  call pgxls.put_cell(xls, 'Large file'::text, font_bold=>true, font_size=>32, alignment_horizontal=>'center');
  call pgxls.add_row(xls);
  call pgxls.add_row(xls);
  call pgxls.put_cell(xls, '10 sheets * 4 columns * 100K rows = 40M cells'::text);
  for v_sheet in 1..10 loop
    call pgxls.add_sheet(xls, array[10,10,10,50], array['Sheet','Row','Value','md5'], v_sheet::text);
    call pgxls.set_column_format(xls, 4, font_name=>pgxls.font_name$monospace());
    for v_row in 1..100000 loop          
      v_value := v_sheet*v_row;
      call pgxls.add_row(xls);
      call pgxls.put_cell_integer(xls, v_sheet);     
      call pgxls.put_cell_integer(xls, v_row);      
      call pgxls.put_cell_integer(xls, v_value);
      call pgxls.put_cell_text(xls, md5(v_value::text));     
    end loop;
  end loop;
  return query execute pgxls.get_file_parts_query(xls);
  call pgxls.clear_file_parts(xls);
end
$$;

-- Get large file by parts
-- Each row is a bytea that must be read separately to avoid OutOfMemory error on the client 
-- Example, psql -Aqt -c "select excel_large_file()" | xxd -r -ps > excel_large_file.xlsx
select excel_large_file();

pgxls.create

Функция создает, инициализирует и возвращает документ (тип pgxls.xls).
Если параметр columns_widths не задан, документ создается без листа и первый лист необходимо добавить вручную.
Если задан параметр columns_captions, то на лист добавляется строка с заголовками столбцов и к ней можно применить процедуру форматирования pgxls.format_row

Параметры

Наименование	Тип	Описание	Обязательный	По умолчанию
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');
  call pgxls.format_row(xls, font_size=>10, fill_foreground_color=>pgxls.color$light_blue());

  xls := pgxls.create();
  call pgxls.add_sheet_by_query(xls, 'select * from pg_roles', 'Roles');

  ...

pgxls.add_sheet

Процедура добавляет новый лист в документ.
Если задан параметр columns_captions, то на лист добавляется строка с заголовками столбцов и к ней можно применить процедуру форматирования pgxls.format_row

Параметры

Наименование	Тип	Описание	Обязательный	По умолчанию
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

Процедура выполняет SQL-запрос и добавляет новый лист с полученными данными.
Не рекомендуется к использованию в функциях-отчетах, т.к. является оберткой к pgxls.add_sheet для быстрого использования и не предполагает форматирования

Параметры

Наименование	Тип	Описание	Обязательный	По умолчанию
xls	pgxls.xls	документ	✔
query	text	SQL-запрос	✔
name	varchar	имя листа

Пример использования

call pgxls.add_sheet_by_query(xls, 'select * from pg_class', 'Query data');

pgxls.get_file

Функция формирует и возвращает файл (тип bytea)

Параметр

Наименование	Тип	Описание	Обязательный	По умолчанию
xls	pgxls.xls	документ	✔

Пример использования

return pgxls.get_file(xls);

pgxls.get_file_parts_query

Функция формирует файл и возвращает SQL-запрос (тип varchar) для получения файла по частям. Используется при получении больших файлов, после вычитывания файла необходимо вызвать процедуру pgxls.clear_file_parts

Параметр

Наименование	Тип	Описание	Обязательный	По умолчанию
xls	pgxls.xls	документ	✔

Пример использования

return query execute pgxls.get_file_parts_query(xls);

pgxls.clear_file_parts

Процедура удаляет файл из временной таблицы, используется совместно с pgxls.get_file_parts_query

Параметр

Наименование	Тип	Описание	Обязательный	По умолчанию
xls	pgxls.xls	документ	✔

pgxls.get_file_by_query

Функция создает документ с данными по SQL-запросу, формирует и возвращает файл (тип bytea)

Параметр

Наименование	Тип	Описание	Обязательный	По умолчанию
query	text	SQL-запрос	✔

Пример использования

select pgxls.get_file_by_query('select * from pg_class');

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.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.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.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.set_column_current

Процедура устанавливает текущий столбец

Параметры

Наименование	Тип	Описание	Обязательный	По умолчанию
xls	pgxls.xls	документ	✔
column_	int	столбец	✔

Пример использования

call pgxls.set_column_current(xls, 5);

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_format_[type]

Процедура устанавливает формат ячеек столбца для последующих строк в зависимости от типа данных.
Если тип данных не указан (процедура pgxls.set_column_format), то формат устанавливается для всех типов

Параметры

Наименование	Тип	Описание	Обязательный	По умолчанию
type		тип данных: text,integer,numeric,date,time,timestamp,boolean	✔
xls	pgxls.xls	документ	✔
column_	int	столбец		текущий
format_code	varchar	код формата	только для процедуры pgxls.set_column_format
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.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.set_row_height

Процедура устанавливает высоту текущей строки

Параметры

Наименование	Тип	Описание	Обязательный	По умолчанию
xls	pgxls.xls	документ	✔
height	int	высота	✔

Пример использования

call pgxls.set_row_height(xls, 20);

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.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_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_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_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.page_paper_format

Тип enum, определяет формат бумаги.
Значения: A3,A4,A5

pgxls.page_orientation

Тип enum, определяет ориентацию страницы.
Значения: portrait,landscape

pgxls.alignment_horizontal

Тип enum, определяет выравнивание по горизонтали.
Значения: left,center,right,justify,fill,distributed

pgxls.alignment_vertical

Тип enum, определяет выравнивание по вертикали.
Значения: top,center,bottom,justify,distributed

pgxls.border_line

Тип enum, определяет внешний вид границы.
Значения: none,thin,thick,dashed,dotted,dashDot,dashDotDot,double

pgxls.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.get_column_name

Функция возвращает имя столбца по номеру

Параметр

Наименование	Тип	Описание	Обязательный	По умолчанию
column_	int	столбец	✔

Пример использования

column_name := pgxls.get_column_name(5);

pgxls.get_cell_height

Функция возвращает высоту ячейки, обычно используется при явном указании высоты строки при вертикальном объединении ячеек

Параметры

Наименование	Тип	Описание	Обязательный	По умолчанию
line_count	int	кол-во строк		1
font_size	int	размер шрифта

Пример использования

call pgxls.set_row_height(xls, pgxls.get_cell_height());

pgxls.font_name$[family]

Функция возвращает имя (тип varchar) по семейству шрифта

Параметр

Наименование	Тип	Описание	Обязательный	По умолчанию
family		семейство шрифта: sans,sans_serif,monospace	✔

Примеры использования

call pgxls.format_row(xls, font_name=>pgxls.font_name$sans_serif());
call pgxls.put_cell(xls, 123, font_name=>pgxls.font_name$monospace());

pgxls.color$[name]

Функция возвращает цвет (тип varchar(6)) по названию

Параметр

Наименование	Тип	Описание	Обязательный	По умолчанию
name		название цвета: light_red,light_green,light_blue,light_gray,dark_red,dark_green,dark_blue,dark_gray	✔

Примеры использования

call pgxls.format_cell(xls, font_color=>pgxls.color$dark_red(), fill_foreground_color=>pgxls.color$light_gray());
call pgxls.set_column_format(xls, 5, fill_foreground_color=>pgxls.color$light_green());

pgxls.pgxls_version

Функция возвращает версию инструмента

PGXLS Документация

Принцип использования

Безопасность

Примеры

Быстрый старт (простой пример)

Форматирование различными способами, настройка печати

Вывод таблицы и данных SQL-запроса

Создание и получение файла большого размера

Документ и листы

pgxls.create

pgxls.add_sheet

pgxls.add_sheet_by_query

pgxls.get_file

pgxls.get_file_parts_query

pgxls.clear_file_parts

pgxls.get_file_by_query

pgxls.save_file

pgxls.save_file_by_query

Строки и ячейки

pgxls.add_row

pgxls.add_row_texts

pgxls.put_cell

pgxls.put_cell_[type]

pgxls.merge_cells

pgxls.next_column

pgxls.set_column_current

Форматирование

pgxls.set_all_format

pgxls.set_column_format_[type]

pgxls.format_row

pgxls.set_row_height

pgxls.format_cell

Страница и печать

pgxls.set_page_paper

pgxls.set_page_margins

pgxls.set_page_header

pgxls.set_page_rows_repeat

Типы данных

pgxls.page_paper_format

pgxls.page_orientation

pgxls.alignment_horizontal

pgxls.alignment_vertical

pgxls.border_line

pgxls.xls

Вспомогательные функции

pgxls.get_format_code_boolean

pgxls.get_format_code_numeric

pgxls.get_column_name

pgxls.get_cell_height

pgxls.font_name$[family]

pgxls.color$[name]

pgxls.pgxls_version

PGXLS
Документация