tbl2excel-helper

#!/usr/bin/env perl
# tbl2excel-helper: tbl2excel Spreadsheet::WriteExcel helper
# Copyright(c) 2010 EURAC, Institute of Genetic Medicine
use warnings;
use strict;
use locale;
use POSIX qw{locale_h};
use Getopt::Std;

# flags and arguments
my %flags;
getopts("hx", \%flags);
my ($file) = @ARGV;
if(defined($flags{'h'}))
{
  print(STDERR qq{$0 [-hx] [file]:
Write an Excel file to standard output (or into the specified file), using
simple one-line commands:

  ns [name]	Begin new worksheet (with "name")
  i y x n	Write integer n at xy
  d y x n	Write double n at xy
  s y x s	Write string s at xy
  f y x f	Write formula f at xy
  b ...		Run the next command with bold
  a c ...	Run the command c at cursor and move to next column
  nr		Move to next row

Examples:

  ns test		Start worksheet "test" (mandatory!)
  b a s Column 1	Write first column header
  b a s Column 2	Second column header
  nr			Move to next row
  a d 1.0		First column data
  a d 2.0		Second column data
  s 2 0	Test 1		Absolute movement (row 3, column 1)
  s 2 1	Test 2		Absolute movement (row 3, column 2)

The following flags are supported:

  -h:	this help
  -x:	write XLSX (Excel 2012+) files instead of XLS (Excel 97-2003)
});
  exit(2);
}

# output file
my $fd;
if($file) {
  open($fd, ">$file") or die("$!\n");
} else {
  $fd = \*STDOUT;
}
binmode($fd);

# excel writer
local $g::wb;
if($flags{'x'}) {
  use Excel::Writer::XLSX;
  $g::wb = Excel::Writer::XLSX->new($fd);
} else {
  use Spreadsheet::WriteExcel;
  $g::wb = Spreadsheet::WriteExcel->new($fd);
}

# define shared variables and formats
my $thousandsSep = localeconv()->{'thousands_sep'} || ',';
local $g::f_bold = $g::wb->add_format(bold => 1);
local $g::f_double = $g::wb->add_format(num_format => "0.000000");
local $g::f_bold_double = $g::wb->add_format(bold => 1, num_format => "0.000000");

# current state
local $g::cs = undef;
local $g::cx = 0;
local $g::cy = 0;
local $g::cb = 0;

sub at($$)
{
  my ($y, $x) = @_;
  $g::cy = $y;
  $g::cx = $x;
  return @_;
}

sub req_cs()
{
  die("undefined worksheet") unless(defined($g::cs));
};

# command handlers
sub parse($);

sub parse($)
{
  $_ = shift;
  my ($cmd, $args) = /^(\S+)\s*(.*)/;
  return if(!$cmd);

  if($cmd eq "ns")
  {
    $args = substr($args, 0, 31);
    $args =~ s/[\[\]:*?\/\\]/_/g;
    $g::cs = $g::wb->add_worksheet($args);
    $g::cx = $g::cy = $g::cb = 0;
  }
  elsif($cmd eq "nr")
  {
    ++$g::cy;
    $g::cx = 0;
  }
  elsif($cmd eq "b")
  {
    $g::cb = !$g::cb;
    parse($args);
    $g::cb = !$g::cb;
  }
  elsif($cmd eq "a")
  {
    my ($ncmd, $nargs) = ($args =~ /^(\S+)\s*(.*)/);
    parse("$ncmd $g::cy $g::cx $nargs");
    ++$g::cx;
  }
  elsif($cmd eq "i")
  {
    my ($y, $x, $n) = ($args =~ /^(\d+) (\d+) (.*)/);
    $n =~ s/\Q$thousandsSep\E//g;
    req_cs();
    $g::cs->write_number(at($y, $x), $n, ($g::cb? $g::f_bold: undef));
  }
  elsif($cmd eq "d")
  {
    my ($y, $x, $n) = ($args =~ /^(\d+) (\d+) (.*)/);
    $n =~ s/\Q$thousandsSep\E//g;
    req_cs();
    $g::cs->write_number(at($y, $x), $n, ($g::cb? $g::f_bold_double: $g::f_double));
  }
  elsif($cmd eq "s")
  {
    my ($y, $x, $s) = ($args =~ /^(\d+) (\d+) (.*)/);
    req_cs();
    $g::cs->write_string(at($y, $x), $s, ($g::cb? $g::f_bold: undef));
  }
  elsif($cmd eq "f")
  {
    my ($y, $x, $f) = ($args =~ /^(\d+) (\d+) (.*)/);
    req_cs();
    $g::cs->write_formula(at($y, $x), "=$f", ($g::cb? $g::f_bold: undef));
  }

  # backward aliases
  elsif($cmd eq "bi")
  {
    parse("b i $args");
  }
  elsif($cmd eq "bd")
  {
    parse("b d $args");
  }
  elsif($cmd eq "bs")
  {
    parse("b s $args");
  }
}

# main
while(<STDIN>)
{
  chomp;
  parse($_);
}
$g::wb->close();