#!/usr/bin/perl -w
use strict;
use diagnostics;

require MP3::Tag;
require Spreadsheet::WriteExcel;

#############################################
#                                           #
# This Script is used to export a Songlist  #
# from any Playlist (.m3u) to Excel (.xls)  #
#                                           #
# Thanks to Michael Schilli for explaining  #
# how to write Excel-Files with Perl.       #
#                                           #
# 2003, Martin Holz <martin@perl-newbie.de> #
#                                           #
#############################################

my @args = @ARGV;
my $playlist = $args[0];
if (($playlist !~ m/\.m3u/) || (not -e $playlist)) {
	die "Playlist is not available!\n";
}

my $xls_file = $args[1];
if ($xls_file !~ m/\.xls/) {
	die "The Excel-File must have suffix: .xls\n";
}

my @files = (); # contains filenames from the .m3u
my @order = (); # contains the numbering (001...134)
my @title = (); # contains the song-title from id3
my @artst = (); # contains the artist from id3

if (get_playlist(@files)) {
  &get_id3_tags;
  &write_xls_file;
}
else {
  die "Playlist in wrong format?.\n";
}

#############################################
sub get_playlist {
#############################################
  print "Reading Playlist: $playlist ";
  open (PLAYLIST, "<$playlist");
  my @temp = <PLAYLIST>;
  close (PLAYLIST);

  foreach my $temp_filename (@temp) {
    if ($temp_filename =~ m/\.mp3\Z/i) {
      if ($temp_filename =~ m/\A\s(.{1,}\.mp3)/i) {
	$temp_filename = $1;
      }
      push (@files, $temp_filename);
    }
  }
  chomp @files;
  print "... done\n";
}

#############################################
sub get_id3_tags {
#############################################
  print "Collecting ID3-Tags from MP3-Files ";
  use MP3::Tag;

  my $cnt = 0;
  foreach my $filename (@files) {
    if (-e "$filename") {
      my $order_nr = 0;
      $cnt++;

      if (length($cnt)<2) { $order_nr = "00".$cnt; } elsif (length($cnt)<3) { $order_nr = "0".$cnt; } else { $order_nr = $cnt; }

      my $mp3 = MP3::Tag->new($filename);
      $mp3->config("autoinfo","ID3v1","ID3v2","filename");
      my ($song, $track, $artist, $album) = $mp3->autoinfo();
      push (@order, $order_nr);
      push (@title, $song);
      push (@artst, $artist);
      $mp3->close();
    }
  }
  print "...done\n";
}

#############################################
sub write_xls_file {
#############################################
  print "Writing to Excel-File: $xls_file ";
  use Spreadsheet::WriteExcel;

  my $end = @order;
  $end = $end - 1;
  my $max = 40; # means 41(!) 'cause 0..40 = 41

  my $book = Spreadsheet::WriteExcel->new($xls_file);
  my $sheet = $book->addworksheet($playlist);
  my $format = $book->addformat(font => "Arial", size => 6, bottom => 1, bottom_color => "black", color => "blue");
  my $format2 = $book->addformat(font => "Arial", size => 6, bottom => 1, bottom_color => "black", color => "blue", left => 4, left_color => "blue");

  foreach (@order) {
    #
    # Front Page of Booklet
    #
    for my $row (0..$max) {

      my $song = "";
      my $num  = "";
      if ($title[$row]) {
	$song  = $title[$row]." - ".$artst[$row];
	$num   = $order[$row];
      }

      my $song2 = "";
      my $num2  = "";
      if ($title[$row+$max+1]) {
	$song2 = $title[$row+$max+1]." - ".$artst[$row+$max+1];
	$num2  = $order[$row+$max+1];
      }

      $sheet -> write($row, 0, $num, $format);
      $sheet -> write($row, 1, $song, $format);
      $sheet -> write($row, 2, $num2, $format);
      $sheet -> write($row, 3, $song2, $format);
      $sheet -> set_row($row, 8);
    }

    #
    # Second Page of Booklet
    #
    my $remain = ($max*2)+2;
    for my $row2 (0..$max) {

      my $song3 = "";
      my $num3  = "";
      if ($title[$remain]) {
        $song3 = $title[$remain]." - ".$artst[$remain];
	$num3  = $order[$remain];
      }

      my $song4 = "";
      my $num4  = "";
      if ($title[$remain+$max+1]) {
	$song4 = $title[$remain+$max+1]." - ".$artst[$remain+$max+1];
	$num4  = $order[$remain+$max+1];
      }

      $remain++;

      $sheet -> write($row2, 4, $num3, $format2);
      $sheet -> write($row2, 5, $song3, $format);
      $sheet -> write($row2, 6, $num4, $format);
      $sheet -> write($row2, 7, $song4, $format);
    }
  }
  $sheet->set_landscape();
  $sheet->set_column(0,0,3);
  $sheet->set_column(1,1,26);
  $sheet->set_column(2,2,3);
  $sheet->set_column(3,3,26);
  $sheet->set_column(4,4,3);
  $sheet->set_column(5,5,26);
  $sheet->set_column(6,6,3);
  $sheet->set_column(7,7,26);
  $book->close();
  print "... done\n";
}