Excel-Writer-XLSX

 view release on metacpan or  search on metacpan

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

# very_hidden()
#
# Hide this worksheet. This can only be unhidden from VBA.
#
sub very_hidden {

    my $self = shift;

    $self->hide( 2 );
}


###############################################################################
#
# set_first_sheet()
#
# Set this worksheet as the first visible sheet. This is necessary
# when there are a large number of worksheets and the activated
# worksheet is not visible on the screen.
#
sub set_first_sheet {

    my $self = shift;

    $self->{_hidden} = 0;    # Active worksheet can't be hidden.
    ${ $self->{_firstsheet} } = $self->{_index};
}


###############################################################################
#
# protect( $password )
#
# Set the worksheet protection flags to prevent modification of worksheet
# objects.
#
sub protect {

    my $self     = shift;
    my $password = shift || '';
    my $options  = shift || {};

    if ( $password ne '' ) {
        $password = $self->_encode_password( $password );
    }

    # Default values for objects that can be protected.
    my %defaults = (
        sheet                 => 1,
        content               => 0,
        objects               => 0,
        scenarios             => 0,
        format_cells          => 0,
        format_columns        => 0,
        format_rows           => 0,
        insert_columns        => 0,
        insert_rows           => 0,
        insert_hyperlinks     => 0,
        delete_columns        => 0,
        delete_rows           => 0,
        select_locked_cells   => 1,
        sort                  => 0,
        autofilter            => 0,
        pivot_tables          => 0,
        select_unlocked_cells => 1,
    );


    # Overwrite the defaults with user specified values.
    for my $key ( keys %{$options} ) {

        if ( exists $defaults{$key} ) {
            $defaults{$key} = $options->{$key};
        }
        else {
            carp "Unknown protection object: $key\n";
        }
    }

    # Set the password after the user defined values.
    $defaults{password} = $password;

    $self->{_protect} = \%defaults;
}


###############################################################################
#
# unprotect_range( $range, $range_name, $password )
#
# Unprotect ranges within a protected worksheet.
#
sub unprotect_range {

    my $self       = shift;
    my $range      = shift;
    my $range_name = shift;
    my $password   = shift;

    if ( !defined $range ) {
        carp "The range must be defined in unprotect_range())\n";
        return;
    }
    else {
        $range =~ s/\$//g;
        $range =~ s/^=//;
        $self->{_num_protected_ranges}++;
    }


    if ( !defined $range_name ) {
        $range_name = 'Range' . $self->{_num_protected_ranges};
    }

    if ( defined $password ) {
        $password = $self->_encode_password( $password );
    }

    push @{ $self->{_protected_ranges} }, [ $range, $range_name, $password ];
}


###############################################################################
#
# _encode_password($password)

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

# Insert a button form object into the worksheet.
#
sub insert_button {

    my $self = shift;

    # Check for a cell reference in A1 notation and substitute row and column
    if ( $_[0] =~ /^\D/ ) {
        @_ = $self->_substitute_cellref( @_ );
    }

    # Check the number of args.
    if ( @_ < 3 ) { return -1 }

    my $button = $self->_button_params( @_ );

    push @{ $self->{_buttons_array} }, $button;

    $self->{_has_vml} = 1;
}


###############################################################################
#
# set_vba_name()
#
# Set the VBA name for the worksheet.
#
sub set_vba_name {

    my $self         = shift;
    my $vba_codename = shift;

    if ( $vba_codename ) {
        $self->{_vba_codename} = $vba_codename;
    }
    else {
        $self->{_vba_codename} = "Sheet" . ($self->{_index} + 1);
    }
}



###############################################################################
#
# ignore_errors()
#
# Ignore worksheet errors/warnings in user defined ranges.
#
sub ignore_errors {

    my $self    = shift;
    my $ignores = shift;

    # List of valid input parameters.
    my %valid_parameter = (
        number_stored_as_text => 1,
        eval_error            => 1,
        formula_differs       => 1,
        formula_range         => 1,
        formula_unlocked      => 1,
        empty_cell_reference  => 1,
        list_data_validation  => 1,
        calculated_column     => 1,
        two_digit_text_year   => 1,
    );

    for my $param_key ( keys %$ignores ) {
        if ( not exists $valid_parameter{$param_key} ) {
            carp "Unknown parameter '$param_key' in ignore_errors()";
            return -3;
        }
    }

    $self->{_ignore_errors} = {%$ignores};
}


###############################################################################
#
# Internal methods.
#
###############################################################################


###############################################################################
#
# _table_function_to_formula
#
# Convert a table total function to a worksheet formula.
#
sub _table_function_to_formula {

    my $function = shift;
    my $col_name = shift;
    my $formula  = '';

    # Escape special characters, as required by Excel.
    $col_name =~ s/'/''/g;
    $col_name =~ s/#/'#/g;
    $col_name =~ s/\[/'[/g;
    $col_name =~ s/]/']/g;

    my %subtotals = (
        average   => 101,
        countNums => 102,
        count     => 103,
        max       => 104,
        min       => 105,
        stdDev    => 107,
        sum       => 109,
        var       => 110,
    );

    if ( exists $subtotals{$function} ) {
        my $func_num = $subtotals{$function};
        $formula = qq{SUBTOTAL($func_num,[$col_name])};
    }
    else {
        carp "Unsupported function '$function' in add_table()";
    }

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

    my $rgb = $self->_get_palette_color( $color_index );

    my @attributes = ( 'rgb' => $rgb );

    $self->xml_empty_tag( 'tabColor', @attributes );
}


##############################################################################
#
# _write_outline_pr()
#
# Write the <outlinePr> element.
#
sub _write_outline_pr {

    my $self       = shift;
    my @attributes = ();

    return unless $self->{_outline_changed};

    push @attributes, ( "applyStyles"        => 1 ) if $self->{_outline_style};
    push @attributes, ( "summaryBelow"       => 0 ) if !$self->{_outline_below};
    push @attributes, ( "summaryRight"       => 0 ) if !$self->{_outline_right};
    push @attributes, ( "showOutlineSymbols" => 0 ) if !$self->{_outline_on};

    $self->xml_empty_tag( 'outlinePr', @attributes );
}


##############################################################################
#
# _write_sheet_protection()
#
# Write the <sheetProtection> element.
#
sub _write_sheet_protection {

    my $self = shift;
    my @attributes;

    return unless $self->{_protect};

    my %arg = %{ $self->{_protect} };

    push @attributes, ( "password"    => $arg{password} ) if $arg{password};
    push @attributes, ( "sheet"       => 1 )              if $arg{sheet};
    push @attributes, ( "content"     => 1 )              if $arg{content};
    push @attributes, ( "objects"     => 1 )              if !$arg{objects};
    push @attributes, ( "scenarios"   => 1 )              if !$arg{scenarios};
    push @attributes, ( "formatCells" => 0 )              if $arg{format_cells};
    push @attributes, ( "formatColumns"    => 0 ) if $arg{format_columns};
    push @attributes, ( "formatRows"       => 0 ) if $arg{format_rows};
    push @attributes, ( "insertColumns"    => 0 ) if $arg{insert_columns};
    push @attributes, ( "insertRows"       => 0 ) if $arg{insert_rows};
    push @attributes, ( "insertHyperlinks" => 0 ) if $arg{insert_hyperlinks};
    push @attributes, ( "deleteColumns"    => 0 ) if $arg{delete_columns};
    push @attributes, ( "deleteRows"       => 0 ) if $arg{delete_rows};

    push @attributes, ( "selectLockedCells" => 1 )
      if !$arg{select_locked_cells};

    push @attributes, ( "sort"        => 0 ) if $arg{sort};
    push @attributes, ( "autoFilter"  => 0 ) if $arg{autofilter};
    push @attributes, ( "pivotTables" => 0 ) if $arg{pivot_tables};

    push @attributes, ( "selectUnlockedCells" => 1 )
      if !$arg{select_unlocked_cells};


    $self->xml_empty_tag( 'sheetProtection', @attributes );
}


##############################################################################
#
# _write_protected_ranges()
#
# Write the <protectedRanges> element.
#
sub _write_protected_ranges {

    my $self = shift;

    return if $self->{_num_protected_ranges} == 0;

    $self->xml_start_tag( 'protectedRanges' );

    for my $aref (@{ $self->{_protected_ranges} }) {
        $self->_write_protected_range(@$aref);
    }

    $self->xml_end_tag( 'protectedRanges' );
}


##############################################################################
#
# _write_protected_range()
#
# Write the <protectedRange> element.
#
sub _write_protected_range {

    my $self     = shift;
    my $sqref    = shift;
    my $name     = shift;
    my $password = shift;

    my @attributes = ();

    push @attributes, ( 'password' => $password ) if $password;
    push @attributes, ( 'sqref' => $sqref );
    push @attributes, ( 'name' =>  $name );

    $self->xml_empty_tag( 'protectedRange', @attributes );
}


##############################################################################
#
# _write_drawings()
#
# Write the <drawing> elements.
#
sub _write_drawings {

    my $self = shift;

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

#
sub _write_color_high {

    my $self = shift;

    $self->_write_spark_color( 'x14:colorHigh', @_ );
}


##############################################################################
#
# _write_color_low()
#
# Write the <x14:colorLow> element.
#
sub _write_color_low {

    my $self = shift;

    $self->_write_spark_color( 'x14:colorLow', @_ );
}


##############################################################################
#
# _write_ignored_errors()
#
# Write the <ignoredErrors> element.
#
sub _write_ignored_errors {

    my $self   = shift;
    my $ignore = $self->{_ignore_errors};

    if ( !defined $ignore ) {
        return;
    }

    $self->xml_start_tag( 'ignoredErrors' );

    if ( exists $ignore->{number_stored_as_text} ) {
        my $range = $ignore->{number_stored_as_text};
        $self->_write_ignored_error( 'numberStoredAsText', $range );
    }

    if ( exists $ignore->{eval_error} ) {
        my $range = $ignore->{eval_error};
        $self->_write_ignored_error( 'evalError', $range );
    }

    if ( exists $ignore->{formula_differs} ) {
        my $range = $ignore->{formula_differs};
        $self->_write_ignored_error( 'formula', $range );
    }

    if ( exists $ignore->{formula_range} ) {
        my $range = $ignore->{formula_range};
        $self->_write_ignored_error( 'formulaRange', $range );
    }

    if ( exists $ignore->{formula_unlocked} ) {
        my $range = $ignore->{formula_unlocked};
        $self->_write_ignored_error( 'unlockedFormula', $range );
    }

    if ( exists $ignore->{empty_cell_reference} ) {
        my $range = $ignore->{empty_cell_reference};
        $self->_write_ignored_error( 'emptyCellReference', $range );
    }

    if ( exists $ignore->{list_data_validation} ) {
        my $range = $ignore->{list_data_validation};
        $self->_write_ignored_error( 'listDataValidation', $range );
    }

    if ( exists $ignore->{calculated_column} ) {
        my $range = $ignore->{calculated_column};
        $self->_write_ignored_error( 'calculatedColumn', $range );
    }

    if ( exists $ignore->{two_digit_text_year} ) {
        my $range = $ignore->{two_digit_text_year};
        $self->_write_ignored_error( 'twoDigitTextYear', $range );
    }

    $self->xml_end_tag( 'ignoredErrors' );
}

##############################################################################
#
# _write_ignored_error()
#
# Write the <ignoredError> element.
#
sub _write_ignored_error {

    my $self  = shift;
    my $type  = shift;
    my $sqref = shift;

    my @attributes = (
        'sqref' => $sqref,
        $type   => 1,
    );

    $self->xml_empty_tag( 'ignoredError', @attributes );
}


1;


__END__


=head1 NAME

Worksheet - A class for writing Excel Worksheets.

=head1 SYNOPSIS

See the documentation for L<Excel::Writer::XLSX>