title: How can insert formula in excel sheet using phpexcel
body: <p>I have checked many sites for solution of my problem but not found proper solution.
  I want to insert formula which will calculate the total of marks obtained by students. Marks will will be entered by teachers. I have written the php code which is downloading the excel file after inserting students names from database and maxmarks.
  My excel file look like this kindly see the image as I don't have 10 reputation I am not allowed to insert image in post, please follow this link <a href="http://cbsecsnip.in/Capture.JPG" rel="noreferrer">http://cbsecsnip.in/Capture.JPG</a> 
  Marks column is blank where teacher will marks other data are fetched from database and Percentage column need a formula which will calculate the marks automatically when teacher enter marks. Filled column and Percentage column is protected. I using PHPExcel.
  Here is php code which is generating this excel file</p>
  
  <pre><code>&lt;?php
  $host='localhost'; $user='vishal'; $pass='*****'; $DataBase='school';//define the correct values
  // open the connexion to the databases server
  $Link=@mysqli_connect($host,$user,$pass,$DataBase) or die('Can\'t connect !');
  mysqli_set_charset($Link, 'utf8');//if not by default
  
  //your request
  $SQL="SELECT `admissionnumber`,`pre_name`,`pre_fathersoccupation`,`pre_motheroccupation` FROM `es_preadmission` WHERE `pre_class`=25 AND `pre_fromdate`&gt;='2014-04-01' AND `pre_todate`&lt;='2015-03-31'";
  $rs=mysqli_query($Link, $SQL);//get the result (ressource)
  $SQL1="SELECT a.`total_marks`,a.`pass_marks`,b.es_subjectname FROM `es_exam_details` as a JOIN `es_subject` as b ON a.`subject_id`=b.`es_subjectid` JOIN es_exam_academic as c ON c.es_exam_academicid=a.academicexam_id WHERE es_subjectshortname=25";
  $rs1=mysqli_query($Link, $SQL1);//get the result (ressource)
  $SQL2="SELECT distinct(b.es_subjectname) as subjects FROM `es_exam_details` as a JOIN `es_subject` as b ON a.`subject_id`=b.`es_subjectid` JOIN es_exam_academic as c ON c.es_exam_academicid=a.academicexam_id WHERE es_subjectshortname=25";
  
  $rs2=mysqli_query($Link, $SQL2);//get the result (ressource)
  while($objResult1 =mysqli_fetch_assoc($rs2)){
      $subjects[] = $objResult1["subjects"];
  } 
  while($objResult =mysqli_fetch_assoc($rs1)){
      $totalmarks[] = $objResult["total_marks"];
      $term[] = $objResult["aca_term_name"]; 
  }
  
  $totalStudents=array();
  while($objResult =mysqli_fetch_assoc($rs)){
  $totalStudents[] = $objResult;
  }
  
  $Sa1Sa2marks=60;
  require_once 'Classes/PHPExcel.php';
  require_once 'Classes/PHPExcel/IOFactory.php';
  
  // read in the existing file
  $objPHPExcel = PHPExcel_IOFactory::load("blank.xls");
  
  // modify/insert data in worksheet cells
  $styleArray = array(
    'borders' =&gt; array(
       'outline' =&gt; array(
          'style' =&gt; PHPExcel_Style_Border::BORDER_THIN,
             'color' =&gt; array('argb' =&gt; '000000'),
               ),
         ),
         'fill' =&gt; array(
            'type' =&gt; PHPExcel_Style_Fill::FILL_SOLID,
            'color' =&gt; array('rgb' =&gt; '686868')
          )
  );
  
  $F=$objPHPExcel-&gt;getActiveSheet();
  $G=$objPHPExcel-&gt;setActiveSheetIndex(0);
  $G-&gt;getProtection()-&gt;setSheet(true);
  
  // for 1st row heading subject heading setting 
  $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells("A1:B1")-&gt;setCellValueByColumnAndRow(0,1, "Subject");
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1')-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A1:B1')-&gt;applyFromArray($styleArray);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getColumnDimension('B')-&gt;setWidth(40);
  
  // for 2nd row heading subject heading setting 
  
  $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells("A2:B2")-&gt;setCellValueByColumnAndRow(0,2, "Term");
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A2')-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A2:B2')-&gt;applyFromArray($styleArray);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getColumnDimension('B')-&gt;setWidth(40);
  
  // for 3rd row heading subject heading setting 
  $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells("A3:B3")-&gt;setCellValueByColumnAndRow(0,3, "Exam");
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A3')-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A3:B3')-&gt;applyFromArray($styleArray);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getColumnDimension('B')-&gt;setWidth(40);
  
  // for 4th row heading subject heading setting 
  $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue('A4', "ROLL");
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A4')-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('A4')-&gt;applyFromArray($styleArray);
  $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue('B4', "NAME");
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B4')-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle('B4')-&gt;applyFromArray($styleArray);
  
  //subject list setting
  $row=1;
  $coumnStart=1;
  $subjectCtr=0;
  foreach($subjects as $subject)
  {   
  $coumnStart++;
  $StartcolumnIndex=PHPExcel_Cell::stringFromColumnIndex($coumnStart);
  $EndcolumnIndex = PHPExcel_Cell::stringFromColumnIndex(($coumnStart-1) + 13);
  
  // 1st row
  $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells($StartcolumnIndex.$row.':'.$EndcolumnIndex.$row)-&gt;setCellValueByColumnAndRow($coumnStart,1, $subject);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.$row)-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.$row.':'.$EndcolumnIndex.$row)-&gt;applyFromArray($styleArray);
  
  // 2nd  row for Term1
  $EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($coumnStart-1) + 6);
  
  $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells($StartcolumnIndex.($row+1).':'.$EndcolumnIndex2.($row+1))-&gt;setCellValueByColumnAndRow($coumnStart,2, 'TERM 1');
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+1))-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+1).':'.$EndcolumnIndex2.($row+1))-&gt;applyFromArray($styleArray);
  
  //for FA1 Sem setting param
  $semIndex=$coumnStart;
  $EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($semIndex-1) + 2);
  $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))-&gt;setCellValueByColumnAndRow($semIndex,3, 'FA1');
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+2))-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))-&gt;applyFromArray($styleArray);
  $semIndex=$semIndex+2;
  
  //for FA2
  $StartcolumnIndex = PHPExcel_Cell::stringFromColumnIndex($semIndex);
  $EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($semIndex-1) + 2);
  $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))-&gt;setCellValueByColumnAndRow($semIndex,3, 'FA2');
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+2))-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))-&gt;applyFromArray($styleArray);
  $semIndex=$semIndex+2;
  
  //for SA1
  $StartcolumnIndex = PHPExcel_Cell::stringFromColumnIndex($semIndex);
  $EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($semIndex-1) + 2);
  $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))-&gt;setCellValueByColumnAndRow($semIndex,3, 'SA1');
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+2))-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))-&gt;applyFromArray($styleArray);
  $semIndex=$semIndex+2;
  
  // for Term2
  $StartcolumnIndex2=PHPExcel_Cell::stringFromColumnIndex(($coumnStart) + 6);
  $EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex($coumnStart + 12);
  $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells($StartcolumnIndex2.($row+1).':'.$EndcolumnIndex2.($row+1))-&gt;setCellValueByColumnAndRow($coumnStart+6,2, 'TERM 2');
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex2.($row+1))-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex2.($row+1).':'.$EndcolumnIndex2.($row+1))-&gt;applyFromArray($styleArray);
  
  //FA3
  $StartcolumnIndex = PHPExcel_Cell::stringFromColumnIndex($semIndex);
  $EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($semIndex-1) + 2);
  $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))-&gt;setCellValueByColumnAndRow($semIndex,3, 'FA3');
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+2))-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))-&gt;applyFromArray($styleArray);
  $semIndex=$semIndex+2;
  
  //FA4
  $StartcolumnIndex = PHPExcel_Cell::stringFromColumnIndex($semIndex);
  $EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($semIndex-1) + 2);
  $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))-&gt;setCellValueByColumnAndRow($semIndex,3, 'FA4');
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+2))-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))-&gt;applyFromArray($styleArray);
  $semIndex=$semIndex+2;
  
  //SA2
  $StartcolumnIndex = PHPExcel_Cell::stringFromColumnIndex($semIndex);
  $EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($semIndex-1) + 2);
  $objPHPExcel-&gt;getActiveSheet()-&gt;mergeCells($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))-&gt;setCellValueByColumnAndRow($semIndex,3, 'SA2');
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+2))-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))-&gt;applyFromArray($styleArray);
  $semIndex=$semIndex+2;
  
  // every 13th column total marks &amp; precentage
  $totalMarksColumIndex=PHPExcel_Cell::stringFromColumnIndex($coumnStart + 12);
  $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($coumnStart+12,3, 'TOTAL MARKS');
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($totalMarksColumIndex.($row+2))-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($totalMarksColumIndex.($row+2))-&gt;applyFromArray($styleArray);
  
  $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($coumnStart+12,4, 'PERCENTAGE');
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($totalMarksColumIndex.($row+3))-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($totalMarksColumIndex.($row+3))-&gt;applyFromArray($styleArray);
  $objPHPExcel-&gt;getActiveSheet()-&gt;getColumnDimension($totalMarksColumIndex)-&gt;setWidth(20);
  
  // putting marks column heading
  $flag=1;
  for($i=$coumnStart;$i&lt;$coumnStart+12;$i=$i+1)
  {
  $MarksheadingIndex=PHPExcel_Cell::stringFromColumnIndex($i);    
   if($flag%2==1) 
    {
     $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($i,4, 'MARKS');
     $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($MarksheadingIndex)-&gt;getProtection()-&gt;setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
    }
   else 
    {
     $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($i,4, 'MAXMARKS');
      // setting the max marks
        for($j=5;$j&lt;count($totalStudents)+5;$j++){
          if($flag%6==0)
          {
          $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($i,$j, $Sa1Sa2marks);    
          }
          else if($flag==2 || $flag==8)
          {
           $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($i,$j, 40);  
          }
          else
          {
           $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValueByColumnAndRow($i,$j, 20);     
          }
        }
    }
  
   $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($MarksheadingIndex.($row+3))-&gt;getAlignment()-&gt;setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
   $objPHPExcel-&gt;getActiveSheet()-&gt;getStyle($MarksheadingIndex.($row+3))-&gt;applyFromArray($styleArray); 
   $objPHPExcel-&gt;getActiveSheet()-&gt;getColumnDimension($MarksheadingIndex)-&gt;setWidth(15);
  $flag++;    
  }
  
  $coumnStart=($coumnStart-1)+13; $subjectCtr++;
  }
  
  // putting students name with rollno
  
  $Line=5;
   foreach($totalStudents as $student){//extract each record
          $studentName= $student['pre_name'];
          if($student['pre_fathersoccupation']!=''){$studentName.=' '.$student['pre_fathersoccupation'];}
          if($student['pre_motheroccupation']!=''){$studentName.=' '.$student['pre_motheroccupation'];}
          $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue('A'.$Line,  $student['admissionnumber']);
          $objPHPExcel-&gt;getActiveSheet()-&gt;setCellValue('B'.$Line,  $studentName);
      ++$Line;
         }
  
  header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  header('Content-Disposition: attachment;filename="newFile.xlsx"');
  header('Cache-Control: max-age=0');
  
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  $objWriter-&gt;save('php://output'); 
  
  ?&gt;
  </code></pre>
  
  <p>If any Expert guide me how can I do this I will be thankful to all of them</p>