本文介绍了如何使用phpexcel在Excel工作表中插入公式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我已经检查了许多站点来解决我的问题，但是找不到合适的解决方案.我想插入一个公式，该公式将计算学生获得的总分数.分数将由老师输入.我已经写好了php代码，该代码是在从数据库和maxmarks中插入学生姓名后下载excel文件的.我的excel文件看起来像这样，请亲切地查看图像，因为我没有10信誉，所以我不允许在帖子中插入图像，请点击此链接 http://cbsecsnip.in/Capture.JPG 分数列为空白，教师将标记从数据库中获取的其他数据，百分比列需要一个公式，当教师输入分数时，该公式将自动计算分数.填充列和百分比列受到保护.我使用PHPExcel.这是生成此excel文件的php代码
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.JPGMarks 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
推荐答案
@Mark Baker感谢您展示我这样尝试和完成的方法-
@Mark Baker Thank you for show the way I tried and done like this -
$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++;
}
这篇关于如何使用phpexcel在Excel工作表中插入公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！