/ 
XLSperl-0.7
River stage zero No dependents
/ XLSperl

NAME

XLSperl - use Perl "one-liners" with Microsoft Excel files

SYNOPSYS

XLSperl [options] -e '...' file1.xls file2.xls ... fileX.xls
cat file.txt | XLSperl [options] -e '...'

DESCRIPTION

Background

Perl "one-liners" have a great many uses for quick data processing tasks, often replacing the UNIX tools grep, sed, and awk. For example, a simple "grep" function can be written as:

perl -lne '/pattern/ and print' file.txt

which improves on the standard grep function by allowing the extended features of Perl regular expressions to be used.

However, this form of processing is only suitable for data that can be read (or needs to be written) in plain text format. XLSperl lets you use the same commands to process and create Microsoft Excel files, e.g. the following command will "grep" an Excel document:

XLSperl -lne '/pattern/ and print' file.xls

Usage

Basic usage of XLSperl is as follows:

XLSperl [options] -e 'perl_code' file1.xls file2.xls ... fileX.xls

Additionally Microsoft Excel files can be piped in to XLSperl

cat file.xls | XLSperl [options] -e 'perl_code'

Options

Options to XLSperl mirror the standard options to perl:

-n

Assumes an input loop which will iterate over each cell in the spreadsheet, assigning $_ with the cell's value.

-a

Changes the input loop to process Excel files row by row, splitting column values to @F and %F.

-F

Sets input record separator when processing text files (defaults to splitting on whitespace)

-l

Automated line-end processing - chomps the value of each cell and sets $/ to "\n"

-p

Prints the value of $_ after each iteration of the input loop

-e <perl_code>

Perl code to execute on each iteration of the input loop (required)

-m
-M

The -m and -M options load additional modules into XLSperl in the same way as the -m and -M options to perl (see http://perldoc.perl.org/perlrun.html.

-w

Enables warnings

-v

Prints version number and exits

Special variables

XLSperl adds the following special varables:

$WS

Worksheet name

$ROW

Current row (1 .. x)

$COL

Current column name (A .. x)

$COLNUM

Current column number (1 .. x)

$CELL

Current cell (A1 .. ZZx)

@F

Array of cell values (in autosplit mode)

%F

Hash of cell values (in autosplit mode). Entries in %F are aliases for the corresponding elements of @F, so modifying $F{A} will also update the value of $F[0] and vice-versa.

Exported functions

XLSperl adds the following new functions for use in your Perl code:

XLSprint( cell_1, cell_2, ... cell_X );

Outputs a row of data in Excel format. Usage is as follows:

# Print row to default filehandle
XLSprint @F;

# Print row to named filehandle
XLSprint STDERR @F;
XLSprint $fh,@F;

Note that once a filehandle has been used with the XLSprint function, to avoid corruption of the generated Excel file the 'normal' print function should not be used on that filehandle, i.e. do not do this:

XLSprint STDERR @errors;
warn "An error happened";

Examples

Use a regular expression to extract data from a spreadsheet
XLSperl -nle "/[A-Z](\d+)\d/ and print $1" cells.xls
Basic conversion from XLS to CSV
XLSperl -nale 'print join ",",@F' file.xls >file.csv
More correct conversion from XLS to CSV (thanks to Sam Vilain):
XLSperl -MText::CSV_XS -nale 'BEGIN{$c=Text::CSV_XS->new} if ($w ne $WS){open CSV,">$ARGV.$WS.csv" or die $!;$w = $WS} $c->print(\*CSV,\@F)' file1.xls file2.xls
Extract a single row from a spreadsheet
cat file1.xls | XLSperl -nle 'print if ($ROW == 2)'
Convert a text file to Excel format, removing comment lines
XLSperl -F: -nale 'next if /^#/; XLSprint @F' /etc/passwd >passwd.xls

SYSTEM REQUIREMENTS

XLSperl binary packages have no external dependencies, and have been tested on the following platforms:

  • Linux i686 (tested on Ubuntu version 6.0.6)

  • Microsoft Windows (tested on Windows XP SP 2)

  • Mac OS X (tested on OS X 10.5.3, Intel CPU only)

To run XLSperl from source, the following CPAN modules must be installed:

  • Spreadsheet::ParseExcel (tested with version 0.28)

  • Spreadsheet::WriteExcel::Simple (tested with version 1.04)

  • Variable::Alias (tested with version 0.01)

XLSperl has been tested with Perl versions 5.8.8 and 5.10.0.

TODO

  • Include a mechanism for creation and in-place editing of Excel documents.

  • Ability to use XLSperl as a command interpreter (#! /usr/bin/XLSperl in scripts).

SEE ALSO

XLSperl homepage - http://perl.jonallen.info/xlstools
Excel on the Command Line talk slides - http://perl.jonallen.info/talks
Minimal Perl by Tim Maher - http://minimalperl.com

AUTHOR

Written by Jon Allen <jj@jonallen.info>

COPYRIGHT and LICENSE

Copyright (C) 2007 Jon Allen

This software is licensed under the terms of the Artistic License version 2.0.

For full license details, please read the file 'artistic-2_0.txt' included with this distribution, or see http://www.perlfoundation.org/legal/licenses/artistic-2_0.html