On Wednesday 12 August 2009, Les Denham wrote:
> On Wednesday 12 August 2009, Paul A. Rubin wrote:
> > Dynamic link (user sees a table in the LyX doc, and can interact
> > with it, making changes in the spreadsheet). This would typically occur
> > with the user reading the finished product (PDF, DVI) in a viewer, not
> > reading it in LyX, so you get into issues of whether the viewer program
> > supports this sort of embedding. For instance, if you export the
> > document in HTML and view it in a browser, I think there are browser
> > plugins that let you edit a spreadsheet in situ (although I confess I've
> > never done it).
>
> This kind of approach is possible in theory: for example, if you have a
> figure in LyX which is a Grace file, editing that file in Grace will change
> the figure in Lyx immediately. That is not the same as just editing an
> image: the Grace file is actually displayed in LyX using Grace in a command
> line mode.
>
> The problem with a spreadsheet is that Excel does not (as far as I know)
> have a command line interface. Neither does OpenOffice.org. Gnumeric has
> a Python API which might be usable, but I haven't tried it. The best
> possibility I'm aware of is the Perl module XLSperl
> (http://search.cpan.org/~jonallen/XLSperl-0.7/bin/XLSperl) which could be
> used to build an image from specified sheet, rows and columns. Once you
> have done this, the Perl script could be used in a converter (XLS->EPS, for
> example). But I don't think it would be a trivial task to do this.
If all you need is row+column data from an excel spreadsheet, you could also
use the perl module Spreadsheet::ParseExcel
to parse the excel file, and then write the cells out in latex tabular format.
Then just \input{exceltable.tex}. I've done something similar to generate
longtables using excel data. Of course if you want charts, etc. from the excel
file, that would be more difficult.
You could use something like the following perl code to parse your excel file
and output a latex table:
#!/usr/bin/perl
use strict;
use warnings;
#use Text::CSV;
use Text::CSV_XS;
use Spreadsheet::ParseExcel;
if($#ARGV!=1){
print "Usage $0 excelfile outfile";
exit;
}
my $file = $ARGV[0];
my $ofile = $ARGV[1];
open (TEX, ">$ofile") or die $!;
my @fields;
&process_xls();
close TEX
exit;
# need to quote latex special characters
sub latexquote(){
my $s=$_[0];
$s =~ s/&/\\&/g;
$s =~ s/#/\\#/g;
$s =~ s/\$/\\\$/g;
$s =~ s/%/\\%/g;
$s =~ s/{/\\{/g;
$s =~ s/}/\\}/g;
$s =~ s/_/\\_/g;
$s =~ s/\.\ /\.\\\ /g;
return $s;
1;
}
sub print_record()
{
my $flds = $_[0];
if(ref($flds) ne 'ARRAY' ) { die "Expected array ref, not $flds\n"; }
#unless (@_ == 1 && ref($flds) eq 'ARRAY') { die "usage:
print_record(array_ref)\n"; }
my $l...@$flds;
for(my $i=0; $i<$len; $i++)
{
print(TEX, "\&") if($i !=0); #Tab Character
print(TEX, &latexquote($flds->[$i]);
} print(TEX, "\\\\ \n"); #End of Row
}
sub process_xls()
{
my $oExcel = new Spreadsheet::ParseExcel;
my $oBook = $oExcel->Parse($ARGV[0]);
my($iR, $iC, $oWkS, $oWkC, $fld);
print "FILE :", $oBook->{File} , "\n";
print "COUNT :", $oBook->{SheetCount} , "\n";
print "AUTHOR:", $oBook->{Author} , "\n" if defined $oBook->{Author};
print(TEX, "\\begin{tabular}");
#print {column formatting}
#print column headings
# Loop over sheets
for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
{
$oWkS = $oBook->{Worksheet}[$iSheet];
print "--------- SHEET:", $oWkS->{Name}, "\n";
# Loop over all records
for($iR = $oWkS->{MinRow} ;
defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;
$iR++)
{
@fields=(); # Clear fields array
#printf "%04d: ", $iR;
# Loop over fields
for($iC = $oWkS->{MinCol} ;
defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;
$iC++)
{
$oWkC = $oWkS->{Cells}[$iR][$iC];
#print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);
$fld = ($oWkC)? $oWkC->Value : "";
push(@fields, $fld);
#print ", " if($iC != $oWkS->{MinCol});
#print "\"", $fld, "\"";
#print "\n" if ($iC == $oWkS->{MaxCol});
}
&print_record(\...@fields);
}
}
print(TEX,"\\end{tabular}");
}
