I'm currently trying to set all borders for my spreadsheet, also formatting such as autosize.
My code below is working, for sheet 1. All other sheets inside the spreadsheet are completely untouched. I've been trying to get it to work with all other sheets inside this spreadsheet but with no luck.
Any ideas on how i can global set the formatting so that all sheets have borders and autosize? The layout of all sheets in this spreadsheet are all the same. I'm exporting to XLSX file.
Cheers,
/**autosize*/
for ($col = 'A'; $col != 'P'; $col++) {
$objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
}
/** Borders for all data */
$objPHPExcel->getActiveSheet()->getStyle(
'A2:' .
$objPHPExcel->getActiveSheet()->getHighestColumn() .
$objPHPExcel->getActiveSheet()->getHighestRow()
)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
/** Borders for heading */
$objPHPExcel->getActiveSheet()->getStyle(
'A1:O1'
)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
EXTRA QUESTION: I've currently set headings. The headings do appear on sheet 1, but do not appear in any other sheets.. is it possible to show the headings in all sheets? The headings are set in Row 1. and the results are from Row 2 down.
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Asset_id');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Asset_name');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Asset_type');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'Asset_make');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'Asset_model');
You can set a default style for the entire workbook (all worksheets):
$objPHPExcel->getDefaultStyle()
->getBorders()
->getTop()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getDefaultStyle()
->getBorders()
->getBottom()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getDefaultStyle()
->getBorders()
->getLeft()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getDefaultStyle()
->getBorders()
->getRight()
->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
or
$styleArray = array(
'borders' => array(
'allborders' => array(
'style' => PHPExcel_Style_Border::BORDER_THIN
)
)
);
$objPHPExcel->getDefaultStyle()->applyFromArray($styleArray);
And this can be used for all style properties, not just borders.
But column autosizing is structural
rather than stylistic
, and has to be set for each column on each worksheet individually.
EDIT
Note that default workbook style only applies to Excel5 Writer
for ($s=65; $s<=90; $s++) {
//echo chr($s);
$objPHPExcel->getActiveSheet()->getColumnDimension(chr($s))->setAutoSize(true);
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With