WebSVN – eFlore/Applications.cel – Path Comparison – / – /trunk/jrest/tests/ Rev 1802 and /trunk/jrest/tests/ Rev 1803

Ignore whitespace Rev 1802 → Rev 1803

 /trunk/jrest/tests/bin/csv2xls
 New file
 ,0 → 1,203
+#!/usr/bin/env perl
+# csv2xls: Convert csv to xls
+#          (m)'11 [06 Oct 2011] Copyright H.M.Brand 2007-2013
+use strict;
+use warnings;
+our $VERSION = "1.71";
+sub usage
+{
+    my $err = shift and select STDERR;
+    print <<EOU;
+usage: csv2xls [-s <sep>] [-q <quot>] [-w <width>] [-d <dtfmt>]
+               [-o <xls>] [file.csv]
+       -s <sep>   use <sep>   as seperator char. Auto-detect, default = ','
+                  The string "tab" is allowed.
+       -e <esc>   use <sep>   as seperator char. Auto-detect, default = ','
+                  The string "undef" is allowed.
+       -q <quot>  use <quot>  as quotation char. Default = '"'
+                  The string "undef" will disable quotation.
+       -w <width> use <width> as default minimum column width (4)
+       -o <xls>   write output to file named <xls>, defaults
+                  to input file name with .csv replaced with .xls
+                  if from standard input, defaults to csv2xls.xls
+       -F         allow formula's. Otherwise fields starting with
+                  an equal sign are forced to string
+       -f         force usage of <xls> if already exists (unlink before use)
+       -d <dtfmt> use <dtfmt> as date formats.   Default = 'dd-mm-yyyy'
+       -D cols    only convert dates in columns <cols>. Default is everywhere.
+       -u         CSV is UTF8
+       -v [<lvl>] verbosity (default = 1)
+EOU
+    exit $err;
+    } # usage
+use Getopt::Long qw(:config bundling nopermute passthrough);
+my $sep;        # Set after reading first line in a flurry attempt to auto-detect
+my $quo = '"';
+my $esc = '"';
+my $wdt = 4;    # Default minimal column width
+my $xls;        # Excel out file name
+my $frc = 0;    # Force use of file
+my $utf = 0;    # Data is encoded in Unicode
+my $frm = 0;    # Allow formula's
+my $dtf = "dd-mm-yyyy"; # Date format
+my $opt_v = 1;
+my $dtc;
+GetOptions (
+    "help|?"    => sub { usage (0); },
+    "c|s=s"     => \$sep,
+    "q=s"       => \$quo,
+    "e=s"       => \$esc,
+    "w=i"       => \$wdt,
+    "o|x=s"     => \$xls,
+    "d=s"       => \$dtf,
+    "D=s"       => \$dtc,
+    "f"         => \$frc,
+    "F"         => \$frm,
+    "u"         => \$utf,
+    "v:1"       => \$opt_v,
+    ) or usage (1);
+my $title = @ARGV && -f $ARGV[0] ? $ARGV[0] : "csv2xls";
+($xls ||= $title) =~ s/(?:\.csv)?$/.xls/i;
+-s $xls && $frc and unlink $xls;
+if (-s $xls) {
+    print STDERR "File '$xls' already exists. Overwrite? [y/N] > N\b";
+    scalar <STDIN> =~ m/^[yj](es|a)?$/i or exit;
+    }
+# Don't split ourselves when modules do it _much_ better, and follow the standards
+use Text::CSV_XS;
+use Date::Calc qw( Delta_Days Days_in_Month );
+use Spreadsheet::WriteExcel;
+use Encode qw( from_to );
+my $csv;
+my $wbk = Spreadsheet::WriteExcel->new ($xls);
+my $wks = $wbk->add_worksheet ();
+   $dtf =~ s/j/y/g;
+my %fmt = (
+    date        => $wbk->add_format (
+        num_format      => $dtf,
+        align           => "center",
+        ),
+    rest        => $wbk->add_format (
+        align           => "left",
+        ),
+    );
+my ($h, $w, @w) = (0, 1); # data height, -width, and default column widths
+my $row;
+my $firstline;
+unless ($sep) { # No sep char passed, try to auto-detect;
+    while (<>) {
+        m/\S/ or next;  # Skip empty leading blank lines
+        $sep = # start auto-detect with quoted strings
+               m/["\d];["\d;]/  ? ";"  :
+               m/["\d],["\d,]/  ? ","  :
+               m/["\d]\t["\d,]/ ? "\t" :
+               # If neither, then for unquoted strings
+               m/\w;[\w;]/      ? ";"  :
+               m/\w,[\w,]/      ? ","  :
+               m/\w\t[\w,]/     ? "\t" :
+                                  ";"  ;
+            # Yeah I know it should be a ',' (hence Csv), but the majority
+            # of the csv files to be shown comes from fucking Micky$hit,
+            # that uses semiColon ';' instead.
+        $firstline = $_;
+        last;
+        }
+    }
+$csv = Text::CSV_XS-> new ({
+    sep_char       => $sep eq "tab"   ? "\t"  : $sep,
+    quote_char     => $quo eq "undef" ? undef : $quo,
+    escape_char    => $esc eq "undef" ? undef : $esc,
+    binary         => 1,
+    keep_meta_info => 1,
+    auto_diag      => 1,
+    });
+if ($firstline) {
+    $csv->parse ($firstline) or die $csv->error_diag ();
+    $row = [ $csv->fields ];
+    }
+if ($opt_v > 3) {
+    foreach my $k (qw( sep_char quote_char escape_char )) {
+        my $c = $csv->$k () || "undef";
+        $c =~ s/\t/\\t/g;
+        $c =~ s/\r/\\r/g;
+        $c =~ s/\n/\\n/g;
+        $c =~ s/\0/\\0/g;
+        $c =~ s/([\x00-\x1f\x7f-\xff])/sprintf"\\x{%02x}",ord$1/ge;
+        printf STDERR "%-11s = %s\n", $k, $c;
+        }
+    }
+if (my $rows = $dtc) {
+    $rows =~ s/-$/-999/;                        # 3,6-
+    $rows =~ s/-/../g;
+    eval "\$dtc = { map { \$_ => 1 } $rows }";
+    }
+while ($row && @$row or $row = $csv->getline (*ARGV)) {
+    my @row = @$row;
+    @row > $w and push @w, ($wdt) x (($w = @row) - @w);
+    foreach my $c (0 .. $#row) {
+        my $val = $row[$c] || "";
+        my $l = length $val;
+        $l > $w[$c] and $w[$c] = $l;
+        if ($utf and $csv->is_binary ($c)) {
+            from_to ($val, "utf-8", "ucs2");
+            $wks->write_unicode ($h, $c, $val);
+            next;
+            }
+        if ($csv->is_quoted ($c)) {
+            if ($utf) {
+                from_to ($val, "utf-8", "ucs2");
+                $wks->write_unicode ($h, $c, $val);
+                }
+            else {
+                $wks->write_string  ($h, $c, $val);
+                }
+            next;
+            }
+        if (!$dtc or $dtc->{$c + 1}) {
+            my @d = (0, 0, 0);  # Y, M, D
+            $val =~ m/^(\d{4})(\d{2})(\d{2})$/   and @d = ($1, $2, $3);
+            $val =~ m/^(\d{2})-(\d{2})-(\d{4})$/ and @d = ($3, $2, $1);
+            if ( $d[2] >=    1 && $d[2] <=   31 &&
+                 $d[1] >=    1 && $d[1] <=   12 &&
+                 $d[0] >= 1900 && $d[0] <= 2199) {
+                my $dm = Days_in_Month (@d[0,1]);
+                $d[2] <   1 and $d[2] = 1;
+                $d[2] > $dm and $d[2] = $dm;
+                my $dt = 2 + Delta_Days (1900, 1, 1, @d);
+                $wks->write ($h, $c, $dt, $fmt{date});
+                next;
+                }
+            }
+        if (!$frm && $val =~ m/^=/) {
+            $wks->write_string  ($h, $c, $val);
+            }
+        else {
+            $wks->write ($h, $c, $val);
+            }
+        }
+    ++$h % 100 or $opt_v && printf STDERR "%6d x %6d\r", $w, $h;
+    } continue { $row = undef }
+$opt_v && printf STDERR "%6d x %6d\n", $w, $h;
+$wks->set_column ($_, $_, $w[$_]) for 0 .. $#w;
+$wbk->close ();
 Property changes:
 Added: svn:executable
 +*
 \ No newline at end of property

 /trunk/jrest/tests/bin/xls2csv-separate-sheets.pl
 New file
 ,0 → 1,159
+#!/usr/bin/env perl
+use strict;
+use warnings;
+use Modern::Perl;
+use autodie qw{ :all };
+use utf8;
+use File::Spec;
+use Smart::Comments '####';
+use Getopt::Euclid;
+use Text::CSV;
+use File::Basename;
+use Spreadsheet::ParseExcel;
+use Text::Trim;
+my $input_filename = $ARGV{'--infile'};
+warn "Warning: The input file does not look like an Excel spreadsheet file."
+    unless looks_like_xls($input_filename);
+my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($input_filename);
+my ($inbase,$inpath,$insuffix) = fileparse($input_filename, qr{\.[^.]*});
+my $output_base = File::Spec->catfile($inpath, $inbase);
+my $csv = Text::CSV->new();
+for my $worksheet ( $workbook->worksheets() ) { #### Processing worksheets (% done)...
+    my ( $row_min, $row_max ) = $worksheet->row_range();
+    my ( $col_min, $col_max ) = $worksheet->col_range();
+    my @sheet_data;
+    for my $row ( $row_min .. $row_max ) {
+        for my $col ( $col_min .. $col_max ) {
+            my $cell = $worksheet->get_cell( $row, $col ) or undef;
+            next unless $cell;
+            $sheet_data[$row][$col] = trim($cell->value());
+        }
+    }
+    ### @sheet_data;
+    my $ws_name = $worksheet->{Name};
+    $ws_name =~ s{\s}{_}xsmg;   # no spaces
+    my $csvname = "${output_base}-${ws_name}.csv";
+    ### $csvname
+    my $output = IO::File->new($csvname, '>') or die $!;
+    ### $output
+    foreach my $line (@sheet_data) {
+        ### $line
+        $csv->print($output, $line) or die $csv->error_diag();
+        print $output "\n";
+    }
+    close $output;
+}
+sub extract_sheet_contents {
+    my $sheet = $_[0];
+    my ($nrow, $ncol) = ($sheet->{maxrow}, $sheet->{maxcol});
+    return extract_rect_from_listref($sheet->{cell}, 1, $nrow, 1, $ncol);
+}
+sub extract_slice_of_listref {
+    my ($listref, @slice) = @_;
+    return [ map { $listref->[$_] } @slice ];
+}
+sub extract_rect_from_listref {
+    my ($listref, $row_start, $row_end, $col_start, $col_end) = @_;
+    return [ map {
+        extract_slice_of_listref($_, $col_start..$col_end)
+    } @{extract_slice_of_listref($listref, $row_start..$row_end)} ];
+}
+sub looks_like_xls {
+    state $xls_regex = qr{\.xls$};
+    return 1 if $_[0] =~ m{$xls_regex}i;
+    return;
+}
+__END__
+=head1 NAME
+spreadsheet2csv-separate-sheets.pl - Split a spreadsheet into one csv file for each worksheet
+=head1 VERSION
+Version 1.0
+=head1 USAGE
+    progname [options]
+=head1 REQUIRED ARGUMENTS
+=over
+=item --infile [=] <file> | -i <file>
+The input spreadsheet file.
+=for Euclid:
+    file.type: readable
+    file.default: '-'
+=back
+=head1 OPTIONS
+=over
+=item --version
+=item --usage
+=item --help
+=item --man
+Print the usual program information
+=back
+=head1 DESCRIPTION
+This program will read a spreadsheet file and output one csv file for
+each worksseht in the input file. The name of each output file will be
+determined by the name of the input file and the name of the
+worksheet. For example, a worksheet "Sheet1" in a file called
+"reports.xls" will be output to "reports-Sheet1.csv".
+=head1 NOTES
+Empty rows and columns at the beginning of a worksheet will be
+omitted. So if a worksheet has columns C through F filled, then the
+output for that sheet will have exactly 4 columns, not 6.
+=head1 AUTHOR
+Ryan C. Thompson
+=head1 BUGS
+If you encounter a problem with this program, please email
+rct+perlbug@thompsonclan.org. Bug reports and other feedback are
+welcome.
+=head1 COPYRIGHT
+Copyright (c) 2010, Ryan C. Thompson
 Property changes:
 Added: svn:executable
 +*
 \ No newline at end of property

 /trunk/jrest/tests/phptests/determ-espece-bdnffnt.result.php
 ,7 → 1,7
 <?php return array (
   'nom_sel' => 'Viola riviniana subsp. riviniana',
   'nom_sel_nn' => '72478',
-  'nom_ret' => 'Viola riviniana subsp. riviniana ',
+  'nom_ret' => 'Viola riviniana subsp. riviniana',
   'nom_ret_nn' => '72478',
   'nt' => '5757',
   'famille' => 'Violaceae',

 /trunk/jrest/tests/api-old.php
 ,9 → 182,9
         $URL = DOMAIN . '/jrest/InventoryImportExcel';
         if(!is_file($d)) return FALSE;
         exec("csvtool -o /tmp/a.csv col 1- $d");
-        exec("/home/raphael/bin/csv2xls -v 0 -f -o /tmp/a /tmp/a.csv");
-        //if(DEBUG) fwrite(STDERR, "/home/raphael/bin/csv2xls -v 0 -f -o /tmp/a $d\n");
-        if(DEBUG) fwrite(STDERR, "/home/raphael/bin/csv2xls -v 0 -f -o /tmp/a /tmp/a.csv\n");
+        exec(__DIR__ . "/bin/csv2xls -v 0 -f -o /tmp/a /tmp/a.csv");
+        //if(DEBUG) fwrite(STDERR, __DIR__ . "/bin/csv2xls -v 0 -f -o /tmp/a $d\n");
+        if(DEBUG) fwrite(STDERR, __DIR__ . "/bin/csv2xls -v 0 -f -o /tmp/a /tmp/a.csv\n");
         $d = "/tmp/a.xls";
         $ch = curl_init();

 /trunk/jrest/tests/IO-fields-unittest.php
 ,8 → 51,7
         define('COOKIE', isset($options['P']) ? $options['P'] : $options['phpsessid']);
 }
-//require_once('api.php');
-require_once('api-old.php');
+require_once('api.php');
 if(!auth()) {
         die('auth problem');

 /trunk/jrest/tests/api.php
 ,7 → 149,8
         $sess = $id = array();
         preg_match('/.*PHPSESSID=(\w+)/', $result, $sess);
         preg_match('/"id_utilisateur":"(\d+)"/', $result, $id);
-        if(DEBUG) fwrite(STDERR, "curl \"".$URL ."\" : {$sess[1]} / {$id[1]}\n");
+        if(DEBUG) @fwrite(STDERR, "curl \"".$URL ."\" : {$sess[1]} / {$id[1]}\n");
         if(isset($sess[1])) {
                 define('COOKIE', $sess[1]);
                 if(!defined('USER')) {
 ,6 → 203,38
         return $result;
 }
+function importXLS($d) {
+        $URL = DOMAIN . '/jrest/ImportXLS';
+        if(!is_file($d)) return FALSE;
+        exec("csvtool -o /tmp/a.csv col 1- $d");
+        exec(__DIR__ . "/bin/csv2xls -v 0 -f -o /tmp/a /tmp/a.csv &>/dev/null");
+        if(DEBUG) fwrite(STDERR,
+                                         "csvtool -o /tmp/a.csv col 1- $d;\n" .
+                                         __DIR__ . "/bin/csv2xls -v 0 -f -o /tmp/a /tmp/a.csv\n");
+        $d = "/tmp/a.xls";
+        if(!is_file($d)) { die('error: ' . __FILE__ . ':' . __LINE__); }
+        $ch = curl_init();
+        // curl does not support setting filename="X.csv";type=application/octet-stream'
+        // with plain POST variables
+        curl_setopt($ch,CURLOPT_URL, $URL);
+        curl_setopt($ch,CURLOPT_POSTFIELDS, array(
+                'upload' => '@' . $d . ';filename=test.xls',
+                'utilisateur' => USER
+        ));
+        curl_setopt($ch,CURLOPT_RETURNTRANSFER, TRUE);
+        if(DEBUG) fwrite(STDERR, "curl -F \"upload=@$d;filename=test.xls\" -F utilisateur=" . USER . " \"$URL\"\n");
+        //execute post
+        $result = curl_exec($ch);
+        curl_close($ch);
+        return $result;
+}
 function export($cols = "standard,avance") {
         if(!defined('COOKIE')) return FALSE;
 ,9 → 247,9
         curl_setopt($ch,CURLOPT_URL, $URL);
         curl_setopt($ch,CURLOPT_COOKIE, 'PHPSESSID=' . COOKIE);
+        curl_setopt($ch,CURLOPT_RETURNTRANSFER, TRUE);
         if(DEBUG) fwrite(STDERR, "curl -b PHPSESSID=" . COOKIE . ' "' . $URL ."\"\n");
-        curl_setopt($ch,CURLOPT_RETURNTRANSFER, TRUE);
         //execute post
         $result = curl_exec($ch);
         curl_close($ch);
 ,6 → 256,46
         return $result;
 }
+function exportXLS($cols = "standard,avance") {
+        if(!defined('COOKIE')) return FALSE;
+        $file = "/tmp/b.xls";
+        $URL = DOMAIN . '/jrest/CelWidgetExport/export?';
+        $URL .= http_build_query(array('colonnes' => $cols,
+                                                                   'id_utilisateur' => USER,
+                                                                   'format' => 'xls'));
+        $ch = curl_init();
+        curl_setopt($ch,CURLOPT_URL, $URL);
+        curl_setopt($ch,CURLOPT_COOKIE, 'PHPSESSID=' . COOKIE);
+        $fh = fopen($file, 'w');
+        curl_setopt($ch,CURLOPT_FILE, $fh);
+        if(DEBUG) fwrite(STDERR, "curl -o $file -b PHPSESSID=" . COOKIE . ' "' . $URL ."\"\n");
+        //execute post
+        curl_exec($ch);
+        fflush($fh);
+        fclose($fh);
+        curl_close($ch);
+        // if(DEBUG) fwrite(STDERR, "xls2csv $file\n");
+        // exec("xls2csv $file", $result, $ret);
+        // return implode("\n", $result);
+        if(DEBUG) fwrite(STDERR, __DIR__ . "/bin/xls2csv-separate-sheets.pl -i $file\n");
+        exec(__DIR__ . "/bin/xls2csv-separate-sheets.pl -i $file &>/dev/null", $result, $ret);
+        $newfile = substr($file, 0, -4) . '-Liste' . '.csv';
+        // because xls2csv-separate-sheets.pl does latin9
+        if(DEBUG) fwrite(STDERR, "iconv -f latin9 -t utf8 $newfile >| /tmp/.x; mv -f /tmp/.x $newfile\n");
+        exec("iconv -f latin9 -t utf8 -o /tmp/.x $newfile; mv -f /tmp/.x $newfile");
+        if($ret) die('error: ' . __FILE__ . ':' . __LINE__);
+        return file_get_contents($newfile);
+}
 function getCSV_line($csv, $line) {
         $line = max(0, intval($line - 1)); // -1 à cause du header
 ,6 → 308,10
         $l = fgetcsv($out);
         fclose($out);
         if(!$l) return FALSE;
+        // xls2csv-separate-sheets.pl supprime les trailing champs vides
+        if(count($l) != count($head)) {
+                $l += array_fill(count($l), count($head) - count($l), "");
+        }
         return array_combine($head, $l);
 }

Subversion Repositories eFlore/Applications.cel

Compare Revisions

Ignore whitespace Rev 1802 → Rev 1803