Usage method
PL/pgSQL function is created that does: created document (pgxls.create), adds rows and sheets, sets document cells (pgxls.add_row, pgxls.add_sheet), fills cells (pgxls.put_cell), builds and returns file (pgxls.get_file).
Column count and their width are determined when creating a document or adding a sheet. For each column, can override style depending on data type using procedures pgxls.set_column_format_[type]. The pgxls.set_all_format procedure sets the formatting for all columns and all data types. The formatting set by these procedures only applies to subsequent rows; it does not change the formatting for the current or previous rows.
The pgxls.put_cell procedure sets the cell value and format depending on the value type (set for column), has optional parameters to change formatting properties.
The pgxls.format_cell and pgxls.format_row procedures change the formatting of the current cell and row.
For ease of use, the current column is determined, which is reset when a row is added and incremented when setting a cell value without specifying a column. Procedures pgxls.set_column_current and pgxls.next_column change current column explicitly.
When adding a row or sheet, the current row is written to temporary table and cannot be changed. The row height is calculated automatically, but can be set manually using procedure pgxls.set_row_height.
Print setup sets the paper size and orientation, header, repeating rows, margins using procedures pgxls.set_page_paper, pgxls.set_page_header, pgxls.set_page_rows_repeat, pgxls.set_page_margins.
When creating large files, must use procedures pgxls.set_cell_[type] for sets cells and get file in parts (pgxls.get_file_parts_query).
There are functions pgxls.get_file_by_query and pgxls.add_sheet_by_query to get file and add sheet by SQL query.
Procedures pgxls.save_file and pgxls.save_file_by_query save the document to a file on the server, called as superuser.
Not allowed to access internal fields of pgxls.xls and use procedures and types that start with underscore "_"
Security
Stored procedures are created with option security invoker, passed through parameters dynamic SQL queries are called with these rights.
If the developer needs to save files on server, the superuser creates wrapper with option security definer and checks.
Example:
-- 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();
pgxls.add_row
Procedure adds one or more rows to the sheet, sets current column to zero
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| count | int | row count | 1 |
Examples
call pgxls.add_row(xls); call pgxls.add_row(xls, 5);
pgxls.add_row_texts
Procedure adds row with text values, allows to change the style parameters
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| texts | texts | cell values | ✔ | |
| font_bold | boolean | bold font | ||
| fill_foreground_color | varchar(6) | fill color in format 'RRGGBB' or 'none' | ||
| alignment_horizontal | pgxls.alignment_horizontal | horizontal alignment |
Examples
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
Procedure adds new sheet to document.
If the columns_captions parameter is specified, a row with column captions is added to the sheet, and the pgxls.format_row procedure can be applied to it
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| columns_widths | int[] | columns widths | ✔ | |
| columns_captions | text[] | columns captions | ||
| name | varchar | sheet name |
Example
call pgxls.add_sheet(xls, array[10,20], array['ID','Name'], 'Sheet2');
pgxls.add_sheet_by_query
The procedure executes an SQL query and adds a new sheet with the received data.
Not recommended for use in report functions, as it is a wrapper around pgxls.add_sheet for quick use and does not support formatting
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| query | text | SQL query | ✔ | |
| name | varchar | name |
Example
call pgxls.add_sheet_by_query(xls, 'select * from pg_class', 'Query data');
pgxls.alignment_horizontal
Enum type, defines horizontal alignment
Values: left,center,right,justify,fill,distributed
pgxls.alignment_vertical
Enum type, defines vertical alignment
Values: top,center,bottom,justify,distributed
pgxls.border_line
Enum type, defines border line
Values: none,thin,thick,dashed,dotted,dashDot,dashDotDot,double
pgxls.clear_file_parts
Procedure removes file from temporary table, used together with pgxls.get_file_parts_query
Parameter
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ |
pgxls.color$[name]
Function returns color (type varchar(6)) by name
Parameter
| Name | Type | Description | Required | Default |
| name | color name: light_red,light_green,light_blue,light_gray,dark_red,dark_green,dark_blue,dark_gray | ✔ |
Examples
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.create
The function creates, initializes and returns a document (type pgxls.xls).
If the columns_widths parameter is not specified, the document is created without a sheet and the first sheet must be added manually.
If the columns_captions parameter is specified, a row with column captions is added to the sheet, and the pgxls.format_row procedure can be applied to it
Parameters
| Name | Type | Description | Required | Default |
| columns_widths | int[] | column widths of the first sheet | ||
| columns_captions | text[] | column captions | ||
| sheet_name | varchar | sheet name |
Examples
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.font_name$[family]
Function returns name (type varchar) by font family
Parameter
| Name | Type | Description | Required | Default |
| family | font family: sans,sans_serif,monospace | ✔ |
Examples
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.format_cell
The procedure formats a cell
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| column_ | int | column | current | |
| format_code | varchar | format code | ||
| font_name | varchar | font name | ||
| font_size | int | font size | ||
| font_bold | boolean | bold font | ||
| font_italic | boolean | italic font | ||
| font_underline | boolean | underline font | ||
| font_strike | boolean | strike font | ||
| font_color | varchar(6) | font color in format 'RRGGBB' or 'auto' | ||
| border_around | pgxls.border_line | border around | ||
| border_left | pgxls.border_line | border left | ||
| border_top | pgxls.border_line | border top | ||
| border_right | pgxls.border_line | border right | ||
| border_bottom | pgxls.border_line | border bottom | ||
| fill_foreground_color | varchar(6) | fill color in format 'RRGGBB' or 'none' | ||
| alignment_horizontal | pgxls.alignment_horizontal | horizontal alignment | ||
| alignment_indent | int | indent on alignment | ||
| alignment_vertical | pgxls.alignment_vertical | vertical alignment | ||
| alignment_text_wrap | boolean | wrap text on alignment |
Examples
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
The procedure formats all cells in the current row
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| font_name | varchar | font name | ||
| font_size | int | font size | ||
| font_bold | boolean | bold font | ||
| font_color | varchar(6) | font color in format 'RRGGBB' or 'auto' | ||
| border | pgxls.border_line | border around | ||
| fill_foreground_color | varchar(6) | fill color in format 'RRGGBB' or 'none' | ||
| alignment_horizontal | pgxls.alignment_horizontal | horizontal alignment | ||
| alignment_vertical | pgxls.alignment_vertical | vertical alignment | ||
| alignment_text_wrap | boolean | wrap text on alignment |
Examples
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
Function returns the cell height, usually used when specifying the row height when vertically merging cells
Parameters
| Name | Type | Description | Required | Default |
| line_count | int | line count | 1 | |
| font_size | int | font size |
Example
call pgxls.set_row_height(xls, pgxls.get_cell_height());
pgxls.get_column_name
Function returns column name (type varchar) by number
Parameter
| Name | Type | Description | Required | Default |
| column_ | int | column | ✔ |
Example
column_name := pgxls.get_column_name(5);
pgxls.get_file
Function builds and returns file (type bytea)
Parameter
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ |
Example
return pgxls.get_file(xls);
pgxls.get_file_by_query
Function creates document by SQL query, builds and returns file (type bytea)
Parameter
| Name | Type | Description | Required | Default |
| query | text | SQL query | ✔ |
Example
select pgxls.get_file_by_query('select * from pg_class');
pgxls.get_file_parts_query
Function builds file and returns SQL query (varchar type) to get file in parts. Used for large files, after receiving file, need to call procedure pgxls.clear_file_parts
Parameter
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ |
Example
return query execute pgxls.get_file_parts_query(xls);
pgxls.get_format_code_boolean
The function builds a format code (varchar type) for a logical value
Parameters
| Name | Type | Description | Required | Default |
| text_true | varchar | text for true value | ||
| text_false | varchar | text for false value | ||
| text_null | varchar | text for null value |
Examples
format1 := pgxls.get_format_code_boolean('Yes', 'No', 'Null'); format2 := pgxls.get_format_code_boolean(text_true=>'1');
pgxls.get_format_code_numeric
The function builds a format code (varchar type) for a numeric value
Parameters
| Name | Type | Description | Required | Default |
| decimal_places | int | decimal places | ✔ | |
| thousands_separated | boolean | thousands separated |
Examples
format1 := pgxls.get_format_code_numeric(3);
format2 := pgxls.get_format_code_numeric(decimal_places=>0, thousands_separated=>true);
pgxls.merge_cells
Procedure merges cells
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| column_count | int | column count | 1 | |
| row_count | int | row count | 1 | |
| column_ | int | column from which the merge begins | current |
Examples
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
Procedure increases current column
Parameter
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ |
Example
call pgxls.next_column(xls);
pgxls.page_paper_format
Enum type, defines paper format
Values: A3,A4,A5
pgxls.page_orientation
Enum type, defines page orientation
Values: portrait,landscape
pgxls.pgxls_version
Function returns tool version
pgxls.put_cell
The procedure fills the cell: sets the value and allows change the style parameters, assigns current column
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| value | anyelement | value | ✔ | |
| column_ | int | column | current | |
| format_code | varchar | format code | ||
| font_name | varchar | font name | ||
| font_size | int | font size | ||
| font_bold | boolean | bold font | ||
| font_italic | boolean | italic font | ||
| font_underline | boolean | underline font | ||
| font_strike | boolean | strike font | ||
| font_color | varchar(6) | font color in format 'RRGGBB' or 'auto' | ||
| border_around | pgxls.border_line | border around | ||
| border_left | pgxls.border_line | border left | ||
| border_top | pgxls.border_line | border top | ||
| border_right | pgxls.border_line | border right | ||
| border_bottom | pgxls.border_line | border bottom | ||
| fill_foreground_color | varchar(6) | fill color in format 'RRGGBB' or 'none' | ||
| alignment_horizontal | pgxls.alignment_horizontal | horizontal alignment | ||
| alignment_indent | int | indent on alignment | ||
| alignment_vertical | pgxls.alignment_vertical | vertical alignment | ||
| alignment_text_wrap | boolean | wrap text on alignment |
Examples
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]
Procedure sets cell value, assigns current column.
Used for large documents (no type checking and no style change). In other cases, recommended to use pgxls.put_cell
Parameters
| Name | Type | Description | Required | Default |
| type | data type: text,integer,numeric,date,time,timestamp,boolean | ✔ | ||
| xls | pgxls.xls | document | ✔ | |
| value | [type] | value | ✔ | |
| column_ | int | column | current |
Examples
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
The procedure saves the document to file on server, calls as superuser (privilege to execute lo_export is required)
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| filepath | varchar | absolute path to file | ✔ |
Examples
call pgxls.save_file(xls, '/tmp/myreport.xlsx'); call pgxls.save_file(xls, 'C:\Reports\report1.xlsx');
pgxls.save_file_by_query
The procedure creates document using an SQL query and saves it to file on server, calls as superuser (privilege to execute lo_export is required)
Parameters
| Name | Type | Description | Required | Default |
| filepath | varchar | absolute path to file | ✔ | |
| query | text | SQL query | ✔ |
Examples
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
The procedure for subsequent rows sets the cell format for all columns and all data types
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| font_name | varchar | font name | ||
| font_size | int | font size | ||
| font_bold | boolean | bold font | ||
| font_color | varchar(6) | font color in format 'RRGGBB' or 'auto' | ||
| border | pgxls.border_line | border around | ||
| fill_foreground_color | varchar(6) | fill color in format 'RRGGBB' or 'none' | ||
| alignment_horizontal | pgxls.alignment_horizontal | horizontal alignment | ||
| alignment_vertical | pgxls.alignment_vertical | vertical alignment | ||
| alignment_text_wrap | boolean | wrap text on alignment |
Examples
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
Procedure sets current column
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| column_ | int | column | ✔ |
Example
call pgxls.set_column_current(xls, 5);
pgxls.set_column_format_[type]
The procedure sets the format of the column cells for subsequent rows depending on the data type.
If data type not specified (procedure pgxls.set_column_format), then format set for all types
Parameters
| Name | Type | Description | Required | Default |
| type | data type: text,integer,numeric,date,time,timestamp,boolean | ✔ | ||
| xls | pgxls.xls | document | ✔ | |
| column_ | int | column | current | |
| format_code | varchar | format code | for procedure pgxls.set_column_format | |
| font_name | varchar | font name | ||
| font_size | int | font size | ||
| font_bold | boolean | bold font | ||
| font_italic | boolean | italic font | ||
| font_underline | boolean | underline font | ||
| font_strike | boolean | strike font | ||
| font_color | varchar(6) | font color in format 'RRGGBB' or 'auto' | ||
| border_around | pgxls.border_line | border around | ||
| border_left | pgxls.border_line | border left | ||
| border_top | pgxls.border_line | border top | ||
| border_right | pgxls.border_line | border right | ||
| border_bottom | pgxls.border_line | border bottom | ||
| fill_foreground_color | varchar(6) | fill color in format 'RRGGBB' or 'none' | ||
| alignment_horizontal | pgxls.alignment_horizontal | horizontal alignment | ||
| alignment_indent | int | indent on alignment | ||
| alignment_vertical | pgxls.alignment_vertical | vertical alignment | ||
| alignment_text_wrap | boolean | wrap text on alignment |
Examples
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
Procedure sets paper parameters: format and orientation
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| format | pgxls.page_paper_format | format | ||
| orientation | pgxls.page_orientation | orientation |
Examples
call pgxls.set_page_paper(xls, 'A3', 'portrait'); call pgxls.set_page_paper(xls, orientation=>'landscape');
pgxls.set_page_header
Procedure sets page header
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| header | text | header text, null value removes header | ✔ | |
| alignment | pgxls.alignment_horizontal | horizontal position: left,center,right | right | |
| font_name | varchar | font_name | Arial | |
| font_size | int | font size | 6 |
Examples
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
Procedure sets page margins
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| left_ | numeric | left margin | ||
| top | numeric | top margin | ||
| right_ | numeric | right margin | ||
| bottom | numeric | bottom margin |
Examples
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
Procedure sets rows that are repeated on each page
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| row_from | int | starting row number, null value disables repeating | ||
| row_to | int | ending row number | row_from |
Examples
call pgxls.set_page_rows_repeat(xls, 1); call pgxls.set_page_rows_repeat(xls, 3, 5);
pgxls.set_row_height
Procedure sets height of current row
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| height | int | height | ✔ |
Example
call pgxls.set_row_height(xls, 20);
pgxls.xls
Composite type, document data