set column name and column width using PHPExcel in codeigniter

Question

Good day, I'm struggling to find out how to set a column name before each value in PHPExcel with codeigniter. Since my PHPExcel works to get the value from database, my problem is how to set a column name of each column.

current output image:

My code:

       //load our new PHPExcel library
        $this->load->library('excel');
        //activate worksheet number 1
        $this->excel->setActiveSheetIndex(0);
        //name the worksheet
        $this->excel->getActiveSheet()->setTitle('Users list');

        // Trying to set a column name
        $this->excel->getActiveSheet()->SetCellValue('A1', 'ID');
        $this->excel->getActiveSheet()->SetCellValue('B1', 'FIRST NAME');
        $this->excel->getActiveSheet()->SetCellValue('C1', 'LAST NAME');
        $this->excel->getActiveSheet()->SetCellValue('D1', 'EMAIL');
        $this->excel->getActiveSheet()->SetCellValue('E1', 'TIME');
        // get all users in array formate
        $users = $this->User_info->getallusers();


        // read data to active sheet
        $this->excel->getActiveSheet()->fromArray($users);

        $filename='just_some_random_name.xlsx'; //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

        //save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
        //if you want to save it as .XLSX Excel 2007 format

        $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');

        //force user to download the Excel file without writing it to server's HD
                ob_end_clean();
        $objWriter->save('php://output');

I want the output look like this

One more problem that I've seen is the width of every column. can you fix it also for me?

Answer 1

$this->excel->getActiveSheet()->fromArray($users);

The default top-left cell for the fromArray() method is cell A1, and it will overwrite anything that is already in cell A1.... if you want to avoid overwriting the headers that you have already set in row 1, then you'll need to tell fromArray() to start at row 2, so the top-left cell will be A2

$this->excel->getActiveSheet()->fromArray($users, null, 'A2');

Answer 2

You can try this solution for export excel.

//load our new PHPExcel library
$this->load->library('excel');
//activate worksheet number 1
$this->excel->setActiveSheetIndex(0);
//name the worksheet
$this->excel->getActiveSheet()->setTitle('Users list');

$this->excel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
$this->excel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);


$this->excel->getActiveSheet()->getStyle("A1:E1")->applyFromArray(array("font" => array("bold" => true)));

$this->excel->setActiveSheetIndex(0)->setCellValue('A1', 'ID');
$this->excel->setActiveSheetIndex(0)->setCellValue('B1', 'FIRST NAME');
$this->excel->setActiveSheetIndex(0)->setCellValue('C1', 'LAST NAME');
$this->excel->setActiveSheetIndex(0)->setCellValue('D1', 'EMAIL ADDRESS');
$this->excel->setActiveSheetIndex(0)->setCellValue('E1', 'TIME');

// get all users in array formate
$this->excel->getActiveSheet()->fromArray($users, null, 'A2');

// read data to active sheet
$this->excel->getActiveSheet()->fromArray($users);

$filename='just_some_random_name.xlsx'; //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

//save it to Excel5 format (excel 2003 .XLS file), change this to 'Excel2007' (and adjust the filename extension, also the header mime type)
//if you want to save it as .XLSX Excel 2007 format

$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');

//force user to download the Excel file without writing it to server's HD
        ob_end_clean();
$objWriter->save('php://output');

you can add more style for heading column using applyFromArray.