# John Simpson `<jms1@jms1.net>` 2019-06-20
# Last updated 2019-07-28
# Original: /keybase/public/jms1/notes/perl parse ods file.txt

###############################################################################
# Read and parse an ODS spreadsheet file
# https://how-to.fandom.com/wiki/How_to_read_OpenOffice_OpenDocument_spreadsheets_in_Perl

sub parse_ods($)
{
    my $file = $_[0] ;
    my @data ;
    
    my $zip = new Archive::Zip( $file ) ;
    my $content = $zip->contents( 'content.xml' ) ;
    
    $content =~ s/<\?xml .*\?>\n// ;
    $content =~ s/^.*<office:spreadsheet>// ;
    $content =~ s/<\/office:spreadsheet>.*$// ;
    $content =~ s/<table:named-expressions>.*<\/table:named-expressions>// ;
    $content =~ s/<table:database-ranges>.*<\/table:database-ranges>// ; # keep greedy?
    $content =~ s/<\/table:table-cell>// ;  # maybe not neeeded
    $content =~ s/<\/table:table-row>// ;   # maybe not neeeded
    
    my @sheets = split( /<table:table .*?>/ , $content ) ;
    shift( @sheets ) ;                      # skips the first _blank_ element
    
    my $sheet_num = 0 ;
    for my $sheet ( @sheets )
    {
        my $row_num = -1 ;
        for my $row ( split( /<table:table-row .*?>/ , $sheet ) )
        {
            if ( $row_num != -1 )
            {
                # skips first element
                my $col_num = -1 ;

                for my $cell ( split( /<table:table-cell/ , $row ) )
                {
                    if ( $col_num == -1 )
                    {
                        # skips first element
                        $col_num ++ ;
                    }
                    else
                    {
                        my $cell_content = $cell ;

                        if ( $cell_content =~ /<text:p>/ )
                        {
                            $cell_content =~ s/.*<text:p>// ;
                            $cell_content =~ s/<\/text:p>.*// ;
                        }
                        else
                        {
                            # no content in cell
                            $cell_content = '' ;
                        }

                        if ($cell =~ /number-columns-repeated/)
                        {
                            # repeat cells contents n-times
                            my $col_repeated = $cell ;
                            $col_repeated =~ s/.*table:number-columns-repeated="// ; # this worked better
                            $col_repeated =~ s/".*// ;
                            foreach ( 1 .. $col_repeated )
                            {
                                $data[$sheet_num][$row_num][$col_num] = $cell_content ;
                                $col_num ++ ;
                            }
                        }
                        else
                        {
                            # no repeated cols
                            $data[$sheet_num][$row_num][$col_num] = $cell_content ;
                            $col_num ++ ;
                        }
                    }
                }
            }
            $row_num ++ ;
        }
        $sheet_num ++ ;
    }
    return @data ;
}