PHPExcel Cells over lapping each other >setRowHeight(-1) auto cell height not working

Question

---

Update

Tested using PHPSpreadsheet I have this code below that I have tried. It seems to work on MSOffice Excel if I use xls when I write the file. Note not working with Libre Office does not auto resize row need it to work with libre office as well.

$spreadsheet->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);

foreach($spreadsheet->getActiveSheet()->getRowDimensions() as $rowID) { 
    $rowID->setRowHeight(-1); 
}

New Controller

<?php

require(APPPATH . 'vendor/autoload.php');

use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Events extends MX_Controller {

    public function test() {
        $spreadsheet = new Spreadsheet();

        $spreadsheet->getDefaultStyle()->getFont()->setName('Arial');
        $spreadsheet->getDefaultStyle()->getFont()->setSize(24);

        foreach(range('A','B') as $columnID) {
            $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
        }

        $spreadsheet->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);

        foreach($spreadsheet->getActiveSheet()->getRowDimensions() as $rowID) { 
            $rowID->setRowHeight(-1); 
        }

        $spreadsheet->setActiveSheetIndex(0)
                ->setCellValue("A1",'Firstname')
                ->setCellValue("B1",'Lastname');

        $spreadsheet->getActiveSheet()->setTitle('Users Information');

        $spreadsheet->setActiveSheetIndex(0);

        /* Here there will be some code where you create $spreadsheet */

        // redirect output to client browser
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="myfile.xls"');
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
        $writer->save('php://output');
                
        exit;

    }
}

---

Original Question

I am new to phpexcel When I download my file the cells are overlapping each other.

As you can see in image the are all bunched up when have large font size.

Question How can I make sure the cells are not over lapping each other. I tried file in libreoffice & msoffice excel and same issue.

I have tried still no change

foreach(range('A','D') as $columnID) {
   $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
            ->setAutoSize(true);
}
    
foreach (range('A', $objPHPExcel->getActiveSheet()->getHighestDataColumn()) as $col) {
  $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
}

foreach(range(1, 4) as $rowID) {
  $objPHPExcel->getActiveSheet()->getRowDimension($rowID)->setRowHeight(-1);
}

Controller

<?php

class Events extends MX_Controller {

    public function generate_excel() {

        $query = $this->db->get('event');
        $excelresults = $query->result_array();

        require (APPPATH . 'third_party/PHPExcel-1.8/Classes/PHPExcel.php');
        require (APPPATH . 'third_party/PHPExcel-1.8/Classes/PHPExcel/Writer/Excel2007.php');

        $objPHPExcel = new PHPExcel();

        $objPHPExcel->getProperties()->setCreator("");
        $objPHPExcel->getProperties()->setLastModifiedBy("");
        $objPHPExcel->getProperties()->setSubject("");
        $objPHPExcel->getProperties()->setCreator("");
        $objPHPExcel->getProperties()->setDescription("");

        $objPHPExcel->setActiveSheetIndex(0);

        $objPHPExcel->getActiveSheet()->SetCellValue("A1", 'Event');
        $objPHPExcel->getActiveSheet()->SetCellValue("B1", 'Event Title');
        $objPHPExcel->getActiveSheet()->SetCellValue("C1", 'Event Date');
        $objPHPExcel->getActiveSheet()->SetCellValue("D1", 'Event Start Time');

        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

        foreach(range('A','D') as $columnID) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)
                ->setAutoSize(true);
        }

        $headerstyle = array(
            'font'  => array(
            'size'  => 25,
            'name'  => 'Candara'
            )
        );

        $objPHPExcel->getActiveSheet()->getStyle('A1:D1')->applyFromArray($headerstyle);


        foreach (range('A', $objPHPExcel->getActiveSheet()->getHighestDataColumn()) as $col) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
        } 

        $excelrow = 2;

        foreach ($excelresults as $excelresult => $excelvalue) {

            $columnstyle = array(
                'font'  => array(
                'size'  =>25,
                'name'  => 'Candara'
                )
            );

            $objPHPExcel->getActiveSheet()->getStyle('A' . $excelrow . ':D' . $excelrow)->applyFromArray($columnstyle);


            $objPHPExcel->getActiveSheet()->SetCellValue("A" . $excelrow, $excelvalue['event']);
            $objPHPExcel->getActiveSheet()->SetCellValue("B" . $excelrow, $excelvalue['event_title']);
            $objPHPExcel->getActiveSheet()->SetCellValue("C" . $excelrow, $excelvalue['event_date']);
            $objPHPExcel->getActiveSheet()->SetCellValue("D" . $excelrow, $excelvalue['event_start_time']);

            $excelrow++;
        }

        ///exit();

        $filename = 'Bowling-Events-For-' . date('Y') . '.xlsx';

        $objPHPExcel->getProperties()->setTitle("Riwaka Bowling Club Events");

        header("Content-Type: application/vnd.ms-excel; charset=utf-8"); # Important 
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");

        header("Content-Disposition: attachment; filename=".$filename."");
        header("Content-Transfer-Encoding: binary");

        header("Pragma: no-cache");
        
        header("Expires: 0");
        
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Cache-Control: private",false);

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');

        exit();
    }
}

Answer 1

please check the code below, is working for me:

$excel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);

To change height of all rows to auto you can do:

foreach($xls->getActiveSheet()->getRowDimensions() as $rowID) { 
    $rowID->setRowHeight(-1); 
}