ONO::Lib::File::Convert

package ONO::Lib::File::Convert;
################################################################################
# COPYRIGHT / LICENSE #
################################################################################
#
# This file is part of the ONO Software Project.
#
# Copyright (C) 2000-2025 Jos KIRPS [ www.kirps.com | jos_AT_kirps_DOT_com ]
# and The Joopita Project [ www.joopita.org | contact_AT_joopita_DOT_com ]
#
# This file, as well as other parts of the ONO Software Project or related
# elements, are FREE SOFTWARE available under the ARTISTIC LICENSE 2.0.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
#
# For the full license, see /ono/osr/license/LICENSE.txt, or write to
# jos_AT_kirps_DOT_com or contact_AT_joopita_DOT_com.
#
################################################################################
# END OF COPYRIGHT / LICENSE, HERE COMES THE CODE ... #
################################################################################

use strict;

use ONO::IO;

###############################################################################
# Excel to CSV
###############################################################################

sub csv_get_colnames {

#: Return CSV column names (first row as an array)

my $lines_ref = $_[1];
my @lines = @$lines_ref;
my @COLNAME;

foreach my $line (@lines) {

$line =~ s~^"~~;
$line =~ s~"$~~;

if ($line !~ /","/ && $line =~ /;/) {
$line =~ s~;~","~g;
}

my $c;
foreach my $COL (split(/","/,$line)) {
if (!$COLNAME[$c]) {
$COLNAME[$c] = $COL;
}
$c++;
}

}

return @COLNAME;

}

sub csv_line_processor {

#: CSV processor, return record as HTML table row.

my $line = $_[1];

if ($line !~ /","/ && $line =~ /;/) {
$line =~ s~;~","~g;
$line = qq~"$line"~;
}
$line =~ s~^"~~;
$line =~ s~"$~~;
$line =~ s~","~</td><td class="pad5_2 bl br">~g;

my $BG;
if ($line =~ s~^(title|red|green|blue|yellow)\:~~) {
$BG = " bg_$1";
}

$line = qq~<tr class="bt bb$BG"><td class="pad5_2 bl br">$line</td></tr>~;

return $line;

}


sub excel_csv {

my (
$self,
$source,
$target,
$switches,
) = @_;

#: EXCEL to CSV save, not implemented yet.

my $DEBUG = "ONO_Lib_File_Convert, excel_csv, ";

# save to file not implemented yet

return $DEBUG;

}

sub excel_csv_reader {

my (
$self,
$source,
$switches,
) = @_;

#: EXCEL to CSV reader
#:
#: -C CSV compatibility processor, makes Apple Numbers exports better. Note that this is ONLY needed if csv_line_processor cannot be used later on...

my @DATA;

if (ONO::IO->exists($source)) {

if ($source =~ /\.csv$/i) {

if ($switches =~ /C/) {

foreach my $line (ONO::IO->list($source)) {

if ($line !~ /","/ && $line =~ /;/) {
$line =~ s~;~","~g;
$line = qq~"$line"~;
}

@DATA = (@DATA,$line);

}

} else {

@DATA = ONO::IO->list($source);

}

}

if ($source =~ /\.xls$/i) {

eval "use Spreadsheet::ParseExcel";
if (!$@) {

my $parser = Spreadsheet::ParseExcel->new();
my $vpath = ONO::IO->path();
my $workbook = $parser->parse("$vpath/$source");

for my $worksheet ($workbook->worksheets()) {

my ($row_min,$row_max) = $worksheet->row_range();
my ($col_min,$col_max) = $worksheet->col_range();

for my $row ($row_min .. $row_max) {
for my $col ( $col_min .. $col_max ) {

my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;

my $DAT = $cell->unformatted();
$DAT =~ s~\"~\'~g;

$DATA[$row] .= qq~"$DAT",~;

}

$DATA[$row] =~ s~\,$~~;
}
}
}
}

if ($source =~ /\.xlsx$/i) {

eval "use Spreadsheet::XLSX";
if (!$@) {

my $vpath = ONO::IO->path();

my $converter;

my $excel = Spreadsheet::XLSX->new("$vpath/$source", $converter);

foreach my $sheet (@{$excel->{Worksheet}}) {
$sheet->{MaxRow} ||= $sheet->{MinRow};
foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
$sheet->{MaxCol} ||= $sheet->{MinCol};
foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {

my $cell = $sheet->{Cells}[$row][$col];

if ($cell) {

my $DAT = $cell->{Val};
$DAT =~ s~\"~\'~g;

$DATA[$row] .= qq~"$DAT",~;

}
}

$DATA[$row] =~ s~\,$~~;

}
}
}
}

}

return @DATA;

}

###############################################################################
# end of script
###############################################################################

1;

__END__