todas - ¿Cómo se puede insertar la fórmula en la hoja de Excel usando phpexcel?

todas las formulas de excel

Question

He revisado muchos sitios para encontrar una solución a mi problema, pero no he encontrado la solución adecuada. Quiero insertar una fórmula que calcule el total de calificaciones obtenidas por los estudiantes. Las marcas serán ingresadas por los maestros. He escrito el código php que está descargando el archivo de Excel después de insertar los nombres de los estudiantes de la base de datos y los valores máximos. Mi archivo de Excel se ve así. Por favor, vea la imagen, ya que no tengo reputación. No se me permite insertar la imagen en la publicación. Por favor, siga este enlace http://cbsecsnip.in/Capture.JPG columna de marcas está en blanco donde el profesor las marcas se obtienen otros datos de la base de datos y la columna Porcentaje necesita una fórmula que calcule las marcas automáticamente cuando el maestro ingrese las calificaciones. La columna rellena y la columna de porcentaje están protegidas. Estoy usando PHPExcel. Aquí está el código PHP que está generando este archivo de Excel

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

?>

Si algún experto me guía, ¿cómo puedo hacer esto? Estaré agradecido a todos.

Answer 1

@Mark Baker Gracias por mostrar la forma en que lo intenté y lo hice de esta manera:

$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++;
}

Answer 2

Lea la sección 4.6.4 de la documentación del desarrollador y ejemplos como 03formulas.php .

Puede escribir una fórmula como la escribiría en Excel, simplemente almacene la fórmula con el prefijo = en la celda

p.ej

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

Escribirá la fórmula =SUM(A10:E9) en la celda E10

Al escribir una fórmula en una celda, recuerda que:

• Separador decimal es (período)
• El separador de argumento de función es , (coma)
• Matriz de separador de filas es ; (punto y coma)
• Deben usarse nombres de funciones en inglés

EDITAR

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