php excel 单元格换行_笔记php

php Excel 导出

public function excelexport($filename, $data = [],$year) {
vendor('excel.PHPExcel');
$excel = new PHPExcel();
$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$excel->getActiveSheet()->setCellValue('A1', $year.'年度订单'); //设置 A1单元格的内容
$excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);//设置 A1单元格的字体为18
$excel->getActiveSheet()->mergeCells('A1:AB1'); //合并 A1到AB1单元格
$excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //设置 A1单元格水平居中
$k =0;
//mergeCellsByColumnAndRow ($i,$k,$m,$n) $i 第一个单元格的数字列坐标 $k 第一个单元格的数值行坐标 $m 最后单元格的数字列坐标 $n 最后单元格的数字行坐标
//setCellValueByColumnAndRow ($i,$k,$m) $i 单元格的数字列坐标 （A=0，B=1...） $k 单元格的数字行坐标 $m 单元格的值
for($i = 4; $i < 28; $i+=2){
$k++;
$excel->getActiveSheet()->mergeCellsByColumnAndRow($i,2,$i+1,2)->setCellValueByColumnAndRow($i, 2, $k.'月'); // 合并单元格，并设置值为12个月
}
$excel->getActiveSheet()->mergeCellsByColumnAndRow(0,2,0,3)->setCellValue('A2', '客户名称'); // 合并单元格，并添加内容
$excel->getActiveSheet()->mergeCellsByColumnAndRow(1,2,1,3)->setCellValue('B2', '所有人');
$excel->getActiveSheet()->mergeCellsByColumnAndRow(2,2,2,3)->setCellValue('C2', '总签约额');
$excel->getActiveSheet()->mergeCellsByColumnAndRow(3,2,3,3)->setCellValue('D2', '总回款额');
$excel->getActiveSheet()->getColumnDimension('A')->setWidth(18); // 设置 A 列宽度为18
for ($i = 4; $i < 28; $i++) {
$col = PHPExcel_Cell::stringFromColumnIndex($i); // A 列 为 0 ，将 $i 转化为 ABC...
$excel->getActiveSheet()->setCellValue($col . '3', $i % 2 == 1 ? '回款额' : '签约额');
$excel->getActiveSheet()->getColumnDimension($col)->setWidth(12);
}
$line = 3;
foreach ($data as $value) {
$line++;
$excel->getActiveSheet()->setCellValueExplicit('A' . $line, $value['custname_tx_os']); //设置 A4单元格的内容
$excel->getActiveSheet()->setCellValueExplicit('B' . $line, $value['owner_tx_os']);
$excel->getActiveSheet()->setCellValueExplicit('C' . $line, $value['amount_dm_def']);
$excel->getActiveSheet()->setCellValueExplicit('D' . $line, $value['back_dm_def']);
for ($kk = 4; $kk < 28; $kk++) {
$col = PHPExcel_Cell::stringFromColumnIndex($kk);
$excel->getActiveSheet()->setCellValueExplicit($col . $line, $value[$kk]);
}
$excel->getActiveSheet()->getStyle($col . $line)->getAlignment()->setWrapText(TRUE); // 自动换行
}
$styleThinBlackBorderOutline =['borders' => ['allborders' => ['style' => PHPExcel_Style_Border::BORDER_THIN,],],];//加边框
$excel->getActiveSheet()->getStyle('A1:AB' . $line)->applyFromArray($styleThinBlackBorderOutline);
$excel->getActiveSheet()->getStyle('A3:AB' . $line)->getAlignment()->setWrapText(TRUE)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //居中
$excel->getActiveSheet()->getStyle('A2:AB' . $line)->getAlignment()->setWrapText(TRUE)->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //居中
//清空缓存
ob_end_clean();
// 头部
header('Pragma: public');
header('Expires: 0');
header('Cache-Control:must-revalidate, post-check=0, pre-check=0');
header('Content-Type:application/force-download');
header('Content-Type: application/vnd.ms-excel;charset=utf-8');
header('Content-Type:application/octet-stream');
header('Content-Type:application/download');
header('Content-Disposition:attachment;filename=' . $filename . '.xls');
header('Content-Transfer-Encoding:binary');
$writer->save('php://output');
exit;
}

导出格式

php7.4和js前段和后台密码加密解密