0

I am trying to set the value of a cell in an Excel spreadsheet to a formula that references a worksheet and has several parameters to it. My formula is:

=SUMIFS('Sheet1'!AY:AY,'Sheet1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "NP")+SUMIFS('Sheet1'!AY:AY,'Sheet1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "PHBO")+SUMIFS('Sheet1'!AY:AY,'Shee1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "PR", 'Sheet1'!AN:AN, "Both")+SUMIFS('Sheet1'!AY:AY,'Sheet1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "PR", 'Sheet1'!AN:AN, "Area")

My PHP is:

$objPHPExcel->setActiveSheetIndex(1)
            ->setCellValue('B2', 'XXX');

Where XXX = the formula above. The problem is because of the ' and " marks in the formula I get errors.

Thamilhan
  • 13,040
  • 5
  • 37
  • 59
Kyle Souza
  • 127
  • 9
  • 1
    you can escape the quotes `$foo = 'Something\'s funny';` – JimL Jun 03 '16 at 15:53
  • Related: [What does it mean to escape a string?](http://stackoverflow.com/questions/10646142/what-does-it-mean-to-escape-a-string) – HPierce Jun 03 '16 at 15:55
  • 1
    You need to learn about PHP strings properly: http://php.net/manual/en/language.types.string.php#language.types.string.syntax.single – Marc B Jun 03 '16 at 16:00

1 Answers1

1
<?PHP
$stmt = <<<EOF
=SUMIFS('Sheet1'!AY:AY,'Sheet1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "NP")+SUMIFS('Sheet1'!AY:AY,'Sheet1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "PHBO")+SUMIFS('Sheet1'!AY:AY,'Shee1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "PR", 'Sheet1'!AN:AN, "Both")+SUMIFS('Sheet1'!AY:AY,'Sheet1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "PR", 'Sheet1'!AN:AN, "Area")
EOF;

$objPHPExcel->setActiveSheetIndex(1)
        ->setCellValue('B2', $stmt);
?>

Assigns your String to the variable $stmt then uses that variable in your function. This allows for the ' and " to be read as characters in the string instead of being read by the interpreter to break the sting.

Sparky
  • 66
  • 10
  • When I open up the Excel spreadsheet it shows the formula as: =SUMIFS($sheet0.AY:AY,$sheet0.J:J, "Q1", $sheet0.M:M, "Extensions", $sheet0.AW:AW, "NP")+SUMIFS($sheet0.AY:AY,$sheet0.J:J, "Q1", $sheet0.M:M, "Extensions", $sheet0.AW:AW, "PHBO")+SUMIFS($sheet0.AY:AY,$sheet0.J:J, "Q1", $sheet0.M:M, "Extensions", $sheet0.AW:AW, "PR", $sheet0.AN:AN, "Both")+SUMIFS($sheet0.AY:AY,$sheet0.J:J, "Q1", $sheet0.M:M, "Extensions", $sheet0.AW:AW, "PR", $sheet0.AN:AN, "Area") Any idea why it is getting changes when saved? – Kyle Souza Jun 03 '16 at 16:54
  • While testing it i only assigned and echoed the variable. So it should work for passing the string to your function as it is. Does the php interpreter error or does it just leave that charcter in the excel sheet? – Sparky Jun 03 '16 at 16:56
  • no error, just changes the ! to . and adds the $ to the formula. – Kyle Souza Jun 03 '16 at 17:01
  • Try Chaning the HEREDOC declaration to a NOWDOC, $stmt=<<<'EOF' – Sparky Jun 03 '16 at 17:17
  • Nope! But I do appreciate all your help, you've gotten me closer. I'll keep trying different things with it. – Kyle Souza Jun 03 '16 at 17:41