PHP Excel CodeIgniter

hello, programmers.

Would you help me in this problem?

I am using PHPExcel to generate excel files.

Now my query looks like this.

MODEL:

Code:

class ReportModel extends CI_Model
{
 public function getReport() {
     $this->db->select('a.LnCode as Line Code, c.MachDesc as Machine');
     $this->db->from('tbl_Audit_H as a');
     $this->db->join('tbl_Audit_D as b', 'a.ChkNum = b.ChkNum');
     $this->db->join('tbl_Machines as c', 'a.MachCode = c.MachCode');
   $result = $this->db->get();

   return $result->result_array();
 }

 public function getMachine() {
     $result =  $this->db->get('tbl_Machines');
     return $result->result_array();
 }
}

CONTROLLER:

Code:

 public function getReport() {
   $machines = $this->reportmodel->getMachine();
   $results = $this->reportmodel->getReport();
   $objPHPExcel = new PHPExcel();
   /* Cache Results */
   $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory;
   PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
   
   foreach ($machines as $machine) {
     // Create a new worksheet called “My Data”
     $myWorkSheet = new PHPExcel_Worksheet($objPHPExcel, $machine['MachDesc']);
     // Attach the “My Data” worksheet as the first worksheet in the PHPExcel object
     $objPHPExcel->addSheet($myWorkSheet);
     $objPHPExcel->setActiveSheetIndexByName($machine['MachDesc']);
     $activeSheet = $machine['MachDesc'];


   }
   $sheetIndex = $objPHPExcel->getIndex($objPHPExcel->getSheetByName('Worksheet'));
   $objPHPExcel->removeSheetByIndex($sheetIndex);
   
   header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
   header('Content-Disposition: attachment;filename="myfile.xlsx"');
   header('Cache-Control: max-age=0');
   $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
   $objWriter->save('php://output');

 }

Now I have attached an excel file. That is how the client wants the report to look like.

So basically the report create a sheet per machine.

It fills each sheet with the data related to that machine. 

It groups the result per line number, then per product then per date.

Please see the attached file. Thanks so much. I hope I explain myself well. If you have question for clarification please let me know. Thanks!

Attached Files

  Checklist(With Findings) Summary.xlsx (Size: 27.31 KB / Downloads: 137)

In your model you need to make another method that reads data for a specific only.

Code:

public function getMachineReport($id) {

    $id = (int) $id;

    $this->db->select('a.LnCode as Line Code, c.MachDesc as Machine');
    $this->db->from('tbl_Audit_H as a');
    $this->db->join('tbl_Audit_D as b', 'a.ChkNum = b.ChkNum');
    $this->db->join('tbl_Machines as c', 'a.MachCode = c.MachCode');
    $this->db->where('id', $id); // See what your field name is here.
    $result = $this->db->get();

   return $result->result_array()
}

Then in your controller traverse data this way:

Code:

$objPHPExcel = new PHPExcel();

// ...

$machines = $this->reportmodel->getMachine();

foreach ($machines as $machine) {

    $machine_id = (int) $machine['id']; // See what is the actual name, 'id' or else.
    $machine_result = $this->reportmodel->getMachineReport($machine_id);

    // Add the specific machine result to its own sheet.
}

// ... Output the document.

exit;

I need also Excel export, but I've chosen https://github.com/PHPOffice/PhpSpreadsheet , it still has no stable release. At this point

"phpoffice/phpspreadsheet": "dev-develop#dfcab0c13f1a53d4bdaf1656ba29495ef94a1f26"

it is good enough for me.

I also need export functionality, but i've settled on https://github.com/mk-j/PHP_XLSXWriter as i've always run out of memory with PHPExcel due to the large data sets my users were exporting.