Inputting & outputting values

Topics: User Forum
Sep 17, 2010 at 7:28 AM

Hello.

I've been directed here by a colleague who claims these classes should be able to do what I'm after.

I have a working 2003 xls file configured for simple calculations, what I'm trying to do if at all possible is put together a simple PHP script for submitting and returning values to and from this file.

The file has 5 cells, 3 input (B18, E18, H18) and 2 output (K18, N18).

What I'm basically wanting to do here is submit input to the relevant cells via HTML/PHP, crunch the numbers then extract the results and echo them back out.

In other words, the script would start like this: 

      $B18 = $_POST['B18'];

      $E18 = $_POST['E18'];

      $H18 = $_POST['H18'];

Then each value would be "entered" into it's corresponding cell e.g. $B18-->Cell B18;  $E18-->Cell E18;  $H18-->Cell H18;  the calculation would be performed and then the process reversed with the output e.g. Cell K18-->$K18;  Cell N18-->$N18;

     

  echo $K18. ' ' . $N18;

with any memory or cache being cleared

 

I'm not sure how complicated this is or isn't so I'd appreciate any assistance.

Thank you.

 

Coordinator
Sep 17, 2010 at 2:39 PM

Two possible solutions:
a) Your proposed method with an existing workbook, with the formulae built-in, so you simply populate the data in the input cells from your form, then read and display the calculation results from the formula cells.
b) Skip the existing workbook, and execute the formulae directly from PHPExcel.

I've have two versions of a Quadratic Equation solver script, which work using these two methods.

Quadratic1.php

<html>
<head>
<title>Quadratic Equation Solver</title>
</head>
<body>
<?php
/** Error reporting  **/
error_reporting(E_ALL);
/** Include path  **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../Classes/');
?>
<h1>Quadratic Equation Solver</h1>
<form action="Quadratic1.php" method="POST">
Enter the coefficients for the Ax<sup>2</sup> + Bx + C = 0
<table border="0" cellpadding="0" cellspacing="0">
 <tr><td><b>A&nbsp;</b></td>
  <td><input name="A" type="text" size="8" value="<?php echo (isset($_POST['A'])) ? htmlentities($_POST['A']) : ''; ?>"></td>
 </tr>
 <tr><td><b>B&nbsp;</b></td>
  <td><input name="B" type="text" size="8" value="<?php echo (isset($_POST['B'])) ? htmlentities($_POST['B']) : ''; ?>"></td>
 </tr>
 <tr><td><b>C&nbsp;</b></td>
  <td><input name="C" type="text" size="8" value="<?php echo (isset($_POST['C'])) ? htmlentities($_POST['C']) : ''; ?>"></td>
 </tr>
</table>
<input name="submit" type="submit" value="calculate"><br />
If A=0, the equation is not quadratic.
</form>
<?php
/** If the user has submitted the form, then we need to execute a calculation **/
if (isset($_POST['submit'])) {
 if ($_POST['A'] == 0) {
  echo 'The equation is not quadratic';
 } else {
  /** So we include PHPExcel to perform the calculations **/
  include 'PHPExcel/IOFactory.php';
  /** Load the quadratic equation solver worksheet into memory   **/
  $objPHPExcel = PHPExcel_IOFactory::load('./Quadratic.xlsx');
  /** Set our A, B and C values   **/
  $objPHPExcel->getActiveSheet()->setCellValue('A1', $_POST['A']);
  $objPHPExcel->getActiveSheet()->setCellValue('B1', $_POST['B']);
  $objPHPExcel->getActiveSheet()->setCellValue('C1', $_POST['C']);

  /** Calculate and Display the results   **/
  echo '<hr /><b>Roots:</b><br />';
  $callStartTime = microtime(true);
  echo $objPHPExcel->getActiveSheet()->getCell('B5')->getCalculatedValue().'<br />';
  echo $objPHPExcel->getActiveSheet()->getCell('B6')->getCalculatedValue().'<br />';
  $callEndTime = microtime(true);
  $callTime = $callEndTime - $callStartTime;
  echo '<hr />Call time for Quadratic Equation Solution was '.sprintf('%.4f',$callTime).' seconds<br /><hr />';
  echo ' Peak memory usage: '.(memory_get_peak_usage(true) / 1024 / 1024).' MB<br />';
 }
}
?>
</body>
<html>

In the workbook Quadratic.xlsx, A1, B1 and C1 are the cells for the three input values, while cells B5 and B6 contain the formulae for the end result of the calculation 

Quadratic2.php

<html>
<head>
<title>Quadratic Equation Solver</title>
</head>
<body>
<?php
/** Error reporting  **/
error_reporting(E_ALL);
/** Include path  **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../Classes/');
?>
<h1>Quadratic Equation Solver</h1>
<form action="Quadratic2.php" method="POST">
Enter the coefficients for the Ax<sup>2</sup> + Bx + C = 0
<table border="0" cellpadding="0" cellspacing="0">
 <tr><td><b>A&nbsp;</b></td>
  <td><input name="A" type="text" size="8" value="<?php echo (isset($_POST['A'])) ? htmlentities($_POST['A']) : ''; ?>"></td>
 </tr>
 <tr><td><b>B&nbsp;</b></td>
  <td><input name="B" type="text" size="8" value="<?php echo (isset($_POST['B'])) ? htmlentities($_POST['B']) : ''; ?>"></td>
 </tr>
 <tr><td><b>C&nbsp;</b></td>
  <td><input name="C" type="text" size="8" value="<?php echo (isset($_POST['C'])) ? htmlentities($_POST['C']) : ''; ?>"></td>
 </tr>
</table>
<input name="submit" type="submit" value="calculate"><br />
If A=0, the equation is not quadratic.
</form>
<?php
/** If the user has submitted the form, then we need to execute a calculation **/
if (isset($_POST['submit'])) {
 if ($_POST['A'] == 0) {
  echo 'The equation is not quadratic';
 } else {
  /** So we include PHPExcel to perform the calculations **/
  include 'PHPExcel/Calculation.php';
  /** Calculate and Display the results   **/
  echo '<hr /><b>Roots:</b><br />';
  $callStartTime = microtime(true);
  $discriminantFormula = '=POWER('.$_POST['B'].',2) - (4 * '.$_POST['A'].' * '.$_POST['C'].')';
  $discriminant = PHPExcel_Calculation::getInstance()->calculateFormula($discriminantFormula);
  $r1Formula = '=IMDIV(IMSUM(-'.$_POST['B'].',IMSQRT('.$discriminant.')),2 * '.$_POST['A'].')';
  $r2Formula = '=IF('.$discriminant.'=0,"Only one root",IMDIV(IMSUB(-'.$_POST['B'].',IMSQRT('.$discriminant.')),2 * '.$_POST['A'].'))';
  echo PHPExcel_Calculation::getInstance()->calculateFormula($r1Formula).'<br />';
  echo PHPExcel_Calculation::getInstance()->calculateFormula($r2Formula).'<br />';
  $callEndTime = microtime(true);
  $callTime = $callEndTime - $callStartTime;
  echo '<hr />Call time for Quadratic Equation Solution was '.sprintf('%.4f',$callTime).' seconds<br /><hr />';
  echo ' Peak memory usage: '.(memory_get_peak_usage(true) / 1024 / 1024).' MB<br />';
 }
}
?>
</body>
<html>

In this second exanmple, we build the Excel formulae as strings, and feed them directly to the calculation engine without needing to load any workbook: it's faster and uses less memory, but the inclusion of the formulae directly within the PHP make the code more complex

These scripts were written simply as proofs of concept, and neither example validates the input values in any way, though they should in any production-quality system

Sep 18, 2010 at 6:35 AM

Thanks for the help, I managed to get the first option working great.

Just one more thing: which file would I have to modify to alter the number formatting?

Currently the results are output like this: 1000 or 1000.5 or 1000.508929829 (second output cell only)

I'd like them to be presented like this: 1.000,00 or 1.000,50

 

 

Coordinator
Sep 18, 2010 at 11:46 AM

If you're using the first option, ie. working from your workbook file, then set cell formatting options in your worksheet and use:

$cellData = PHPExcel_Style_NumberFormat::toFormattedString(
        $objPHPExcel->getActiveSheet()->getCell('B5')->getCalculatedValue(),
        $objPHPExcel->getActiveSheet()->getCellXfByIndex( $objPHPExcel->getActiveSheet()->getCell('B5')->getXfIndex() )->getNumberFormat()->getFormatCode()
       );

to retrieve the calculated value of the cell (B5 in this example) as a string formatted using the formatting mask defined in the Excel worksheet for that cell

Alternatively, use standard PHP functions (such as numberformat()) to render the value as you want it displayed