Usage method
A PL/pgSQL function is created that does the following: creates a 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).
The column count and column widths are determined when creating a document or adding a sheet. For each column, you can override the style depending on data type using procedures pgxls.set_column_format_[type]. The set_row_default_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 a 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, you must use the procedures pgxls.put_cell_[type] to set cells and get a file as a stream (pgxls.get_query_file_stream).
There are functions pgxls.get_file_by_query and pgxls.add_sheet_by_query to get the 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.
Colors are specified in RRGGBB format. Named presets are also supported: light_red,light_green,light_blue,light_gray,dark_red,dark_green,dark_blue,dark_gray - these will be automatically converted.
Accessing internal fields of pgxls.xls and use procedures and types that start with underscore "_" is not allowed
The tool is row-based and follows the general approach in PostgreSQL:
- in the for record in [SQL query] loop, retrieve record by record from an implicit cursor
- in the loop body, add a new row (the current row is written to a temporary table and cannot be modified);
write data from record to the cells of the new current row and format it. - retrieve the report as a stream (rows + additional information) using the pgxls.get_query_file_stream function
A similar logic applies to sheets: when adding a new sheet, the current sheet, along with its page and print settings, is written to a temporary table and cannot be changed. Sheet and column formatting functions only affect new rows in the current sheet, i.e., they do not change existing data.
The variable of pgxls.xls type stores formatting options without binding them to the current row of the current sheet. This minimizes memory usage and avoids an Out of memory error.
For easy navigation of the current row, the pgxls.xls type includes a column_current field, which indicates the current column in which the data will be output. Numbering starts at 0: 0 is column A, 1 is B, 2 is C, and so on.
Procedures affecting column_current
| Procedures | Pseudocode | Action |
| pgxls.add_row | column_current := 0 | Set to the first cell of the current row |
| pgxls.put_cell without parameter column_ pgxls.next_column | column_current := column_current + 1 (or +column_count from procedure merge_cells) | Move to the next cell, accounting for merged cells |
| pgxls.put_cell with parameter column_ set_column_current | column_current := column_ | Set to an explicitly specified value |
| pgxls.merge_cells | does not change column_current | Skip merged cells for the current and subsequent row_count-1 rows |
Example
declare xls pgxls.xls; begin ... call pgxls.add_row(xls); -- column_current := 0 call pgxls.put_cell(xls, 'abc'); -- column_current := 1 call pgxls.put_cell_integer(xls, 12); -- column_current := 2 call pgxls.put_cell(xls, 'xyz', column_=>5); -- column_current := 5 call pgxls.next_column(xls); -- column_current := 6 call pgxls.set_column_current(xls, 10); -- column_current := 10 call pgxls.add_row(xls); -- column_current := 0 call pgxls.put_cell(xls, 'abc'); -- column_current := 1 call pgxls.merge_cells(xls, column_count=>2, row_count=>2); -- column_current not changed call pgxls.put_cell(xls, 34); -- column_current := 3, (1+2) - merge has an effect call pgxls.add_row(xls); -- column_current := 0 call pgxls.put_cell(xls, 'qwe'); -- column_current := 1 call pgxls.put_cell(xls, 56); -- column_current := 3, (1+2) - merge still has an effect call pgxls.add_row(xls); -- column_current := 0 call pgxls.put_cell(xls, 'xyz'); -- column_current := 1 call pgxls.put_cell(xls, 78); -- column_current := 2, (1+1) - merge no longer has an effect ... end;
Stored procedures are created with option security invoker, dynamic SQL queries passed via parameters are executed with these privileges.
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();
Quick start (simple example)
-- 1. Create and get file (bytea) by SQL query select pgxls.get_file_by_query('select * from pg_tables'); -- 2. Save Excel file on server by SQL query 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. Create function that returns file (bytea) create or replace function excel_top_relations_by_size() returns bytea language plpgsql as $$ declare rec record; xls pgxls.xls; begin -- Create document, specify widths and captions of columns in parameters xls := pgxls.create(array[10,80,15], array['oid','Name','Size, bytes']); -- Select data in loop for rec in select oid,relname,pg_relation_size(oid) size from pg_class order by 3 desc limit 10 loop -- Add row call pgxls.add_row(xls); -- Set data from query into cells call pgxls.put_cell(xls, rec.oid); call pgxls.put_cell(xls, rec.relname); call pgxls.put_cell(xls, rec.size); end loop; -- Returns file(bytea) return pgxls.get_file(xls); end $$; -- Get file select excel_top_relations_by_size();
Formatting in different ways, page and print setup
-- 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.get_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.get_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=>'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=>'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=>'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_row_default_format(xls, border=>'thin'); call pgxls.add_row_texts(xls, array['x','√x','x²','md5(x)'], font_bold=>true, fill_foreground_color=>'dark_gray', alignment_horizontal=>'center'); call pgxls.set_row_default_format(xls, fill_foreground_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();
Output data from a table and SQL query to a report
-- 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 and receive a large file (report)
-- 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.get_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_query_file_stream(xls); call pgxls.delete_file_data(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
The function creates, initializes and returns a document (pgxls.xls type).
If the column_widths parameter is not specified, the document is created without a sheet and the first sheet must be added manually.
If the column_widths parameter is specified, a new sheet is added (actually called pgxls.add_sheet(xls,column_widths,column_headers,sheet_name))
Parameters
| Name | Type | Description | Required | Default |
| column_widths | int[] | column widths of the first sheet | ||
| column_headers | text[] | column headers | ||
| 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=>'light_blue'); xls := pgxls.create(); call pgxls.add_sheet_by_query(xls, 'select * from pg_roles', 'Roles'); ...
pgxls.add_sheet
The procedure adds a new sheet to the document.
If the column_headers parameter is specified, a row with column headers is added to the sheet with formatting (bold, centered, and repeated on every page), the row is current and can be changed using the pgxls.format_row and pgxls.format_cell procedures
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| column_widths | int[] | column widths | ✔ | |
| column_headers | text[] | column headers | ||
| name | varchar | sheet name |
Examples
call pgxls.add_sheet(xls, array[50,100], name=>'Sheet2'); -- Add sheet without row header call pgxls.add_sheet(xls, array[10,50], array['ID','Name'], 'Sheet3'); -- Add sheet with column headers call pgxls.format_row(xls, font_size=>16, border=>'thin'); -- Format the header row
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 | sheet name |
Example
call pgxls.add_sheet_by_query(xls, 'select * from pg_class', 'Query data');
pgxls.get_file
The function builds and returns a file (bytea type)
Parameter
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ |
Example
return pgxls.get_file(xls);
pgxls.get_query_file_stream
Function builds file and returns SQL query (varchar type) to get file as a stream. Used for large files, after receiving file, need to call procedure pgxls.delete_file_data
Parameter
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ |
Examples
return query execute pgxls.get_query_file_stream(xls); create or replace function my_large_report() returns setof bytea language plpgsql as $$ declare xls pgxls.xls := pgxls.create(); v_value bigint; begin ... return query execute pgxls.get_query_file_stream(xls); call pgxls.delete_file_data(xls); end $$;
pgxls.delete_file_data
Procedure removes file data from temporary table, used together with pgxls.get_query_file_stream
Parameter
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ |
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.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.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 changing the style parameters
Parameters
| Name | Type | Description | Required | Default |
| xls | pgxls.xls | document | ✔ | |
| texts | text[] | 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=>'dark_gray', alignment_horizontal=>'center');
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, it is 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.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.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 the data type is 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, missing in the 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=>'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_row_default_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_row_default_format(xls, font_size=>10, font_bold=>true, fill_foreground_color='dark_red'); call pgxls.set_row_default_format(xls, border=>'thin', alignment_horizontal=>'center');
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=>'light_green'); call pgxls.format_row(xls, border=>'thin', alignment_horizontal=>'center');
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.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.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_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_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_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.page_paper_format
Enum type, defines paper format
Values: A3,A4,A5
pgxls.page_orientation
Enum type, defines page orientation
Values: portrait,landscape
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.font_family
Enum type, defines font family.
Values: sans,sans_serif,monospace
pgxls.border_line
Enum type, defines border line
Values: none,thin,thick,dashed,dotted,dashDot,dashDotDot,double
pgxls.xls
Composite type, document data
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.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_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_font_name
Function returns font name (type varchar) by font family
Parameter
| Name | Type | Description | Required | Default |
| font_family | pgxls.font_family | Font family | ✔ |
Examples
call pgxls.format_row(xls, font_name=>pgxls.get_font_name('sans_serif')); call pgxls.put_cell(xls, 123, font_name=>pgxls.get_font_name('monospace'));
pgxls.pgxls_version
Function returns tool version