Export to Excel from PostgreSQL

Description
Simple examples
Important qualities
Full examples
Restrictions
Video

Description

Tool PGXLS is SQL schema with stored procedures for creating files (bytea type) in Excel format (.xlsx). Implemented dependence format on data type, conversion SQL query into sheet with autoformat and more (for more details, see Important qualities)

Usage method

PL/pgSQL function is created that does:
1. Defines variable of type pgxls.xls
2. Sets document cells via stored procedure calls
3. Builds file from varible and returns it

There is also function that builds file by SQL query

Basic procedures

pgxls.create	-	create document
pgxls.add_row	-	add row
pgxls.put_cell	-	fill cell: set value and format
pgxls.get_file	-	build and get file
pgxls.get_file_by_query	-	create file by SQL query (wrapper)
pgxls.save_file_by_query	-	save file by SQL query on server (call as superuser)

More details on page Documentation

Get and save files in SQL manager

-- 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();

Save files on command line

The psql command line utility returns bytea in hex format, so the reverse conversion is required.
When using programming languages(such as Java, JavaScript, Python, etc.), conversion from hex is not required

Linux

#!/bin/bash
# Examples of saving files in Linux command line

# To convert hex to binary, use xxd utility

# When running psql on non-server, install postgres client and specify connection parameters via URI:
# psql postgres://[USERNAME]:[PASSWORD]@[SERVER]/[DATABASE] -Aqt -c "...

# 1. Create file by SQL query
psql -Aqt -c "select pgxls.get_file_by_query('select * from pg_tables')" | xxd -r -ps > pg_tables.xlsx

# 2. Save Excel file on server by SQL query
psql -c "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. Save file from SQL function
psql -Aqt -c "select excel_top_relations_by_size()" | xxd -r -ps > top_relations_by_size.xlsx

Windows

rem Examples of saving files in Windows command line

rem To convert hex to binary, use certutil utility and temporary file

rem When running psql on non-server, install postgres client and specify connection parameters via URI:
rem psql postgres://[USERNAME]:[PASSWORD]@[SERVER]/[DATABASE] -Aqt -c "...

rem 1. Create file by SQL query
psql -Aqt -c "select pgxls.get_file_by_query('select * from pg_tables')" -o hex.tmp 
certutil -decodehex -f hex.tmp pg_tables.xlsx

rem 2. Save Excel file on server by SQL query
psql -c "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')" 

rem 3. Save file from SQL function
psql -Aqt -c "select excel_top_relations_by_size()" -o hex.tmp 
certutil -decodehex -f hex.tmp top_relations_by_size.xlsx

Large files - data row by row inserted into temporary table, which not requires memory. Separate function is implemented to get large file
Auto-format - for each column, format is configured depending on data type
SQL queries - it is possible to add sheet with the results of SQL query
Styles - for columns and cells, support format, font, border, fill and alignment
Print setup - for each sheet, the paper format and orientation, title, repeating rows (table header) and margins are specified
Parallelism - possible to create several files in parallel in one session

Set cells and format in different ways, print setup

-- Create function that returns file
create or replace function excel_full() returns bytea language plpgsql as $$
declare 
  xls pgxls.xls; 
begin
  -- Create excel, first sheet has 1 column 30 wide and named "Styles" 
  xls := pgxls.create(array[30], sheet_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 by query
  call pgxls.add_sheet_by_query(xls, 'select * from pg_class order by 1 limit 10', 'SQL query');
  --------------------------------------------------------------------------------------------------------------------------------
  -- 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 full / 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 excel_full();

Create and get large file

-- 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 * 10K rows = 4M 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..10000 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
select excel_large_file();

Large projects - use on large projects is not recommended: processing binary data in SQL is very inefficient, reports take a long time to create, high CPU usage. In large projects, reports should be created by the backend part of the application
Compression not supported - data copied, identical lines duplicated
Row height not optimal - row height calculated with assumptions and may be not optimal

Review posted on Youtube

PGXLS

Export to Excel from PostgreSQL

PGXLS

About