method toExcel documentation in bbn\X
Creates an Excel file from a given array.
function(array $data, string $file, bool $with_titles = true, array $cfg = [])
{
if (!class_exists('\\PhpOffice\\PhpSpreadsheet\\Spreadsheet')) {
throw new Exception(X::_("You need the PhpOffice library to use this function"));
}
$excel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $excel->getActiveSheet();
$ow = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($excel);
$can_save = false;
if (empty($cfg)) {
$todo = [];
$checked = false;
foreach ($data as $d) {
if (!$checked && self::isAssoc($d)) {
if ($with_titles) {
$line1 = [];
$line2 = [];
foreach ($d as $k => $v) {
$line1[] = $k;
$line2[] = '';
}
$todo[] = $line1;
$todo[] = $line2;
}
$checked = true;
}
$todo[] = array_values($d);
}
if (count($todo)) {
$sheet->fromArray($todo, null, 'A1');
$excel->getDefaultStyle()->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);
$can_save = true;
}
}
else {
foreach ($cfg['fields'] as $i => $field) {
// Get cell object
$cell = $sheet->getCellByColumnAndRow($i + 1, 0);
// Get colum name
$col_idx = $cell->getColumn();
// Set auto width to the column
$sheet->getColumnDimension($col_idx)->setAutoSize(true);
// Cell style object
$style = $sheet->getStyle("$col_idx:$col_idx");
// Get number format object
$format = $style->getNumberFormat();
// Set the vertical alignment to center
$style->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
// Set the correct data type
switch ($field['type']) {
case 'integer':
// Set code's format to number
$format->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
break;
case 'decimal':
// Set code's format to decimal
$format->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00);
break;
case 'money':
// Set code's format to currency
$format->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
break;
case 'date':
// Set code's format to date
$format->setFormatCode('dd/mm/yyyy');
// Set the horizontal alignment to center
$style->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
break;
case 'datetime':
// Set code's format to datetime
$format->setFormatCode('dd/mm/yyyy hh:mm');
// Set the horizontal alignment to center
$style->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
break;
case 'boolean':
// Set the horizontal alignment to center
$style->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
break;
case 'phone':
// Set the custom format
$format->setFormatCode('+#');
break;
case 'string':
default:
// Set code's format to text
$format->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);
// Set wrap text
$style->getAlignment()->setWrapText(true);
break;
}
if ($with_titles) {
$cell = $sheet->getCellByColumnAndRow($i + 1, 1);
$style = $cell->getStyle();
// Set code's format to text
$style->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT);
// Set the horizontal alignment to center
$style->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
// Set bold to true
$style->getFont()->setBold(true);
// Set the column's title
$cell->setValue($field['title'] ?? $field['field']);
}
}
if (isset($cfg['map'], $cfg['map']['callable'])
&& is_callable($cfg['map']['callable'])
) {
array_walk($data, $cfg['map']['callable'], is_array($cfg['map']['params']) ? $cfg['map']['params'] : []);
}
$sheet->fromArray($data, null, 'A' . ($with_titles ? '2' : '1'));
$can_save = true;
}
if ($can_save
&& \bbn\File\Dir::createPath(self::dirname($file))
) {
$ow->save($file);
return \is_file($file);
}
return false;
}
Creates an Excel file from a given array. BBN is a suite of PHP and JS libraries and VueJS components - all open-source! bbn.io, build applications, the quick way
This website uses cookies to ensure you get the best experience on our website.
© 2011-2025
BBN Solutions
