view release on metacpan or  search on metacpan

lib/Excel/Grinder.pm  view on Meta::CPAN

	$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__

=head1 NAME

Excel::Grinder - Import/export plain Excel (XLSX) files as simply as possible.

=head1 DESCRIPTION / PURPOSE

This module should help you read/write XLSX spreadsheets to/from Perl arrays 
as simply as possible. The use cases are (1) when you need to export data from 
your database/application for non-programmers to enjoy in their beloved Excel 
and (2) when you need to allow for batch import/update operations via 
user-provided Excel.

There are so many awesome things you can do with Excel (formatting, formulas, 
pivot tables, etc.) but this module does none of that.  This is for the basic 
read-it-in and write-it-out -- which might just fit the bill.

This module will read an Excel (XLSX) file into a three-level arrayref.  The first
level is the worksheets, second level is the rows, and third level is the cells, such that:

	$$my_data[4][2][10] --> Worksheet 5, Row 3, Column 11 (aka as Column K)
	
Form a three-level arrayref to represent worksheets/rows/cells in this way, and you can create
a plain Excel XLSX file.  No formatting or formulas.  Ready for Tableau or just to confuse
your favorite front-line manager.

I put this together because I was offended at how difficult it is just to create an Excel
file in certain non-Perl environments, and since Excel is just a part of life for so many of us,
it really should be dead-simple.

To pursue additional Excel features, please see the excellent L<Excel::Writer::XLSX> and 
L<Spreadsheet::XLSX> modules, of which this module is just a simple abstraction.

=head1 SYNOPSIS

	# create the object to read/write excel files
	my $xlsx = Excel::Grinder->new('/opt/data/excel_files'); 
	# the directory can be anywhere that is writable; leave blank for /tmp/excel_grinder

	# to create a two-worksheet Excel workbook at /opt/data/excel_files/our_family.xlsx
	my $full_file_path = $xlsx->write_excel(
		'filename' => 'our_family.xlsx',
		'headings_in_data' => 1,
		'worksheet_names' => ['Dogs','People'],
		'the_data' => [
			[
				['Name','Main Trait','Age Type'],
				['Ginger','Wonderful','Old'],
				['Pepper','Loving','Passed'],
				['Polly','Fun','Young'],
				['Daisy','Crazy','Puppy']
			],
			[
				['Name','Main Trait','Age Type'],
				['Melanie','Smart','Oldish'],
				['Lorelei','Fun','Young'],
				['Eric','Fat','Old']
			]
		],
	);	
	
	# if you prebuilt had that three-level array in $our_family_data:
	$full_file_path = $xlsx->write_excel(
		'filename' => 'our_family.xlsx',
		'headings_in_data' => 1,
		'worksheet_names' => ['Dogs','People'],	
		'the_data' => $our_family_data
	);
	
	# to read that spreadsheet back into an three-level arrayref that is just like
	# what we fed in to write_excel() above:	
	my $family_data = $xlsx->read_excel('our_family.xlsx');

	# Now you can modify or add to $family_data, and overwrite our_family.xlsx 
	# or create another XLSX file.

=head1 METHODS

=head2 new()

Creates a new object to use this module.  Accepts a 'default directory' path for where
to save / load the Excel files:

	$xlsx = Excel::Grinder->new('/home/ginger/excel_files');