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.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 procedures pgxls.format_cell, pgxls.format_row and pgxls.format_all change the formatting.
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 row or sheet, current row written to temporary table and cannot be changed. Row height 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 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
Procedure adds one or more rows to sheet, sets current column to zero
Parameters:
| xls | pgxls.xls | - | document | ||
| count | int | - | row count (optional, default 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:
| xls | pgxls.xls | - | document | ||
| texts | texts | - | cell values | ||
| font_bold | boolean | - | bold font (optional) | ||
| fill_foreground_color | varchar(6) | - | fill color in format 'RRGGBB' or 'none' (optional) | ||
| alignment_horizontal | pgxls.alignment_horizontal | - | horizontal alignment (optional) |
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
Parameters:
| xls | pgxls.xls | - | document | ||
| columns_widths | int[] | - | columns widths | ||
| columns_captions | text[] | - | columns captions (optional) | ||
| name | varchar | - | sheet name (optional) |
Example:
call pgxls.add_sheet(xls, array[10,20], array['ID','Name'], 'Sheet2');
pgxls.add_sheet_by_query
Procedure adds new sheet with SQL query data
Parameters:
| xls | pgxls.xls | - | document | ||
| query | text | - | SQL query | ||
| name | varchar | - | name (optional) |
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:
| xls | pgxls.xls | - | document |
pgxls.color$[name]
Function returns color (type varchar(6)) by name
Parameter:
| name | - | name: light_red,light_green,light_blue,light_gray,dark_red,dark_green,dark_blue,dark_gray |
Examples:
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
Function creates, initializes and returns document (type pgxls.xls)
Parameters:
| columns_widths | int[] | - | columns widths | ||
| columns_captions | text[] | - | columns captions (optional) | ||
| sheet_name | varchar | - | sheet name (optional) |
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');
pgxls.font_name$[family]
Function returns name (type varchar) by font family
Parameter:
| family | - | font family: sans,sans_serif,monospace |
Examples:
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
The procedure formats a cell
Parameters:
| xls | pgxls.xls | - | document | ||
| value | anyelement | - | value | ||
| column_ | int | - | column (optional, default current) | ||
| format_code | varchar | - | format code (optional) | ||
| font_name | varchar | - | font name (optional) | ||
| font_size | int | - | font size (optional) | ||
| font_bold | boolean | - | bold font (optional) | ||
| font_italic | boolean | - | italic font (optional) | ||
| font_underline | boolean | - | underline font (optional) | ||
| font_strike | boolean | - | strike font (optional) | ||
| font_color | varchar(6) | - | font color in format 'RRGGBB' or 'auto' (optional) | ||
| border_around | pgxls.border_line | - | border around (optional) | ||
| border_left | pgxls.border_line | - | border left (optional) | ||
| border_top | pgxls.border_line | - | border top (optional) | ||
| border_right | pgxls.border_line | - | border right (optional) | ||
| border_bottom | pgxls.border_line | - | border bottom (optional) | ||
| fill_foreground_color | varchar(6) | - | fill color in format 'RRGGBB' or 'none' (optional) | ||
| alignment_horizontal | pgxls.alignment_horizontal | - | horizontal alignment (optional) | ||
| alignment_indent | int | - | indent on alignment (optional) | ||
| alignment_vertical | pgxls.alignment_vertical | - | vertical alignment (optional) | ||
| alignment_text_wrap | boolean | - | wrap text on alignment (optional) |
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 a row
Parameters:
| xls | pgxls.xls | - | document | ||
| font_name | varchar | - | font name (optional) | ||
| font_size | int | - | font size (optional) | ||
| font_bold | boolean | - | bold font (optional) | ||
| font_color | varchar(6) | - | font color in format 'RRGGBB' or 'auto' (optional) | ||
| border | pgxls.border_line | - | border around (optional) | ||
| fill_foreground_color | varchar(6) | - | fill color in format 'RRGGBB' or 'none' (optional) | ||
| alignment_horizontal | pgxls.alignment_horizontal | - | horizontal alignment (optional) | ||
| alignment_vertical | pgxls.alignment_vertical | - | vertical alignment (optional) | ||
| alignment_text_wrap | boolean | - | wrap text on alignment (optional) |
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:
| line_count | int | - | line count (optional, default 1) | ||
| font_size | int | - | font size (optional) |
Example:
call pgxls.set_row_height(xls, pgxls.get_cell_height());
pgxls.get_column_name
Function returns column name (type varchar) by number
Parameter:
| column_ | int | - | column |
Example:
column_name := pgxls.get_column_name(5);
pgxls.get_file
Function builds and returns file (type bytea)
Parameter:
| 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:
| 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:
| xls | pgxls.xls | - | document |
Example:
return query execute pgxls.get_file_parts_query(xls);
pgxls.get_format_code_boolean
Function builds logical format code (varchar type)
Parameters:
| text_true | varchar | - | text for true value (optional) | ||
| text_false | varchar | - | text for false value (optional) | ||
| text_null | varchar | - | text for null value (optional) |
Examples:
format1 := pgxls.get_format_code_boolean('Yes', 'No', 'Null'); format2 := pgxls.get_format_code_boolean(text_true=>'1');
pgxls.get_format_code_numeric
Function builds logical format code (varchar type)
Parameters:
| decimal_places | int | - | decimal places | ||
| thousands_separated | boolean | - | thousands separated (optional) |
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:
| xls | pgxls.xls | - | document | ||
| column_count | int | - | column count (optional, default 1) | ||
| row_count | int | - | row count (optional, default 1) | ||
| column_ | int | - | column from which the merge begins (optional, default 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:
| 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:
| xls | pgxls.xls | - | document | ||
| value | anyelement | - | value | ||
| column_ | int | - | column (optional, default current) | ||
| format_code | varchar | - | format code (optional) | ||
| font_name | varchar | - | font name (optional) | ||
| font_size | int | - | font size (optional) | ||
| font_bold | boolean | - | bold font (optional) | ||
| font_italic | boolean | - | italic font (optional) | ||
| font_underline | boolean | - | underline font (optional) | ||
| font_strike | boolean | - | strike font (optional) | ||
| font_color | varchar(6) | - | font color in format 'RRGGBB' or 'auto' (optional) | ||
| border_around | pgxls.border_line | - | border around (optional) | ||
| border_left | pgxls.border_line | - | border left (optional) | ||
| border_top | pgxls.border_line | - | border top (optional) | ||
| border_right | pgxls.border_line | - | border right (optional) | ||
| border_bottom | pgxls.border_line | - | border bottom (optional) | ||
| fill_foreground_color | varchar(6) | - | fill color in format 'RRGGBB' or 'none' (optional) | ||
| alignment_horizontal | pgxls.alignment_horizontal | - | horizontal alignment (optional) | ||
| alignment_indent | int | - | indent on alignment (optional) | ||
| alignment_vertical | pgxls.alignment_vertical | - | vertical alignment (optional) | ||
| alignment_text_wrap | boolean | - | wrap text on alignment (optional) |
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.set_cell
Parameters:
| type | - | data type: text,integer,numeric,date,time,timestamp,boolean | |||
| xls | pgxls.xls | - | document | ||
| value | [type] | - | value | ||
| column_ | int | - | column (optional, default 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:
| 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:
| 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 sets the cell format for all columns and all data types
Parameters:
| xls | pgxls.xls | - | document | ||
| font_name | varchar | - | font name (optional) | ||
| font_size | int | - | font size (optional) | ||
| font_bold | boolean | - | bold font (optional) | ||
| font_color | varchar(6) | - | font color in format 'RRGGBB' or 'auto' (optional) | ||
| border | pgxls.border_line | - | border around (optional) | ||
| fill_foreground_color | varchar(6) | - | fill color in format 'RRGGBB' or 'none' (optional) | ||
| alignment_horizontal | pgxls.alignment_horizontal | - | horizontal alignment (optional) | ||
| alignment_vertical | pgxls.alignment_vertical | - | vertical alignment (optional) | ||
| alignment_text_wrap | boolean | - | wrap text on alignment (optional) |
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:
| xls | pgxls.xls | - | document | ||
| column_ | int | - | column |
Example:
call pgxls.set_column_current(xls, 5);
pgxls.set_column_format_[type]
Procedure sets format cells of column depending on data type.
If data type not specified (procedure pgxls.set_column_format), then format set for all types
Parameters:
| type | - | data type: text,integer,numeric,date,time,timestamp,boolean | |||
| xls | pgxls.xls | - | document | ||
| column_ | int | - | column (optional, default current) | ||
| format_code | varchar | - | format code (optional, only for typed procedures) | ||
| font_name | varchar | - | font name (optional) | ||
| font_size | int | - | font size (optional) | ||
| font_bold | boolean | - | bold font (optional) | ||
| font_italic | boolean | - | italic font (optional) | ||
| font_underline | boolean | - | underline font (optional) | ||
| font_strike | boolean | - | strike font (optional) | ||
| font_color | varchar(6) | - | font color in format 'RRGGBB' or 'auto' (optional) | ||
| border_around | pgxls.border_line | - | border around (optional) | ||
| border_left | pgxls.border_line | - | border left (optional) | ||
| border_top | pgxls.border_line | - | border top (optional) | ||
| border_right | pgxls.border_line | - | border right (optional) | ||
| border_bottom | pgxls.border_line | - | border bottom (optional) | ||
| fill_foreground_color | varchar(6) | - | fill color in format 'RRGGBB' or 'none' (optional) | ||
| alignment_horizontal | pgxls.alignment_horizontal | - | horizontal alignment (optional) | ||
| alignment_indent | int | - | indent on alignment (optional) | ||
| alignment_vertical | pgxls.alignment_vertical | - | vertical alignment (optional) | ||
| alignment_text_wrap | boolean | - | wrap text on alignment (optional) |
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:
| xls | pgxls.xls | - | document | ||
| format | pgxls.page_paper_format | - | format (optional) | ||
| orientation | pgxls.page_orientation | - | orientation (optional) |
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:
| xls | pgxls.xls | - | document | ||
| header | text | - | header text, null value removes header | ||
| alignment | pgxls.alignment_horizontal | - | horizontal position: left,center,right (optional, default right) | ||
| font_name | varchar | - | font_name (optional, default Arial) | ||
| font_size | int | - | font size (optional, default 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:
| xls | pgxls.xls | - | document | ||
| left_ | numeric | - | left margin (optional) | ||
| top | numeric | - | top margin (optional) | ||
| right_ | numeric | - | right margin (optional) | ||
| bottom | numeric | - | bottom margin (optional) |
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:
| xls | pgxls.xls | - | document | ||
| row_from | int | - | starting row number, null value disables repeating | ||
| row_to | int | - | ending row number (optional, default 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:
| xls | pgxls.xls | - | document | ||
| height | int | - | height |
Example:
call pgxls.set_row_height(xls, 20);
pgxls.xls
Composite type, document data