use 5.010001;
use strict;
use warnings;
use Log::ger;
#use Log::ger::Screen;

use File::chdir;
use File::Slurper qw(read_binary);
use File::Temp qw(tempdir);
use File::Which qw(which);
use CLI::MetaUtil::Getopt::Long qw(GetOptionsCLIWrapper);
use IPC::System::Options 'system', -log=>1, -die=>1;

our $DATE = '2022-11-26'; # DATE
our $DIST = 'App-DiffXlsText'; # DIST
our $VERSION = '0.012'; # VERSION

unless (which "xlsx2csv") {
    die "diff-xls-text: This tool requires xlsx2csv from CPAN module Spreadsheet::Read\n";

my @files;
Getopt::Long::Configure("gnu_getopt", "no_ignore_case", "pass_through");

my $fail;
my $opt_transpose_csv;

my $tempdir;
my %basenames;

my $has_checked_xlsx2csv;
    cli => 'diff',
    add_opts => {
        'transpose-csv' => \$opt_transpose_csv,
        '<>' => sub {
            return if $fail;

            my $filename = $_[0];
            unless (-f $filename) {
                warn "diff-xls-text: No such file or not a file: '$filename'\n";

            if (!$tempdir) {
                $tempdir = File::Temp::tempdir(CLEANUP => !$ENV{DEBUG});

            (my $basename0 = $filename) =~ s!.+/!!;
            my $basename;
            my $i = 0;
            while (1) {
                $basename = $basename0 . ($i++ ? ".$i" : "");
                last unless $basenames{ $basename }++;

            unless ($has_checked_xlsx2csv++) {
                my $content = read_binary(which "xlsx2csv");
                unless ($content =~ /use Spreadsheet::Read/) {
                    warn "diff-xls-text: xlsx2csv in PATH doesn't seem to be the one from Spreadsheet::Read, please make sure we have the correct xlsx2csv\n";

            mkdir "$tempdir/$basename";
            system "xlsx2csv", "-A", "-o", "$tempdir/$basename/workbook", $filename;
            push @CLI::MetaUtil::Getopt::Long::cli_argv, "$tempdir/$basename";

            if ($opt_transpose_csv) {
                log_trace "Transposing CSV files in $tempdir/$basename ...";
                local $CWD = "$tempdir/$basename";
                for my $file (glob "*.csv") {
                    (my $newfile = $file) =~ s/\.csv$/.transpose.csv/;
                    system({shell=>1}, "csv-transpose", $file, \">", $newfile);
                    unlink $file;

exit 1 if $fail;

require File::Which;
my $diff_cmd =
    $ENV{DIFF_CMD} //
    (File::Which::which("diffwc") ? "diffwc" : undef) // "diff";

require IPC::System::Options;
    $diff_cmd, "-ruN", @ARGV,

# ABSTRACT: Diff the text of two Office spreadsheets (.ods, .xls, .xlsx) as two directories of CSV files
# PODNAME: diff-xls-text



=encoding UTF-8

=head1 NAME

diff-xls-text - Diff the text of two Office spreadsheets (.ods, .xls, .xlsx) as two directories of CSV files

=head1 VERSION

This document describes version 0.012 of diff-xls-text (from Perl distribution App-DiffXlsText), released on 2022-11-26.


Use like you would use the Unix command B<diff>:

 % diff-xls-text [options] <FILE>...

Sample F<old.xlsx> (C<*foo*> marks bolded text):

 # sheet 1: en_id
 |  english  |  indonesian    |
 | boy       | anak laki-laki |
 | girl      | anak wanita    |
 | man       | Laki-laki      |
 | lad       | pemuda         |

 # sheet 2: id_en
 |  indonesian  |  english    |
 | pemuda       | young man   |
 | pemudi       | young woman |

Sample F<new.xlsx>:

 # sheet 1: en_id
 | *English*  | *Indonesian*   |
 | boy        | anak laki-laki |
 | girl       | anak perempuan |
 | man        | laki-laki      |
 | woman      | perempuan      |

 # sheet 2: en_su
 |  English   |  Sundanese     |
 | man        | lalaki         |

Sample session:

 % diff-xls-text old.xlsx new.xlsx
 Saving sheet to /tmp/IZxMSeSyr_/old.xlsx/workbook-en_id.csv ...
 Saving sheet to /tmp/IZxMSeSyr_/old.xlsx/workbook-id_en.csv ...
 Saving sheet to /tmp/IZxMSeSyr_/new.xlsx/workbook-en_id.csv ...
 Saving sheet to /tmp/IZxMSeSyr_/new.xlsx/workbook-en_su.csv ...
 diff -ruN /tmp/IZxMSeSyr_/old.xlsx/workbook-en_id.csv /tmp/IZxMSeSyr_/new.xlsx/workbook-en_id.csv
 --- /tmp/IZxMSeSyr_/old.xlsx/workbook-en_id.csv 2022-11-26 07:43:56.293111001 +0700
 +++ /tmp/IZxMSeSyr_/new.xlsx/workbook-en_id.csv 2022-11-26 07:43:56.661107387 +0700
 @@ -1,5 +1,5 @@
  boy,"anak laki-laki"
 -girl,"anak wanita"
 +girl,"anak perempuan"
 diff -ruN /tmp/IZxMSeSyr_/old.xlsx/workbook-en_su.csv /tmp/IZxMSeSyr_/new.xlsx/workbook-en_su.csv
 --- /tmp/IZxMSeSyr_/old.xlsx/workbook-en_su.csv 1970-01-01 07:00:00.000000000 +0700
 +++ /tmp/IZxMSeSyr_/new.xlsx/workbook-en_su.csv 2022-11-26 07:43:56.661107387 +0700
 @@ -0,0 +1,2 @@
 diff -ruN /tmp/IZxMSeSyr_/old.xlsx/workbook-id_en.csv /tmp/IZxMSeSyr_/new.xlsx/workbook-id_en.csv
 --- /tmp/IZxMSeSyr_/old.xlsx/workbook-id_en.csv 2022-11-26 07:43:56.293111001 +0700
 +++ /tmp/IZxMSeSyr_/new.xlsx/workbook-id_en.csv 1970-01-01 07:00:00.000000000 +0700
 @@ -1,3 +0,0 @@
 -pemuda,"young man"
 -pemudi,"young woman"


This is a wrapper for the Unix command B<diff>. It assumes that each input file
is an Office spreadsheet (.ods, .xls, or .xlsx) and tries to convert the file to
a directory of CSV files (where each CSV file is converted from a single
worksheet) using L<xlsx2csv>. It then passes the converted directories to C<<
diff -ruN >> command. Thus, formatting differences will not be diff'ed.

=head1 OPTIONS

These are options that are interpreted by B<diff-xls-text> and not passed to

B<Please specify these options before file names.>


=item * --transpose-csv

Transpose CSV first using L<csv-transpose> from L<App::CSVUtils>. This allows
you to do column-based instead of row-based diff.



=head2 DEBUG

If set to true, do not cleanup temporary directories.

=head2 DIFF_CMD

String. Can be used to set path to diff command. See also
L</DIFF_XLS_TEXT_DIFF_CMD> which takes precedence.


String. Can be used to set path to diff command. The defaultl is L<diffwc> if
available in PATH, or C<diff>. Takes precedence over L</DIFF_CMD>.


Please visit the project's homepage at L<https://metacpan.org/release/App-DiffXlsText>.

=head1 SOURCE

Source repository is at L<https://github.com/perlancar/perl-App-DiffXlsText>.

=head1 SEE ALSO

Unix command L<diff>.

L<ssdiff> from L<Spreadsheet::Read> which also diffs two spreadsheets but
presents the result differently.

L<xlsx2csv> from L<Spreadsheet::Read>

=head1 AUTHOR

perlancar <perlancar@cpan.org>


To contribute, you can send patches by email/via RT, or send pull requests on

Most of the time, you don't need to build the distribution yourself. You can
simply modify the code, then test via:

 % prove -l

If you want to build the distribution (e.g. to try to install it locally on your
system), you can install L<Dist::Zilla>,
L<Pod::Weaver::PluginBundle::Author::PERLANCAR>, and sometimes one or two other
Dist::Zilla- and/or Pod::Weaver plugins. Any additional steps required beyond
that are considered a bug and can be reported to me.


This software is copyright (c) 2022, 2021, 2020 by perlancar <perlancar@cpan.org>.

This is free software; you can redistribute it and/or modify it under
the same terms as the Perl 5 programming language system itself.

=head1 BUGS

Please report any bugs or feature requests on the bugtracker website L<https://rt.cpan.org/Public/Dist/Display.html?Name=App-DiffXlsText>

When submitting a bug or request, please include a test-file or a
patch to an existing test-file that illustrates the bug or desired