PostgreSQL Export Data to XLSX File – How to Guide

excelexportpostgresql

I want to export the result of a query to xlsx file format.
I know we can export it to csv file, but I want to export it to xlsx file.

Like the following: 

copy(select name_related from hr_employee limit 3) to '/tmp/ABC.xlsx'
delimiter ',' xlsx header;

Best Answer

PostgreSQL can't do this natively, but you can convert the CSV output to an XLSX file with a simple perl script.

csv2xlsx:

For this script to work, install Excel::Writer::XLSX and Text::CSV perl modules, either through apt-get/yum, or through sudo perl -MCPAN -e 'install Excel::Writer::XLSX' and sudo perl -MCPAN -e 'install Text::CSV'.

#!/usr/bin/perl -w
use strict;
use warnings;
use Excel::Writer::XLSX;
use Text::CSV;

my ($filename) = @ARGV;
open my $io, "-" or die "$!";

# Create a new Excel workbook
my $workbook = Excel::Writer::XLSX->new( $filename ) or die "$!";

# Add a worksheet
my $worksheet = $workbook->add_worksheet();

# Data
my $fdata = $workbook->add_format();
$fdata->set_border(1);
$fdata->set_border_color('gray');
$fdata->set_font( 'Segoe UI' );
$fdata->set_size( '8' );

# Titles
my $ftitle = $workbook->add_format();
$ftitle->copy ($fdata);
$ftitle->set_bg_color( '#B0CBF0' );

# Dates
my $fdate = $workbook->add_format();
$fdate->copy($fdata);
$fdate->set_num_format('dd/mm/yyyy' );
my $fdatetime = $workbook->add_format();
$fdatetime->copy($fdata);
$fdatetime->set_num_format('dd/mm/yyyy hh:mm:ss' );

$worksheet->set_column('A:ZZ', 15);

my $csv = Text::CSV->new({ binary => 1, eol => $/ });

my $line = 0;
while (my $row = $csv->getline($io)) {
  my @fields = @$row;
  my $col = 0;
  if($line == 0) {
    foreach my $fld (@fields) {
      $worksheet->write_string( 0, $col++, $fld, $ftitle );
    }
  } else {
    my $col = 0;
    foreach my $fld (@fields) {
      if ($fld =~ /^[0-9]/) {
        if($fld =~ /^-?(0(\.[0-9]{1,14})?|[1-9][0-9]{0,13}(\.[0-9]{1,14})?)$/) {
          $worksheet->write_number( $line, $col, $fld, $fdata );
        } elsif ($fld =~ /^[0-9]{4}-[0-9]{2}-[0-9]{2}$/) {
          $worksheet->write_date_time( $line, $col, "${fld}T", $fdate );
        } elsif ($fld =~ /^[0-9]{4}-[0-9]{2}-[0-9]{2}[ T][0-9]{2}:[0-9]{2}:[0-9]{2}(\.[0-9]+)?$/) {
          $fld =~ s/ /T/;
          $worksheet->write_date_time( $line, $col, $fld, $fdatetime );
        } else {
          $worksheet->write_string( $line, $col, $fld, $fdata );
        }
      } else {
        $worksheet->write_string( $line, $col, $fld, $fdata );
      }

      $col++;
    }
  }
  $line++;
}

Usage example:

psql -c "COPY (SELECT 1 as id, 'foo' as bar, now()::date as date) TO STDOUT CSV HEADER" template1 |\
  ./cvs2xlsx /tmp/ABC
Related Question