Spreadsheet::Engine::Sheet - Spreadsheet basics
parse_sheet_save(\@lines, \my %sheetdata); my $outstr = create_sheet_save(\%sheetdata); add_to_editlog(\%headerdata, $str); parse_header_save(\@lines, my \%headerdata); my $outstr = create_header_save(\%headerdata); execute_sheet_command($sheetdata, $command); recalc_sheet(\%sheetdata);
This is a motley bunch of functions for dealing with a spreadsheet file and/or data structure. If you plan to use any of these directly, be aware that they may move, vanish, or have significant interface changes in future releases.
parse_sheet_save(\@lines, \my %sheetdata);
Sheet input routine. Fills %sheetdata given lines of text @lines.
Currently always returns nothing.
Sheet save format:
linetype:param1:param2:...
Linetypes are:
version:versionname - version of this format. Currently 1.3. cell:coord:type:value...:type:value... - Types are as follows: v:value - straight numeric value t:value - straight text/wiki-text in cell, encoded to handle \, :, newlines vt:fulltype:value - value with value type/subtype vtf:fulltype:value:formulatext - formula resulting in value with value type/subtype, value and text encoded vtc:fulltype:value:valuetext - formatted text constant resulting in value with value type/subtype, value and text encoded vf:fvalue:formulatext - formula resulting in value, value and text encoded (obsolete: only pre format version 1.1) fvalue - first char is "N" for numeric value, "T" for text value, "H" for HTML value, rest is the value e:errortext - Error text. Non-blank means formula parsing/calculation results in error. b:topborder#:rightborder#:bottomborder#:leftborder# - border# in sheet border list or blank if none l:layout# - number in cell layout list f:font# - number in sheet fonts list c:color# - sheet color list index for text bg:color# - sheet color list index for background color cf:format# - sheet cell format number for explicit format (align:left, etc.) cvf:valueformat# - sheet cell value format number (obsolete: only pre format v1.2) tvf:valueformat# - sheet cell text value format number ntvf:valueformat# - sheet cell non-text value format number colspan:numcols - number of columns spanned in merged cell rowspan:numrows - number of rows spanned in merged cell cssc:classname - name of CSS class to be used for cell when published instead of one calculated here csss:styletext - explicit CSS style information, encoded to handle :, etc. mod:allow - if "y" allow modification of cell for live "view" recalc col: w:widthval - number, "auto" (no width in <col> tag), number%, or blank (use default) hide: - yes/no, no is assumed if missing row: hide - yes/no, no is assumed if missing sheet: c:lastcol - number r:lastrow - number w:defaultcolwidth - number, "auto", number%, or blank (default->80) h:defaultrowheight - not used tf:format# - cell format number for sheet default for text values ntf:format# - cell format number for sheet default for non-text values (i.e., numbers) layout:layout# - default cell layout number in cell layout list font:font# - default font number in sheet font list vf:valueformat# - default number value format number in sheet valueformat list (obsolete: only pre format version 1.2) ntvf:valueformat# - default non-text (number) value format number in sheet valueformat list tvf:valueformat# - default text value format number in sheet valueformat list color:color# - default number for text color in sheet color list bgcolor:color# - default number for background color in sheet color list circularreferencecell:coord - cell coord with a circular reference recalc:value - on/off (on is default). If "on", appropriate changes to the sheet cause a recalc needsrecalc:value - yes/no (no is default). If "yes", formula values are not up to date name:name:description:value - name definition, name in uppercase, with value being "B5", "A1:B7", or "=formula" font:fontnum:value - text of font definition (style weight size family) for font fontnum "*" for "style weight", size, or family, means use default (first look to sheet, then builtin) color:colornum:rgbvalue - text of color definition (e.g., rgb(255,255,255)) for color colornum border:bordernum:value - text of border definition (thickness style color) for border bordernum layout:layoutnum:value - text of vertical alignment and padding style for cell layout layoutnum: vertical-alignment:vavalue;padding topval rightval bottomval leftval; cellformat:cformatnum:value - text of cell alignment (left/center/right) for cellformat cformatnum valueformat:vformatnum:value - text of number format (see format_value_for_display) for valueformat vformatnum (changed in v1.2) clipboardrange:upperleftcoord:bottomrightcoord - origin of clipboard data. Not present if clipboard empty. There must be a clipboardrange before any clipboard lines clipboard:coord:type:value:... - clipboard data, in same format as cell data
The resulting $sheetdata data structure is as follows:
$sheetdata{version} - version of save file read in $sheetdata{datatypes}->{$coord} - Origin of {datavalues} value: v - typed in numeric value of some sort, constant, no formula t - typed in text, constant, no formula f - result of formula calculation ({formulas} has formula to calculate) c - constant of some sort with typed in text in {formulas} and value in {datavalues} $sheetdata{formulas}->{$coord} - Text of formula if {datatypes} is "f", no leading "=", or text of constant if "c" $sheetdata{datavalues}->{$coord} - a text or numeric value ready to be formatted for display or used in calculation $sheetdata{valuetypes}->{$coord} - the value type of the datavalue as 1 or more characters First char is "n" for numeric or "t" for text Second chars, if present, are sub-type, like "l" for logical (0=false, 1=true) $sheetdata{cellerrors}->{$coord} - If non-blank, error text for error in formula calculation $sheetdata{cellattribs}->{$coord}-> {coord} - coord of cell - existence means non-blank cell {bt}, {br}, {bb}, {bl} - border number or null if no border {layout} - cell layout number or blank for default {font} - font number or blank for default {color} - color number for text or blank for default {bgcolor} - color number for the cell background or blank for default {cellformat} - cell format number if not default - controls horizontal alignment {textvalueformat} - value format number if not default - controls how the cell's text values are formatted into text for display {nontextvalueformat} - value format number if not default - controls how the cell's non-text values are turned into text for display {colspan}, {rowspan} - column span and row span for merged cells or blank for 1 {cssc}, {csss} - explicit CSS class and CSS style for cell {mod} - if "y" allow modification in live view $sheetdata{colattribs}->{$colcoord}-> {width} - column width if not default {hide} - hide column if yes $sheetdata{rowattribs}->{$rowcoord}-> {height} - ignored {hide} - hide row if yes $sheetdata{sheetattribs}->{$attrib}-> {lastcol} - number of columns in sheet {lastrow} - number of rows in sheet (more may be displayed when editing) {defaultcolwidth} - number, "auto", number%, or blank (default->80) {defaultrowheight} - not used {defaulttextformat} - cell format number for sheet default for text values {defaultnontextformat} - cell format number for sheet default for non-text values (i.e., numbers) {defaultlayout} - default cell layout number in sheet cell layout list {defaultfont} - default font number in sheet font list {defaulttextvalueformat} - default text value format number in sheet valueformat list {defaultnontextvalueformat} - default number value format number in sheet valueformat list {defaultcolor} - default number for text color in sheet color list {defaultbgcolor} - default number for background color in sheet color list {circularreferencecell} - cell coord with a circular reference {recalc} - on/off (on is default). If "on", appropriate changes to the sheet cause a recalc {needsrecalc} - yes/no (no is default). If "yes", formula values are not up to date $sheetdata{names}->{$name}-> - name is uppercase {desc} - description (optional) {definiton} - in the form of B5, A1:B7, or =formula $sheetdata{fonts}->[$index] - font specifications addressable by array position $sheetdata{fonthash}->{$value} - hash with font specification as keys and {fonts}->[] index position as values $sheetdata{colors}->[$index] - color specifications addressable by array position $sheetdata{colorhash}->{$value} - hash with color specification as keys and {colors}->[] index position as values $sheetdata{borderstyles}->[$index] - border style specifications addressable by array position $sheetdata{borderstylehash}->{$value} - hash with border style specification as keys and {borderstyles}->[] index position as values $sheetdata{layoutstyles}->[$index] - cell layout specifications addressable by array position $sheetdata{layoutstylehash}->{$value} - hash with cell layout specification as keys and {layoutstyle}->[] index position as values $sheetdata{cellformats}->[$index] - cell format specifications addressable by array position $sheetdata{cellformathash}->{$value} - hash with cell format specification as keys and {cellformats}->[] index position as values $sheetdata{valueformats}->[$index] - value format specifications addressable by array position $sheetdata{valueformathash}->{$value} - hash with value format specification as keys and {valueformats}->[] index position as values $sheetdata{clipboard}-> - the sheet's clipboard {range} - coord:coord range of where the clipboard contents came from or null if empty {datavalues} - like $sheetdata{datavalues} but for clipboard copy of cells {datatypes} - like $sheetdata{datatypes} but for clipboard copy of cells {valuetypes} - like $sheetdata{valuetypes} but for clipboard copy of cells {formulas} - like $sheetdata{formulas} but for clipboard copy of cells {cellerrors} - like $sheetdata{cellerrors} but for clipboard copy of cells {cellattribs} - like $sheetdata{cellattribs} but for clipboard copy of cells $sheetdata{loaderror} - if non-blank, there was an error loading this sheet and this is the text of that error
my $outstr = create_sheet_save(\%sheetdata)
Sheet output routine. Returns a string ready to be saved in a file.
$ok = execute_sheet_command($sheetdata, $command);
Executes commands that modify the sheet data. Sets sheet "needsrecalc" as needed.
The commands are in the forms:
set sheet attributename value (plus lastcol and lastrow) set 22 attributename value set B attributename value set A1 attributename value1 value2... (see each attribute below for details) set A1:B5 attributename value1 value2... erase/copy/cut/paste/fillright/filldown A1:B5 all/formulas/format clearclipboard merge C3:F3 unmerge C3 insertcol/insertrow C5 deletecol/deleterow C5:E7 name define NAME definition name desc NAME description name delete NAME
recalc_sheet(\%sheetdata);
Recalculates the entire spreadsheet
parse_header_save(\@lines, my \%headerdata);
Returns "" if OK, otherwise error string.
Fills in %headerdata:
$headerdata{version} - version number, currently 1.1 $headerdata{fullname} - title of page $headerdata{templatetext} - template HTML $headerdata{templatefile} - where to get template (location:name), see get_template $headerdata{lastmodified} - date/time last modified $headerdata{lastauthor} - author when last modified $headerdata{basefiledt} - date/time of backup file before this set of edits or blank if new file first edits (survives rename) $headerdata{backupfiledt} - date/time of backup file holding this data (blank during edits, yyyy-mm-... in published/backup/archive) $headerdata{reverted} - if non-blank, name of backup file this came from (only during initial editing) $headerdata{editcomments} - comment text about this series of edits, used when listing backups and RSS $headerdata{publishhtml} - publish the HTML for this page - sometimes you only want access-controlled live view (yes/no - default yes) $headerdata{publishsource} - put a copy of the published .txt file along with HTML and allow live view of source (yes/no - default no) $headerdata{publishjs} - put an embeddable copy of the published HTML as a .js file along with HTML (yes/no - default no) $headerdata{publishlive} - (ignored and removed after 0.91) make the HTML be a redirect to the recalc code (yes/no - default no) $headerdata{viewwithoutlogin} - allow live view without being logged in (ignore login for this page) $headerdata{editlog} - array of entries about edits made since editing started (cleared on new open for edit) [0] - log entry: command string to execute_sheet_command or comment (starts with "# ")
my $outstr = create_header_save(\%headerdata);
Header output routine
add_to_editlog(\%headerdata, $str);
Adds $str to the header editlog. This should be either a string acceptable to execute_sheet_command or start with "# "
These are currently exported, as they are used from multiple places. You shouldn't rely on this, however, as they will likely move somewhere else RSN.
$juliandate = convert_date_gregorian_to_julian($year, $month, $day);
From: http://aa.usno.navy.mil/faq/docs/JD_Formula.html
Uses: Fliegel, H. F. and van Flandern, T. C. (1968). Communications of the ACM, Vol. 11, No. 10 (October, 1968). Translated from the FORTRAN.
($year, $month, $day) = convert_date_julian_to_gregorian($juliandate)
$value = determine_value_type($rawvalue, \$type)
Takes a value and looks for special formatting like $, %, numbers, etc. Returns the value as a number or string and the type. Tries to follow the spec for spreadsheet function VALUE(v).
test_criteria($value, $type, $criteria);
Determines whether a value/type meets the criteria. A criteria can be a numeric value, text beginning with <, <=, =, >=, >, <>, text by itself is start of text to match.
Returns 1 or 0 for true or false.
$resulttype = lookup_result_type($type1, $type2, \%typelookup);
%typelookup has values of the following form:
$typelookup{"typespec1"} = "|typespec2A:resultA|typespec2B:resultB|..."
First $type1 is looked up. If no match, then the first letter (major type) of $type1 plus "*" is looked up. $resulttype is $type1 if result is "1", $type2 if result is "2", otherwise the value of result.
copy_function_args(\@operand, \@foperand)
Pops operands from @operand and pushes on @foperand up to function start reversing order in the process.
function_args_error($fname, \@operand, $$errortext)
Pushes appropriate error on operand stack and sets errortext, including $fname
function_specific_error($fname, \@operand, $errortext, $errortype, $text)
Pushes specified error and text on operand stack
($value, $type) = top_of_stack_value_and_type(\%sheetdata, \@operand, \$errortext,)
Returns top of stack value and type and then pops the stack
$value = operand_as_number(\%sheetdata, \@operand, \$errortext, \$tostype)
Uses operand_value_and_type to get top of stack and pops it. Returns numeric value and type. Text values are treated as 0 if they can't be converted somehow.
$value = operand_as_text(\%sheetdata, \@operand, \$errortext, \$tostype)
Uses operand_value_and_type to get top of stack and pops it. Returns text value, preserving sub-type.
$value = operand_value_and_type(\%sheetdata, \@operand, \$errortext, \$operandtype)
Pops the top of stack and returns it, following a coord reference if necessary. Ranges are returned as if they were pushed onto the stack first coord first. Also sets $operandtype with "t", "n", "th", etc., as appropriate. Errortext is set if there is a reference to a cell with error.
($sheetdata, $col1num, $ncols, $row1num, $nrows) = decode_range_parts(\@sheetdata, $rangevalue, $rangetype)
Returns \@sheetdata for the sheet where the range is, as well as the number of the first column in the range, the number of columns, and equivalent row information.
If any errors, $sheetdata is returned as null.
($col, $row) = coord_to_cr($coord)
Turns B3 into (2, 3). The default for both is 1. If range, only do this to first coord.
$coord = cr_to_coord($col, $row)
Turns (2, 3) into B3. The default for both is 1.
my $estring = encode_for_save($string)
Returns $estring where :, \n, and \ are escaped
my $estring = decode_from_save($string)
Returns $estring with \c, \n, \b and \\ un-escaped
my $estring = html_escape($string)
Returns $estring where &, <, >, " are HTML escaped.
This used to be known as special_chars() but that usage is deprecated.
my $estring = special_chars_nl($string)
Returns $estring where &, <, >, ", and LF are HTML escaped, CR's are removed
These are 'private' functions, not exported, and should not be relied on. The interface to any of these is subject to change at any time.
$updatedformula = offset_formula_coords($formula, $coloffset, $rowoffset);
Change relative cell references by offsets (even those to other worksheets so fill, paste, sort work as expected). If not what you want, use absolute references.
$updatedformula = adjust_formula_coords($formula, $col, $coloffset, $row, $rowoffset)
Change all cell references to cells starting with $col/$row by offsets
$displayvalue = format_value_for_display(\%sheetdata, $value, $cr, $linkstyle)
$displayvalue = format_text_for_display($rawvalue, $valuetype, $valueformat, $sheetdata, $linkstyle)
$displayvalue = format_number_for_display($rawvalue, $valuetype, $valueformat)
$result = format_number_with_format_string($value, $format_string, $currency_char)
Use a format string to format a numeric value. Returns a string with the result. This is a subset of the normal styles accepted by many other spreadsheets, without fractions, E format, and @, and with any number of comparison fields and with [style=style-specification] (e.g., [style=color:red])
parse_format_string(\%format_defs, $format_string)
Takes a format string (e.g., "#,##0.00_);(#,##0.00)") and fills in %foramt_defs with the parsed info
%format_defs {"#,##0.0"}->{} # elements in the hash are one hash for each format {operators}->[] # array of operators from parsing the format string (each a number) {operands}->[] # array of corresponding operators (each usually a string) {sectioninfo}->[] # one hash for each section of the format {start} {integerdigits} {fractiondigits} {commas} {percent} {thousandssep} {hasdates} {hascomparison} # true if any section has [<100], etc.
($operator, $operand) = parse_format_bracket($bracketstr)
$circref = check_and_calc_cell(\%sheetdata, $coord)
Recalculates one cell after making sure dependencies are calc'ed, too. If circular reference, returns non-null. Circular referenced detected by using $sheetdata->{checked}->{$coord}: null - not evaluated 1 - cell is being recursed into -- if get back here => circular reference 2 - cell was fully recursed into and calculated this recalc cycle
\%parseinfo = parse_formula_into_tokens($line)
Parses a text string as if it was a spreadsheet formula
This uses a simple state machine run on each character in turn. States remember whether a number is being gathered, etc. The result is %parseinfo which has the following arrays with one entry for each token: {tokentext}->[] - the characters making up the parsed token, {tokentype}->[] - the type of the token, {tokenopcode}->[] - a single character version of an operator suitable for use in the precedence table and distinguishing between unary and binary + and -.
($value, $valuetype, $errortext) = evaluate_parsed_formula(\%parseinfo, \%sheetdata, $allowrangereturn)
Does the calculation expressed in a parsed formula, returning a value, its type, and error info.
If $allowrangereturn is present and true, can return a range (e.g., "A1:A10" - translated from "A1|A10|")
$value = operand_as_coord(\%sheetdata, \@operand, \$errortext)
Gets top of stack and pops it. Returns coord value. All others are treated as an error.
$value = operands_as_coord_on_sheet(\%sheetdata, \@operand, \$returntype, \$errortext)
Gets 2 at top of stack and pops them, treating them as sheetname!coord-or-name. Returns stack-style coord value (coord!sheetname, or coord!sheetname|coord|) and sets $returntype to coord or range. All others are treated as an error.
$value = operands_as_range_on_sheet(\%sheetdata, \@operand, \$returntype, \$errortext)
Gets 2 at top of stack and pops them, treating them as coord2-or-name:coord1. Name is evaluated on sheet of coord1. Returns stack-style range value (coord!sheetname|coord|) and sets $returntype to range. All others are treated as an error.
$value = operand_as_sheetname(\%sheetdata, \@operand, \$errortext)
Gets top of stack and pops it. Returns sheetname value. All others are treated as an error. Accepts text, cell reference, and named value which is one of those two.
$value = lookup_name(\%sheetdata, $name, \$valuetype, \$errortext)
Returns value and type of a named value. Names are case insensitive. Names may have a definition which is a coord (A1), a range (A1:B7), or a formula (=OFFSET(A1,0,0,5,1))
$value = step_through_range_up(\@operand, $rangevalue, \$operandtype)
Returns next coord in a range, keeping track on the operand stack. Goes from bottom right across and up to upper left.
$value = step_through_range_down(\@operand, $rangevalue, \$operandtype)
Returns next coord in a range, keeping track on the operand stack. Goes from upper left across and down to bottom right.
$col = col_to_number($colname)
Turns B into 2. The default is 1.
$coord = number_to_col($col)
Turns 2 into B. The default is 1.
my $estring = special_chars_markup($string)
Returns $estring where &, <, >, " are HTML escaped ready for expand markup
my $estring = url_encode($string)
Returns $estring with special chars URL encoded.
Based on Mastering Regular Expressions, Jeffrey E. F. Friedl, additional legal characters added
my $estring = url_encode_plain($string)
Returns $estring with special chars URL encoded for sending to others by HTTP, not publishing.
Based on Mastering Regular Expressions, Jeffrey E. F. Friedl, additional legal characters added.
my $othersheet_sheetdata = find_in_sheet_cache(\%sheetdata, $datafilename)
Load additional sheet's information for worksheet references as a sheetdata structure stored in $sheetdata->{sheetcache}->{sheets}->{$datafilename} if necessary. Return that structure as \%othersheet_sheetdata
Modified version of SocialCalc::Sheet from SocialCalc 1.1.0
Portions (c) Copyright 2005, 2006, 2007 Software Garden, Inc. All Rights Reserved.
Portions (c) Copyright 2007 Socialtext, Inc. All Rights Reserved.
Portions (c) Copyright 2007, 2008 Tony Bowden
The contents of this file are subject to the Artistic License 2.0; you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.perlfoundation.org/artistic_license_2_0
To install Spreadsheet::Engine, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Spreadsheet::Engine
CPAN shell
perl -MCPAN -e shell install Spreadsheet::Engine
For more information on module installation, please visit the detailed CPAN module installation guide.