| /trunk/jrest/tests/phptests/determ-espece-bdnffnt.result.php |
|---|
| 1,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 |
|---|
| 182,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 |
|---|
| 51,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 |
|---|
| 149,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')) { |
| 202,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; |
| 214,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); |
| 223,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 |
| 235,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); |
| } |
| /trunk/jrest/tests/bin/csv2xls |
|---|
| New file |
| 0,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,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 |