如何使用phpexcel在Excel工作表中插入公式 [英] How can insert formula in excel sheet using phpexcel
本文介绍了如何使用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.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
推荐答案
@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工作表中插入公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
