~pvigo/+junk/owncloud-14.04

} elseif (preg_match("/^" . self::REGEX_SHEET_TITLE_UNQUOTED . "(\:" . self::REGEX_SHEET_TITLE_UNQUOTED . ")?\!\\$?[A-Ia-i]?[A-Za-z]\\$?(\d+)$/u",$token)) {

529

return $this->_convertRef3d($token);

530

531

// match external references like 'Sheet1'!A1 or 'Sheet1:Sheet2'!A1 or 'Sheet1'!$A$1 or 'Sheet1:Sheet2'!$A$1

532

} elseif (preg_match("/^'" . self::REGEX_SHEET_TITLE_QUOTED . "(\:" . self::REGEX_SHEET_TITLE_QUOTED . ")?'\!\\$?[A-Ia-i]?[A-Za-z]\\$?(\d+)$/u",$token)) {

533

return $this->_convertRef3d($token);

534

535

// match ranges like A1:B2 or $A$1:$B$2

536

} elseif (preg_match('/^(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)\:(\$)?[A-Ia-i]?[A-Za-z](\$)?(\d+)$/', $token)) {

537

return $this->_convertRange2d($token);

538

539

// match external ranges like Sheet1!A1:B2 or Sheet1:Sheet2!A1:B2 or Sheet1!$A$1:$B$2 or Sheet1:Sheet2!$A$1:$B$2

540

} elseif (preg_match("/^" . self::REGEX_SHEET_TITLE_UNQUOTED . "(\:" . self::REGEX_SHEET_TITLE_UNQUOTED . ")?\!\\$?([A-Ia-i]?[A-Za-z])?\\$?(\d+)\:\\$?([A-Ia-i]?[A-Za-z])?\\$?(\d+)$/u",$token)) {

541

return $this->_convertRange3d($token);

542

543

// match external ranges like 'Sheet1'!A1:B2 or 'Sheet1:Sheet2'!A1:B2 or 'Sheet1'!$A$1:$B$2 or 'Sheet1:Sheet2'!$A$1:$B$2

544

} elseif (preg_match("/^'" . self::REGEX_SHEET_TITLE_QUOTED . "(\:" . self::REGEX_SHEET_TITLE_QUOTED . ")?'\!\\$?([A-Ia-i]?[A-Za-z])?\\$?(\d+)\:\\$?([A-Ia-i]?[A-Za-z])?\\$?(\d+)$/u",$token)) {

545

return $this->_convertRange3d($token);

546

547

// operators (including parentheses)

548

} elseif (isset($this->ptg[$token])) {

549

return pack("C", $this->ptg[$token]);

550

551

// match error codes

552

} elseif (preg_match("/^#[A-Z0\/]{3,5}[!?]{1}$/", $token) or $token == '#N/A') {

553

return $this->_convertError($token);

554

555

// commented so argument number can be processed correctly. See toReversePolish().

556

/*elseif (preg_match("/[A-Z0-9\xc0-\xdc\.]+/",$token))

557

{

558

return($this->_convertFunction($token,$this->_func_args));

559

}*/

560

561

// if it's an argument, ignore the token (the argument remains)

562

} elseif ($token == 'arg') {

563

return '';

564

}

565

566

// TODO: use real error codes

567

throw new PHPExcel_Writer_Exception("Unknown token $token");

568

}

569

570

/**

571

* Convert a number token to ptgInt or ptgNum

572

573

* @access private

574

* @param mixed $num an integer or double for conversion to its ptg value

575

576

function _convertNumber($num)

577

{

578

// Integer in the range 0..2**16-1

579

if ((preg_match("/^\d+$/", $num)) and ($num <= 65535)) {

580

return pack("Cv", $this->ptg['ptgInt'], $num);

581

} else { // A float

582

if (PHPExcel_Writer_Excel5_BIFFwriter::getByteOrder()) { // if it's Big Endian

583

$num = strrev($num);

584

}

585

return pack("Cd", $this->ptg['ptgNum'], $num);

586

}

587

}

588

589

/**

590

* Convert a string token to ptgStr

591

592

* @access private

593

* @param string $string A string for conversion to its ptg value.

594

* @return mixed the converted token on success

595

596

function _convertString($string)

597

{

598

// chop away beggining and ending quotes

599

$string = substr($string, 1, strlen($string) - 2);

600

if (strlen($string) > 255) {

601

throw new PHPExcel_Writer_Exception("String is too long");

602

}

603

604

return pack('C', $this->ptg['ptgStr']) . PHPExcel_Shared_String::UTF8toBIFF8UnicodeShort($string);

605

}

606

607

/**

608

* Convert a function to a ptgFunc or ptgFuncVarV depending on the number of

609

* args that it takes.

610

611

* @access private

612

* @param string $token The name of the function for convertion to ptg value.

613

* @param integer $num_args The number of arguments the function receives.

614

* @return string The packed ptg for the function

615

616

function _convertFunction($token, $num_args)

617

{

618

$args = $this->_functions[$token][1];

619

// $volatile = $this->_functions[$token][3];

620

621

// Fixed number of args eg. TIME($i,$j,$k).

622

if ($args >= 0) {

623

return pack("Cv", $this->ptg['ptgFuncV'], $this->_functions[$token][0]);

624

}

625

// Variable number of args eg. SUM($i,$j,$k, ..).

626

if ($args == -1) {

627

return pack("CCv", $this->ptg['ptgFuncVarV'], $num_args, $this->_functions[$token][0]);

628

}

629

}

630

631

/**

632

* Convert an Excel range such as A1:D4 to a ptgRefV.

633

634

* @access private

635

* @param string $range An Excel range in the A1:A2

636

* @param int $class

637

638

function _convertRange2d($range, $class=0)

639

{

640

641

// TODO: possible class value 0,1,2 check Formula.pm

642

// Split the range into 2 cell refs

643

if (preg_match('/^(\$)?([A-Ia-i]?[A-Za-z])(\$)?(\d+)\:(\$)?([A-Ia-i]?[A-Za-z])(\$)?(\d+)$/', $range)) {

644

list($cell1, $cell2) = explode(':', $range);

645

} else {

646

// TODO: use real error codes

647

throw new PHPExcel_Writer_Exception("Unknown range separator");

648

}

649

650

// Convert the cell references

651

list($row1, $col1) = $this->_cellToPackedRowcol($cell1);

652

list($row2, $col2) = $this->_cellToPackedRowcol($cell2);

653

654

// The ptg value depends on the class of the ptg.

655

if ($class == 0) {

656

$ptgArea = pack("C", $this->ptg['ptgArea']);

657

} elseif ($class == 1) {

658

$ptgArea = pack("C", $this->ptg['ptgAreaV']);

659

} elseif ($class == 2) {

660

$ptgArea = pack("C", $this->ptg['ptgAreaA']);

661

} else {

662

// TODO: use real error codes

663

throw new PHPExcel_Writer_Exception("Unknown class $class");

664

}

665

return $ptgArea . $row1 . $row2 . $col1. $col2;

666

}

667

668

/**

669

* Convert an Excel 3d range such as "Sheet1!A1:D4" or "Sheet1:Sheet2!A1:D4" to

670

* a ptgArea3d.

671

672

* @access private

673

* @param string $token An Excel range in the Sheet1!A1:A2 format.

674

* @return mixed The packed ptgArea3d token on success.

675

676

function _convertRange3d($token)

677

{

678

// $class = 0; // formulas like Sheet1!$A$1:$A$2 in list type data validation need this class (0x3B)

679

680

// Split the ref at the ! symbol

681

list($ext_ref, $range) = explode('!', $token);

682

683

// Convert the external reference part (different for BIFF8)

684

$ext_ref = $this->_getRefIndex($ext_ref);

685

686

// Split the range into 2 cell refs

687

list($cell1, $cell2) = explode(':', $range);

688

689

// Convert the cell references

690

if (preg_match("/^(\\$)?[A-Ia-i]?[A-Za-z](\\$)?(\d+)$/", $cell1)) {

691

list($row1, $col1) = $this->_cellToPackedRowcol($cell1);

692

list($row2, $col2) = $this->_cellToPackedRowcol($cell2);

693

} else { // It's a rows range (like 26:27)

694

list($row1, $col1, $row2, $col2) = $this->_rangeToPackedRange($cell1.':'.$cell2);

695

}

696

697

// The ptg value depends on the class of the ptg.

698

// if ($class == 0) {

699

$ptgArea = pack("C", $this->ptg['ptgArea3d']);

700

// } elseif ($class == 1) {

701

// $ptgArea = pack("C", $this->ptg['ptgArea3dV']);

702

// } elseif ($class == 2) {

703

// $ptgArea = pack("C", $this->ptg['ptgArea3dA']);

704

// } else {

705

// throw new PHPExcel_Writer_Exception("Unknown class $class");

706

// }

707

708

return $ptgArea . $ext_ref . $row1 . $row2 . $col1. $col2;

709

}

710

711

/**

712

* Convert an Excel reference such as A1, $B2, C$3 or $D$4 to a ptgRefV.

713

714

* @access private

715

* @param string $cell An Excel cell reference

716

* @return string The cell in packed() format with the corresponding ptg

717

718

function _convertRef2d($cell)

719

{

720

// $class = 2; // as far as I know, this is magick.

721

722

// Convert the cell reference

723

$cell_array = $this->_cellToPackedRowcol($cell);

724

list($row, $col) = $cell_array;

725

726

// The ptg value depends on the class of the ptg.

727

// if ($class == 0) {

728

// $ptgRef = pack("C", $this->ptg['ptgRef']);

729

// } elseif ($class == 1) {

730

// $ptgRef = pack("C", $this->ptg['ptgRefV']);

731

// } elseif ($class == 2) {

732

$ptgRef = pack("C", $this->ptg['ptgRefA']);

733

// } else {

734

// // TODO: use real error codes

735

// throw new PHPExcel_Writer_Exception("Unknown class $class");

736

// }

737

return $ptgRef.$row.$col;

738

}

739

740

/**

741

* Convert an Excel 3d reference such as "Sheet1!A1" or "Sheet1:Sheet2!A1" to a

742

* ptgRef3d.

743

744

* @access private

745

* @param string $cell An Excel cell reference

746

* @return mixed The packed ptgRef3d token on success.

747

748

function _convertRef3d($cell)

749

{

750

// $class = 2; // as far as I know, this is magick.

751

752

// Split the ref at the ! symbol

753

list($ext_ref, $cell) = explode('!', $cell);

754

755

// Convert the external reference part (different for BIFF8)

756

$ext_ref = $this->_getRefIndex($ext_ref);

757

758

// Convert the cell reference part

759

list($row, $col) = $this->_cellToPackedRowcol($cell);

760

761

// The ptg value depends on the class of the ptg.

762

// if ($class == 0) {

763

// $ptgRef = pack("C", $this->ptg['ptgRef3d']);

764

// } elseif ($class == 1) {

765

// $ptgRef = pack("C", $this->ptg['ptgRef3dV']);

766

// } elseif ($class == 2) {

767

$ptgRef = pack("C", $this->ptg['ptgRef3dA']);

768

// } else {

769

// throw new PHPExcel_Writer_Exception("Unknown class $class");

770

// }

771

772

return $ptgRef . $ext_ref. $row . $col;

773

}

774

775

/**

776

* Convert an error code to a ptgErr

777

778

* @access private

779

* @param string $errorCode The error code for conversion to its ptg value

780

* @return string The error code ptgErr

781

782

function _convertError($errorCode)

783

{

784

switch ($errorCode) {

785

case '#NULL!': return pack("C", 0x00);

786

case '#DIV/0!': return pack("C", 0x07);

787

case '#VALUE!': return pack("C", 0x0F);

788

case '#REF!': return pack("C", 0x17);

789

case '#NAME?': return pack("C", 0x1D);

790

case '#NUM!': return pack("C", 0x24);

791

case '#N/A': return pack("C", 0x2A);

792

}

793

return pack("C", 0xFF);

794

}

795

796

/**

797

* Convert the sheet name part of an external reference, for example "Sheet1" or

798

* "Sheet1:Sheet2", to a packed structure.

799

800

* @access private

801

* @param string $ext_ref The name of the external reference

802

* @return string The reference index in packed() format

803

804

function _packExtRef($ext_ref)

805

{

806

$ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.

807

$ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.

808

809

// Check if there is a sheet range eg., Sheet1:Sheet2.

810

if (preg_match("/:/", $ext_ref)) {

811

list($sheet_name1, $sheet_name2) = explode(':', $ext_ref);

812

813

$sheet1 = $this->_getSheetIndex($sheet_name1);

814

if ($sheet1 == -1) {

815

throw new PHPExcel_Writer_Exception("Unknown sheet name $sheet_name1 in formula");

816

}

817

$sheet2 = $this->_getSheetIndex($sheet_name2);

818

if ($sheet2 == -1) {

819

throw new PHPExcel_Writer_Exception("Unknown sheet name $sheet_name2 in formula");

820

}

821

822

// Reverse max and min sheet numbers if necessary

823

if ($sheet1 > $sheet2) {

824

list($sheet1, $sheet2) = array($sheet2, $sheet1);

825

}

826

} else { // Single sheet name only.

827

$sheet1 = $this->_getSheetIndex($ext_ref);

828

if ($sheet1 == -1) {

829

throw new PHPExcel_Writer_Exception("Unknown sheet name $ext_ref in formula");

830

}

831

$sheet2 = $sheet1;

832

}

833

834

// References are stored relative to 0xFFFF.

835

$offset = -1 - $sheet1;

836

837

return pack('vdvv', $offset, 0x00, $sheet1, $sheet2);

838

}

839

840

/**

841

* Look up the REF index that corresponds to an external sheet name

842

* (or range). If it doesn't exist yet add it to the workbook's references

843

* array. It assumes all sheet names given must exist.

844

845

* @access private

846

* @param string $ext_ref The name of the external reference

847

* @return mixed The reference index in packed() format on success

848

849

function _getRefIndex($ext_ref)

850

{

851

$ext_ref = preg_replace("/^'/", '', $ext_ref); // Remove leading ' if any.

852

$ext_ref = preg_replace("/'$/", '', $ext_ref); // Remove trailing ' if any.

853

$ext_ref = str_replace('\'\'', '\'', $ext_ref); // Replace escaped '' with '

854

855

// Check if there is a sheet range eg., Sheet1:Sheet2.

856

if (preg_match("/:/", $ext_ref)) {

857

list($sheet_name1, $sheet_name2) = explode(':', $ext_ref);

858

859

$sheet1 = $this->_getSheetIndex($sheet_name1);

860

if ($sheet1 == -1) {

861

throw new PHPExcel_Writer_Exception("Unknown sheet name $sheet_name1 in formula");

862

}

863

$sheet2 = $this->_getSheetIndex($sheet_name2);

864

if ($sheet2 == -1) {

865

throw new PHPExcel_Writer_Exception("Unknown sheet name $sheet_name2 in formula");

866

}

867

868

// Reverse max and min sheet numbers if necessary

869

if ($sheet1 > $sheet2) {

870

list($sheet1, $sheet2) = array($sheet2, $sheet1);

871

}

872

} else { // Single sheet name only.

873

$sheet1 = $this->_getSheetIndex($ext_ref);

874

if ($sheet1 == -1) {

875

throw new PHPExcel_Writer_Exception("Unknown sheet name $ext_ref in formula");

876

}

877

$sheet2 = $sheet1;

878

}

879

880

// assume all references belong to this document

881

$supbook_index = 0x00;

882

$ref = pack('vvv', $supbook_index, $sheet1, $sheet2);

883

$total_references = count($this->_references);

884

$index = -1;

885

for ($i = 0; $i < $total_references; ++$i) {

886

if ($ref == $this->_references[$i]) {

887

$index = $i;

888

break;

889

}

890

}

891

// if REF was not found add it to references array

892

if ($index == -1) {

893

$this->_references[$total_references] = $ref;

894

$index = $total_references;

895

}

896

897

return pack('v', $index);

898

}

899

900

/**

901

* Look up the index that corresponds to an external sheet name. The hash of

902

* sheet names is updated by the addworksheet() method of the

903

* PHPExcel_Writer_Excel5_Workbook class.

904

905

* @access private

906

* @param string $sheet_name Sheet name

907

* @return integer The sheet index, -1 if the sheet was not found

908

909

function _getSheetIndex($sheet_name)

910

{

911

if (!isset($this->_ext_sheets[$sheet_name])) {

912

return -1;

913

} else {

914

return $this->_ext_sheets[$sheet_name];

915

}

916

}

917

918

/**

919

* This method is used to update the array of sheet names. It is

920

* called by the addWorksheet() method of the

921

* PHPExcel_Writer_Excel5_Workbook class.

922

923

* @access public

924

* @see PHPExcel_Writer_Excel5_Workbook::addWorksheet()

925

* @param string $name The name of the worksheet being added

926

* @param integer $index The index of the worksheet being added

927

928

function setExtSheet($name, $index)

929

{

930

$this->_ext_sheets[$name] = $index;

931

}

932

933

/**

934

* pack() row and column into the required 3 or 4 byte format.

935

936

* @access private

937

* @param string $cell The Excel cell reference to be packed

938

* @return array Array containing the row and column in packed() format

939

940

function _cellToPackedRowcol($cell)

941

{

942

$cell = strtoupper($cell);

943

list($row, $col, $row_rel, $col_rel) = $this->_cellToRowcol($cell);

944

if ($col >= 256) {

945

throw new PHPExcel_Writer_Exception("Column in: $cell greater than 255");

946

}

947

if ($row >= 65536) {

948

throw new PHPExcel_Writer_Exception("Row in: $cell greater than 65536 ");

949

}

950

951

// Set the high bits to indicate if row or col are relative.

952

$col |= $col_rel << 14;

953

$col |= $row_rel << 15;

954

$col = pack('v', $col);

955

956

$row = pack('v', $row);

957

958

return array($row, $col);

959

}

960

961

/**

962

* pack() row range into the required 3 or 4 byte format.

963

* Just using maximum col/rows, which is probably not the correct solution

964

965

* @access private

966

* @param string $range The Excel range to be packed

967

* @return array Array containing (row1,col1,row2,col2) in packed() format

968

969

function _rangeToPackedRange($range)

970

{

971

preg_match('/(\$)?(\d+)\:(\$)?(\d+)/', $range, $match);

972

// return absolute rows if there is a $ in the ref

973

$row1_rel = empty($match[1]) ? 1 : 0;

974

$row1 = $match[2];

975

$row2_rel = empty($match[3]) ? 1 : 0;

976

$row2 = $match[4];

977

// Convert 1-index to zero-index

978

--$row1;

979

--$row2;

980

// Trick poor inocent Excel

981

$col1 = 0;

982

$col2 = 65535; // FIXME: maximum possible value for Excel 5 (change this!!!)

983

984

// FIXME: this changes for BIFF8

985

if (($row1 >= 65536) or ($row2 >= 65536)) {

986

throw new PHPExcel_Writer_Exception("Row in: $range greater than 65536 ");

987

}

988

989

// Set the high bits to indicate if rows are relative.

990

$col1 |= $row1_rel << 15;

991

$col2 |= $row2_rel << 15;

992

$col1 = pack('v', $col1);

993

$col2 = pack('v', $col2);

994

995

$row1 = pack('v', $row1);

996

$row2 = pack('v', $row2);

997

998

return array($row1, $col1, $row2, $col2);

999

}

1000

1001

/**

1002

* Convert an Excel cell reference such as A1 or $B2 or C$3 or $D$4 to a zero

1003

* indexed row and column number. Also returns two (0,1) values to indicate

1004

* whether the row or column are relative references.

1005

1006

* @access private

1007

* @param string $cell The Excel cell reference in A1 format.

1008

* @return array

1009

1010

function _cellToRowcol($cell)

1011

{

1012

preg_match('/(\$)?([A-I]?[A-Z])(\$)?(\d+)/',$cell,$match);

1013

// return absolute column if there is a $ in the ref

1014

$col_rel = empty($match[1]) ? 1 : 0;

1015

$col_ref = $match[2];

1016

$row_rel = empty($match[3]) ? 1 : 0;

1017

$row = $match[4];

1018

1019

// Convert base26 column string to a number.

1020

$expn = strlen($col_ref) - 1;

1021

$col = 0;

1022

$col_ref_length = strlen($col_ref);

1023

for ($i = 0; $i < $col_ref_length; ++$i) {

1024

$col += (ord($col_ref{$i}) - 64) * pow(26, $expn);

1025

--$expn;

1026

}

1027

1028

// Convert 1-index to zero-index

1029

--$row;

1030

--$col;

1031

1032

return array($row, $col, $row_rel, $col_rel);

1033

}

1034

1035

/**

1036

* Advance to the next valid token.

1037

1038

* @access private

1039

1040

function _advance()

1041

{

1042

$i = $this->_current_char;

1043

$formula_length = strlen($this->_formula);

1044

// eat up white spaces

1045

if ($i < $formula_length) {

1046

while ($this->_formula{$i} == " ") {

1047

++$i;

1048

}

1049

1050

if ($i < ($formula_length - 1)) {

1051

$this->_lookahead = $this->_formula{$i+1};

1052

}

1053

$token = '';

1054

}

1055

1056

while ($i < $formula_length) {

1057

$token .= $this->_formula{$i};

1058

1059

if ($i < ($formula_length - 1)) {

1060

$this->_lookahead = $this->_formula{$i+1};

1061

} else {

1062

$this->_lookahead = '';

1063

}

1064

1065

if ($this->_match($token) != '') {

1066

//if ($i < strlen($this->_formula) - 1) {

1067

// $this->_lookahead = $this->_formula{$i+1};

1068

//}

1069

$this->_current_char = $i + 1;

1070

$this->_current_token = $token;

1071

return 1;

1072

}

1073

1074

if ($i < ($formula_length - 2)) {

1075

$this->_lookahead = $this->_formula{$i+2};

1076

} else { // if we run out of characters _lookahead becomes empty

1077

$this->_lookahead = '';

1078

}

1079

++$i;

1080

}

1081

//die("Lexical error ".$this->_current_char);

1082

}

1083

1084

/**

1085

* Checks if it's a valid token.

1086

1087

* @access private

1088

* @param mixed $token The token to check.

1089

* @return mixed The checked token or false on failure

1090

1091

function _match($token)

1092

{

1093

switch($token) {

1094

case "+":

1095

case "-":

1096

case "*":

1097

case "/":

1098

case "(":

1099

case ")":

1100

case ",":

1101

case ";":

1102

case ">=":

1103

case "<=":

1104

case "=":

1105

case "<>":

1106

case "^":

1107

case "&":

1108

case "%":

1109

return $token;

1110

break;

1111

case ">":

1112

if ($this->_lookahead == '=') { // it's a GE token

1113

break;

1114

}

1115

return $token;

1116

break;

1117

case "<":

1118

// it's a LE or a NE token

1119

if (($this->_lookahead == '=') or ($this->_lookahead == '>')) {

1120

break;

1121

}

1122

return $token;

1123

break;

1124

default:

1125

// if it's a reference A1 or $A$1 or $A1 or A$1

1126

if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$token) and

1127

!preg_match("/[0-9]/",$this->_lookahead) and

1128

($this->_lookahead != ':') and ($this->_lookahead != '.') and

1129

($this->_lookahead != '!'))

1130

{

1131

return $token;

1132

}

1133

// If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1 or Sheet1!$A$1 or Sheet1:Sheet2!$A$1)

1134

elseif (preg_match("/^" . self::REGEX_SHEET_TITLE_UNQUOTED . "(\:" . self::REGEX_SHEET_TITLE_UNQUOTED . ")?\!\\$?[A-Ia-i]?[A-Za-z]\\$?[0-9]+$/u",$token) and

1135

!preg_match("/[0-9]/",$this->_lookahead) and

1136

($this->_lookahead != ':') and ($this->_lookahead != '.'))

1137

{

1138

return $token;

1139

}

1140

// If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1 or 'Sheet1'!$A$1 or 'Sheet1:Sheet2'!$A$1)

1141

elseif (preg_match("/^'" . self::REGEX_SHEET_TITLE_QUOTED . "(\:" . self::REGEX_SHEET_TITLE_QUOTED . ")?'\!\\$?[A-Ia-i]?[A-Za-z]\\$?[0-9]+$/u",$token) and

1142

!preg_match("/[0-9]/",$this->_lookahead) and

1143

($this->_lookahead != ':') and ($this->_lookahead != '.'))

1144

{

1145

return $token;

1146

}

1147

// if it's a range A1:A2 or $A$1:$A$2

1148

elseif (preg_match('/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/', $token) and

1149

!preg_match("/[0-9]/",$this->_lookahead))

1150

{

1151

return $token;

1152

}

1153

// If it's an external range like Sheet1!A1:B2 or Sheet1:Sheet2!A1:B2 or Sheet1!$A$1:$B$2 or Sheet1:Sheet2!$A$1:$B$2

1154

elseif (preg_match("/^" . self::REGEX_SHEET_TITLE_UNQUOTED . "(\:" . self::REGEX_SHEET_TITLE_UNQUOTED . ")?\!\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+:\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+$/u",$token) and

1155

!preg_match("/[0-9]/",$this->_lookahead))

1156

{

1157

return $token;

1158

}

1159

// If it's an external range like 'Sheet1'!A1:B2 or 'Sheet1:Sheet2'!A1:B2 or 'Sheet1'!$A$1:$B$2 or 'Sheet1:Sheet2'!$A$1:$B$2

1160

elseif (preg_match("/^'" . self::REGEX_SHEET_TITLE_QUOTED . "(\:" . self::REGEX_SHEET_TITLE_QUOTED . ")?'\!\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+:\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+$/u",$token) and

1161

!preg_match("/[0-9]/",$this->_lookahead))

1162

{

1163

return $token;

1164

}

1165

// If it's a number (check that it's not a sheet name or range)

1166

elseif (is_numeric($token) and

1167

(!is_numeric($token.$this->_lookahead) or ($this->_lookahead == '')) and

1168

($this->_lookahead != '!') and ($this->_lookahead != ':'))

1169

{

1170

return $token;

1171

}

1172

// If it's a string (of maximum 255 characters)

1173

elseif (preg_match("/\"([^\"]|\"\"){0,255}\"/",$token) and $this->_lookahead != '"' and (substr_count($token, '"')%2 == 0))

1174

{

1175

return $token;

1176

}

1177

// If it's an error code

1178

elseif (preg_match("/^#[A-Z0\/]{3,5}[!?]{1}$/", $token) or $token == '#N/A')

1179

{

1180

return $token;

1181

}

1182

// if it's a function call

1183

elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$token) and ($this->_lookahead == "("))

1184

{

1185

return $token;

1186

}

1187

// It's an argument of some description (e.g. a named range),

1188

// precise nature yet to be determined

1189

elseif(substr($token,-1) == ')') {

1190

return $token;

1191

}

1192

return '';

1193

}

1194

}

1195

1196

/**

1197

* The parsing method. It parses a formula.

1198

1199

* @access public

1200

* @param string $formula The formula to parse, without the initial equal

1201

* sign (=).

1202

* @return mixed true on success

1203

1204

function parse($formula)

1205

{

1206

$this->_current_char = 0;

1207

$this->_formula = $formula;

1208

$this->_lookahead = isset($formula{1}) ? $formula{1} : '';

1209

$this->_advance();

1210

$this->_parse_tree = $this->_condition();

1211

return true;

1212

}

1213

1214

/**

1215

* It parses a condition. It assumes the following rule:

1216

* Cond -> Expr [(">" | "<") Expr]

1217

1218

* @access private

1219

* @return mixed The parsed ptg'd tree on success

1220

1221

function _condition()

1222

{

1223

$result = $this->_expression();

1224

if ($this->_current_token == "<") {

1225

$this->_advance();

1226

$result2 = $this->_expression();

1227

$result = $this->_createTree('ptgLT', $result, $result2);

1228

} elseif ($this->_current_token == ">") {

1229

$this->_advance();

1230

$result2 = $this->_expression();

1231

$result = $this->_createTree('ptgGT', $result, $result2);

1232

} elseif ($this->_current_token == "<=") {

1233

$this->_advance();

1234

$result2 = $this->_expression();

1235

$result = $this->_createTree('ptgLE', $result, $result2);

1236

} elseif ($this->_current_token == ">=") {

1237

$this->_advance();

1238

$result2 = $this->_expression();

1239

$result = $this->_createTree('ptgGE', $result, $result2);

1240

} elseif ($this->_current_token == "=") {

1241

$this->_advance();

1242

$result2 = $this->_expression();

1243

$result = $this->_createTree('ptgEQ', $result, $result2);

1244

} elseif ($this->_current_token == "<>") {

1245

$this->_advance();

1246

$result2 = $this->_expression();

1247

$result = $this->_createTree('ptgNE', $result, $result2);

1248

} elseif ($this->_current_token == "&") {

1249

$this->_advance();

1250

$result2 = $this->_expression();

1251

$result = $this->_createTree('ptgConcat', $result, $result2);

1252

}

1253

return $result;

1254

}

1255

1256

/**

1257

* It parses a expression. It assumes the following rule:

1258

* Expr -> Term [("+" | "-") Term]

1259

* -> "string"

1260

* -> "-" Term : Negative value

1261

* -> "+" Term : Positive value

1262

* -> Error code

1263

1264

* @access private

1265

* @return mixed The parsed ptg'd tree on success

1266

1267

function _expression()

1268

{

1269

// If it's a string return a string node

1270

if (preg_match("/\"([^\"]|\"\"){0,255}\"/", $this->_current_token)) {

1271

$tmp = str_replace('""', '"', $this->_current_token);

1272

if (($tmp == '"') || ($tmp == '')) $tmp = '""'; // Trap for "" that has been used for an empty string

1273

$result = $this->_createTree($tmp, '', '');

1274

$this->_advance();

1275

return $result;

1276

// If it's an error code

1277

} elseif (preg_match("/^#[A-Z0\/]{3,5}[!?]{1}$/", $this->_current_token) or $this->_current_token == '#N/A'){

1278

$result = $this->_createTree($this->_current_token, 'ptgErr', '');

1279

$this->_advance();

1280

return $result;

1281

// If it's a negative value

1282

} elseif ($this->_current_token == "-") {

1283

// catch "-" Term

1284

$this->_advance();

1285

$result2 = $this->_expression();

1286

$result = $this->_createTree('ptgUminus', $result2, '');

1287

return $result;

1288

// If it's a positive value

1289

} elseif ($this->_current_token == "+") {

1290

// catch "+" Term

1291

$this->_advance();

1292

$result2 = $this->_expression();

1293

$result = $this->_createTree('ptgUplus', $result2, '');

1294

return $result;

1295

}

1296

$result = $this->_term();

1297

while (($this->_current_token == "+") or

1298

($this->_current_token == "-") or

1299

($this->_current_token == "^")) {

1300

/**/

1301

if ($this->_current_token == "+") {

1302

$this->_advance();

1303

$result2 = $this->_term();

1304

$result = $this->_createTree('ptgAdd', $result, $result2);

1305

} elseif ($this->_current_token == "-") {

1306

$this->_advance();

1307

$result2 = $this->_term();

1308

$result = $this->_createTree('ptgSub', $result, $result2);

1309

} else {

1310

$this->_advance();

1311

$result2 = $this->_term();

1312

$result = $this->_createTree('ptgPower', $result, $result2);

1313

}

1314

}

1315

return $result;

1316

}

1317

1318

/**

1319

* This function just introduces a ptgParen element in the tree, so that Excel

1320

* doesn't get confused when working with a parenthesized formula afterwards.

1321

1322

* @access private

1323

* @see _fact()

1324

* @return array The parsed ptg'd tree

1325

1326

function _parenthesizedExpression()

1327

{

1328

$result = $this->_createTree('ptgParen', $this->_expression(), '');

1329

return $result;

1330

}

1331

1332

/**

1333

* It parses a term. It assumes the following rule:

1334

* Term -> Fact [("*" | "/") Fact]

1335

1336

* @access private

1337

* @return mixed The parsed ptg'd tree on success

1338

1339

function _term()

1340

{

1341

$result = $this->_fact();

1342

while (($this->_current_token == "*") or

1343

($this->_current_token == "/")) {

1344

/**/

1345

if ($this->_current_token == "*") {

1346

$this->_advance();

1347

$result2 = $this->_fact();

1348

$result = $this->_createTree('ptgMul', $result, $result2);

1349

} else {

1350

$this->_advance();

1351

$result2 = $this->_fact();

1352

$result = $this->_createTree('ptgDiv', $result, $result2);

1353

}

1354

}

1355

return $result;

1356

}

1357

1358

/**

1359

* It parses a factor. It assumes the following rule:

1360

* Fact -> ( Expr )

1361

* | CellRef

1362

* | CellRange

1363

* | Number

1364

* | Function

1365

1366

* @access private

1367

* @return mixed The parsed ptg'd tree on success

1368

1369

function _fact()

1370

{

1371

if ($this->_current_token == "(") {

1372

$this->_advance(); // eat the "("

1373

$result = $this->_parenthesizedExpression();

1374

if ($this->_current_token != ")") {

1375

throw new PHPExcel_Writer_Exception("')' token expected.");

1376

}

1377

$this->_advance(); // eat the ")"

1378

return $result;

1379

}

1380

// if it's a reference

1381

if (preg_match('/^\$?[A-Ia-i]?[A-Za-z]\$?[0-9]+$/',$this->_current_token))

1382

{

1383

$result = $this->_createTree($this->_current_token, '', '');

1384

$this->_advance();

1385

return $result;

1386

}

1387

// If it's an external reference (Sheet1!A1 or Sheet1:Sheet2!A1 or Sheet1!$A$1 or Sheet1:Sheet2!$A$1)

1388

elseif (preg_match("/^" . self::REGEX_SHEET_TITLE_UNQUOTED . "(\:" . self::REGEX_SHEET_TITLE_UNQUOTED . ")?\!\\$?[A-Ia-i]?[A-Za-z]\\$?[0-9]+$/u",$this->_current_token))

1389

{

1390

$result = $this->_createTree($this->_current_token, '', '');

1391

$this->_advance();

1392

return $result;

1393

}

1394

// If it's an external reference ('Sheet1'!A1 or 'Sheet1:Sheet2'!A1 or 'Sheet1'!$A$1 or 'Sheet1:Sheet2'!$A$1)

1395

elseif (preg_match("/^'" . self::REGEX_SHEET_TITLE_QUOTED . "(\:" . self::REGEX_SHEET_TITLE_QUOTED . ")?'\!\\$?[A-Ia-i]?[A-Za-z]\\$?[0-9]+$/u",$this->_current_token))

1396

{

1397

$result = $this->_createTree($this->_current_token, '', '');

1398

$this->_advance();

1399

return $result;

1400

}

1401

// if it's a range A1:B2 or $A$1:$B$2

1402

elseif (preg_match('/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+:(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/',$this->_current_token) or

1403

preg_match('/^(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+\.\.(\$)?[A-Ia-i]?[A-Za-z](\$)?[0-9]+$/',$this->_current_token))

1404

{

1405

// must be an error?

1406

$result = $this->_createTree($this->_current_token, '', '');

1407

$this->_advance();

1408

return $result;

1409

}

1410

// If it's an external range (Sheet1!A1:B2 or Sheet1:Sheet2!A1:B2 or Sheet1!$A$1:$B$2 or Sheet1:Sheet2!$A$1:$B$2)

1411

elseif (preg_match("/^" . self::REGEX_SHEET_TITLE_UNQUOTED . "(\:" . self::REGEX_SHEET_TITLE_UNQUOTED . ")?\!\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+:\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+$/u",$this->_current_token))

1412

{

1413

// must be an error?

1414

//$result = $this->_current_token;

1415

$result = $this->_createTree($this->_current_token, '', '');

1416

$this->_advance();

1417

return $result;

1418

}

1419

// If it's an external range ('Sheet1'!A1:B2 or 'Sheet1'!A1:B2 or 'Sheet1'!$A$1:$B$2 or 'Sheet1'!$A$1:$B$2)

1420

elseif (preg_match("/^'" . self::REGEX_SHEET_TITLE_QUOTED . "(\:" . self::REGEX_SHEET_TITLE_QUOTED . ")?'\!\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+:\\$?([A-Ia-i]?[A-Za-z])?\\$?[0-9]+$/u",$this->_current_token))

1421

{

1422

// must be an error?

1423

//$result = $this->_current_token;

1424

$result = $this->_createTree($this->_current_token, '', '');

1425

$this->_advance();

1426

return $result;

1427

}

1428

// If it's a number or a percent

1429

elseif (is_numeric($this->_current_token))

1430

{

1431

if($this->_lookahead == '%'){

1432

$result = $this->_createTree('ptgPercent', $this->_current_token, '');

1433

} else {

1434

$result = $this->_createTree($this->_current_token, '', '');

1435

}

1436

$this->_advance();

1437

return $result;

1438

}

1439

// if it's a function call

1440

elseif (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/i",$this->_current_token))

1441

{

1442

$result = $this->_func();

1443

return $result;

1444

}

1445

throw new PHPExcel_Writer_Exception("Syntax error: ".$this->_current_token.

1446

", lookahead: ".$this->_lookahead.

1447

", current char: ".$this->_current_char);

1448

}

1449

1450

/**

1451

* It parses a function call. It assumes the following rule:

1452

* Func -> ( Expr [,Expr]* )

1453

1454

* @access private

1455

* @return mixed The parsed ptg'd tree on success

1456

1457

function _func()

1458

{

1459

$num_args = 0; // number of arguments received

1460

$function = strtoupper($this->_current_token);

1461

$result = ''; // initialize result

1462

$this->_advance();

1463

$this->_advance(); // eat the "("

1464

while ($this->_current_token != ')') {

1465

/**/

1466

if ($num_args > 0) {

1467

if ($this->_current_token == "," or

1468

$this->_current_token == ";")

1469

{

1470

$this->_advance(); // eat the "," or ";"

1471

} else {

1472

throw new PHPExcel_Writer_Exception("Syntax error: comma expected in ".

1473

"function $function, arg #{$num_args}");

1474

}

1475

$result2 = $this->_condition();

1476

$result = $this->_createTree('arg', $result, $result2);

1477

} else { // first argument

1478

$result2 = $this->_condition();

1479

$result = $this->_createTree('arg', '', $result2);

1480

}

1481

++$num_args;

1482

}

1483

if (!isset($this->_functions[$function])) {

1484

throw new PHPExcel_Writer_Exception("Function $function() doesn't exist");

1485

}

1486

$args = $this->_functions[$function][1];

1487

// If fixed number of args eg. TIME($i,$j,$k). Check that the number of args is valid.

1488

if (($args >= 0) and ($args != $num_args)) {

1489

throw new PHPExcel_Writer_Exception("Incorrect number of arguments in function $function() ");

1490

}

1491

1492

$result = $this->_createTree($function, $result, $num_args);

1493

$this->_advance(); // eat the ")"

1494

return $result;

1495

}

1496

1497

/**

1498

* Creates a tree. In fact an array which may have one or two arrays (sub-trees)

1499

* as elements.

1500

1501

* @access private

1502

* @param mixed $value The value of this node.

1503

* @param mixed $left The left array (sub-tree) or a final node.

1504

* @param mixed $right The right array (sub-tree) or a final node.

1505

* @return array A tree

1506

1507

function _createTree($value, $left, $right)

1508

{

1509

return array('value' => $value, 'left' => $left, 'right' => $right);

1510

}

1511

1512

/**

1513

* Builds a string containing the tree in reverse polish notation (What you

1514

* would use in a HP calculator stack).

1515

* The following tree:

1516

1517

* +

1518

* / \

1519

* 2 3

1520

1521

* produces: "23+"

1522

1523

* The following tree:

1524

1525

* +

1526

* / \

1527

* 3 *

1528

* / \

1529

* 6 A1

1530

1531

* produces: "36A1*+"

1532

1533

* In fact all operands, functions, references, etc... are written as ptg's

1534

1535

* @access public

1536

* @param array $tree The optional tree to convert.

1537

* @return string The tree in reverse polish notation

1538

1539

function toReversePolish($tree = array())

1540

{

1541

$polish = ""; // the string we are going to return

1542

if (empty($tree)) { // If it's the first call use _parse_tree

1543

$tree = $this->_parse_tree;

1544

}

1545

1546

if (is_array($tree['left'])) {

1547

$converted_tree = $this->toReversePolish($tree['left']);

1548

$polish .= $converted_tree;

1549

} elseif ($tree['left'] != '') { // It's a final node

1550

$converted_tree = $this->_convert($tree['left']);

1551

$polish .= $converted_tree;

1552

}

1553

if (is_array($tree['right'])) {

1554

$converted_tree = $this->toReversePolish($tree['right']);

1555

$polish .= $converted_tree;

1556

} elseif ($tree['right'] != '') { // It's a final node

1557

$converted_tree = $this->_convert($tree['right']);

1558

$polish .= $converted_tree;

1559

}

1560

// if it's a function convert it here (so we can set it's arguments)

1561

if (preg_match("/^[A-Z0-9\xc0-\xdc\.]+$/",$tree['value']) and

1562

!preg_match('/^([A-Ia-i]?[A-Za-z])(\d+)$/',$tree['value']) and

1563

!preg_match("/^[A-Ia-i]?[A-Za-z](\d+)\.\.[A-Ia-i]?[A-Za-z](\d+)$/",$tree['value']) and

1564

!is_numeric($tree['value']) and

1565

!isset($this->ptg[$tree['value']]))

1566

{

1567

// left subtree for a function is always an array.

1568

if ($tree['left'] != '') {

1569

$left_tree = $this->toReversePolish($tree['left']);

1570

} else {

1571

$left_tree = '';

1572

}

1573

// add it's left subtree and return.

1574

return $left_tree.$this->_convertFunction($tree['value'], $tree['right']);

1575

} else {

1576

$converted_tree = $this->_convert($tree['value']);

1577

}

1578

$polish .= $converted_tree;

1579

return $polish;

1580

}

1581

1582

}

Older »