#!/usr/bin/perl

# Script to convert spreadsheets to tb delimited files
# W.Rayner 2017
# Requires Spreadsheet::Read
#          Spreadsheet::ReadSXC
#          Spreadsheet::ParseExcel
#          Spreadsheet::ParseXLSX
#          Text::CSV_XS
#
# v1.0 - Created

use strict;
use warnings;
use Getopt::Long;
use Spreadsheet::Read;
use File::Basename;
use HTML::Entities;

my $infile;
my $outfile;
my $formatted = 0; 
my @suffixes = qw(.xls .xlsx .ods .csv .sxc);
my $suffix;
my $path;

GetOptions
 (
 "i|input=s"     => \$infile,  
 "o|output=s"    => \$outfile,
 "f|format"      => \$formatted #set whether the cell value is read formatted or unformatted
 );

if (!$infile)
 {
 print "ERROR: No input file specified\n";
 exit;
 }
if (!$outfile)
 {
 ($outfile, $path, $suffix) = fileparse($infile, @suffixes); 
 }

if (-e $infile)
 {
 print "Input File:  $infile\n";
 }
else
 {
 print "File not found:  $infile\n";
 }

my $book = Spreadsheet::Read->new($infile);
my @sheets = $book->sheets;

my $sheetcount = @sheets;
print "Found $sheetcount Sheets\n";

foreach my $s (@sheets)
 {
 my $out;
 if ($suffix ne '.csv')
  {
  $out = $outfile.'.'.$s.'.txt';
  }
 else
  {
  $out = $outfile.'.txt';
  }
 print "Output File: $out ";
 
 if (-e $out)
  {
  print "exists, will not overwrite\n";
  }
 else
  {
  print "\n";
  open OUT, ">$out" or die $!;
  my $sheet = $book->sheet($s);
  my $col = $sheet->maxcol;
  my $row = $sheet->maxrow;
  print "Processing Sheet $s  $col x $row\n";
  
  foreach my $r (1 .. $row)
   {
   my $cell;
   
   if ($formatted)
    {
    my $cellref = $sheet->cr2cell(1, $r);
    $cell = $sheet->cell ($cellref);
    }
   else
    {
    $cell = $sheet->cell (1, $r);
    }
   $cell = decode_entities($cell); 
   print OUT "$cell";
  
   foreach my $c (2 .. $col)
    {
    if ($formatted)
     {
     my $cellref = $sheet->cr2cell($c, $r);
     $cell = $sheet->cell($cellref);
     }
    else
     {
     $cell = $sheet->cell($c, $r);
     }
    $cell = decode_entities($cell); 
    print OUT "\t$cell";
    }
   print OUT "\n"; 
   }
  close OUT; 
  }
 }

