Экспорт в Excel из PostgreSQL

Описание
Простые примеры
Ключевые особенности
Полные примеры
Ограничения
Видео

Описание

Инструмент PGXLS - SQL схема с набором хранимых процедур для создания файлов(тип bytea) в формате электронной таблицы Excel(.xlsx). Реализована зависимость форматирования от типа данных, преобразование результатов SQL-запроса в лист с автоформатированием и прочее (более подробно в разделе Ключевые особенности)

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

На процедурном языке PL/pgSQL создается функция, которая выполняет следующее:
1. Определяет переменную типа pgxls.xls
2. Заполняет документ через вызов хранимых процедур
3. Формирует из переменной файл и возвращает его

Также есть готовая функция, формирующая файл по SQL-запросу

Основные процедуры

pgxls.create	-	создание документа
pgxls.add_row	-	добавление строки
pgxls.put_cell	-	заполнение ячейки: установка значения и форматирование
pgxls.get_file	-	формирование и получение файла
pgxls.get_file_by_query	-	создание файла по SQL-запросу (функция-обертка)
pgxls.save_file_by_query	-	сохранение файла по SQL-запросу на сервере (выполняется под суперпользователем)

Более подробно на странице Документация

Получение и сохранение файлов в SQL-менеджере

-- 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_cellvalue(xls, rec.size);
  end loop;  
  -- Возвращаем файл(bytea)
  return pgxls.get_file(xls);      
end
$$;
-- Получаем файл
select excel_top_relations_by_size();

Сохранение файлов в командной строке

Утилита командной строки psql возвращает bytea в hex-формате, поэтому требуется обратное преобразование.
При использовании языков программирования (например, Java, JavaScript, Python и т.п) преобразование из hex не требуется

Linux

#!/bin/bash
# Примеры сохранения файлов в командной строке Linux
#
# Для преобразования hex в binary используется утилита xxd
#
# При запуске psql не на сервере необходимо установить postgres client и указать параметры подключения через URI:
# psql postgres://[ПОЛЬЗОВАТЕЛЬ]:[ПАРОЛЬ]@[СЕРВЕР]/[БАЗА_ДАННЫХ] -Aqt -c "...

# 1. Создание файла по SQL-запросу
psql -Aqt -c "select pgxls.get_file_by_query('select * from pg_tables')" | xxd -r -ps > pg_tables.xlsx

# 2. Сохраняем Excel-файл на сервере по SQL-запросу
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. Сохранение файла из SQL-функции  
psql -Aqt -c "select excel_top_relations_by_size()" | xxd -r -ps > top_relations_by_size.xlsx

Windows

rem Примеры сохранения файлов в командной строке Windows

rem Для преобразования hex в binary используется утилита certutil и промежуточный временный файл

rem При запуске psql не на сервере необходимо установить postgres client и указать параметры подключения через URI:
rem psql postgres://[ПОЛЬЗОВАТЕЛЬ]:[ПАРОЛЬ]@[СЕРВЕР]/[БАЗА_ДАННЫХ] -Aqt -c "...

rem  1. Создание файла по SQL-запросу
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. Сохраняем Excel-файл на сервере по SQL-запросу
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. Сохранение файла из SQL-функции  
psql -Aqt -c "select excel_top_relations_by_size()" -o hex.tmp 
certutil -decodehex -f hex.tmp top_relations_by_size.xlsx

Большие файлы - данные построчно записываются во временную таблицу, что снимает требование к памяти. Для получения файла частями реализована отдельная функция
Автоформатирование - для каждого столбца форматирование настраивается в зависимости от типа данных
SQL-запросы - возможно добавлять лист с результатами SQL-запроса
Стили - для столбцов и ячеек поддерживается установка формата, шрифта, границы, заливки и выравнивания
Настройка печати - для каждого листа можно установить формат и ориентацию бумаги, заголовок, повторяющиеся строки ("шапку" таблицы) и отступы
Параллельность - возможно создавать в одной сессии несколько файлов параллельно

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

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

Большие проекты - применение в больших проектах не рекомендуется, т.к. обработка бинарных данных в SQL очень неэффективна, отчеты формируются долго и возникает значительное потребление CPU. В больших проектах отчеты должна формировать backend-чаcть приложения
Сжатие не поддерживается - данные копируются, одинаковые строки дублируются
Неоптимальная высота строк - высота строк расчитывается с допущениями и может быть неоптимальной

Обзорное видео размещено на Youtube

PGXLS

Экспорт в Excel из PostgreSQL

PGXLS

О проекте