using phpexcel in codeigniter

Question

i am using the phpexcel library to export my report to excel file. but i want to have my own excel file and use it with phpexcel. here is the code i used:

public function event_reportexcel($id)
{
    $this->load->library('excel');
    $this->excel->setActiveSheetIndex(0);
    $this->excel->getActiveSheet()->setTitle('test worksheet');
    $this->excel->getActiveSheet()->setCellValue('A1', 'This is just some text value');
    $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
    $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
    $this->excel->getActiveSheet()->mergeCells('A1:D1');
    $this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

    $filename='just_some_random_name.xls'; //save our workbook as this file name
    header('Content-Type: application/vnd.ms-excel'); //mime type
    header('Content-Disposition: attachment;filename="'.$filename.'"'); //tell browser what's the file name
    header('Cache-Control: max-age=0'); //no cache


    $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');  
    //force user to download the Excel file without writing it to server's HD
    $objWriter->save('php://output');
}

it will put my data in a new excel file, but i need this function to use a excel file as template.

possible duplicate of [How to open an Excel file with PHPExcel for both reading and writing?](http://stackoverflow.com/questions/8797103/how-to-open-an-excel-file-with-phpexcel-for-both-reading-and-writing) — kittycat, Apr 16 '13 at 14:51

score 4 · Accepted Answer · edited May 23 '17 at 11:59

4

You can open the file and edit it as you need then save it

$fileType = 'Excel5';
$fileName = 'testFile.xls';

// Read the file
$objReader = PHPExcel_IOFactory::createReader($fileType);
$objPHPExcel = $objReader->load($fileName);

// Change the file
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Hello')
            ->setCellValue('B1', 'World!');

// Write the file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $fileType);
$objWriter->save($fileName);

You can see this post for more details.

edited May 23 '17 at 11:59

Community

1
1

answered Apr 16 '13 at 14:31

Mohamed Nagy

1,054
1
11
31

I have used this code as well as every other example I can find on google to open a template file that contains a chart in Excel 2010. I changed the filetype to Excel2007 and added setIncludeCharts(TRUE) to both the reader and writer. In all examples I tried the spreadhseet is left corrupted and I removes the chart object when I try to open it. – PrestonDocks Apr 27 '14 at 20:41

score 0 · Answer 2 · edited Apr 06 '23 at 08:14

THis will help u

public function export() 
{
    $this->load->model('authors_model');
    $this->load->library('excel');

    $object = new PHPExcel();

    $object->setActiveSheetIndex(0);

    $table_columns = ['ID', 'first_name', 'last_name', 'Email', 'BirthDate', 'Added'];

    $column = 1;

    foreach ($table_columns as $dd) 
    {
        $object
            ->getActiveSheet()
            ->setCellValueByColumnAndRow($column,1, $dd);

        $column++;
    }

    $employee_data = $this->authors_model->get_all_data();

    $row_no = 2 ;
    foreach ($employee_data as  $value) 
    {
        $object->getActiveSheet()->setCellValueByColumnAndRow(0 ,$row_no,$value->id );
        $object->getActiveSheet()->setCellValueByColumnAndRow(1 ,$row_no,$value->first_name );
        $object->getActiveSheet()->setCellValueByColumnAndRow(2 ,$row_no,$value->last_name );
        $object->getActiveSheet()->setCellValueByColumnAndRow(3 ,$row_no,$value->email );
        $object->getActiveSheet()->setCellValueByColumnAndRow(4 ,$row_no,$value->birthdate );
        $object->getActiveSheet()->setCellValueByColumnAndRow(5 ,$row_no,$value->added );

        $row_no++;
    }

    ob_end_clean(); 

    header('Content-Type: application/vnd.ms-excel'); //mime type
    header("Content-Disposition: attachment; filename=\"filename.xls\"");
    header("Cache-Control: max-age=0");

    $objWriter = PHPExcel_IOFactory::createWriter($object, 'Excel5');
    $objWriter->save('php://output');
}

using phpexcel in codeigniter

2 Answers2