Excel-Writer-XLSX
view release on metacpan or search on metacpan
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
if ( $type eq 's' ) {
# Handle standard shared strings.
$string = $strings[$token];
}
else {
# Handle rich strings without html formatting.
$string = $cell->[3];
}
if ( $string !~ /\n/ ) {
$length = xl_cell_autofit_width( $string );
}
else {
# Handle multiline strings.
my @segments = split "\n", $string;
for my $string ( @segments ) {
my $seg_length =
xl_cell_autofit_width( $string );
if ( $seg_length > $length ) {
$length = $seg_length;
}
}
}
}
elsif ( $type eq 'n' ) {
# Handle numbers.
#
# We use a workaround/optimization for numbers since
# digits all have a pixel width of 7. This gives a
# slightly greater width for the decimal place and
# minus sign but only by a few pixels and
# over-estimation is okay.
$length = 7 * length $token;
}
elsif ( $type eq 't' ) {
# Handle dates.
#
# The following uses the default width for mm/dd/yyyy
# dates. It isn't feasible to parse the number format
# to get the actual string width for all format types.
$length = $self->{_default_date_pixels};
}
elsif ( $type eq 'l' ) {
# Handle boolean values.
#
# Use the Excel standard widths for TRUE and FALSE.
if ( $token ) {
$length = 31;
}
else {
$length = 36;
}
}
elsif ( $type eq 'f' ) {
# Handle formulas.
#
# We only try to autofit a formula if it has a
# non-zero value.
my $value = $cell->[3];
if ( $value ) {
$length = xl_cell_autofit_width( $value );
}
}
elsif ( $type eq 'a' || $type eq 'd' ) {
# Handle array and dynamic formulas.
my $value = $cell->[4];
if ( $value ) {
$length = xl_cell_autofit_width( $value );
}
}
# If the cell is in an autofilter header we add an
# additional 16 pixels for the dropdown arrow.
if ( $length > 0
&& exists $self->{_filter_cells}->{"$row_num:$col_num"}
)
{
$length += 16;
}
# Add the string length to the lookup hash.
my $max = $col_width{$col_num} || 0;
if ( $length > $max ) {
$col_width{$col_num} = $length;
}
}
}
}
}
# Apply the width to the column.
while ( my ( $col_num, $pixel_width ) = each %col_width ) {
# Convert the string pixel width to a character width using an
# additional padding of 7 pixels, like Excel.
my $width = _pixels_to_width( $pixel_width + 7 );
# Limit the width to the maximum user or Excel value.
if ( $width > $max_width ) {
$width = $max_width;
}
# Add the width to an existing col info structure or add a new one.
if ( exists $self->{_col_info}->{$col_num} ) {
$self->{_col_info}->{$col_num}->[0] = $width;
$self->{_col_info}->{$col_num}->[5] = 1;
}
else {
$self->{_col_info}->{$col_num} = [ $width, undef, 0, 0, 0, 1 ];
}
}
}
###############################################################################
#
# set_selection()
#
# Set which cell or cells are selected in a worksheet.
#
sub set_selection {
my $self = shift;
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
#
# Returns: return value of called subroutine
#
sub write {
my $self = shift;
# Check for a cell reference in A1 notation and substitute row and column
if ( $_[0] =~ /^\D/ ) {
@_ = $self->_substitute_cellref( @_ );
}
my $token = $_[2];
# Handle undefs as blanks
$token = '' unless defined $token;
# First try user defined matches.
for my $aref ( @{ $self->{_write_match} } ) {
my $re = $aref->[0];
my $sub = $aref->[1];
if ( $token =~ /$re/ ) {
my $match = &$sub( $self, @_ );
return $match if defined $match;
}
}
# Match an array ref.
if ( ref $token eq "ARRAY" ) {
return $self->write_row( @_ );
}
# Match integer with leading zero(s)
elsif ( $self->{_leading_zeros} and $token =~ /^0\d+$/ ) {
return $self->write_string( @_ );
}
# Match number
elsif ( $token =~ /^([+-]?)(?=[0-9]|\.[0-9])[0-9]*(\.[0-9]*)?([Ee]([+-]?[0-9]+))?$/ ) {
return $self->write_number( @_ );
}
# Match http, https or ftp URL
elsif ( $token =~ m|^[fh]tt?ps?://| ) {
return $self->write_url( @_ );
}
# Match mailto:
elsif ( $token =~ m/^mailto:/ ) {
return $self->write_url( @_ );
}
# Match internal or external sheet link
elsif ( $token =~ m[^(?:in|ex)ternal:] ) {
return $self->write_url( @_ );
}
# Match formula
elsif ( $token =~ /^=/ ) {
return $self->write_formula( @_ );
}
# Match array formula
elsif ( $token =~ /^{=.*}$/ ) {
return $self->write_formula( @_ );
}
# Match blank
elsif ( $token eq '' ) {
splice @_, 2, 1; # remove the empty string from the parameter list
return $self->write_blank( @_ );
}
# Default: match string
else {
return $self->write_string( @_ );
}
}
###############################################################################
#
# write_row($row, $col, $array_ref, $format)
#
# Write a row of data starting from ($row, $col). Call write_col() if any of
# the elements of the array ref are in turn array refs. This allows the writing
# of 1D or 2D arrays of data in one go.
#
# Returns: the first encountered error value or zero for no errors
#
sub write_row {
my $self = shift;
# Check for a cell reference in A1 notation and substitute row and column
if ( $_[0] =~ /^\D/ ) {
@_ = $self->_substitute_cellref( @_ );
}
# Catch non array refs passed by user.
if ( ref $_[2] ne 'ARRAY' ) {
croak "Not an array ref in call to write_row()$!";
}
my $row = shift;
my $col = shift;
my $tokens = shift;
my @options = @_;
my $error = 0;
my $ret;
for my $token ( @$tokens ) {
# Check for nested arrays
if ( ref $token eq "ARRAY" ) {
$ret = $self->write_col( $row, $col, $token, @options );
}
else {
$ret = $self->write( $row, $col, $token, @options );
}
# Return only the first error encountered, if any.
$error ||= $ret;
$col++;
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
$self->_write_single_row( $row );
}
$self->{_table}->{$row}->{$col} = [ $type, $index, $xf, $raw_string ];
return 0;
}
###############################################################################
#
# write_blank($row, $col, $format)
#
# Write a blank cell to the specified row and column (zero indexed).
# A blank cell is used to specify formatting without adding a string
# or a number.
#
# A blank cell without a format serves no purpose. Therefore, we don't write
# a BLANK record unless a format is specified. This is mainly an optimisation
# for the write_row() and write_col() methods.
#
# Returns 0 : normal termination (including no format)
# -1 : insufficient number of arguments
# -2 : row or column out of range
#
sub write_blank {
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
return -1 if @_ < 2;
# Don't write a blank cell unless it has a format
return 0 if not defined $_[2];
my $row = $_[0]; # Zero indexed row
my $col = $_[1]; # Zero indexed column
my $xf = $_[2]; # The cell format
my $type = 'b'; # The data type
# Check that row and col are valid and store max and min values
return -2 if $self->_check_dimensions( $row, $col );
# Write previous row if in in-line string optimization mode.
if ( $self->{_optimization} == 1 && $row > $self->{_previous_row} ) {
$self->_write_single_row( $row );
}
$self->{_table}->{$row}->{$col} = [ $type, undef, $xf ];
return 0;
}
###############################################################################
#
# _prepare_formula($formula)
#
# Utility method to strip equal sign and array braces from a formula and also
# expand out future and dynamic array formulas.
#
sub _prepare_formula {
my $self = shift;
my $formula = shift;
my $expand_future_functions = shift;
# Ignore empty/null formulas.
return $formula if !$formula;
# Remove array formula braces and the leading =.
$formula =~ s/^{(.*)}$/$1/;
$formula =~ s/^=//;
# # Don't expand formulas that the user has already expanded.
return $formula if $formula =~ m/_xlfn\./;
# Expand dynamic array formulas.
$formula =~ s/\b(ANCHORARRAY\()/_xlfn.$1/g;
$formula =~ s/\b(BYCOL\()/_xlfn.$1/g;
$formula =~ s/\b(BYROW\()/_xlfn.$1/g;
$formula =~ s/\b(CHOOSECOLS\()/_xlfn.$1/g;
$formula =~ s/\b(CHOOSEROWS\()/_xlfn.$1/g;
$formula =~ s/\b(DROP\()/_xlfn.$1/g;
$formula =~ s/\b(EXPAND\()/_xlfn.$1/g;
$formula =~ s/\b(FILTER\()/_xlfn._xlws.$1/g;
$formula =~ s/\b(HSTACK\()/_xlfn.$1/g;
$formula =~ s/\b(LAMBDA\()/_xlfn.$1/g;
$formula =~ s/\b(MAKEARRAY\()/_xlfn.$1/g;
$formula =~ s/\b(MAP\()/_xlfn.$1/g;
$formula =~ s/\b(RANDARRAY\()/_xlfn.$1/g;
$formula =~ s/\b(REDUCE\()/_xlfn.$1/g;
$formula =~ s/\b(SCAN\()/_xlfn.$1/g;
$formula =~ s/\b(SEQUENCE\()/_xlfn.$1/g;
$formula =~ s/\b(SINGLE\()/_xlfn.$1/g;
$formula =~ s/\b(SORT\()/_xlfn._xlws.$1/g;
$formula =~ s/\b(SORTBY\()/_xlfn.$1/g;
$formula =~ s/\b(SWITCH\()/_xlfn.$1/g;
$formula =~ s/\b(TAKE\()/_xlfn.$1/g;
$formula =~ s/\b(TEXTSPLIT\()/_xlfn.$1/g;
$formula =~ s/\b(TOCOL\()/_xlfn.$1/g;
$formula =~ s/\b(TOROW\()/_xlfn.$1/g;
$formula =~ s/\b(UNIQUE\()/_xlfn.$1/g;
$formula =~ s/\b(VSTACK\()/_xlfn.$1/g;
$formula =~ s/\b(WRAPCOLS\()/_xlfn.$1/g;
$formula =~ s/\b(WRAPROWS\()/_xlfn.$1/g;
$formula =~ s/\b(XLOOKUP\()/_xlfn.$1/g;
if ( !$self->{_use_future_functions} && !$expand_future_functions ) {
return $formula;
}
# Future functions.
$formula =~ s/\b(ACOTH\()/_xlfn.$1/g;
$formula =~ s/\b(ACOT\()/_xlfn.$1/g;
$formula =~ s/\b(AGGREGATE\()/_xlfn.$1/g;
$formula =~ s/\b(ARABIC\()/_xlfn.$1/g;
$formula =~ s/\b(ARRAYTOTEXT\()/_xlfn.$1/g;
$formula =~ s/\b(BASE\()/_xlfn.$1/g;
$formula =~ s/\b(BETA.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(BETA.INV\()/_xlfn.$1/g;
$formula =~ s/\b(BINOM.DIST.RANGE\()/_xlfn.$1/g;
$formula =~ s/\b(BINOM.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(BINOM.INV\()/_xlfn.$1/g;
$formula =~ s/\b(BITAND\()/_xlfn.$1/g;
$formula =~ s/\b(BITLSHIFT\()/_xlfn.$1/g;
$formula =~ s/\b(BITOR\()/_xlfn.$1/g;
$formula =~ s/\b(BITRSHIFT\()/_xlfn.$1/g;
$formula =~ s/\b(BITXOR\()/_xlfn.$1/g;
$formula =~ s/\b(CEILING.MATH\()/_xlfn.$1/g;
$formula =~ s/\b(CEILING.PRECISE\()/_xlfn.$1/g;
$formula =~ s/\b(CHISQ.DIST.RT\()/_xlfn.$1/g;
$formula =~ s/\b(CHISQ.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(CHISQ.INV.RT\()/_xlfn.$1/g;
$formula =~ s/\b(CHISQ.INV\()/_xlfn.$1/g;
$formula =~ s/\b(CHISQ.TEST\()/_xlfn.$1/g;
$formula =~ s/\b(COMBINA\()/_xlfn.$1/g;
$formula =~ s/\b(CONCAT\()/_xlfn.$1/g;
$formula =~ s/\b(CONFIDENCE.NORM\()/_xlfn.$1/g;
$formula =~ s/\b(CONFIDENCE.T\()/_xlfn.$1/g;
$formula =~ s/\b(COTH\()/_xlfn.$1/g;
$formula =~ s/\b(COT\()/_xlfn.$1/g;
$formula =~ s/\b(COVARIANCE.P\()/_xlfn.$1/g;
$formula =~ s/\b(COVARIANCE.S\()/_xlfn.$1/g;
$formula =~ s/\b(CSCH\()/_xlfn.$1/g;
$formula =~ s/\b(CSC\()/_xlfn.$1/g;
$formula =~ s/\b(DAYS\()/_xlfn.$1/g;
$formula =~ s/\b(DECIMAL\()/_xlfn.$1/g;
$formula =~ s/\b(ERF.PRECISE\()/_xlfn.$1/g;
$formula =~ s/\b(ERFC.PRECISE\()/_xlfn.$1/g;
$formula =~ s/\b(EXPON.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(F.DIST.RT\()/_xlfn.$1/g;
$formula =~ s/\b(F.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(F.INV.RT\()/_xlfn.$1/g;
$formula =~ s/\b(F.INV\()/_xlfn.$1/g;
$formula =~ s/\b(F.TEST\()/_xlfn.$1/g;
$formula =~ s/\b(FILTERXML\()/_xlfn.$1/g;
$formula =~ s/\b(FLOOR.MATH\()/_xlfn.$1/g;
$formula =~ s/\b(FLOOR.PRECISE\()/_xlfn.$1/g;
$formula =~ s/\b(FORECAST.ETS.CONFINT\()/_xlfn.$1/g;
$formula =~ s/\b(FORECAST.ETS.SEASONALITY\()/_xlfn.$1/g;
$formula =~ s/\b(FORECAST.ETS.STAT\()/_xlfn.$1/g;
$formula =~ s/\b(FORECAST.ETS\()/_xlfn.$1/g;
$formula =~ s/\b(FORECAST.LINEAR\()/_xlfn.$1/g;
$formula =~ s/\b(FORMULATEXT\()/_xlfn.$1/g;
$formula =~ s/\b(GAMMA.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(GAMMA.INV\()/_xlfn.$1/g;
$formula =~ s/\b(GAMMALN.PRECISE\()/_xlfn.$1/g;
$formula =~ s/\b(GAMMA\()/_xlfn.$1/g;
$formula =~ s/\b(GAUSS\()/_xlfn.$1/g;
$formula =~ s/\b(HYPGEOM.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(IFNA\()/_xlfn.$1/g;
$formula =~ s/\b(IFS\()/_xlfn.$1/g;
$formula =~ s/\b(IMAGE\()/_xlfn.$1/g;
$formula =~ s/\b(IMCOSH\()/_xlfn.$1/g;
$formula =~ s/\b(IMCOT\()/_xlfn.$1/g;
$formula =~ s/\b(IMCSCH\()/_xlfn.$1/g;
$formula =~ s/\b(IMCSC\()/_xlfn.$1/g;
$formula =~ s/\b(IMSECH\()/_xlfn.$1/g;
$formula =~ s/\b(IMSEC\()/_xlfn.$1/g;
$formula =~ s/\b(IMSINH\()/_xlfn.$1/g;
$formula =~ s/\b(IMTAN\()/_xlfn.$1/g;
$formula =~ s/\b(ISFORMULA\()/_xlfn.$1/g;
$formula =~ s/\b(ISOMITTED\()/_xlfn.$1/g;
$formula =~ s/\b(ISOWEEKNUM\()/_xlfn.$1/g;
$formula =~ s/\b(LET\()/_xlfn.$1/g;
$formula =~ s/\b(LOGNORM.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(LOGNORM.INV\()/_xlfn.$1/g;
$formula =~ s/\b(MAXIFS\()/_xlfn.$1/g;
$formula =~ s/\b(MINIFS\()/_xlfn.$1/g;
$formula =~ s/\b(MODE.MULT\()/_xlfn.$1/g;
$formula =~ s/\b(MODE.SNGL\()/_xlfn.$1/g;
$formula =~ s/\b(MUNIT\()/_xlfn.$1/g;
$formula =~ s/\b(NEGBINOM.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(NORM.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(NORM.INV\()/_xlfn.$1/g;
$formula =~ s/\b(NORM.S.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(NORM.S.INV\()/_xlfn.$1/g;
$formula =~ s/\b(NUMBERVALUE\()/_xlfn.$1/g;
$formula =~ s/\b(PDURATION\()/_xlfn.$1/g;
$formula =~ s/\b(PERCENTILE.EXC\()/_xlfn.$1/g;
$formula =~ s/\b(PERCENTILE.INC\()/_xlfn.$1/g;
$formula =~ s/\b(PERCENTRANK.EXC\()/_xlfn.$1/g;
$formula =~ s/\b(PERCENTRANK.INC\()/_xlfn.$1/g;
$formula =~ s/\b(PERMUTATIONA\()/_xlfn.$1/g;
$formula =~ s/\b(PHI\()/_xlfn.$1/g;
$formula =~ s/\b(POISSON.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(QUARTILE.EXC\()/_xlfn.$1/g;
$formula =~ s/\b(QUARTILE.INC\()/_xlfn.$1/g;
$formula =~ s/\b(QUERYSTRING\()/_xlfn.$1/g;
$formula =~ s/\b(RANK.AVG\()/_xlfn.$1/g;
$formula =~ s/\b(RANK.EQ\()/_xlfn.$1/g;
$formula =~ s/\b(RRI\()/_xlfn.$1/g;
$formula =~ s/\b(SECH\()/_xlfn.$1/g;
$formula =~ s/\b(SEC\()/_xlfn.$1/g;
$formula =~ s/\b(SHEETS\()/_xlfn.$1/g;
$formula =~ s/\b(SHEET\()/_xlfn.$1/g;
$formula =~ s/\b(SKEW.P\()/_xlfn.$1/g;
$formula =~ s/\b(STDEV.P\()/_xlfn.$1/g;
$formula =~ s/\b(STDEV.S\()/_xlfn.$1/g;
$formula =~ s/\b(T.DIST.2T\()/_xlfn.$1/g;
$formula =~ s/\b(T.DIST.RT\()/_xlfn.$1/g;
$formula =~ s/\b(T.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(T.INV.2T\()/_xlfn.$1/g;
$formula =~ s/\b(T.INV\()/_xlfn.$1/g;
$formula =~ s/\b(T.TEST\()/_xlfn.$1/g;
$formula =~ s/\b(TEXTAFTER\()/_xlfn.$1/g;
$formula =~ s/\b(TEXTBEFORE\()/_xlfn.$1/g;
$formula =~ s/\b(TEXTJOIN\()/_xlfn.$1/g;
$formula =~ s/\b(UNICHAR\()/_xlfn.$1/g;
$formula =~ s/\b(UNICODE\()/_xlfn.$1/g;
$formula =~ s/\b(VALUETOTEXT\()/_xlfn.$1/g;
$formula =~ s/\b(VAR.P\()/_xlfn.$1/g;
$formula =~ s/\b(VAR.S\()/_xlfn.$1/g;
$formula =~ s/\b(WEBSERVICE\()/_xlfn.$1/g;
$formula =~ s/\b(WEIBULL.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(XMATCH\()/_xlfn.$1/g;
$formula =~ s/\b(XOR\()/_xlfn.$1/g;
$formula =~ s/\b(Z.TEST\()/_xlfn.$1/g;
return $formula;
}
###############################################################################
#
# write_formula($row, $col, $formula, $format)
#
# Write a formula to the specified row and column (zero indexed).
#
# $format is optional.
#
# Returns 0 : normal termination
# -1 : insufficient number of arguments
# -2 : row or column out of range
#
sub write_formula {
my $self = shift;
# Check for a cell reference in A1 notation and substitute row and column
if ( $_[0] =~ /^\D/ ) {
@_ = $self->_substitute_cellref( @_ );
}
if ( @_ < 3 ) { return -1 } # Check the number of args
my $row = $_[0]; # Zero indexed row
my $col = $_[1]; # Zero indexed column
my $formula = $_[2]; # The formula text string
my $xf = $_[3]; # The format object.
my $value = $_[4]; # Optional formula value.
my $type = 'f'; # The data type
# Check for dynamic array functions.
local $_ = $formula;
if ( m{\bANCHORARRAY\(}
|| m{\bBYCOL\(}
|| m{\bBYROW\(}
|| m{\bCHOOSECOLS\(}
|| m{\bCHOOSEROWS\(}
|| m{\bDROP\(}
|| m{\bEXPAND\(}
|| m{\bFILTER\(}
|| m{\bHSTACK\(}
|| m{\bLAMBDA\(}
|| m{\bMAKEARRAY\(}
|| m{\bMAP\(}
|| m{\bRANDARRAY\(}
|| m{\bREDUCE\(}
|| m{\bSCAN\(}
|| m{\bSEQUENCE\(}
|| m{\bSINGLE\(}
|| m{\bSORT\(}
|| m{\bSORTBY\(}
|| m{\bSWITCH\(}
|| m{\bTAKE\(}
|| m{\bTEXTSPLIT\(}
|| m{\bTOCOL\(}
|| m{\bTOROW\(}
|| m{\bUNIQUE\(}
|| m{\bVSTACK\(}
|| m{\bWRAPCOLS\(}
|| m{\bWRAPROWS\(}
|| m{\bXLOOKUP\(} )
{
return $self->write_dynamic_array_formula( $row, $col, $row, $col,
$formula, $xf, $value );
}
# Hand off array formulas.
if ( $formula =~ /^{=.*}$/ ) {
return $self->write_array_formula( $row, $col, $row, $col, $formula,
$xf, $value );
}
# Check that row and col are valid and store max and min values
return -2 if $self->_check_dimensions( $row, $col );
# Expand out the formula.
$formula = $self->_prepare_formula($formula);
# Write previous row if in in-line string optimization mode.
if ( $self->{_optimization} == 1 && $row > $self->{_previous_row} ) {
$self->_write_single_row( $row );
}
$self->{_table}->{$row}->{$col} = [ $type, $formula, $xf, $value ];
return 0;
}
# Internal method shared by the write_array_formula() and
# write_dynamic_array_formula() methods.
sub _write_array_formula {
my $self = shift;
my $type = shift;
my @args = @_;
# Check for a cell reference in A1 notation and substitute row and column
if ( $args[0] =~ /^\D/ ) {
my $cellref = shift @args;
# Convert single cell to range.
my @dims = $self->_substitute_cellref( $cellref );
if ( @dims == 2 ) {
@args = ( @dims, @dims, @args );
}
else {
@args = ( @dims, @args );
}
}
if ( @args < 5 ) { return -1 } # Check the number of args
my $row1 = $args[0]; # First row
my $col1 = $args[1]; # First column
my $row2 = $args[2]; # Last row
my $col2 = $args[3]; # Last column
my $formula = $args[4]; # The formula text string
my $xf = $args[5]; # The format object.
my $value = $args[6]; # Optional formula value.
# Swap last row/col with first row/col as necessary
( $row1, $row2 ) = ( $row2, $row1 ) if $row1 > $row2;
( $col1, $col2 ) = ( $col1, $col2 ) if $col1 > $col2;
# Check that row and col are valid and store max and min values.
return -2 if $self->_check_dimensions( $row1, $col1 );
return -2 if $self->_check_dimensions( $row2, $col2 );
# Define array range
my $range;
if ( $row1 == $row2 and $col1 == $col2 ) {
$range = xl_rowcol_to_cell( $row1, $col1 );
}
else {
$range =
xl_rowcol_to_cell( $row1, $col1 ) . ':'
. xl_rowcol_to_cell( $row2, $col2 );
}
# Modify the formula string, as needed.
$formula = $self->_prepare_formula($formula);
# Write previous row if in in-line string optimization mode.
my $row = $row1;
if ( $self->{_optimization} == 1 && $row > $self->{_previous_row} ) {
$self->_write_single_row( $row );
}
$self->{_table}->{$row1}->{$col1} =
[ $type, $formula, $xf, $range, $value ];
# Pad out the rest of the area with formatted zeroes.
if ( !$self->{_optimization} ) {
for my $row ( $row1 .. $row2 ) {
for my $col ( $col1 .. $col2 ) {
next if $row == $row1 and $col == $col1;
$self->write_number( $row, $col, 0, $xf );
}
}
}
return 0;
}
###############################################################################
#
# write_array_formula($row1, $col1, $row2, $col2, $formula, $format)
#
# Write an array formula to the specified row and column (zero indexed).
#
# $format is optional.
#
# Returns 0 : normal termination
# -1 : insufficient number of arguments
# -2 : row or column out of range
#
sub write_array_formula {
my $self = shift;
return $self->_write_array_formula( 'a', @_ );
}
###############################################################################
#
# write_dynamic_array_formula($row1, $col1, $row2, $col2, $formula, $format)
#
# Write a dynamic formula to the specified row and column (zero indexed).
#
# $format is optional.
#
# Returns 0 : normal termination
# -1 : insufficient number of arguments
# -2 : row or column out of range
#
sub write_dynamic_array_formula {
my $self = shift;
my $error = $self->_write_array_formula( 'd', @_ );
if ( $error == 0 ) {
$self->{_has_dynamic_functions} = 1;
}
return $error;
}
###############################################################################
#
# write_blank($row, $col, $format)
#
# Write a boolean value to the specified row and column (zero indexed).
#
# Returns 0 : normal termination (including no format)
# -2 : row or column out of range
#
sub write_boolean {
my $self = shift;
# Check for a cell reference in A1 notation and substitute row and column
if ( $_[0] =~ /^\D/ ) {
@_ = $self->_substitute_cellref( @_ );
}
my $row = $_[0]; # Zero indexed row
my $col = $_[1]; # Zero indexed column
my $val = $_[2] ? 1 : 0; # Boolean value.
my $xf = $_[3]; # The cell format
my $type = 'l'; # The data type
# Check that row and col are valid and store max and min values
return -2 if $self->_check_dimensions( $row, $col );
# Write previous row if in in-line string optimization mode.
if ( $self->{_optimization} == 1 && $row > $self->{_previous_row} ) {
$self->_write_single_row( $row );
}
$self->{_table}->{$row}->{$col} = [ $type, $val, $xf ];
return 0;
}
###############################################################################
#
# outline_settings($visible, $symbols_below, $symbols_right, $auto_style)
#
# This method sets the properties for outlining and grouping. The defaults
# correspond to Excel's defaults.
#
sub outline_settings {
my $self = shift;
$self->{_outline_on} = defined $_[0] ? $_[0] : 1;
$self->{_outline_below} = defined $_[1] ? $_[1] : 1;
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
my $row_last = shift;
my $col_last = shift;
my $string = shift;
my $format = shift;
my @extra_args = @_; # For write_url().
# Excel doesn't allow a single cell to be merged
if ( $row_first == $row_last and $col_first == $col_last ) {
croak "Can't merge single cell";
}
# Swap last row/col with first row/col as necessary
( $row_first, $row_last ) = ( $row_last, $row_first )
if $row_first > $row_last;
( $col_first, $col_last ) = ( $col_last, $col_first )
if $col_first > $col_last;
# Check that the data range is valid and store the max and min values.
return -2 if $self->_check_dimensions( $row_first, $col_first );
return -2 if $self->_check_dimensions( $row_last, $col_last );
# Store the merge range.
push @{ $self->{_merge} }, [ $row_first, $col_first, $row_last, $col_last ];
# Write the first cell
$self->write( $row_first, $col_first, $string, $format, @extra_args );
# Pad out the rest of the area with formatted blank cells.
for my $row ( $row_first .. $row_last ) {
for my $col ( $col_first .. $col_last ) {
next if $row == $row_first and $col == $col_first;
$self->write_blank( $row, $col, $format );
}
}
}
###############################################################################
#
# merge_range_type()
#
# Same as merge_range() above except the type of write() is specified.
#
sub merge_range_type {
my $self = shift;
my $type = shift;
# Check for a cell reference in A1 notation and substitute row and column
if ( $_[0] =~ /^\D/ ) {
@_ = $self->_substitute_cellref( @_ );
}
my $row_first = shift;
my $col_first = shift;
my $row_last = shift;
my $col_last = shift;
my $format;
# Get the format. It can be in different positions for the different types.
if ( $type eq 'array_formula'
|| $type eq 'blank'
|| $type eq 'rich_string' )
{
# The format is the last element.
$format = $_[-1];
}
else {
# Or else it is after the token.
$format = $_[1];
}
# Check that there is a format object.
croak "Format object missing or in an incorrect position"
unless ref $format;
# Excel doesn't allow a single cell to be merged
if ( $row_first == $row_last and $col_first == $col_last ) {
croak "Can't merge single cell";
}
# Swap last row/col with first row/col as necessary
( $row_first, $row_last ) = ( $row_last, $row_first )
if $row_first > $row_last;
( $col_first, $col_last ) = ( $col_last, $col_first )
if $col_first > $col_last;
# Check that the data range is valid and store the max and min values.
return -2 if $self->_check_dimensions( $row_first, $col_first );
return -2 if $self->_check_dimensions( $row_last, $col_last );
# Store the merge range.
push @{ $self->{_merge} }, [ $row_first, $col_first, $row_last, $col_last ];
# Write the first cell
if ( $type eq 'string' ) {
$self->write_string( $row_first, $col_first, @_ );
}
elsif ( $type eq 'number' ) {
$self->write_number( $row_first, $col_first, @_ );
}
elsif ( $type eq 'blank' ) {
$self->write_blank( $row_first, $col_first, @_ );
}
elsif ( $type eq 'date_time' ) {
$self->write_date_time( $row_first, $col_first, @_ );
}
elsif ( $type eq 'rich_string' ) {
$self->write_rich_string( $row_first, $col_first, @_ );
}
elsif ( $type eq 'url' ) {
$self->write_url( $row_first, $col_first, @_ );
}
elsif ( $type eq 'formula' ) {
$self->write_formula( $row_first, $col_first, @_ );
}
elsif ( $type eq 'array_formula' ) {
$self->write_formula_array( $row_first, $col_first, @_ );
}
else {
croak "Unknown type '$type'";
}
# Pad out the rest of the area with formatted blank cells.
for my $row ( $row_first .. $row_last ) {
for my $col ( $col_first .. $col_last ) {
next if $row == $row_first and $col == $col_first;
$self->write_blank( $row, $col, $format );
}
}
}
###############################################################################
#
# data_validation($row, $col, {...})
#
# This method handles the interface to Excel data validation.
# Somewhat ironically this requires a lot of validation code since the
# interface is flexible and covers a several types of data validation.
#
# We allow data validation to be called on one cell or a range of cells. The
# hashref contains the validation parameters and must be the last param:
# data_validation($row, $col, {...})
# data_validation($first_row, $first_col, $last_row, $last_col, {...})
#
# Returns 0 : normal termination
# -1 : insufficient number of arguments
# -2 : row or column out of range
# -3 : incorrect parameter.
#
sub data_validation {
my $self = shift;
# Check for a cell reference in A1 notation and substitute row and column
if ( $_[0] =~ /^\D/ ) {
@_ = $self->_substitute_cellref( @_ );
}
# Check for a valid number of args.
if ( @_ != 5 && @_ != 3 ) { return -1 }
# The final hashref contains the validation parameters.
my $options = pop;
# Make the last row/col the same as the first if not defined.
my ( $row1, $col1, $row2, $col2 ) = @_;
if ( !defined $row2 ) {
$row2 = $row1;
$col2 = $col1;
}
# Check that row and col are valid without storing the values.
return -2 if $self->_check_dimensions( $row1, $col1, 1, 1 );
return -2 if $self->_check_dimensions( $row2, $col2, 1, 1 );
# Check that the last parameter is a hash list.
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
elsif ( not exists $error_type{ lc( $param->{error_type} ) } ) {
carp "Unknown criteria type '$param->{error_type}' for parameter "
. "'error_type' in data_validation()";
return -3;
}
else {
$param->{error_type} = $error_type{ lc( $param->{error_type} ) };
}
# Convert date/times value if required.
if ( $param->{validate} eq 'date' || $param->{validate} eq 'time' ) {
my $date_time = $self->convert_date_time( $param->{value} );
if ( defined $date_time ) {
$param->{value} = $date_time;
}
if ( defined $param->{maximum} ) {
my $date_time = $self->convert_date_time( $param->{maximum} );
if ( defined $date_time ) {
$param->{maximum} = $date_time;
}
}
}
# Check that the input title doesn't exceed the maximum length.
if ( $param->{input_title} and length $param->{input_title} > 32 ) {
carp "Length of input title '$param->{input_title}'"
. " exceeds Excel's limit of 32";
return -3;
}
# Check that the error title don't exceed the maximum length.
if ( $param->{error_title} and length $param->{error_title} > 32 ) {
carp "Length of error title '$param->{error_title}'"
. " exceeds Excel's limit of 32";
return -3;
}
# Check that the input message don't exceed the maximum length.
if ( $param->{input_message} and length $param->{input_message} > 255 ) {
carp "Length of input message '$param->{input_message}'"
. " exceeds Excel's limit of 255";
return -3;
}
# Check that the error message don't exceed the maximum length.
if ( $param->{error_message} and length $param->{error_message} > 255 ) {
carp "Length of error message '$param->{error_message}'"
. " exceeds Excel's limit of 255";
return -3;
}
# Check that the input list don't exceed the maximum length.
if ( $param->{validate} eq 'list' ) {
if ( ref $param->{value} eq 'ARRAY' ) {
my $formula = join ',', @{ $param->{value} };
if ( length $formula > 255 ) {
carp "Length of list items '$formula' exceeds Excel's "
. "limit of 255, use a formula range instead";
return -3;
}
}
}
# Set some defaults if they haven't been defined by the user.
$param->{ignore_blank} = 1 if !defined $param->{ignore_blank};
$param->{dropdown} = 1 if !defined $param->{dropdown};
$param->{show_input} = 1 if !defined $param->{show_input};
$param->{show_error} = 1 if !defined $param->{show_error};
# These are the cells to which the validation is applied.
$param->{cells} = [ [ $row1, $col1, $row2, $col2 ] ];
# A (for now) undocumented parameter to pass additional cell ranges.
if ( exists $param->{other_cells} ) {
push @{ $param->{cells} }, @{ $param->{other_cells} };
}
# Store the validation information until we close the worksheet.
push @{ $self->{_validations} }, $param;
}
###############################################################################
#
# conditional_formatting($row, $col, {...})
#
# This method handles the interface to Excel conditional formatting.
#
# We allow the format to be called on one cell or a range of cells. The
# hashref contains the formatting parameters and must be the last param:
# conditional_formatting($row, $col, {...})
# conditional_formatting($first_row, $first_col, $last_row, $last_col, {...})
#
# Returns 0 : normal termination
# -1 : insufficient number of arguments
# -2 : row or column out of range
# -3 : incorrect parameter.
#
sub conditional_formatting {
my $self = shift;
my $user_range = '';
# Check for a cell reference in A1 notation and substitute row and column
if ( $_[0] =~ /^\D/ ) {
# Check for a user defined multiple range like B3:K6,B8:K11.
if ( $_[0] =~ /,/ ) {
$user_range = $_[0];
$user_range =~ s/^=//;
$user_range =~ s/\s*,\s*/ /g;
$user_range =~ s/\$//g;
}
@_ = $self->_substitute_cellref( @_ );
}
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
mid_type => 1,
max_type => 1,
min_value => 1,
mid_value => 1,
max_value => 1,
min_color => 1,
mid_color => 1,
max_color => 1,
bar_color => 1,
bar_negative_color => 1,
bar_negative_color_same => 1,
bar_solid => 1,
bar_border_color => 1,
bar_negative_border_color => 1,
bar_negative_border_color_same => 1,
bar_no_border => 1,
bar_direction => 1,
bar_axis_position => 1,
bar_axis_color => 1,
bar_only => 1,
icon_style => 1,
reverse_icons => 1,
icons_only => 1,
icons => 1,
data_bar_2010 => 1,
);
# Check for valid input parameters.
for my $param_key ( keys %$param ) {
if ( not exists $valid_parameter{$param_key} ) {
carp "Unknown parameter '$param_key' in conditional_formatting()";
return -3;
}
}
# 'type' is a required parameter.
if ( not exists $param->{type} ) {
carp "Parameter 'type' is required in conditional_formatting()";
return -3;
}
# List of valid validation types.
my %valid_type = (
'cell' => 'cellIs',
'date' => 'date',
'time' => 'time',
'average' => 'aboveAverage',
'duplicate' => 'duplicateValues',
'unique' => 'uniqueValues',
'top' => 'top10',
'bottom' => 'top10',
'text' => 'text',
'time_period' => 'timePeriod',
'blanks' => 'containsBlanks',
'no_blanks' => 'notContainsBlanks',
'errors' => 'containsErrors',
'no_errors' => 'notContainsErrors',
'2_color_scale' => '2_color_scale',
'3_color_scale' => '3_color_scale',
'data_bar' => 'dataBar',
'formula' => 'expression',
'icon_set' => 'iconSet',
);
# Check for valid validation types.
if ( not exists $valid_type{ lc( $param->{type} ) } ) {
carp "Unknown validation type '$param->{type}' for parameter "
. "'type' in conditional_formatting()";
return -3;
}
else {
$param->{direction} = 'bottom' if $param->{type} eq 'bottom';
$param->{type} = $valid_type{ lc( $param->{type} ) };
}
# List of valid criteria types.
my %criteria_type = (
'between' => 'between',
'not between' => 'notBetween',
'equal to' => 'equal',
'=' => 'equal',
'==' => 'equal',
'not equal to' => 'notEqual',
'!=' => 'notEqual',
'<>' => 'notEqual',
'greater than' => 'greaterThan',
'>' => 'greaterThan',
'less than' => 'lessThan',
'<' => 'lessThan',
'greater than or equal to' => 'greaterThanOrEqual',
'>=' => 'greaterThanOrEqual',
'less than or equal to' => 'lessThanOrEqual',
'<=' => 'lessThanOrEqual',
'containing' => 'containsText',
'not containing' => 'notContains',
'begins with' => 'beginsWith',
'ends with' => 'endsWith',
'yesterday' => 'yesterday',
'today' => 'today',
'last 7 days' => 'last7Days',
'last week' => 'lastWeek',
'this week' => 'thisWeek',
'next week' => 'nextWeek',
'last month' => 'lastMonth',
'this month' => 'thisMonth',
'next month' => 'nextMonth',
);
# Check for valid criteria types.
if ( defined $param->{criteria}
&& exists $criteria_type{ lc( $param->{criteria} ) } )
{
$param->{criteria} = $criteria_type{ lc( $param->{criteria} ) };
}
# Convert date/times value if required.
if ( $param->{type} eq 'date' || $param->{type} eq 'time' ) {
$param->{type} = 'cellIs';
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
}
# List of valid icon styles.
my %icon_set_styles = (
"3_arrows" => "3Arrows", # 1
"3_flags" => "3Flags", # 2
"3_traffic_lights_rimmed" => "3TrafficLights2", # 3
"3_symbols_circled" => "3Symbols", # 4
"4_arrows" => "4Arrows", # 5
"4_red_to_black" => "4RedToBlack", # 6
"4_traffic_lights" => "4TrafficLights", # 7
"5_arrows_gray" => "5ArrowsGray", # 8
"5_quarters" => "5Quarters", # 9
"3_arrows_gray" => "3ArrowsGray", # 10
"3_traffic_lights" => "3TrafficLights", # 11
"3_signs" => "3Signs", # 12
"3_symbols" => "3Symbols2", # 13
"4_arrows_gray" => "4ArrowsGray", # 14
"4_ratings" => "4Rating", # 15
"5_arrows" => "5Arrows", # 16
"5_ratings" => "5Rating", # 17
);
# Set properties for icon sets.
if ( $param->{type} eq 'iconSet' ) {
if ( !defined $param->{icon_style} ) {
carp "The 'icon_style' parameter must be specified when "
. "'type' == 'icon_set' in conditional_formatting()";
return -3;
}
# Check for valid icon styles.
if ( not exists $icon_set_styles{ $param->{icon_style} } ) {
carp "Unknown icon style '$param->{icon_style}' for parameter "
. "'icon_style' in conditional_formatting()";
return -3;
}
else {
$param->{icon_style} = $icon_set_styles{ $param->{icon_style} };
}
# Set the number of icons for the icon style.
$param->{total_icons} = 3;
if ( $param->{icon_style} =~ /^4/ ) {
$param->{total_icons} = 4;
}
elsif ( $param->{icon_style} =~ /^5/ ) {
$param->{total_icons} = 5;
}
$param->{icons} =
$self->_set_icon_properties( $param->{total_icons}, $param->{icons} );
}
# Set the formatting range.
my $range = '';
my $start_cell = ''; # Use for formulas.
# Swap last row/col for first row/col as necessary
if ( $row1 > $row2 ) {
( $row1, $row2 ) = ( $row2, $row1 );
}
if ( $col1 > $col2 ) {
( $col1, $col2 ) = ( $col2, $col1 );
}
$range = xl_range( $row1, $row2, $col1, $col2 );
$start_cell = xl_rowcol_to_cell( $row1, $col1 );
# Override with user defined multiple range if provided.
if ( $user_range ) {
$range = $user_range;
}
# Get the dxf format index.
if ( defined $param->{format} && ref $param->{format} ) {
$param->{format} = $param->{format}->get_dxf_index();
}
# Set the priority based on the order of adding.
$param->{priority} = $self->{_dxf_priority}++;
# Check for 2010 style data_bar parameters.
if ( $self->{_use_data_bars_2010}
|| $param->{data_bar_2010}
|| $param->{bar_solid}
|| $param->{bar_border_color}
|| $param->{bar_negative_color}
|| $param->{bar_negative_color_same}
|| $param->{bar_negative_border_color}
|| $param->{bar_negative_border_color_same}
|| $param->{bar_no_border}
|| $param->{bar_axis_position}
|| $param->{bar_axis_color}
|| $param->{bar_direction} )
{
$param->{_is_data_bar_2010} = 1;
}
# Special handling of text criteria.
if ( $param->{type} eq 'text' ) {
if ( $param->{criteria} eq 'containsText' ) {
$param->{type} = 'containsText';
$param->{formula} = sprintf 'NOT(ISERROR(SEARCH("%s",%s)))',
$param->{value}, $start_cell;
}
elsif ( $param->{criteria} eq 'notContains' ) {
$param->{type} = 'notContainsText';
$param->{formula} = sprintf 'ISERROR(SEARCH("%s",%s))',
$param->{value}, $start_cell;
}
elsif ( $param->{criteria} eq 'beginsWith' ) {
$param->{type} = 'beginsWith';
$param->{formula} = sprintf 'LEFT(%s,%d)="%s"',
$start_cell, length( $param->{value} ), $param->{value};
}
elsif ( $param->{criteria} eq 'endsWith' ) {
$param->{type} = 'endsWith';
$param->{formula} = sprintf 'RIGHT(%s,%d)="%s"',
$start_cell, length( $param->{value} ), $param->{value};
}
else {
carp "Invalid text criteria '$param->{criteria}' "
. "in conditional_formatting()";
}
}
# Special handling of time time_period criteria.
if ( $param->{type} eq 'timePeriod' ) {
if ( $param->{criteria} eq 'yesterday' ) {
$param->{formula} = sprintf 'FLOOR(%s,1)=TODAY()-1', $start_cell;
}
elsif ( $param->{criteria} eq 'today' ) {
$param->{formula} = sprintf 'FLOOR(%s,1)=TODAY()', $start_cell;
}
elsif ( $param->{criteria} eq 'tomorrow' ) {
$param->{formula} = sprintf 'FLOOR(%s,1)=TODAY()+1', $start_cell;
}
elsif ( $param->{criteria} eq 'last7Days' ) {
$param->{formula} =
sprintf 'AND(TODAY()-FLOOR(%s,1)<=6,FLOOR(%s,1)<=TODAY())',
$start_cell, $start_cell;
}
elsif ( $param->{criteria} eq 'lastWeek' ) {
$param->{formula} =
sprintf 'AND(TODAY()-ROUNDDOWN(%s,0)>=(WEEKDAY(TODAY())),'
. 'TODAY()-ROUNDDOWN(%s,0)<(WEEKDAY(TODAY())+7))',
$start_cell, $start_cell;
}
elsif ( $param->{criteria} eq 'thisWeek' ) {
$param->{formula} =
sprintf 'AND(TODAY()-ROUNDDOWN(%s,0)<=WEEKDAY(TODAY())-1,'
. 'ROUNDDOWN(%s,0)-TODAY()<=7-WEEKDAY(TODAY()))',
$start_cell, $start_cell;
}
elsif ( $param->{criteria} eq 'nextWeek' ) {
$param->{formula} =
sprintf 'AND(ROUNDDOWN(%s,0)-TODAY()>(7-WEEKDAY(TODAY())),'
. 'ROUNDDOWN(%s,0)-TODAY()<(15-WEEKDAY(TODAY())))',
$start_cell, $start_cell;
}
elsif ( $param->{criteria} eq 'lastMonth' ) {
$param->{formula} =
sprintf
'AND(MONTH(%s)=MONTH(TODAY())-1,OR(YEAR(%s)=YEAR(TODAY()),'
. 'AND(MONTH(%s)=1,YEAR(A1)=YEAR(TODAY())-1)))',
$start_cell, $start_cell, $start_cell;
}
elsif ( $param->{criteria} eq 'thisMonth' ) {
$param->{formula} =
sprintf 'AND(MONTH(%s)=MONTH(TODAY()),YEAR(%s)=YEAR(TODAY()))',
$start_cell, $start_cell;
}
elsif ( $param->{criteria} eq 'nextMonth' ) {
$param->{formula} =
sprintf
'AND(MONTH(%s)=MONTH(TODAY())+1,OR(YEAR(%s)=YEAR(TODAY()),'
. 'AND(MONTH(%s)=12,YEAR(%s)=YEAR(TODAY())+1)))',
$start_cell, $start_cell, $start_cell, $start_cell;
}
else {
carp "Invalid time_period criteria '$param->{criteria}' "
. "in conditional_formatting()";
}
}
# Special handling of blanks/error types.
if ( $param->{type} eq 'containsBlanks' ) {
$param->{formula} = sprintf 'LEN(TRIM(%s))=0', $start_cell;
}
if ( $param->{type} eq 'notContainsBlanks' ) {
$param->{formula} = sprintf 'LEN(TRIM(%s))>0', $start_cell;
}
if ( $param->{type} eq 'containsErrors' ) {
$param->{formula} = sprintf 'ISERROR(%s)', $start_cell;
}
if ( $param->{type} eq 'notContainsErrors' ) {
$param->{formula} = sprintf 'NOT(ISERROR(%s))', $start_cell;
}
# Special handling for 2 color scale.
if ( $param->{type} eq '2_color_scale' ) {
$param->{type} = 'colorScale';
# Color scales don't use any additional formatting.
$param->{format} = undef;
# Turn off 3 color parameters.
$param->{mid_type} = undef;
$param->{mid_color} = undef;
$param->{min_type} ||= 'min';
$param->{max_type} ||= 'max';
$param->{min_value} ||= 0;
$param->{max_value} ||= 0;
$param->{min_color} ||= '#FF7128';
$param->{max_color} ||= '#FFEF9C';
$param->{max_color} = $self->_get_palette_color( $param->{max_color} );
$param->{min_color} = $self->_get_palette_color( $param->{min_color} );
}
# Special handling for 3 color scale.
if ( $param->{type} eq '3_color_scale' ) {
$param->{type} = 'colorScale';
# Color scales don't use any additional formatting.
$param->{format} = undef;
$param->{min_type} ||= 'min';
$param->{mid_type} ||= 'percentile';
$param->{max_type} ||= 'max';
$param->{min_value} ||= 0;
$param->{mid_value} = 50 unless defined $param->{mid_value};
$param->{max_value} ||= 0;
$param->{min_color} ||= '#F8696B';
$param->{mid_color} ||= '#FFEB84';
$param->{max_color} ||= '#63BE7B';
$param->{max_color} = $self->_get_palette_color( $param->{max_color} );
$param->{mid_color} = $self->_get_palette_color( $param->{mid_color} );
$param->{min_color} = $self->_get_palette_color( $param->{min_color} );
}
# Special handling for data bar.
if ( $param->{type} eq 'dataBar' ) {
# Excel 2007 data bars don't use any additional formatting.
$param->{format} = undef;
if ( !defined $param->{min_type} ) {
$param->{min_type} = 'min';
$param->{_x14_min_type} = 'autoMin';
}
else {
$param->{_x14_min_type} = $param->{min_type};
}
if ( !defined $param->{max_type} ) {
$param->{max_type} = 'max';
$param->{_x14_max_type} = 'autoMax';
}
else {
$param->{_x14_max_type} = $param->{max_type};
}
$param->{min_value} ||= 0;
$param->{max_value} ||= 0;
$param->{bar_color} ||= '#638EC6';
$param->{bar_border_color} ||= $param->{bar_color};
$param->{bar_only} ||= 0;
$param->{bar_no_border} ||= 0;
$param->{bar_solid} ||= 0;
$param->{bar_direction} ||= '';
$param->{bar_negative_color} ||= '#FF0000';
$param->{bar_negative_border_color} ||= '#FF0000';
$param->{bar_negative_color_same} ||= 0;
$param->{bar_negative_border_color_same} ||= 0;
$param->{bar_axis_position} ||= '';
$param->{bar_axis_color} ||= '#000000';
$param->{bar_color} =
$self->_get_palette_color( $param->{bar_color} );
$param->{bar_border_color} =
$self->_get_palette_color( $param->{bar_border_color} );
$param->{bar_negative_color} =
$self->_get_palette_color( $param->{bar_negative_color} );
$param->{bar_negative_border_color} =
$self->_get_palette_color( $param->{bar_negative_border_color} );
$param->{bar_axis_color} =
$self->_get_palette_color( $param->{bar_axis_color} );
}
# Adjust for 2010 style data_bar parameters.
if ( $param->{_is_data_bar_2010} ) {
$self->{_excel_version} = 2010;
if ( $param->{min_type} eq 'min' && $param->{min_value} == 0 ) {
$param->{min_value} = undef;
}
if ( $param->{max_type} eq 'max' && $param->{max_value} == 0 ) {
$param->{max_value} = undef;
}
# Store range for Excel 2010 data bars.
$param->{_range} = $range;
}
# Strip the leading = from formulas.
$param->{min_value} =~ s/^=// if defined $param->{min_value};
$param->{mid_value} =~ s/^=// if defined $param->{mid_value};
$param->{max_value} =~ s/^=// if defined $param->{max_value};
# Store the validation information until we close the worksheet.
push @{ $self->{_cond_formats}->{$range} }, $param;
}
###############################################################################
#
# Set the sub-properties for icons.
#
sub _set_icon_properties {
my $self = shift;
my $total_icons = shift;
my $user_props = shift;
my $props = [];
# Set the default icon properties.
for ( 0 .. $total_icons - 1 ) {
push @$props,
{
criteria => 0,
value => 0,
type => 'percent'
};
}
# Set the default icon values based on the number of icons.
if ( $total_icons == 3 ) {
$props->[0]->{value} = 67;
$props->[1]->{value} = 33;
}
if ( $total_icons == 4 ) {
$props->[0]->{value} = 75;
$props->[1]->{value} = 50;
$props->[2]->{value} = 25;
}
if ( $total_icons == 5 ) {
$props->[0]->{value} = 80;
$props->[1]->{value} = 60;
$props->[2]->{value} = 40;
$props->[3]->{value} = 20;
}
# Overwrite default properties with user defined properties.
if ( defined $user_props ) {
# Ensure we don't set user properties for lowest icon.
my $max_data = @$user_props;
if ( $max_data >= $total_icons ) {
$max_data = $total_icons -1;
}
for my $i ( 0 .. $max_data - 1 ) {
# Set the user defined 'value' property.
if ( defined $user_props->[$i]->{value} ) {
$props->[$i]->{value} = $user_props->[$i]->{value};
$props->[$i]->{value} =~ s/^=//;
}
# Set the user defined 'type' property.
if ( defined $user_props->[$i]->{type} ) {
my $type = $user_props->[$i]->{type};
if ( $type ne 'percent'
&& $type ne 'percentile'
&& $type ne 'number'
&& $type ne 'formula' )
{
carp "Unknown icon property type '$props->{type}' for sub-"
. "property 'type' in conditional_formatting()";
}
else {
$props->[$i]->{type} = $type;
if ( $props->[$i]->{type} eq 'number' ) {
$props->[$i]->{type} = 'num';
}
}
}
# Set the user defined 'criteria' property.
if ( defined $user_props->[$i]->{criteria}
&& $user_props->[$i]->{criteria} eq '>' )
{
$props->[$i]->{criteria} = 1;
}
}
}
return $props;
}
###############################################################################
#
# add_table()
#
# Add an Excel table to a worksheet.
#
sub add_table {
my $self = shift;
my $user_range = '';
my %table;
my @col_formats;
# We would need to order the write statements very carefully within this
# function to support optimisation mode. Disable add_table() when it is
# on for now.
if ( $self->{_optimization} == 1 ) {
carp "add_table() isn't supported when set_optimization() is on";
return -1;
}
# Check for a cell reference in A1 notation and substitute row and column
if ( @_ && $_[0] =~ /^\D/ ) {
@_ = $self->_substitute_cellref( @_ );
}
# Check for a valid number of args.
if ( @_ < 4 ) {
carp "Not enough parameters to add_table()";
return -1;
}
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
if ( $name =~ m/^[a-zA-Z][a-zA-Z]?[a-dA-D]?[0-9]+$/ ) {
carp "Invalid name '$name' looks like a cell name in add_table()";
return -3;
}
# 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 add_table()";
return -3;
}
$table{_name} = $param->{name};
}
# Set the table style.
if ( defined $param->{style} ) {
$table{_style} = $param->{style};
# Remove whitespace from style name.
$table{_style} =~ s/\s//g;
}
else {
$table{_style} = "TableStyleMedium9";
}
# Set the data range rows (without the header and footer).
my $first_data_row = $row1;
my $last_data_row = $row2;
$first_data_row++ if $param->{header_row};
$last_data_row-- if $param->{total_row};
# Set the table and autofilter ranges.
$table{_range} = xl_range( $row1, $row2, $col1, $col2 );
$table{_a_range} = xl_range( $row1, $last_data_row, $col1, $col2 );
# If the header row if off the default is to turn autofilter off.
if ( !$param->{header_row} ) {
$param->{autofilter} = 0;
}
# Set the autofilter range.
if ( $param->{autofilter} ) {
$table{_autofilter} = $table{_a_range};
}
# Add the table columns.
my %seen_names;
my $col_id = 1;
for my $col_num ( $col1 .. $col2 ) {
# Set up the default column data.
my $col_data = {
_id => $col_id,
_name => 'Column' . $col_id,
_total_string => '',
_total_function => '',
_custom_total => '',
_formula => '',
_format => undef,
_name_format => undef,
};
# Overwrite the defaults with any use defined values.
if ( $param->{columns} ) {
# Check if there are user defined values for this column.
if ( my $user_data = $param->{columns}->[ $col_id - 1 ] ) {
# Map user defined values to internal values.
if ( defined $user_data->{header}
&& $user_data->{header} ne "" )
{
$col_data->{_name} = $user_data->{header};
}
# Excel requires unique case insensitive header names.
my $name = $col_data->{_name};
my $key = lc $name;
if ( exists $seen_names{$key} ) {
carp "add_table() contains duplicate name: '$name'";
return -1;
}
else {
$seen_names{$key} = 1;
}
# Get the header format if defined.
$col_data->{_name_format} = $user_data->{header_format};
# Handle the column formula.
if ( $user_data->{formula} ) {
my $formula = $user_data->{formula};
# Remove the leading = from formula.
$formula =~ s/^=//;
# Covert Excel 2010 "@" ref to 2007 "#This Row".
$formula =~ s/@/[#This Row],/g;
# Escape any future functions.
$formula = $self->_prepare_formula($formula, 1);
$col_data->{_formula} = $formula;
# We write the formulas below after the table data.
}
# Handle the function for the total row.
if ( $user_data->{total_function} ) {
my $formula = '';
my $function = $user_data->{total_function};
$function = 'countNums' if $function eq 'count_nums';
$function = 'stdDev' if $function eq 'std_dev';
my %subtotals = (
average => 101,
countNums => 102,
count => 103,
max => 104,
min => 105,
stdDev => 107,
sum => 109,
var => 110,
);
if ( exists $subtotals{$function} ) {
$formula =
_table_function_to_formula( $function,
$col_data->{_name} );
}
else {
$formula = $self->_prepare_formula($function, 1);
$col_data->{_custom_total} = $formula;
$function = 'custom';
}
$col_data->{_total_function} = $function;
my $value = $user_data->{total_value} || 0;
$self->write_formula( $row2, $col_num, $formula,
$user_data->{format}, $value );
}
elsif ( $user_data->{total_string} ) {
# Total label only (not a function).
my $total_string = $user_data->{total_string};
$col_data->{_total_string} = $total_string;
$self->write_string( $row2, $col_num, $total_string,
$user_data->{format} );
}
# Get the dxf format index.
if ( defined $user_data->{format} && ref $user_data->{format} )
{
$col_data->{_format} =
$user_data->{format}->get_dxf_index();
}
# Store the column format for writing the cell data.
# It doesn't matter if it is undefined.
$col_formats[ $col_id - 1 ] = $user_data->{format};
}
}
# Store the column data.
push @{ $table{_columns} }, $col_data;
# Write the column headers to the worksheet.
if ( $param->{header_row} ) {
$self->write_string( $row1, $col_num, $col_data->{_name},
$col_data->{_name_format} );
}
$col_id++;
} # Table columns.
# Write the cell data if supplied.
if ( my $data = $param->{data} ) {
my $i = 0; # For indexing the row data.
for my $row ( $first_data_row .. $last_data_row ) {
my $j = 0; # For indexing the col data.
for my $col ( $col1 .. $col2 ) {
my $token = $data->[$i]->[$j];
if ( defined $token ) {
$self->write( $row, $col, $token, $col_formats[$j] );
}
$j++;
}
$i++;
}
}
# Write any columns formulas after the user supplied table data to
# overwrite it if required.
$col_id = 0;
for my $col_num ( $col1 .. $col2 ) {
my $column_data = $table{_columns}->[$col_id];
if ( $column_data && $column_data->{_formula} ) {
my $formula_format = $col_formats[$col_id];
my $formula = $column_data->{_formula};
for my $row ( $first_data_row .. $last_data_row ) {
$self->write_formula( $row, $col_num, $formula,
$formula_format );
}
}
$col_id++;
}
# Store the filter cell positions for use in the autofit calculation.
if ( $param->{autofilter} ) {
for my $col ( $col1 .. $col2 ) {
$self->{_filter_cells}->{"$row1:$col"} = 1;
}
}
# Store the table data.
push @{ $self->{_tables} }, \%table;
return \%table;
}
###############################################################################
#
# add_sparkline()
#
# Add sparklines to the worksheet.
#
sub add_sparkline {
my $self = shift;
my $param = shift;
my $sparkline = {};
# Check that the last parameter is a hash list.
if ( ref $param ne 'HASH' ) {
carp "Parameter list in add_sparkline() must be a hash ref";
return -1;
}
# List of valid input parameters.
my %valid_parameter = (
location => 1,
range => 1,
type => 1,
high_point => 1,
low_point => 1,
negative_points => 1,
first_point => 1,
last_point => 1,
markers => 1,
style => 1,
series_color => 1,
negative_color => 1,
markers_color => 1,
first_color => 1,
last_color => 1,
high_color => 1,
low_color => 1,
max => 1,
min => 1,
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
}
# 'location' is a required parameter.
if ( not exists $param->{location} ) {
carp "Parameter 'location' is required in add_sparkline()";
return -3;
}
# 'range' is a required parameter.
if ( not exists $param->{range} ) {
carp "Parameter 'range' is required in add_sparkline()";
return -3;
}
# Handle the sparkline type.
my $type = $param->{type} || 'line';
if ( $type ne 'line' && $type ne 'column' && $type ne 'win_loss' ) {
carp "Parameter 'type' must be 'line', 'column' "
. "or 'win_loss' in add_sparkline()";
return -4;
}
$type = 'stacked' if $type eq 'win_loss';
$sparkline->{_type} = $type;
# We handle single location/range values or array refs of values.
if ( ref $param->{location} ) {
$sparkline->{_locations} = $param->{location};
$sparkline->{_ranges} = $param->{range};
}
else {
$sparkline->{_locations} = [ $param->{location} ];
$sparkline->{_ranges} = [ $param->{range} ];
}
my $range_count = @{ $sparkline->{_ranges} };
my $location_count = @{ $sparkline->{_locations} };
# The ranges and locations must match.
if ( $range_count != $location_count ) {
carp "Must have the same number of location and range "
. "parameters in add_sparkline()";
return -5;
}
# Store the count.
$sparkline->{_count} = @{ $sparkline->{_locations} };
# Get the worksheet name for the range conversion below.
my $sheetname = quote_sheetname( $self->{_name} );
# Cleanup the input ranges.
for my $range ( @{ $sparkline->{_ranges} } ) {
# Remove the absolute reference $ symbols.
$range =~ s{\$}{}g;
# Remove the = from xl_range_formula(.
$range =~ s{^=}{};
# Convert a simple range into a full Sheet1!A1:D1 range.
if ( $range !~ /!/ ) {
$range = $sheetname . "!" . $range;
}
}
# Cleanup the input locations.
for my $location ( @{ $sparkline->{_locations} } ) {
$location =~ s{\$}{}g;
}
# Map options.
$sparkline->{_high} = $param->{high_point};
$sparkline->{_low} = $param->{low_point};
$sparkline->{_negative} = $param->{negative_points};
$sparkline->{_first} = $param->{first_point};
$sparkline->{_last} = $param->{last_point};
$sparkline->{_markers} = $param->{markers};
$sparkline->{_min} = $param->{min};
$sparkline->{_max} = $param->{max};
$sparkline->{_axis} = $param->{axis};
$sparkline->{_reverse} = $param->{reverse};
$sparkline->{_hidden} = $param->{show_hidden};
$sparkline->{_weight} = $param->{weight};
# Map empty cells options.
my $empty = $param->{empty_cells} || '';
if ( $empty eq 'zero' ) {
$sparkline->{_empty} = 0;
}
elsif ( $empty eq 'connect' ) {
$sparkline->{_empty} = 'span';
}
else {
$sparkline->{_empty} = 'gap';
}
# Map the date axis range.
my $date_range = $param->{date_axis};
if ( $date_range && $date_range !~ /!/ ) {
$date_range = $sheetname . "!" . $date_range;
}
$sparkline->{_date_axis} = $date_range;
# Set the sparkline styles.
my $style_id = $param->{style} || 0;
my $style = $Excel::Writer::XLSX::Package::Theme::spark_styles[$style_id];
$sparkline->{_series_color} = $style->{series};
$sparkline->{_negative_color} = $style->{negative};
$sparkline->{_markers_color} = $style->{markers};
$sparkline->{_first_color} = $style->{first};
$sparkline->{_last_color} = $style->{last};
$sparkline->{_high_color} = $style->{high};
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
# insert_button()
#
# 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()";
}
return $formula;
}
###############################################################################
#
# _set_spark_color()
#
# Set the sparkline colour.
#
sub _set_spark_color {
my $self = shift;
my $sparkline = shift;
my $param = shift;
my $user_color = shift;
my $spark_color = '_' . $user_color;
return unless $param->{$user_color};
$sparkline->{$spark_color} =
{ _rgb => $self->_get_palette_color( $param->{$user_color} ) };
}
###############################################################################
#
# _get_palette_color()
#
# Convert from an Excel internal colour index to a XML style #RRGGBB index
# based on the default or user defined values in the Workbook palette.
#
sub _get_palette_color {
my $self = shift;
my $index = shift;
my $palette = $self->{_palette};
# Handle colours in #XXXXXX RGB format.
if ( $index =~ m/^#([0-9A-F]{6})$/i ) {
return "FF" . uc( $1 );
}
# Adjust the colour index.
$index -= 8;
# Palette is passed in from the Workbook class.
my @rgb = @{ $palette->[$index] };
return sprintf "FF%02X%02X%02X", @rgb[0, 1, 2];
}
###############################################################################
#
# _substitute_cellref()
#
# Substitute an Excel cell reference in A1 notation for zero based row and
# column values in an argument list.
#
# Ex: ("A4", "Hello") is converted to (3, 0, "Hello").
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
push @{ $self->{_drawing_links} },
[ '/chart', '../charts/chart' . $chart_id . '.xml' ];
}
###############################################################################
#
# _get_range_data
#
# Returns a range of data from the worksheet _table to be used in chart
# cached data. Strings are returned as SST ids and decoded in the workbook.
# Return undefs for data that doesn't exist since Excel can chart series
# with data missing.
#
sub _get_range_data {
my $self = shift;
return () if $self->{_optimization};
my @data;
my ( $row_start, $col_start, $row_end, $col_end ) = @_;
# TODO. Check for worksheet limits.
# Iterate through the table data.
for my $row_num ( $row_start .. $row_end ) {
# Store undef if row doesn't exist.
if ( !exists $self->{_table}->{$row_num} ) {
push @data, undef;
next;
}
for my $col_num ( $col_start .. $col_end ) {
if ( my $cell = $self->{_table}->{$row_num}->{$col_num} ) {
my $type = $cell->[0];
my $token = $cell->[1];
if ( $type eq 'n' || $type eq 't') {
# Store a number.
push @data, $token;
}
elsif ( $type eq 's' || $type eq 'r' ) {
# Store a string.
if ( $self->{_optimization} == 0 ) {
push @data, { 'sst_id' => $token };
}
else {
push @data, $token;
}
}
elsif ( $type eq 'f' ) {
# Store a formula.
push @data, $cell->[3] || 0;
}
elsif ( $type eq 'a' || $type eq 'd') {
# Store an array formula.
push @data, $cell->[4] || 0;
}
elsif ( $type eq 'b' ) {
# Store a empty cell.
push @data, '';
}
}
else {
# Store undef if col doesn't exist.
push @data, undef;
}
}
}
return @data;
}
###############################################################################
#
# insert_image( $row, $col, $filename, $options )
#
# Insert an image into the worksheet.
#
sub insert_image {
my $self = shift;
# Check for a cell reference in A1 notation and substitute row and column.
if ( $_[0] =~ /^\D/ ) {
@_ = $self->_substitute_cellref( @_ );
}
my $row = $_[0];
my $col = $_[1];
my $image = $_[2];
my $x_offset;
my $y_offset;
my $x_scale;
my $y_scale;
my $anchor;
my $url;
my $tip;
my $description;
my $decorative;
if ( ref $_[3] eq 'HASH' ) {
# Newer hashref bashed options.
my $options = $_[3];
$x_offset = $options->{x_offset} || 0;
$y_offset = $options->{y_offset} || 0;
$x_scale = $options->{x_scale} || 1;
$y_scale = $options->{y_scale} || 1;
$anchor = $options->{object_position} || 2;
$url = $options->{url};
$tip = $options->{tip};
$description = $options->{description};
$decorative = $options->{decorative};
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
my @vertices = $self->_position_object_pixels(
$params->{start_col}, $params->{start_row}, $params->{x_offset},
$params->{y_offset}, $params->{width}, $params->{height}
);
# Add the width and height for VML.
push @vertices, ( $params->{width}, $params->{height} );
$button->{_vertices} = \@vertices;
return $button;
}
###############################################################################
#
# Deprecated methods for backwards compatibility.
#
###############################################################################
# This method was mainly only required for Excel 5.
sub write_url_range { }
# Deprecated UTF-16 method required for the Excel 5 format.
sub write_utf16be_string {
my $self = shift;
# Convert A1 notation if present.
@_ = $self->_substitute_cellref( @_ ) if $_[0] =~ /^\D/;
# Check the number of args.
return -1 if @_ < 3;
# Convert UTF16 string to UTF8.
require Encode;
my $utf8_string = Encode::decode( 'UTF-16BE', $_[2] );
return $self->write_string( $_[0], $_[1], $utf8_string, $_[3] );
}
# Deprecated UTF-16 method required for the Excel 5 format.
sub write_utf16le_string {
my $self = shift;
# Convert A1 notation if present.
@_ = $self->_substitute_cellref( @_ ) if $_[0] =~ /^\D/;
# Check the number of args.
return -1 if @_ < 3;
# Convert UTF16 string to UTF8.
require Encode;
my $utf8_string = Encode::decode( 'UTF-16LE', $_[2] );
return $self->write_string( $_[0], $_[1], $utf8_string, $_[3] );
}
# No longer required. Was used to avoid slow formula parsing.
sub store_formula {
my $self = shift;
my $string = shift;
my @tokens = split /(\$?[A-I]?[A-Z]\$?\d+)/, $string;
return \@tokens;
}
# No longer required. Was used to avoid slow formula parsing.
sub repeat_formula {
my $self = shift;
# Convert A1 notation if present.
@_ = $self->_substitute_cellref( @_ ) if $_[0] =~ /^\D/;
if ( @_ < 2 ) { return -1 } # Check the number of args
my $row = shift; # Zero indexed row
my $col = shift; # Zero indexed column
my $formula_ref = shift; # Array ref with formula tokens
my $format = shift; # XF format
my @pairs = @_; # Pattern/replacement pairs
# Enforce an even number of arguments in the pattern/replacement list.
croak "Odd number of elements in pattern/replacement list" if @pairs % 2;
# Check that $formula is an array ref.
croak "Not a valid formula" if ref $formula_ref ne 'ARRAY';
my @tokens = @$formula_ref;
# Allow the user to specify the result of the formula by appending a
# result => $value pair to the end of the arguments.
my $value = undef;
if ( @pairs && $pairs[-2] eq 'result' ) {
$value = pop @pairs;
pop @pairs;
}
# Make the substitutions.
while ( @pairs ) {
my $pattern = shift @pairs;
my $replace = shift @pairs;
foreach my $token ( @tokens ) {
last if $token =~ s/$pattern/$replace/;
}
}
my $formula = join '', @tokens;
return $self->write_formula( $row, $col, $formula, $format, $value );
}
# Helper function to compare adjacent column information structures.
sub _compare_col_info {
my $col_options = shift;
my $previous_options = shift;
if ( defined $col_options->[0] != defined $previous_options->[0] ) {
return undef;
}
if ( defined $col_options->[0]
&& defined $previous_options->[0]
&& $col_options->[0] != $previous_options->[0] )
{
return undef;
}
if ( defined $col_options->[1] != defined $previous_options->[1] ) {
return undef;
}
if ( defined $col_options->[1]
&& defined $previous_options->[1]
&& $col_options->[1] != $previous_options->[1] )
{
return undef;
}
return undef if $col_options->[2] != $previous_options->[2];
return undef if $col_options->[3] != $previous_options->[3];
return undef if $col_options->[4] != $previous_options->[4];
return undef if $col_options->[5] != $previous_options->[5];
return 1;
}
###############################################################################
#
# XML writing methods.
#
###############################################################################
###############################################################################
#
# _write_worksheet()
#
# Write the <worksheet> element. This is the root element of Worksheet.
#
sub _write_worksheet {
my $self = shift;
my $schema = 'http://schemas.openxmlformats.org/';
my $xmlns = $schema . 'spreadsheetml/2006/main';
my $xmlns_r = $schema . 'officeDocument/2006/relationships';
my $xmlns_mc = $schema . 'markup-compatibility/2006';
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
push @attributes, ( 'hidden' => 1 ) if $hidden;
# Only add customFormat parameter if the height is non-default.
if (
$height != $self->{_original_row_height}
|| ( $height == $self->{_original_row_height}
&& $height != $self->{_default_row_height} )
)
{
push @attributes, ( 'customHeight' => 1 );
}
push @attributes, ( 'outlineLevel' => $level ) if $level;
push @attributes, ( 'collapsed' => 1 ) if $collapsed;
if ( $self->{_excel_version} == 2010 ) {
push @attributes, ( 'x14ac:dyDescent' => '0.25' );
}
if ( $empty_row ) {
$self->xml_empty_tag_unencoded( 'row', @attributes );
}
else {
$self->xml_start_tag_unencoded( 'row', @attributes );
}
}
###############################################################################
#
# _write_empty_row()
#
# Write and empty <row> element, i.e., attributes only, no cell data.
#
sub _write_empty_row {
my $self = shift;
# Set the $empty_row parameter.
$_[7] = 1;
$self->_write_row( @_ );
}
###############################################################################
#
# _write_cell()
#
# Write the <cell> element. This is the innermost loop so efficiency is
# important where possible. The basic methodology is that the data of every
# cell type is passed in as follows:
#
# [ $row, $col, $aref]
#
# The aref, called $cell below, contains the following structure in all types:
#
# [ $type, $token, $xf, @args ]
#
# Where $type: represents the cell type, such as string, number, formula, etc.
# $token: is the actual data for the string, number, formula, etc.
# $xf: is the XF format object.
# @args: additional args relevant to the specific data type.
#
sub _write_cell {
my $self = shift;
my $row = shift;
my $col = shift;
my $cell = shift;
my $type = $cell->[0];
my $token = $cell->[1];
my $xf = $cell->[2];
my $xf_index = 0;
my %error_codes = (
'#DIV/0!' => 1,
'#N/A' => 1,
'#NAME?' => 1,
'#NULL!' => 1,
'#NUM!' => 1,
'#REF!' => 1,
'#VALUE!' => 1,
);
my %boolean = ( 'TRUE' => 1, 'FALSE' => 0 );
# Get the format index.
if ( ref( $xf ) ) {
$xf_index = $xf->get_xf_index();
}
my $range = _xl_rowcol_to_cell( $row, $col );
my @attributes = ( 'r' => $range );
# Add the cell format index.
if ( $xf_index ) {
push @attributes, ( 's' => $xf_index );
}
elsif ( $self->{_set_rows}->{$row} && $self->{_set_rows}->{$row}->[1] ) {
my $row_xf = $self->{_set_rows}->{$row}->[1];
push @attributes, ( 's' => $row_xf->get_xf_index() );
}
elsif ( $self->{_col_info}->{$col} ) {
my $col_xf = $self->{_col_info}->{$col}->[1];
if (defined $col_xf) {
push @attributes, ( 's' => $col_xf->get_xf_index() );
}
}
# Write the various cell types.
if ( $type eq 'n' || $type eq 't' ) {
# Write a number.
$self->xml_number_element( $token, @attributes );
}
elsif ( $type eq 's' || $type eq 'r' ) {
# Write a string.
if ( $self->{_optimization} == 0 ) {
$self->xml_string_element( $token, @attributes );
}
else {
my $string = $token;
# Escape control characters. See SharedString.pm for details.
$string =~ s/(_x[0-9a-fA-F]{4}_)/_x005F$1/g;
$string =~ s/([\x00-\x08\x0B-\x1F])/sprintf "_x%04X_", ord($1)/eg;
# Write any rich strings without further tags.
if ( $string =~ m{^<r>} && $string =~ m{</r>$} ) {
$self->xml_rich_inline_string( $string, @attributes );
}
else {
# Add attribute to preserve leading or trailing whitespace.
my $preserve = 0;
if ( $string =~ /^\s/ || $string =~ /\s$/ ) {
$preserve = 1;
}
$self->xml_inline_string( $string, $preserve, @attributes );
}
}
}
elsif ( $type eq 'f' ) {
# Write a formula.
my $value = $cell->[3];
$value = 0 if !defined $value;
# Check if the formula value is a string.
if ( $value
&& $value !~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/ )
{
if ( exists $boolean{$value} ) {
push @attributes, ( 't' => 'b' );
$value = $boolean{$value};
}
elsif ( exists $error_codes{$value} ) {
push @attributes, ( 't' => 'e' );
}
else {
push @attributes, ( 't' => 'str' );
$value = Excel::Writer::XLSX::Package::XMLwriter::_escape_data(
$value );
}
}
$self->xml_formula_element( $token, $value, @attributes );
}
elsif ( $type eq 'a' || $type eq 'd') {
# Add metadata linkage for dynamic array formulas.
if ($type eq 'd') {
push @attributes, ( 'cm' => '1' );
}
# Write an array formula.
$self->xml_start_tag( 'c', @attributes );
$self->_write_cell_array_formula( $token, $cell->[3] );
$self->_write_cell_value( $cell->[4] );
$self->xml_end_tag( 'c' );
}
elsif ( $type eq 'l' ) {
# Write a boolean value.
push @attributes, ( 't' => 'b' );
$self->xml_start_tag( 'c', @attributes );
$self->_write_cell_value( $cell->[1] );
$self->xml_end_tag( 'c' );
}
elsif ( $type eq 'b' ) {
# Write a empty cell.
$self->xml_empty_tag( 'c', @attributes );
}
elsif ( $type eq 'e' ) {
# Write a error value (mainly for embedded images).
push @attributes, ( 't' => 'e' );
push @attributes, ( 'vm' => $cell->[3] );
$self->xml_start_tag( 'c', @attributes );
$self->_write_cell_value( $cell->[1] );
$self->xml_end_tag( 'c' );
}
}
###############################################################################
#
# _write_cell_value()
#
# Write the cell value <v> element.
#
sub _write_cell_value {
my $self = shift;
my $value = defined $_[0] ? $_[0] : '';
$self->xml_data_element( 'v', $value );
}
###############################################################################
#
# _write_cell_formula()
#
# Write the cell formula <f> element.
#
sub _write_cell_formula {
my $self = shift;
my $formula = defined $_[0] ? $_[0] : '';
$self->xml_data_element( 'f', $formula );
}
###############################################################################
#
# _write_cell_array_formula()
#
# Write the cell array formula <f> element.
#
sub _write_cell_array_formula {
my $self = shift;
my $formula = shift;
my $range = shift;
my @attributes = ( 't' => 'array', 'ref' => $range );
$self->xml_data_element( 'f', $formula, @attributes );
}
##############################################################################
#
# _write_sheet_calc_pr()
#
# Write the <sheetCalcPr> element for the worksheet calculation properties.
#
sub _write_sheet_calc_pr {
my $self = shift;
my $full_calc_on_load = 1;
my @attributes = ( 'fullCalcOnLoad' => $full_calc_on_load );
$self->xml_empty_tag( 'sheetCalcPr', @attributes );
}
###############################################################################
#
# _write_phonetic_pr()
#
# Write the <phoneticPr> element.
#
sub _write_phonetic_pr {
my $self = shift;
my $font_id = 0;
my $type = 'noConversion';
my @attributes = (
'fontId' => $font_id,
'type' => $type,
);
$self->xml_empty_tag( 'phoneticPr', @attributes );
}
###############################################################################
#
# _write_page_margins()
#
# Write the <pageMargins> element.
#
sub _write_page_margins {
my $self = shift;
my @attributes = (
'left' => $self->{_margin_left},
'right' => $self->{_margin_right},
'top' => $self->{_margin_top},
'bottom' => $self->{_margin_bottom},
'header' => $self->{_margin_header},
'footer' => $self->{_margin_footer},
);
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
# Add a space between multiple cell ranges.
$sqref .= ' ' if $sqref ne '';
my ( $row_first, $col_first, $row_last, $col_last ) = @$cells;
# Swap last row/col for first row/col as necessary
if ( $row_first > $row_last ) {
( $row_first, $row_last ) = ( $row_last, $row_first );
}
if ( $col_first > $col_last ) {
( $col_first, $col_last ) = ( $col_last, $col_first );
}
$sqref .= xl_range( $row_first, $row_last, $col_first, $col_last );
}
if ( $param->{validate} ne 'none' ) {
push @attributes, ( 'type' => $param->{validate} );
if ( $param->{criteria} ne 'between' ) {
push @attributes, ( 'operator' => $param->{criteria} );
}
}
if ( $param->{error_type} ) {
push @attributes, ( 'errorStyle' => 'warning' )
if $param->{error_type} == 1;
push @attributes, ( 'errorStyle' => 'information' )
if $param->{error_type} == 2;
}
push @attributes, ( 'allowBlank' => 1 ) if $param->{ignore_blank};
push @attributes, ( 'showDropDown' => 1 ) if !$param->{dropdown};
push @attributes, ( 'showInputMessage' => 1 ) if $param->{show_input};
push @attributes, ( 'showErrorMessage' => 1 ) if $param->{show_error};
push @attributes, ( 'errorTitle' => $param->{error_title} )
if $param->{error_title};
push @attributes, ( 'error' => $param->{error_message} )
if $param->{error_message};
push @attributes, ( 'promptTitle' => $param->{input_title} )
if $param->{input_title};
push @attributes, ( 'prompt' => $param->{input_message} )
if $param->{input_message};
push @attributes, ( 'sqref' => $sqref );
if ( $param->{validate} eq 'none' ) {
$self->xml_empty_tag( 'dataValidation', @attributes );
}
else {
$self->xml_start_tag( 'dataValidation', @attributes );
# Write the formula1 element.
$self->_write_formula_1( $param->{value} );
# Write the formula2 element.
$self->_write_formula_2( $param->{maximum} )
if defined $param->{maximum};
$self->xml_end_tag( 'dataValidation' );
}
}
##############################################################################
#
# _write_formula_1()
#
# Write the <formula1> element.
#
sub _write_formula_1 {
my $self = shift;
my $formula = shift;
# Convert a list array ref into a comma separated string.
if ( ref $formula eq 'ARRAY' ) {
$formula = join ',', @$formula;
$formula = qq("$formula");
}
$formula =~ s/^=//; # Remove formula symbol.
$self->xml_data_element( 'formula1', $formula );
}
##############################################################################
#
# _write_formula_2()
#
# Write the <formula2> element.
#
sub _write_formula_2 {
my $self = shift;
my $formula = shift;
$formula =~ s/^=//; # Remove formula symbol.
$self->xml_data_element( 'formula2', $formula );
}
##############################################################################
#
# _write_conditional_formats()
#
# Write the Worksheet conditional formats.
#
sub _write_conditional_formats {
my $self = shift;
my @ranges = sort keys %{ $self->{_cond_formats} };
return unless scalar @ranges;
for my $range ( @ranges ) {
$self->_write_conditional_formatting( $range,
$self->{_cond_formats}->{$range} );
}
}
##############################################################################
#
# _write_conditional_formatting()
#
# Write the <conditionalFormatting> element.
#
sub _write_conditional_formatting {
my $self = shift;
my $range = shift;
my $params = shift;
my @attributes = ( 'sqref' => $range );
$self->xml_start_tag( 'conditionalFormatting', @attributes );
for my $param ( @$params ) {
# Write the cfRule element.
$self->_write_cf_rule( $param );
}
$self->xml_end_tag( 'conditionalFormatting' );
}
##############################################################################
#
# _write_cf_rule()
#
# Write the <cfRule> element.
#
sub _write_cf_rule {
my $self = shift;
my $param = shift;
my @attributes = ( 'type' => $param->{type} );
push @attributes, ( 'dxfId' => $param->{format} )
if defined $param->{format};
push @attributes, ( 'priority' => $param->{priority} );
push @attributes, ( 'stopIfTrue' => 1 )
if $param->{stop_if_true};
if ( $param->{type} eq 'cellIs' ) {
push @attributes, ( 'operator' => $param->{criteria} );
$self->xml_start_tag( 'cfRule', @attributes );
if ( defined $param->{minimum} && defined $param->{maximum} ) {
$self->_write_formula( $param->{minimum} );
$self->_write_formula( $param->{maximum} );
}
else {
my $value = $param->{value};
# String "Cell" values must be quoted, apart from ranges.
if ( $value !~ /^(\$?)([A-Z]{1,3})(\$?)(\d+)$/
&& $value !~
/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/ )
{
if ( $value !~ /^".*"$/ ) {
$value = qq("$value");
}
}
$self->_write_formula( $value );
}
$self->xml_end_tag( 'cfRule' );
}
elsif ( $param->{type} eq 'aboveAverage' ) {
if ( $param->{criteria} =~ /below/ ) {
push @attributes, ( 'aboveAverage' => 0 );
}
if ( $param->{criteria} =~ /equal/ ) {
push @attributes, ( 'equalAverage' => 1 );
}
if ( $param->{criteria} =~ /([123]) std dev/ ) {
push @attributes, ( 'stdDev' => $1 );
}
$self->xml_empty_tag( 'cfRule', @attributes );
}
elsif ( $param->{type} eq 'top10' ) {
if ( defined $param->{criteria} && $param->{criteria} eq '%' ) {
push @attributes, ( 'percent' => 1 );
}
if ( $param->{direction} ) {
push @attributes, ( 'bottom' => 1 );
}
my $rank = $param->{value} || 10;
push @attributes, ( 'rank' => $rank );
$self->xml_empty_tag( 'cfRule', @attributes );
}
elsif ( $param->{type} eq 'duplicateValues' ) {
$self->xml_empty_tag( 'cfRule', @attributes );
}
elsif ( $param->{type} eq 'uniqueValues' ) {
$self->xml_empty_tag( 'cfRule', @attributes );
}
elsif ($param->{type} eq 'containsText'
|| $param->{type} eq 'notContainsText'
|| $param->{type} eq 'beginsWith'
|| $param->{type} eq 'endsWith' )
{
push @attributes, ( 'operator' => $param->{criteria} );
push @attributes, ( 'text' => $param->{value} );
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_formula( $param->{formula} );
$self->xml_end_tag( 'cfRule' );
}
elsif ( $param->{type} eq 'timePeriod' ) {
push @attributes, ( 'timePeriod' => $param->{criteria} );
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_formula( $param->{formula} );
$self->xml_end_tag( 'cfRule' );
}
elsif ($param->{type} eq 'containsBlanks'
|| $param->{type} eq 'notContainsBlanks'
|| $param->{type} eq 'containsErrors'
|| $param->{type} eq 'notContainsErrors' )
{
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_formula( $param->{formula} );
$self->xml_end_tag( 'cfRule' );
}
elsif ( $param->{type} eq 'colorScale' ) {
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_color_scale( $param );
$self->xml_end_tag( 'cfRule' );
}
elsif ( $param->{type} eq 'dataBar' ) {
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_data_bar( $param );
if ($param->{_is_data_bar_2010}) {
$self->_write_data_bar_ext( $param );
}
$self->xml_end_tag( 'cfRule' );
}
elsif ( $param->{type} eq 'expression' ) {
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_formula( $param->{criteria} );
$self->xml_end_tag( 'cfRule' );
}
elsif ( $param->{type} eq 'iconSet' ) {
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_icon_set( $param );
$self->xml_end_tag( 'cfRule' );
}
}
##############################################################################
#
# _write_icon_set()
#
# Write the <iconSet> element.
#
sub _write_icon_set {
my $self = shift;
my $param = shift;
my $icon_style = $param->{icon_style};
my $total_icons = $param->{total_icons};
my $icons = $param->{icons};
my $i;
my @attributes = ();
# Don't set attribute for default style.
if ( $icon_style ne '3TrafficLights' ) {
@attributes = ( 'iconSet' => $icon_style );
}
if ( exists $param->{'icons_only'} && $param->{'icons_only'} ) {
push @attributes, ( 'showValue' => 0 );
}
if ( exists $param->{'reverse_icons'} && $param->{'reverse_icons'} ) {
push @attributes, ( 'reverse' => 1 );
}
$self->xml_start_tag( 'iconSet', @attributes );
# Write the properties for different icon styles.
for my $icon ( reverse @{ $param->{icons} } ) {
$self->_write_cfvo(
$icon->{'type'},
$icon->{'value'},
$icon->{'criteria'}
);
}
$self->xml_end_tag( 'iconSet' );
}
##############################################################################
#
# _write_formula()
#
# Write the <formula> element.
#
sub _write_formula {
my $self = shift;
my $data = shift;
# Remove equality from formula.
$data =~ s/^=//;
$self->xml_data_element( 'formula', $data );
}
##############################################################################
#
# _write_color_scale()
#
# Write the <colorScale> element.
#
sub _write_color_scale {
my $self = shift;
my $param = shift;
$self->xml_start_tag( 'colorScale' );
$self->_write_cfvo( $param->{min_type}, $param->{min_value} );
if ( defined $param->{mid_type} ) {
$self->_write_cfvo( $param->{mid_type}, $param->{mid_value} );
}
$self->_write_cfvo( $param->{max_type}, $param->{max_value} );
$self->_write_color( 'rgb' => $param->{min_color} );
if ( defined $param->{mid_color} ) {
$self->_write_color( 'rgb' => $param->{mid_color} );
}
$self->_write_color( 'rgb' => $param->{max_color} );
$self->xml_end_tag( 'colorScale' );
}
##############################################################################
#
# _write_data_bar()
#
# Write the <dataBar> element.
#
sub _write_data_bar {
my $self = shift;
my $data_bar = shift;
my @attributes = ();
if ( $data_bar->{bar_only} ) {
push @attributes, ( 'showValue', 0 );
}
$self->xml_start_tag( 'dataBar', @attributes );
$self->_write_cfvo( $data_bar->{min_type}, $data_bar->{min_value} );
$self->_write_cfvo( $data_bar->{max_type}, $data_bar->{max_value} );
$self->_write_color( 'rgb' => $data_bar->{bar_color} );
$self->xml_end_tag( 'dataBar' );
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
$self->_write_spark_color( 'x14:colorLast', @_ );
}
##############################################################################
#
# _write_color_high()
#
# Write the <x14:colorHigh> element.
#
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>
( run in 0.607 second using v1.01-cache-2.11-cpan-39bf76dae61 )