File Coverage

blib/lib/Excel/Grinder.pm

Criterion	Covered	Total	%
statement	72	75	96.0
branch	13	24	54.1
condition	5	12	41.6
subroutine	8	8	100.0
pod	3	3	100.0
total	101	122	82.7

package Excel::Grinder;

our $VERSION = "1.2";

# time to grow up
use strict;
use warnings;
use Carp;

# this stands on the feet of giants
use Excel::Writer::XLSX;
use Spreadsheet::XLSX;

# OO out of habit
sub new {
	my ($class, $default_directory) = @_;

	my $path_separator = '/';
	my $temp_directory = '/tmp';

	if( $^O eq "MSWin32" ){
		$path_separator = '\\';
		$temp_directory = $ENV{'temp'};
	}
	# default the default directory to /tmp/excel_grinder
	$default_directory ||= $temp_directory . $path_separator . 'excel_grinder';

	# make sure that directory exists
	mkdir $default_directory if !(-d $default_directory);

	# if it still does exist, bail out
	croak "Error: $default_directory does not exist and cannot be auto-created." if !(-d $default_directory);

	# become!
	my $self = bless {
		'default_directory' => $default_directory,
		'path_separator' => $path_separator,
	}, $class;

	return $self;
}

# method to convert a three-level array into a nice excel file
sub write_excel {
	# required arguments are (1) the filename and (2) the data structure to turn into an XLSX file
	my ($self, %args) = @_;
	# looks like:
	#	'filename' => 'some_file.xlsx', # will be saved under /opt/majestica/tmp/DATABASE_NAME/some_file.xlsx; required
	#	'the_data' => @$three_level_arrayref, # worksheets->rows->columns; see below; required
	#	'headings_in_data' => 1, # if filled, first row of each worksheet will be captialized; optional
	#	'worksheet_names' => ['Names','of','Worksheets'], # if filled, will be the names to give the worksheets

	my ($tmp_dir, $item, $col, @bits, $workbook, $worksheet_data, $worksheet, $n, $row_array, $row_upper, $worksheet_name);

	# ensure path separator is escaped properly
	my $separator_regex = qq(\\$self->{path_separator});

	# fail without a filename
	croak 'Error: Filename required for write_excel()' if !$args{filename};

	# the data structure must be an array of arrays of arrays
	# three levels: worksheets, rows, columns
	croak 'Error: Must send a three-level arrayref (workbook->rows->columns) to write_excel()' if !$args{the_data}[0][0][0];

	# place into default_directory unless they specified a directory

	$args{filename} = $self->{default_directory}.$self->{path_separator}.$args{filename} if $args{filename} !~ m#$separator_regex#;
	$args{filename} .= '.xlsx' if $args{filename} !~ /\.xlsx$/;

	# start our workbook
	$workbook = Excel::Writer::XLSX->new( $args{filename} );

	# Set the format for dates.
	my $date_format = $workbook->add_format( num_format => 'mm/dd/yy' );

	# start adding worksheets
	foreach $worksheet_data (@{ $args{the_data} }) {
		$worksheet_name = shift @{ $args{worksheet_names} }; # if it's there
		$worksheet_name =~ s/[^0-9a-z\-\s]//gi; # clean it up
		$worksheet = $workbook->add_worksheet($worksheet_name);

		# go thru each row...
		$n = 0;
		foreach $row_array (@$worksheet_data) {

			# do they want the first row to the headings?
			if ($args{headings_in_data} && $n == 0) { # uppercase the first row
				@$row_upper = map { uc($_) } @$row_array;
				$row_array = $row_upper;
			}

			# now each column...
			$col = 0;
			foreach $item (@$row_array) {
				# dates are no funzies
				if ($item =~ /^(\d{4})-(\d{2})-(\d{2})$/) { # special routine for dates
					$worksheet->write_date_time( $n, $col++, $1.'-'.$2.'-'.$3.'T', $date_format );
				} else {
					 $worksheet->write( $n, $col++, $item );
				}

			}
			$n++;
		}
	}

	# that's not so hard, now is it?
	return $args{filename};
}

# method to import an excel file into a nice three-level array
sub read_excel {
	# require argument is the filename or  full path to the excel xlsx file
	# if it's just a filename, look in the default directory
	my ($self,$filename) = @_;

	# ensure path separator is escaped properly
	my $separator_regex = qq(\\$self->{path_separator});

	$filename = $self->{default_directory}.$self->{path_separator}.$filename if $filename !~ m#$separator_regex#;
	$filename .= '.xlsx' if $filename !~ /\.xlsx$/;

	# gotta exist, after all that
	croak 'Error: Must send a valid full file path to an XLSX file to read_excel()' if !(-e "$filename");

	my ($excel, $sheet_num, $sheet, $row_num, $row, @the_data, $cell, $col);

	# again, stand on the shoulders of giants
	$excel = Spreadsheet::XLSX->new($filename);

	# read it in, sheet by sheet
	$sheet_num = 0;
	foreach $sheet (@{$excel->{Worksheet}}) {

		# set the max = 0 if there is one or none rows
		$sheet->{MaxRow} ||= $sheet->{MinRow};

		# same for the columns
		$sheet->{MaxCol} ||= $sheet->{MinCol};

		# cycle through each row
		$row_num = 0;
		foreach $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
			# go through each available column
			foreach $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {

                # get ahold of the actual cell object
				$cell = $sheet->{Cells}[$row][$col];

				# next if !$cell; # skip if blank

				# add it to our nice array
				push (@{ $the_data[$sheet_num][$row] }, $cell->{Val} );
			}
			# advance
			$row_num++;
        }
		$sheet_num++;
	}

	# send it back
	return \@the_data;
}

1;

__END__