XLSPerl documentation

This is the documentation for XLSperl, version 0.3.

XLSperl can be downloaded from http://perl.jonallen.info/projects/xlstools 

TITLE
    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)

    -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

    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.4.9, 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 version 5.8.8.

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