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