Spreadsheet-Edit
view release on metacpan or search on metacpan
lib/Spreadsheet/Edit/IO.pm view on Meta::CPAN
# License: http://creativecommons.org/publicdomain/zero/1.0/
# (CC0 or Public Domain). To the extent possible under law, the author,
# Jim Avera (email jim.avera at gmail dot com) has waived all copyright and
# related or neighboring rights to this document. Attribution is requested
# but not required.
use strict; use warnings FATAL => 'all'; use utf8;
use feature qw(say state lexical_subs current_sub);
no warnings qw(experimental::lexical_subs);
package Spreadsheet::Edit::IO;
# Allow "use <thismodule. VERSION ..." in development sandbox to not bomb
{ no strict 'refs'; ${__PACKAGE__."::VER"."SION"} = 1999.999; }
our $VERSION = '1001.004'; # VERSION from Dist::Zilla::Plugin::OurPkgVersion
our $DATE = '2026-05-30'; # DATE from Dist::Zilla::Plugin::OurDate
# This module is derived from the old never-released Text:CSV::Spreadsheet
use Exporter 'import';
our @EXPORT = qw/convert_spreadsheet OpenAsCsv cx2let let2cx cxrx2sheetaddr
sheetname_from_spec filepath_from_spec
form_spec_with_sheetname/;
our @EXPORT_OK = qw/can_cvt_spreadsheets can_extract_allsheets can_extract_named_sheet
openlibreoffice_path
@sane_CSV_read_options @sane_CSV_write_options/;
# TODO: Provide "known_attributes" function ala Text::CSV::known_attributes()
use version ();
use Carp;
use File::Copy ();
use File::Copy::Recursive ();
use File::Glob qw/bsd_glob/;
use Path::Tiny 0.146 qw/path/;
# Path::Tiny OBVIATES NEED for many but we still need this
use File::Spec ();
use File::Spec::Functions qw/devnull tmpdir rootdir catdir catfile/;
# Still sometimes convenient...
use File::Basename qw(basename);
use File::BOM ();
use File::Which qw/which/;
use URI::file ();
use Guard qw(guard scope_guard);
use Fcntl qw(:flock :seek);
use Symbol qw/qualify_to_ref/;
use Scalar::Util qw/blessed openhandle/;
use List::Util qw/none all notall first min max/;
use Encode qw(encode decode);
use File::Glob qw/bsd_glob GLOB_NOCASE/;
use Digest::MD5 qw/md5_base64/;
use Data::Hexify qw/Hexify/;
use Text::CSV ();
# DDI 5.025 is needed for Windows-aware qsh()
use Data::Dumper::Interp 5.025 qw/vis visq visO dvis dvisq dvisO ivis ivisq avis hvis qsh qshlist u visnew/;
use Spreadsheet::Edit::Log qw/log_call fmt_call log_methcall fmt_methcall oops :btw/;
our %SpreadsheetEdit_Log_Options = (
is_public_api => sub{
$_[1][3] =~ /(?: ::|^ )(?: [a-z][^:]* | OpenAsCsv | ConvertSpreadsheet )$/x
},
subst_pkg => "IO",
);
my $progname = path($0)->basename;
sub _get_username(;$) {
my ($uid) = @_;
$uid //= eval{ $> } // -1; # default to EUID
state $answer = {};
return $answer->{$uid} //= do {
# https://stackoverflow.com/questions/12081246/how-to-get-system-user-full-name-on-windows-in-perl
eval { getpwuid($uid) // $uid }
||
($^O =~ /MSWin/ && $uid == (eval{$>}//-1) && eval{ # untested...
require Win32API::Net;
Win32API::Net::UserGetInfo($ENV{LOGONSERVER}||'',Win32::LoginName(),10,my $info={});
$info->{fullName}
})
||
"UID$uid"
};
}
# A private Libre/Open Office profile dir is needed to avoid conflicts
# with interactive sessions, see
# https://ask.libreoffice.org/en/question/290306/how-to-start-independent-lo-instance-process
#
# We use a persistent profile dir shared among processes for a given user
# (actually one for each unique external tool which needs one).
# Sharing is okay because we get an exclusive lock before actually using it.
state $profile_parent_dir = do{ # also used for lockfile
my $user = _get_username();
my $dname = __PACKAGE__."_${user}_profileparent";
$dname =~ s/::/-/g;
$dname =~ s/[^-\w.:]/_/g;
(my $path = path(File::Spec->tmpdir)->child($dname))->mkpath;
$path # Path::Tiny
};
sub _get_tool_profdir($) {
lib/Spreadsheet/Edit/IO.pm view on Meta::CPAN
};
my $ifilter = $opts->{soffice_infilter} //= do{
if ($opts->{cvt_from} eq "csv") {
my $filter_name = $suf2ofilter->{$opts->{cvt_from}} or oops;
my $enc = $opts->{input_encoding};
my $charset = _name2LOcharsetnum($enc); # dies if unknown enc
my $colformats = "";
if (my $cf = $opts->{col_formats}) {
$cf = [split /[\/,]/, $cf] unless ref($cf); # fmtA/fmtB/...
for (my $ix=0; $ix <= $#$cf; $ix++) {
local $_ = $cf->[$ix] // 1;
m#^([123459]|10)$#
|| s#^standard$#1#i
|| s#^text$#2#i
|| s#^M+/D+/Y+$#3#i
|| s#^D+/M+/Y+$#4#i
|| s#^Y+/M+/D+$#5#i
|| s#^ignore$#9#i
|| s#^US.*English$#10#i
|| croak "Unknown format code '$_' in {col_formats}";
$colformats .= "/" if $colformats;
$colformats .= ($ix+1)."/$_";
}
}
$filter_name.":"
# Tokens 1-4: FldSep=',' TxtDelim='"' Charset FirstLineNum
#. "44,34,$charset,1"
. ord($opts->{sep_char}//",").","
. ord($opts->{quote_char}//'"').","
. "$charset,1"
# Token 5: Cell format codes:
# If variable-width cells (the norm): colnum/fmt/colnum/fmt...
# colnum: 1-based column number
# fmt: 1=Std 2=Text 3=MM/DD/YY 4=DD/MM/YY 5=YY/MM/DD 6-8 unused
# 9=ignore field (do not import),
# 10=US-English content (e.g. 3.14 not 3,14)
# (I'm guessing 1=Std means use current lang [or per Tok 6?])
# If fixed-width cells... [something else]
. ",$colformats"
# Token 6: MS-LCID Language Id; 0 or omitted means UI language
. "," # default: false
# Token 7: On input: true => Quoted cells are always read a 'text',
# effectively disabling Token 8. This must be false to recognize dates
# like "Jan 1, 2000" which by necessity must be quoted for the comma,
# but will **CORRUPT** zip codes with leading zeroes unless
# {col_formats} overrides (which it does now by default).
.",false" # default: false
# Token 8: on input: "Detect Special Numbers", i.e. date or time values
# in human form, numbers in scientific (expondntial) notation etc.
# If false, ONLY decimal numbers (thousands separators ok).
.",true" # default: false (for import)
# Tokens 9-10: not used on import
.",,"
# Token 11: Remove spaces; trim leading & trailing spaces when reading
."," # default: false
# Token 12: not use on import
.","
# Token 13: Import "=..." as formulas instead of text?
."," # default: false i.e. do not recognize formulas
# Token 14: "Automatically detected since LibreOffice 7.6" [BOM?]
.","
}
else {
undef
}
};
my $ofilter = $opts->{soffice_outfilter} //= do{
# OutputFilterName[:paramtoken,paramtoken,...]
if ($opts->{cvt_to} =~ /^(?:csv|txt)$/) {
# Output is csv format. If converting to "txt" then the output still
# uses the csv filter but with "FIX"ed width fields (no separators
# or quotes). Note that we are converating a *spreadsheet* input, not
# a writer document.
# 6/12/2025: csv --> txt IS NOT WORKING (truncates wide columns)
my $filter_name = $suf2ofilter->{$opts->{cvt_to}} or oops;
#my $enc = $opts->{output_encoding};
my ($enc) = ($opts->{output_binmode} =~ /:encoding\((.*?)\)/) or oops;
my $charset = _name2LOcharsetnum($enc); # dies if unknown enc
$filter_name.":"
# Tokens 1-4: FldSep=,(or FIX for "txt") TxtDelim=" Charset FirstLineNum
#."44,34,$charset,1"
. ($opts->{cvt_to} eq "txt" ? "FIX" : ord($opts->{sep_char}//",")).","
. ord($opts->{quote_char}//'"').","
. "$charset,1"
# Token 5: Cell format codes. Only used for import? (see above)
# What about fixed-width?
.","
# Token 6: Language identifier (uses Microsoft lang ids)
# 1033 means US-English (omitted => use UI's language)
.","
# Token 7: QuoteAllTextCells
# *** true will "quote" even single-bareword cells, which looks
# *** bad and makes t/ tests messier, but preserves information
# *** that such cells were not numbers or dates, etc. This ensures
# *** that Zip codes, etc. with leading zeroes won't be corrupted
# *** if converted back into a spreadsheet
# Option #1: Specify true to quote all cells on export, then post-process
# the result to un-quote obviously safe cells (for yet more overhead).
# Option #2: Specify false, and assume the resulting CSV will never
# be imported into a spreadsheet except via us, and we pre-scan
# the data to generate {col_formats} so will usually be safe.
# 5/30/23: Switching to Option #2...
.",false"
# Token 8: on output: true to store number as numbers; false to
# store number cells as text. No UI equivalent.
.",true" # default: documented as true (for export) BUT IS NOT!
# Token 9: "Save cell contents as shown"
# Generally we DO NOT want this because things like dates
# can be formatted many different ways.
##.",".($opts->{raw_values} ? "false" : "true")
.",false"
# Token 10: "Export cell formulas"
.",false"
# Token 11: not used for export
.","
# Token 12: (LO 7.2+) sheet selections:
# 0 or absent => the "first" sheet
# 1-N => the Nth sheet (arrgh, can not specify name!!)
# -1 => export all sheets to files named filebasenamne.Sheetname.csv
.",".($opts->{allsheets} ? -1 :
$opts->{sheetname} ? die("add named-sheet support here") :
0)
# Token 13: Not used for export
.","
# Token 14: true to include BOM in the result
#.","
}
else {
undef
}
};
# We can only control the output directory path, not the name of
# an individual result file. If $dst is a directory then the result
# could theoretically output into it directly, but instead we always
# output to an ephemeral temp directory and then move the results to $dst
#
# With 'allsheets' the resulting files must be renamed to conform to our
# external API (namely SHEETNAME.csv).
#
# ERROR DETECTION: As of LO 7.5 we always get zero exit status and the
# only way to detect errors is to notice that no files were written.
# https://bugs.documentfoundation.org/show_bug.cgi?id=155415
#
my $tdir = Path::Tiny->tempdir(path($dst)->basename."_cvt-to_XXXXX");
# will be deleted when $tdir goes out of scope
my @cmd = ($prog,
"--headless", "--invisible",
#"--nolockcheck",
"--norestore",
"--view", # open read-only in case can't create lockfile
$ifilter ? ("--infilter=$ifilter") : (),
"--convert-to",
$opts->{cvt_to}.($ofilter ? ":$ofilter" : ""),
"--outdir", $tdir->canonpath,
path($src)->canonpath);
unless ($debug) {
$opts->{suppress_stdout} = 1;
#$opts->{suppress_stderr} = 1;
}
my $cmdstatus = _runcmd($opts, @cmd);
if ($cmdstatus != 0) {
# This should never happen, see ERROR DETECTION above
confess sprintf("($$) UNEXPECTED FAILURE, wstat=0x%04x\n",$cmdstatus),
" converting '$opts->{inpath}' to $opts->{cvt_to}\n",
" Command was: ",join(" ",map{qsh} @cmd);
}
my @result_files = path($tdir)->children;
btw dvis '>> @result_files' if $debug;
if (@result_files == 0) {
#####
#sleep(2);
#my @rf = path($tdir)->children;
#croak "!?!? initially no output files, but now find @rf !?!?" if @rf;
#####
croak qsh($src)." is missing or unreadable\n", "cmd: ",qshlist(@cmd),"\n"
unless -r $src;
croak "Something went wrong, ",path($prog)->basename," produced no output\n",
"cmd: ", (map{" $_=".qsh($ENV{$_})}
lib/Spreadsheet/Edit/IO.pm view on Meta::CPAN
my %opts = (
cvt_from => "",
cvt_to => "",
@_,
);
if (defined $opts{inpath}) {
croak "Initial INPATH arg specified as well as inpath => ... in options"
if defined $leading_inpath;
} else {
$opts{inpath} = $leading_inpath // croak "No inpath was specified";
}
croak "Input path is \"\"\n" if $opts{inpath} eq "";
$opts{verbose}=1 if $opts{debug};
# inpath or outpath may have "!sheetname" appended (or alternate syntaxes),
# but may exist only if a separate 'sheetname' option is not specified.
# Input and output can not both be spreadsheets; one must be a CSV.
if (exists($opts{sheet})) {
carp "WARNING: Deprecated 'sheet' option found (use 'sheetname' instead)\n";
croak "Both {sheet} and {sheetname} specified" if exists $opts{sheetname};
$opts{sheetname} = delete $opts{sheet};
}
{ my ($path_sans_sheet, $sheetname, $key);
for my $thiskey ('inpath', 'outpath') {
my $spec = $opts{$thiskey} || next;
my ($pssn, $sn) = sheetname_from_spec($spec);
if (defined $sn) {
croak "A sheetname is embeeded in both ",
"'$thiskey' ($opts{$thiskey}) and '$key' ($opts{$key})\n"
if $sheetname;
($path_sans_sheet, $sheetname, $key) = ($pssn, $sn, $thiskey);
}
}
if ($opts{sheetname}) {
croak "'sheetname' option conflicts with embedded sheet name\n",
" sheetname => ", qsh($opts{sheetname}),"\n",
" $key => ", qsh($opts{$key}),"\n"
if defined($sheetname) && $sheetname ne $opts{sheetname};
}
elsif (defined $sheetname) {
btw "(extracted sheet name \"$sheetname\" from $key)\n"
if $opts{verbose};
$opts{sheetname} = $sheetname;
}
$opts{inpath_sans_sheet} = path(
($key && $key eq 'inpath') ? $path_sans_sheet : $opts{inpath}
);
croak qsh($opts{inpath_sans_sheet})," does not exist!\n"
unless $opts{inpath_sans_sheet}->exists;
}
# Input file basename sans any .suffix
$opts{ifbase} = $opts{inpath_sans_sheet}->basename(qr/\.[^.]+/);
%opts
}#_process_args
sub _binmode_slurp_and_log($$$) { # *without trying to seek*
my ($fh, $ref2octets, $debug) = @_;
binmode($fh);
local $/ = undef;
#$$ref2octets = <$fh>//""; # Now known to not have a BOM. <<< IS THIS REALLY TRUE???
$$ref2octets = <$fh>;
btwN \3,"Raw slurp-from-0 fh=$fh: ",(defined($$ref2octets) ? visO(substr($$ref2octets,0,300)) : "*undef*")
if $debug;
$$ref2octets //= "";
}
sub _slurp_ifnotslurped($$$) {
my ($fh, $ref2octets, $debug) = @_;
return if defined $$ref2octets;
seek($fh, 0, SEEK_SET) or die $!;
_binmode_slurp_and_log($fh, $ref2octets, $debug);
}
sub _decode_slurped_data($$$;$) {
my ($enc, $ref2octets, $start_pos, $check) = @_;
oops unless defined $$ref2octets;
# More attempts to find cause of mysterious failures on Windows smokers...
my $chars = eval {
decode($enc, substr($$ref2octets, $start_pos),
$check // (Encode::FB_CROAK|Encode::LEAVE_SRC) )
};
die($@,visnew->Useqq(0)->dvis('\n$enc $start_pos $ref2octets')) if $@;
$chars
}
# Detect cvt_from and cvt_to from filename suffixes or by peeking at the data.
# If input is CSV, detect encoding (removing a BOM if present),
# detect separator and quote characters, and generate default
# {col_formats} which, e.g. reads items with leading zeroes
# (e.g. Zip codes) as character data and not numbers.
#
# Unless the input is usable as-is it will be slurped into memory and the
# buffer returned by reference at $$ref2octets. If slurped data is present
# the caller should use that instead of reading the original input;
# either way {input_encoding} should be used to decode.
#
# (If $$ref2octets was already defined it is assumed to contain
# previously-slurped data which is used instead of the the input file.)
#
sub _preprocess($$) {
my ($opts, $ref2octets) = @_;
my $debug = $opts->{debug};
my ($fh, $start_pos);
# Skip to ==BODY== below
# my sub _dump_fh($) {
# return unless $debug;
# my $tag = shift;
# confess dvis "($tag) fh is not open \$start_pos" unless $fh;
# my @layers = PerlIO::get_layers($fh);
# my $cur_pos = tell($fh);
# if (! seek($fh, $start_pos//0, SEEK_SET)) {
# btwN 1,dvis "($tag) **NOT SEEKABLE** \$cur_pos \@layers \$start_pos";
# } else {
# if ((my $n = read $fh, my $buffer, 128) != 0) {
# btwN 1,dvis "($tag) \$cur_pos \$start_pos \@layers First $n items are:\n", Hexify($buffer), "\n";
# } else {
# btwN 1,dvis "($tag) \$cur_pos \$start_pos \@layers **APPEARS EMPTY**";
# }
# seek($fh, $cur_pos, SEEK_SET) or die "re-seek to $cur_pos: $!";
# }
# }
my sub set_fh_encoding() { # returns true if set
my $enc = $opts->{input_encoding}; # must already be resolved!
my $bmode = ":raw:encoding($enc):crlf";
binmode $fh, $bmode or die "binmode '$bmode' : $!";
if ($debug) {
my @layers = PerlIO::get_layers($fh);
warn dvis 'set_fh_encoding: $fh @layers\n';
}
}
my sub open_input() {
oops if defined $fh;
if (defined $$ref2octets) {
open $fh, "<:raw", $ref2octets or confess "BUG:in-mem open:$!";
#_dump_fh("open_input TO PREVIOUSLY SLURPED");
} else {
my $path = $opts->{inpath_sans_sheet};
$fh = openhandle($path); # undef unless $path is a file handle
unless ($fh) {
open $fh, "<", $path or die "$path : $!";
}
binmode($fh);
#_dump_fh("AAA $path open_input raw");
}
if (! seek($fh, 0, SEEK_SET)) {
oops if defined $$ref2octets;
_binmode_slurp_and_log($fh, $ref2octets, $debug);
close $fh;
$fh = undef;
open $fh, "<:raw", $ref2octets or confess "BUG:in-mem open:$!";
#_dump_fh("BBB unseekable, slurped");
}
my $bomenc = File::BOM::get_encoding_from_filehandle($fh);
$start_pos = tell($fh);
if ($bomenc) {
btw dvis 'Input has BOM, $bomenc $start_pos' if $debug;
$opts->{input_encoding} = $bomenc;
binmode($fh); # unnecessary???
binmode($fh, ":raw:encoding($bomenc):crlf") or die "binmode: $!";
}
#_dump_fh("CCC final");
}
my sub determine_input_encoding() {
# If one encoding was specified by the user or implied by a BOM, use it;
# otherwise try multiple encodings specified by the user or defaulted
# until one seems to work.
$opts->{input_encoding} //= $default_input_encodings;
my @enclist = split m#,#, $opts->{input_encoding};
return
if @enclist == 1;
_slurp_ifnotslurped($fh, $ref2octets, $debug);
for my $enc (@enclist) {
eval { _decode_slurped_data($enc, $ref2octets, $start_pos) };
if ($@) {
btw "Input encoding '$enc' did not work...($@)\n" if $debug;
next;
}
btw "Input encoding '$enc' seems to work.\n" if $debug;
@enclist = ($enc);
last
}
confess "Could not detect encoding of $opts->{inpath_sans_sheet}\n"
if @enclist > 1;
$opts->{input_encoding} = $enclist[0];
} #determine_input_encoding
my sub readparse_csv(@) {
my %csvopts = (
@sane_CSV_read_options,
defined($opts->{quote_char}) ? (quote_char=>$opts->{quote_char}) : (),
defined($opts->{sep_char}) ? (sep_char=>$opts->{sep_char}) : (),
auto_diag => 2, # throw on error
@_
);
$csvopts{escape_char} = $csvopts{quote_char}; # must always be the same
my $csv = Text::CSV->new (\%csvopts)
or croak "Text::CSV->new: ", Text::CSV->error_diag(),
dvis('\n## %csvopts\n');
seek($fh, $start_pos, SEEK_SET) or die $!; # skip over possible BOM
my $rows;
while (my $F = $csv->getline( $fh )) {
push(@$rows, $F);
}
$rows
}
my sub determine_csv_q_sep($) {
my ($r2rows) = @_;
return
if defined($opts->{quote_char}) && defined($opts->{sep_char});
# Try combinations starting with the most-common '"' and ',' while
# parsing the file for unsafe unquoted values (throws on syntax error).
# The expectation is that the first try usually succeeds
Q:
for my $q (defined($opts->{quote_char})
? ($opts->{quote_char}) : ("\"", "'")) {
my $found_q;
SEP:
for my $sep (defined($opts->{sep_char})
? ($opts->{sep_char}) : (",","\t")) {
btw dvisq '--- TRYING $q $sep ---' if $debug;
# # Preliminary check for an illegal use of the quote char
# if (defined($chars)
# && $chars =~ /[^${q}${sep}\x{0D}\x{0A}]
# ${q}
# (?=[^${q}${sep}\x{0D}\x{0A}] | \z)/gx) {
# btw ivis '>>>quote_char CAN NOT BE $q with sep=$sep because q exists mid-field before pos ${\(pos($chars))}'
# if $debug;
# next SEP
# }
$$r2rows = eval{ readparse_csv(quote_char=>$q, sep_char=>$sep) };
if ($@ eq "") {
warn ivis '>> Detected quote_char=$q sep_char=$sep\n' if $debug;
$opts->{quote_char} = $q;
$opts->{sep_char} = $sep;
last Q;
}
warn dvis('$q sep=$sep did not work...\n'),vis($@),"\n"
if $debug;
_dump_fd("$q sep=$sep did not work") if $debug;
$$r2rows = undef;
}
}
unless (defined($$r2rows)) {
#confess "Input file is not valid CSV (or we have a bug)\n"
seek($fh, $start_pos, SEEK_SET) or die $!; # skip over possible BOM
my $n = read($fh, my $somechars, 100);
croak "ERROR READING input: $!" unless defined($n);
if ($n == 0) {
warn "File has NO CONTENT. Treating it like an (empty) CSV\n"
if $debug;
$$r2rows = [];
} else {
my @layers = PerlIO::get_layers($fh);
warn dvis '$start_pos @layers $somechars\n';
if (open my $fh99, "<:raw", $opts->{inpath}) {
if ((my $nb = read $fh99, my $octets, 128) != 0) {
warn "First $nb octets are as follows:\n", Hexify($octets), "\n";
} else {
oops
}
}
confess "Input file is not valid CSV (or we have a bug)\n"
}
}
else {
warn dvis '#### CSV DETECTED $opts->{quote_char} $opts->{sep_char} $start_pos'
if $debug;
}
}#determine_csv_q_sep
my sub determine_csv_col_formats($) {
my ($r2rows) = @_;
return
if defined $opts->{col_formats};
$$r2rows //= readparse_csv();
my $max_cols = 0;
for my $row (@{ $$r2rows }) { $max_cols = @$row if $max_cols < @$row }
state $curr_yy = (localtime(time))[5];
my @col_formats;
my sub recognized($$$$;$) {
my ($cx, $rx, $thing, $format, $as_msg) = @_;
$col_formats[$cx] = $format;
return unless $debug;
$as_msg //= " as ".vis($col_formats[$cx])." format";
if (length($thing) > 35) { $thing = substr($thing,0,32)."..."; }
btwN 1, "Recognized $thing in ", cxrx2sheetaddr($cx,$rx), $as_msg;
}
CX:
for my $cx (0..$max_cols-1) {
RX:
for my $rx (0..$#{$$r2rows}) {
my $row = $$r2rows->[$rx];
next if $cx > $#$row; # row has fewer columns than others
for ($row->[$cx]) {
# recognize obvious Y/M/D or M/D/Y or D/M/Y date forms
if (m#\b(?<y>(?:[12]\d)?\d\d)/(?<m>\d\d)/(?<d>\d\d)\b#) {
if ($+{d} > 12 && $+{d} <= 31 && $+{"m"} >= 1 && $+{"m"} <= 12
&& ($+{"y"} < 100 || $+{"y"} >= 1000)) {
recognized($cx,$rx,$_,"YY/MM/DD");
next CX;
}
# If ambiguous YYYY/??/?? we can still assume it is a date and not text
if (length( $+{"y"} )==4) {
#recognized($cx,$rx,$_,""," as some kind of date, fmt unknown");
next RX;
lib/Spreadsheet/Edit/IO.pm view on Meta::CPAN
_tool_write_spreadsheet($opts, $outpath);
}
# If {outpath} is not set, set it to a unique output path in $proctempdir
# Always returns {outpath} as a Path::Tiny object.
sub _finalize_outpath($) {
my $opts = shift;
if (defined $opts->{outpath}) {
return path($opts->{outpath});
} else {
my $suf = $opts->{allsheets} ? undef : $opts->{cvt_to};
return(
($opts->{outpath}=_uniqpath_under_tempdir($opts, suf=>$suf))
);
}
}
sub convert_spreadsheet(@) {
# Set inpath_sans_sheet, sheetname, ifbase, etc.
my %opts = &_process_args;
btw dvis('>>> convert_spreadsheet %opts\n') if $opts{debug};
my %input_opts = %opts;
_create_proctempdir_ifneeded(\%opts);
# intuit cvt_from & cvt_to, detect encoding, and pre-process .csv input
# if needed to avoid corruption of leading zeroes.
my $octets;
my ($fh, $start_pos) = _preprocess(\%opts, \$octets); # sets defaults etc.
my $input_enc = $opts{input_encoding};
my ($output_enc) = $opts{output_binmode}
&& $opts{output_binmode} =~ /:encoding\((.+?)\)/ or oops;
croak "Either input or output must be 'csv'\n"
unless $opts{cvt_from} eq 'csv' || $opts{cvt_to} eq 'csv';
if ($opts{allsheets}) {
croak "'allsheets' is allowed only with cvt_to => 'csv'"
unless ($opts{cvt_to}//"") eq "csv";
croak "With 'allsheets', a sheet name may not be specified\n"
if $opts{sheetname};
croak "With 'allsheets', 'outpath' must be a directory if it exists\n"
if $opts{outpath} && -e $opts{outpath} && ! -d _;
}
my sub get_outcsv_path() {
my $outpath = _finalize_outpath(\%opts);
if ($opts{allsheets}) {
$outpath->mkpath; # nop if exists, croaks if conflicts with file
return $outpath->child( $opts{ifbase}.".csv" );
} else {
return $outpath;
}
}
my $done;
if ($opts{cvt_from} eq $opts{cvt_to}) { # csv to csv
# Special cases: in & out are both CSVs
if ($input_enc ne $output_enc || $start_pos != 0) {
# Special case #1: In & out are CSV but using different encodings,
# or the same encoding but the input contained a BOM
# (which we never want in the output).
# With {allsheets} the output will be inside the {outpath} directory.
my $dst = get_outcsv_path();
warn "> Transcoding csv: $input_enc -> $output_enc into ",qsh($dst),"\n"
if $opts{debug};
_slurp_ifnotslurped($fh, \$octets, $opts{debug});
my $chars = _decode_slurped_data($input_enc, \$octets, $start_pos,
Encode::FB_CROAK);
$octets = encode($output_enc, $chars, Encode::FB_CROAK);
$dst->spew_raw($octets);
$done = 1;
}
elsif (! $opts{allsheets}) {
# Special case #2: Not {allsheets} and no conversion is needed:
# Just pass through the original input.
my $src = $opts{inpath_sans_sheet};
if (my $outpath = $opts{outpath}) {
warn "> No conversion needed! Copying to ", qsh($outpath),"\n"
if $opts{verbose};
$src->copy($outpath);
} else {
warn "> No conversion needed! Returning input as {outpath}\n"
if $opts{verbose};
$opts{outpath} = $src;
}
$done = 1;
}
else {
# Special case #3: {allsheets} with CSV input, no converstion needed:
# Create a symlink to the input in the {outpath} directory.
my $src = $opts{inpath_sans_sheet};
my $dst = get_outcsv_path();
if ((my $s = eval{symlink($src, $dst)}) && !$@) {
warn "> No conversion needed! Left symlink at ", qsh($dst),"\n"
if $opts{verbose};
$done = 1;
} else {
btw dvis 'symlink>> $@' if $opts{debug}; # unimplmented or perms?
warn "> No conversion needed! Copying into ", qsh($dst),"\n"
if $opts{verbose};
$src->copy($dst);
$done = 1;
}
}
}
if (! $done) {
if ($opts{allsheets}) {
_extract_all_csvs(\%opts);
}
else {
# Result will be a single file.
if ($opts{cvt_to} eq "csv") {
_extract_one_csv(\%opts);
} else {
_write_spreadsheet(\%opts);
}
}
}
my $result = {
defined($output_enc) ? (encoding => $output_enc):(),
lib/Spreadsheet/Edit/IO.pm view on Meta::CPAN
# Convert *all* sheets to CSV files in a temp directory
$hash = convert_spreadsheet(inpath => "mywork.xls", cvt_to => "csv",
allsheets => 1);
opendir $dh, $hash->{outpath};
while (readrir($h)) { say "Output csv file is $_" }
# Transcode a CSV from windows-1252 to UTF-8
convert_spreadsheet(
inpath => "input.csv", input_encoding => 'windows-1252',
outpath => "output.csv", output_binmode => ':raw:encoding(UTF-8):crlf'
);
# Translate between 0-based column index and letter code (A, B, etc.)
print cx2let(0); # "A"
print let2cx("A"); # 0
print cx2let(26); # "AA"
print let2cx("ABC"); # 730
# Extract components from "filepath!SHEETNAME" specifiers
my $path = filepath_from_spec("/path/to/spreasheet.xls!Sheet1")
my $sheetname = sheetname_from_spec("/path/to/spreasheet.xls!Sheet1")
# Parse a csv file with sane options
my $csv = Text::CSV->new({ @sane_CSV_read_options, eol => $hash->{eol} })
or die "ERROR: ".Text::CSV->error_diag ();
my @rows
while (my $F = $csv->getline( $infh )) {
push @rows, $F;
}
close $infh or die "Error reading ", $hash->csvpath(), ": $!";
# Write a csv file with sane options
my $ocsv = Text::CSV->new({ @sane_CSV_write_options })
or die "ERROR: ".Text::CSV->error_diag ();
open my $outfh, ">:encoding(utf8)", $outpath
or die "$outpath: $!";
foreach (@rows) { $ocsv->print($outfh, $_) }
close $outfh or die "Error writing $outpath: $!";
=head1 DESCRIPTION
Convert between CSV and spreadsheet files using external tools, plus some utility functions
Currently this uses LibreOffice or OpenOffice (whatever is installed). An external tool is not needed when only CSV files are involved.
=head2 $hash = OpenAsCsv INPUT
=head2 $hash = OpenAsCsv inpath => INPUT, sheetname => SHEETNAME, ...
This is a thin wrapper for C<convert_spreadsheet> followed by C<open>
If a single argument is given it specifies INPUT; otherwise all arguments must
be specified as key => value pairs, and may include any options supported
by C<convert_spreadsheet>.
INPUT may be a csv or spreadsheet workbook path; if a spreadsheet,
then a single "sheet" is converted, specified by either a !SHEETNAME suffix
in the INPUT path, a separate C<< sheetname => SHEETNAME >> option,
or if unspecified to extract the only sheet (croaks if there is more than one).
The resulting file handle refers to a guaranteed-seekable BOM-less CSV file.
This will either be a temporary file (auto-removed at process exit),
or the original INPUT if it was already a seekable csv file without a BOM.
RETURNS: A ref to a hash containing the following:
{
fh => the resulting open file handle
csvpath => the path {fh} refers to, which might be a temporary file
sheetname => sheet name if the input was a spreadsheet
}
=head2 convert_spreadsheet INPUT, cvt_to=>suffix, OPTIONS
=head2 convert_spreadsheet INPUT, cvt_to=>"csv", allsheets => 1, OPTIONS
Convert CSV to spreadsheet or vice-versa, or transcode CSV to CSV.
RETURNS: A ref to a hash containing:
{
outpath => path to the output file (or directory with 'allsheets')
(a temp file/dir if you did not specify outpath in OPTIONS).
encoding => the encoding used when writing .csv files
}
INPUT is the input file path; it may be a separate first argument as
shown above, or else included in OPTIONS as C<< inpath =E<gt> INPUT >>.
If C<outpath =E<gt> OUTPATH> is specifed then results are I<always> saved
to that path. With C<allsheets> this must be a directory, which will be
created if necessary.
If C<outpath> is NOT specified in OPTIONS then, with one exception,
results are saved to a temporary file or directory and that path is returned
as C<outpath> in the result hash.
The exception is if no conversion is necessary
when the input file itself is returned as C<outpath>
(i.e. C<cvt_from> is the same as C<cvt_to> and, if 'csv',
there was no BOM and an encoding change is not needed).
In all cases C<outpath> in the result hash points to the results.
C<cvt_from> or C<cvt_to> are filename suffixes (sans dot)
e.g. "csv", "xlsx", etc., and are only required if INPATH or C<outpath>
parameters do not contain a .suffix .
OPTIONS may also include:
=over 4
=item sheetname => "sheet name"
The workbook 'sheet' name used when reading or writing a spreadsheet.
An input sheet name may also be specified as "!sheetname" appended to
the INPUT path.
=item allsheets => BOOL
B<All> sheets in the input
are converted to separate .csv files named "SHEETNAME.csv" in
the 'outpath' directory. C<< cvt_to =E<gt> 'csv' >> is also requred.
=item input_encoding => ENCODING
Specifies the encoding of INPUT if it is a csv file.
ENCODING may be a comma-separated list of encoding
names which will be tried in the order until one seems to work.
If only one is specified it will be used without trying it first.
The default is "UTF-8,windows-1252". If a BOM is present it overrides.
=item output_binmode => "..."
Used when writing csv file(s), defaults to ':raw:encoding(UTF-8):crlf'.
=item output_encoding => ENCODING
(Deprecated) Implies output_binmode => ':raw:encoding(ENCODIING):crlf'.
=item col_formats => [...]
This specifies how CSV data is imported into a spreadsheet. Each element
of the array may contain:
undef, "standard" or "" (LibreOffice will auto-detect)
"text" (imported as unmolested text)
"MM/DD/YY",
"DD/MM/YY",
"YY/MM/DD",
"ignore" (do not import this column)
Elements may also contain the numeric format codes defined by LibreOffice
at L<https://wiki.documentfoundation.org/Documentation/DevGuide/Spreadsheet_Documents#Filter_Options_for_the_CSV_Filter>
B<Automatic format detection:>
If I<col_formats> is not specified, input CSV data is pre-scanned
to auto-detect column formats as much as possible.
This usually works well as long as dates are
represented unambiguously, e.g. 2021-01-01 or "Jan 1, 2023".
Specifically, this reads columns containing leading zeroes as "text"
(such as in U.S. Zip Codes);
date forms MM/DD/YY or DD/MM/YY are read with corresponding format
if a DD happens to be more than 12 (otherwise LibreOffice's default is used).
=item verbose => BOOL
=back
=head3 B<'binmode' Argument For Reading result CSVs>
The following incantation will correctly read either DOS/Windows (CR,LF)
or *nix (LF) line endings properly, i.e. as a single \n:
open my $fh, "<", $resulthash->{outpath};
my $enc = $resulthash->{encoding};
binmode($fh, ":raw:encoding($enc):crlf");
=head2 @sane_CSV_read_options
=head2 @sane_CSV_write_options
These contain options you will always want to use with
S<<< C<< Text::CSV->new() >> >>>.
Specifically, quotes and embedded newlines are handled correctly.
Not exported by default.
=head2 cx2let COLUMNINDEX
( run in 1.639 second using v1.01-cache-2.11-cpan-6aa56a78535 )