#!/usr/bin/perl -CSDAL

use strict;
use warnings;
use utf8;

my $usagemsg= <<EOF;
usage: unsylk.pl [ -t ] [ -a ] [ -s <sep> ] [ -e <empty> ] [ <spreadsheet file> ]
Convert sylk spreadsheet to text.  -t transposes the table; -a aligns the
colums by filling with spaces; -s sets the column separator (default tab), -e
the empty field indicator (default \~).  Sylk spreadsheets have to have the
file extension .slk or .sylk or be fed into stdin.  Other spreadsheet formats
are converted using the ssconvert program distributed with gnumeric.
EOF

my ($transpose, $align);
my $colsep= "\t";
my $emptyfield= "~";

while( @ARGV && $ARGV[0] =~ /^-/ ) {
    if( $ARGV[0] eq "-h" ) {
        print $usagemsg;
        exit;
    }
    elsif( $ARGV[0] eq "-t" ) {
        $transpose= 1;
    }
    elsif( $ARGV[0] eq "-a" ) {
        $align= 1;
    }
    elsif( $ARGV[0] eq "-s" ) {
        if( !defined $ARGV[1] ) {
            print "Missing argument to -s option\n", $usagemsg;
            exit 1;
        }
        $colsep= $ARGV[1];
        shift @ARGV;
    }
    elsif( $ARGV[0] eq "-e" ) {
        if( !defined $ARGV[1] ) {
            print "Missing argument to -e option\n", $usagemsg;
            exit 1;
        }
        $emptyfield= $ARGV[1];
        shift @ARGV;
    }
    shift @ARGV;
}

my $infile;

if( @ARGV > 1 ) {
    print "Need exactly one file argument (or no argument for stdin)\n", $usagemsg;
    exit 1;
}
elsif( @ARGV == 0 ) {
    $infile= *STDIN{IO};
}
elsif( $ARGV[0] =~ /\.sy?lk$/i ) {
    open $infile, "<", $ARGV[0] or die "Cannot open $ARGV[0] for reading: $!\n";
}
else {
    open $infile, "-|", "ssconvert -T Gnumeric_sylk:sylk $ARGV[0] fd://1" or die "Failed to use ssconvert to convert $ARGV[0] to SYLK\n";
}


my @tab;
my @maxcolwidth;
my ($minx, $miny, $maxx, $maxy)= (0xFFFF, 0xFFFF, 0, 0);
my $prevy;

while( defined($_= <$infile>) ) {
    next unless /^C;/i;
    my ($x, $y);
    next unless /;X(\d+);/i;
    $x= $1;
    $y= /;Y(\d+);/i ? $1 : $prevy;
    next unless defined $y;
    $prevy= $y;
    next unless /;K(.*)$/i;
    my $content= $1;
    $content= $1 if $content =~ /^"(.*)"\s*$/;
    $content =~ s/^\s+//;
    $content =~ s/\s+$//;
    $content =~ s/\t/ /g;
    $content =~ s/\Q$colsep\E/__/g;
    if( $transpose ) {
        my $tmp= $x;
        $x= $y;
        $y= $tmp;
    }
    $tab[$y][$x]= $content;
    $minx= $x unless $minx <= $x;
    $maxx= $x unless $maxx >= $x;
    $miny= $y unless $miny <= $y;
    $maxy= $y unless $maxy >= $y;
    $maxcolwidth[$x]= length($content)
        if !defined($maxcolwidth[$x]) || $maxcolwidth[$x] < length($content);
}

close $infile if @ARGV;

if( $align ) {
    for (@maxcolwidth[$minx..$maxx]) {
        $_= length($emptyfield) if !defined($_) || $_ < length($emptyfield);
    }
}

for my $row (@tab[$miny..$maxy]) {
    if( $row ) {
        my $field= $$row[$minx] // $emptyfield;
        print $field;
        print " " x ($maxcolwidth[$minx] - length($field)) if $align;
        for my $x ($minx+1..$maxx) {
            $field= $$row[$x] // $emptyfield;
            print $colsep, $field;
            print " " x ($maxcolwidth[$x] - length($field)) if $align;
        }
    }
    print "\n";
}