Как можно вставить формулу в лист excel, используя phpexcel

Я проверил множество сайтов для решения моей проблемы, но не нашел правильного решения. Я хочу вставить формулу, которая рассчитает общее количество баллов, полученных студентами. Знаки будут введены учителями. Я написал PHP-код, который загружает файл excel после вставки имен студентов из базы данных и maxmarks. Мой файл excel выглядит так любезно видеть изображение, поскольку у меня нет 10 репутации. Мне не разрешено вставлять изображение в сообщение, пожалуйста, перейдите по этой ссылке. Http://cbsecsnip.in/Capture.JPG Столбец «Знаки» пуст, где учитель будет отметки других данных извлекаются из базы данных, а столбцу Percentage нужна формула, которая автоматически вычисляет метки, когда учитель вводит метки. Столбец заполненной колонки и Процент защищен. Я использую PHPExcel. Вот PHP-код, который генерирует этот файл excel

<?php $host='localhost'; $user='vishal'; $pass='wh0FOR'; $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'); ?> 

Если какой-либо эксперт подскажет мне, как я могу это сделать, я буду благодарен всем им

• Предотвратите PHPExcel для вычисления значений при записи в файл
• PHPExcel Сохранить файл xls в определенной папке
• PHPexcel: удаление изображений
• Плагин PHPExcel не импортирует файл в cakephp
• phpexcel - использование его с помощью шаблона excel (диаграмма отсутствует) php

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

 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' ); } 

 $calculatRow=''; for($ctr=0;$ctr < count($marksColumnArr);$ctr++) { $calculatRow.='(($'.$marksColumnArr[$ctr].'##/$'.$maXmarksColumnArr[$ctr].'$##)*'.$divisionVal[$ctr].')+'; } $calculateArrbyRow=substr($calculatRow,0,-1); // Replicate formula by row for($j=5;$j<count($totalStudents)+5;$j++){ $formula=str_replace('##',$j,$calculateArrbyRow); $cell=PHPExcel_Cell::stringFromColumnIndex($coumnStart+12); $objPHPExcel->getActiveSheet()->setCellValue($cell.$j, '='.$formula); } $coumnStart=($coumnStart-1)+13; $subjectCtr++; }