view release on metacpan or  search on metacpan

lib/Excel/Writer/XLSX/Workbook.pm  view on Meta::CPAN

        [ 0x00, 0x00, 0xff, 0x00 ],    # 39
        [ 0x00, 0xcc, 0xff, 0x00 ],    # 40
        [ 0xcc, 0xff, 0xff, 0x00 ],    # 41
        [ 0xcc, 0xff, 0xcc, 0x00 ],    # 42
        [ 0xff, 0xff, 0x99, 0x00 ],    # 43
        [ 0x99, 0xcc, 0xff, 0x00 ],    # 44
        [ 0xff, 0x99, 0xcc, 0x00 ],    # 45
        [ 0xcc, 0x99, 0xff, 0x00 ],    # 46
        [ 0xff, 0xcc, 0x99, 0x00 ],    # 47
        [ 0x33, 0x66, 0xff, 0x00 ],    # 48
        [ 0x33, 0xcc, 0xcc, 0x00 ],    # 49
        [ 0x99, 0xcc, 0x00, 0x00 ],    # 50
        [ 0xff, 0xcc, 0x00, 0x00 ],    # 51
        [ 0xff, 0x99, 0x00, 0x00 ],    # 52
        [ 0xff, 0x66, 0x00, 0x00 ],    # 53
        [ 0x66, 0x66, 0x99, 0x00 ],    # 54
        [ 0x96, 0x96, 0x96, 0x00 ],    # 55
        [ 0x00, 0x33, 0x66, 0x00 ],    # 56
        [ 0x33, 0x99, 0x66, 0x00 ],    # 57
        [ 0x00, 0x33, 0x00, 0x00 ],    # 58
        [ 0x33, 0x33, 0x00, 0x00 ],    # 59
        [ 0x99, 0x33, 0x00, 0x00 ],    # 60
        [ 0x99, 0x33, 0x66, 0x00 ],    # 61
        [ 0x33, 0x33, 0x99, 0x00 ],    # 62
        [ 0x33, 0x33, 0x33, 0x00 ],    # 63
    ];

    return 0;
}


###############################################################################
#
# set_tempdir()
#
# Change the default temp directory.
#
sub set_tempdir {

    my $self = shift;
    my $dir  = shift;

    croak "$dir is not a valid directory" if defined $dir and not -d $dir;

    $self->{_tempdir} = $dir;

}


###############################################################################
#
# define_name()
#
# Create a defined name in Excel. We handle global/workbook level names and
# local/worksheet names.
#
sub define_name {

    my $self        = shift;
    my $name        = shift;
    my $formula     = shift;
    my $sheet_index = undef;
    my $sheetname   = '';
    my $full_name   = $name;

    # Remove the = sign from the formula if it exists.
    $formula =~ s/^=//;

    # Local defined names are formatted like "Sheet1!name".
    if ( $name =~ /^(.*)!(.*)$/ ) {
        $sheetname   = $1;
        $name        = $2;
        $sheet_index = $self->_get_sheet_index( $sheetname );
    }
    else {
        $sheet_index = -1;    # Use -1 to indicate global names.
    }

    # Warn if the sheet index wasn't found.
    if ( !defined $sheet_index ) {
        carp "Unknown sheet name $sheetname in defined_name()";
        return -1;
    }

    # Warn if the name contains invalid chars as defined by Excel help.
    if ( $name !~ m/^[\w\\][\w\\.]*$/ || $name =~ m/^\d/ ) {
        carp "Invalid character in name '$name' used in defined_name()";
        return -1;
    }

    # Warn if the name looks like a cell name.
    if ( $name =~ m/^[a-zA-Z][a-zA-Z]?[a-dA-D]?[0-9]+$/ ) {
        carp "Invalid name '$name' looks like a cell name in defined_name()";
        return -1;
    }

    # Warn if the name looks like a R1C1.
    if ( $name =~ m/^[rcRC]$/ || $name =~ m/^[rcRC]\d+[rcRC]\d+$/ ) {
        carp "Invalid name '$name' like a RC cell ref in defined_name()";
        return -1;
    }

    push @{ $self->{_defined_names} }, [ $name, $sheet_index, $formula ];
}


###############################################################################
#
# set_size()
#
# Set the workbook size.
#
sub set_size {

    my $self   = shift;
    my $width  = shift;
    my $height = shift;

    if ( !$width ) {
        $self->{_window_width} = 16095;
    }
    else {
        # Convert to twips at 96 dpi.
        $self->{_window_width} = int( $width * 1440 / 96 );
    }

    if ( !$height ) {
        $self->{_window_height} = 9660;
    }
    else {
        # Convert to twips at 96 dpi.
        $self->{_window_height} = int( $height * 1440 / 96 );
    }
}


###############################################################################
#
# set_tab_ratio()
#
# Set the ratio of space for worksheet tabs.
#
sub set_tab_ratio {

    my $self  = shift;
    my $tab_ratio = shift;

    if (!defined $tab_ratio) {
        return;
    }

    if ( $tab_ratio < 0 or $tab_ratio > 100 ) {
        carp "Tab ratio outside range: 0 <= zoom <= 100";
    }
    else {
        $self->{_tab_ratio} = int( $tab_ratio * 10 );
    }
}


###############################################################################
#
# set_properties()

lib/Excel/Writer/XLSX/Workbook.pm  view on Meta::CPAN

        $sheet->_prepare_tables( $table_id + 1, $seen );

        $table_id += $table_count;
    }
}


###############################################################################
#
# _prepare_metadata()
#
# Set the metadata rel link.
#
sub _prepare_metadata {

    my $self = shift;

    for my $sheet ( @{ $self->{_worksheets} } ) {
        if ($sheet->{_has_dynamic_functions} || $sheet->{_has_embedded_images}) {
            $self->{_has_metadata} = 1;
            $self->{_has_dynamic_functions} ||= $sheet->{_has_dynamic_functions};
            $self->{_has_embedded_images} ||= $sheet->{_has_embedded_images};
        }
    }
}


###############################################################################
#
# _add_chart_data()
#
# Add "cached" data to charts to provide the numCache and strCache data for
# series and title/axis ranges.
#
sub _add_chart_data {

    my $self = shift;
    my %worksheets;
    my %seen_ranges;
    my @charts;

    # Map worksheet names to worksheet objects.
    for my $worksheet ( @{ $self->{_worksheets} } ) {
        $worksheets{ $worksheet->{_name} } = $worksheet;
    }

    # Build an array of the worksheet charts including any combined charts.
    for my $chart ( @{ $self->{_charts} } ) {
        push @charts, $chart;

        if ($chart->{_combined}) {
            push @charts, $chart->{_combined};
        }
    }


    CHART:
    for my $chart ( @charts ) {

        RANGE:
        while ( my ( $range, $id ) = each %{ $chart->{_formula_ids} } ) {

            # Skip if the series has user defined data.
            if ( defined $chart->{_formula_data}->[$id] ) {
                if (   !exists $seen_ranges{$range}
                    || !defined $seen_ranges{$range} )
                {
                    my $data = $chart->{_formula_data}->[$id];
                    $seen_ranges{$range} = $data;
                }
                next RANGE;
            }

            # Check to see if the data is already cached locally.
            if ( exists $seen_ranges{$range} ) {
                $chart->{_formula_data}->[$id] = $seen_ranges{$range};
                next RANGE;
            }

            # Convert the range formula to a sheet name and cell range.
            my ( $sheetname, @cells ) = $self->_get_chart_range( $range );

            # Skip if we couldn't parse the formula.
            next RANGE if !defined $sheetname;

            # Handle non-contiguous ranges: (Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5).
            # We don't try to parse the ranges. We just return an empty list.
            if ( $sheetname =~ m/^\([^,]+,/ ) {
                $chart->{_formula_data}->[$id] = [];
                $seen_ranges{$range} = [];
                next RANGE;
            }

            # Die if the name is unknown since it indicates a user error in
            # a chart series formula.
            if ( !exists $worksheets{$sheetname} ) {
                die "Unknown worksheet reference '$sheetname' in range "
                  . "'$range' passed to add_series().\n";
            }

            # Find the worksheet object based on the sheet name.
            my $worksheet = $worksheets{$sheetname};

            # Get the data from the worksheet table.
            my @data = $worksheet->_get_range_data( @cells );

            # Convert shared string indexes to strings.
            for my $token ( @data ) {
                if ( ref $token ) {
                    $token = $self->{_str_array}->[ $token->{sst_id} ];

                    # Ignore rich strings for now. Deparse later if necessary.
                    if ( $token =~ m{^<r>} && $token =~ m{</r>$} ) {
                        $token = '';
                    }
                }
            }

            # Add the data to the chart.
            $chart->{_formula_data}->[$id] = \@data;

            # Store range data locally to avoid lookup if seen again.
            $seen_ranges{$range} = \@data;
        }
    }
}


###############################################################################
#
# _get_chart_range()
#
# Convert a range formula such as Sheet1!$B$1:$B$5 into a sheet name and cell
# range such as ( 'Sheet1', 0, 1, 4, 1 ).
#
sub _get_chart_range {

    my $self  = shift;
    my $range = shift;
    my $cell_1;
    my $cell_2;
    my $sheetname;
    my $cells;

    # Split the range formula into sheetname and cells at the last '!'.
    my $pos = rindex $range, '!';
    if ( $pos > 0 ) {
        $sheetname = substr $range, 0, $pos;
        $cells = substr $range, $pos + 1;
    }
    else {
        return undef;
    }

    # Split the cell range into 2 cells or else use single cell for both.
    if ( $cells =~ ':' ) {
        ( $cell_1, $cell_2 ) = split /:/, $cells;
    }
    else {
        ( $cell_1, $cell_2 ) = ( $cells, $cells );
    }

    # Remove leading/trailing apostrophes and convert escaped quotes to single.
    $sheetname =~ s/^'//g;
    $sheetname =~ s/'$//g;
    $sheetname =~ s/''/'/g;

    my ( $row_start, $col_start ) = xl_cell_to_rowcol( $cell_1 );
    my ( $row_end,   $col_end )   = xl_cell_to_rowcol( $cell_2 );

    # Check that we have a 1D range only.
    if ( $row_start != $row_end && $col_start != $col_end ) {
        return undef;
    }

    return ( $sheetname, $row_start, $col_start, $row_end, $col_end );
}


###############################################################################
#
# _store_externs()
#
# Write the EXTERNCOUNT and EXTERNSHEET records. These are used as indexes for
# the NAME records.
#
sub _store_externs {

    my $self = shift;

}


###############################################################################
#
# _store_names()
#
# Write the NAME record to define the print area and the repeat rows and cols.
#
sub _store_names {

    my $self = shift;

}