3

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;
5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.