How can insert formula in excel sheet using phpexcel

php phpexcel
user3100533 picture user3100533 · Jan 23, 2014 · Viewed 66.5k times · Source

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 http://cbsecsnip.in/Capture.JPG 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

<?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`>='2014-04-01' AND `pre_todate`<='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' => array(
     'outline' => array(
        'style' => PHPExcel_Style_Border::BORDER_THIN,
           'color' => array('argb' => '000000'),
             ),
       ),
       'fill' => array(
          'type' => PHPExcel_Style_Fill::FILL_SOLID,
          'color' => array('rgb' => '686868')
        )
);

$F=$objPHPExcel->getActiveSheet();
$G=$objPHPExcel->setActiveSheetIndex(0);
$G->getProtection()->setSheet(true);

// for 1st row heading subject heading setting 
$objPHPExcel->getActiveSheet()->mergeCells("A1:B1")->setCellValueByColumnAndRow(0,1, "Subject");
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1:B1')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);

// for 2nd row heading subject heading setting 

$objPHPExcel->getActiveSheet()->mergeCells("A2:B2")->setCellValueByColumnAndRow(0,2, "Term");
$objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A2:B2')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);

// for 3rd row heading subject heading setting 
$objPHPExcel->getActiveSheet()->mergeCells("A3:B3")->setCellValueByColumnAndRow(0,3, "Exam");
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A3:B3')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(40);

// for 4th row heading subject heading setting 
$objPHPExcel->getActiveSheet()->setCellValue('A4', "ROLL");
$objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A4')->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->setCellValue('B4', "NAME");
$objPHPExcel->getActiveSheet()->getStyle('B4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B4')->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->getActiveSheet()->mergeCells($StartcolumnIndex.$row.':'.$EndcolumnIndex.$row)->setCellValueByColumnAndRow($coumnStart,1, $subject);
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.$row)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.$row.':'.$EndcolumnIndex.$row)->applyFromArray($styleArray);

// 2nd  row for Term1
$EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($coumnStart-1) + 6);

$objPHPExcel->getActiveSheet()->mergeCells($StartcolumnIndex.($row+1).':'.$EndcolumnIndex2.($row+1))->setCellValueByColumnAndRow($coumnStart,2, 'TERM 1');
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+1).':'.$EndcolumnIndex2.($row+1))->applyFromArray($styleArray);

//for FA1 Sem setting param
$semIndex=$coumnStart;
$EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($semIndex-1) + 2);
$objPHPExcel->getActiveSheet()->mergeCells($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))->setCellValueByColumnAndRow($semIndex,3, 'FA1');
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))->applyFromArray($styleArray);
$semIndex=$semIndex+2;

//for FA2
$StartcolumnIndex = PHPExcel_Cell::stringFromColumnIndex($semIndex);
$EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($semIndex-1) + 2);
$objPHPExcel->getActiveSheet()->mergeCells($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))->setCellValueByColumnAndRow($semIndex,3, 'FA2');
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))->applyFromArray($styleArray);
$semIndex=$semIndex+2;

//for SA1
$StartcolumnIndex = PHPExcel_Cell::stringFromColumnIndex($semIndex);
$EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($semIndex-1) + 2);
$objPHPExcel->getActiveSheet()->mergeCells($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))->setCellValueByColumnAndRow($semIndex,3, 'SA1');
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))->applyFromArray($styleArray);
$semIndex=$semIndex+2;

// for Term2
$StartcolumnIndex2=PHPExcel_Cell::stringFromColumnIndex(($coumnStart) + 6);
$EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex($coumnStart + 12);
$objPHPExcel->getActiveSheet()->mergeCells($StartcolumnIndex2.($row+1).':'.$EndcolumnIndex2.($row+1))->setCellValueByColumnAndRow($coumnStart+6,2, 'TERM 2');
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex2.($row+1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex2.($row+1).':'.$EndcolumnIndex2.($row+1))->applyFromArray($styleArray);

//FA3
$StartcolumnIndex = PHPExcel_Cell::stringFromColumnIndex($semIndex);
$EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($semIndex-1) + 2);
$objPHPExcel->getActiveSheet()->mergeCells($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))->setCellValueByColumnAndRow($semIndex,3, 'FA3');
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))->applyFromArray($styleArray);
$semIndex=$semIndex+2;

//FA4
$StartcolumnIndex = PHPExcel_Cell::stringFromColumnIndex($semIndex);
$EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($semIndex-1) + 2);
$objPHPExcel->getActiveSheet()->mergeCells($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))->setCellValueByColumnAndRow($semIndex,3, 'FA4');
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))->applyFromArray($styleArray);
$semIndex=$semIndex+2;

//SA2
$StartcolumnIndex = PHPExcel_Cell::stringFromColumnIndex($semIndex);
$EndcolumnIndex2 = PHPExcel_Cell::stringFromColumnIndex(($semIndex-1) + 2);
$objPHPExcel->getActiveSheet()->mergeCells($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))->setCellValueByColumnAndRow($semIndex,3, 'SA2');
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($StartcolumnIndex.($row+2).':'.$EndcolumnIndex2.($row+2))->applyFromArray($styleArray);
$semIndex=$semIndex+2;

// every 13th column total marks & precentage
$totalMarksColumIndex=PHPExcel_Cell::stringFromColumnIndex($coumnStart + 12);
$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($coumnStart+12,3, 'TOTAL MARKS');
$objPHPExcel->getActiveSheet()->getStyle($totalMarksColumIndex.($row+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($totalMarksColumIndex.($row+2))->applyFromArray($styleArray);

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($coumnStart+12,4, 'PERCENTAGE');
$objPHPExcel->getActiveSheet()->getStyle($totalMarksColumIndex.($row+3))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle($totalMarksColumIndex.($row+3))->applyFromArray($styleArray);
$objPHPExcel->getActiveSheet()->getColumnDimension($totalMarksColumIndex)->setWidth(20);

// putting marks column heading
$flag=1;
for($i=$coumnStart;$i<$coumnStart+12;$i=$i+1)
{
$MarksheadingIndex=PHPExcel_Cell::stringFromColumnIndex($i);    
 if($flag%2==1) 
  {
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i,4, 'MARKS');
   $objPHPExcel->getActiveSheet()->getStyle($MarksheadingIndex)->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);
  }
 else 
  {
   $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i,4, 'MAXMARKS');
    // setting the max marks
      for($j=5;$j<count($totalStudents)+5;$j++){
        if($flag%6==0)
        {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i,$j, $Sa1Sa2marks);    
        }
        else if($flag==2 || $flag==8)
        {
         $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i,$j, 40);  
        }
        else
        {
         $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i,$j, 20);     
        }
      }
  }

 $objPHPExcel->getActiveSheet()->getStyle($MarksheadingIndex.($row+3))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
 $objPHPExcel->getActiveSheet()->getStyle($MarksheadingIndex.($row+3))->applyFromArray($styleArray); 
 $objPHPExcel->getActiveSheet()->getColumnDimension($MarksheadingIndex)->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->getActiveSheet()->setCellValue('A'.$Line,  $student['admissionnumber']);
        $objPHPExcel->getActiveSheet()->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->save('php://output'); 

?>

If any Expert guide me how can I do this I will be thankful to all of them

Answer

Mark Baker picture Mark Baker · Jan 23, 2014

Read section 4.6.4 of the developer documentation, and examples like 03formulas.php.

You can write a formula as you'd write it in Excel itself, simply store the formula prefixed with = in the cell

e.g.

$objPHPExcel->getActiveSheet()
    ->setCellValue(
        'E10',
        '=SUM(A10:E9)'
    );

Will write the formula =SUM(A10:E9) in cell E10

When writing a formula to a cell, remember that:

  • Decimal separator is . (period)
  • Function argument separator is , (comma)
  • Matrix row separator is ; (semicolon)
  • English function names must be used

EDIT

for ($row = 1; $row <= 10; $row++) {
    $objPHPExcel->getActiveSheet()
        ->setCellValue(
            'J' . $row,
            '=SUM(A'.$row.':C'.$row.')/10 + SUM(D'.$row.':F'.$row.')/20 + SUM(G'.$row.':I'.$row.')/60'
        );
}

Related questions

How to use phpexcel to read data and insert into database?

I have a php application where I want to read data from excel, Insert into database and then generate pdf reports for specific users. I searched a lot but nothing specific given about both things.

Read More
PHPExcel auto size column width

I'm trying to auto size the columns of my sheet. I'm writing the file and in the end I try to resize all of my columns. // Add some data $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('B1', 'test1111111111111111111111') -&…

Read More
phpexcel to download

hello i am new to phpexcel, and i was wondering if there is some way send the excel i have created to the clients download without saving it on my server or to delete it right after he downloads it …

Read More