1.引入依赖
composer require phpoffice/phpspreadsheet
2.在类中引入实例
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Font;
设置整体文字垂直居中
$newExcel = new Spreadsheet();
$newExcel->getDefaultStyle()->getAlignment()->setHorizontal('CENTER');
设置整体文字居中,左对齐
$newExcel = new Spreadsheet()
$objSheet = $newExcel->getActiveSheet();
$styleArray2 = ['alignment' => ['horizontal' => 'LEFT','vertical'=>'CENTER'],
];
$objSheet->getStyle('A5')->applyFromArray($styleArray2);
$objSheet->getStyle('A6')->applyFromArray($styleArray2);
设置整体文字居中,右对齐
$newExcel = new Spreadsheet()
$objSheet = $newExcel->getActiveSheet();
$styleArray3 = ['alignment' => ['horizontal' => 'RIGHT','vertical'=>'CENTER'],
];
$objSheet->getStyle('F5')->applyFromArray($styleArray3);
$objSheet->getStyle('G6')->applyFromArray($styleArray3);
创建文件,存入表格到指定文件
$objWriter = IOFactory::createWriter($newExcel, $format);
$file=ROOT_PATH . 'public' .DS . 'uploads' . DS . date('Ymd'). DS ;
if(!file_exists($file)){
mkdir($file);
}
$objWriter->save($file.$name . '.' . strtolower($format));
3.以存入送货单为列子,列子如下
function out($user_info,$custom_name,$gongyis,$jiner,$order_id){
ini_set('memory_limit','200M');
$newExcel = new Spreadsheet();
$objSheet = $newExcel->getActiveSheet();
$date = date('Ymd',time());
$name = '送货单_'.$custom_name['s_num'];
$objSheet->setTitle($name);
$mobile='电话-'.$user_info['mobile'];
$objSheet->mergeCells('A1:H1');
$objSheet->mergeCells('A2:H2');
$objSheet->mergeCells('A3:H3');
$objSheet->mergeCells('A4:H4');
$newExcel->getDefaultStyle()->getAlignment()->setHorizontal('CENTER');
$objSheet->setCellValue('A1', $user_info['company_name'])
->setCellValue('A2', $user_info['s_address'])
->setCellValue('A3',$mobile)
->setCellValue('A4', '送货单');
$objSheet->mergeCells('A5:B5');
$objSheet->mergeCells('F5:H5');
$objSheet->setCellValue('A5', '客户:'.$custom_name['username'])
->setCellValue('F5', '出货单号:'.$custom_name['s_num']);
$objSheet->mergeCells('A6:E6');
$objSheet->mergeCells('G6:H6');
$objSheet->setCellValue('A6', '送货地址:'.$custom_name['s_address'])
->setCellValue('G6', '日期:'.$custom_name['s_time']);
$styleArray2 = ['alignment' => ['horizontal' => 'LEFT','vertical'=>'CENTER'],
];
$objSheet->getStyle('A5')->applyFromArray($styleArray2);
$objSheet->getStyle('A6')->applyFromArray($styleArray2);
$styleArray3 = ['alignment' => ['horizontal' => 'RIGHT','vertical'=>'CENTER'],
];
$objSheet->getStyle('F5')->applyFromArray($styleArray3);
$objSheet->getStyle('G6')->applyFromArray($styleArray3);
$objSheet->setCellValue('A7', '订单号')
->setCellValue('B7','工艺')
->setCellValue('C7','规格')
->setCellValue('D7', '单位')
->setCellValue('E7', '数量')
->setCellValue('F7', '单价')
->setCellValue('G7', '金额')
->setCellValue('H7', '备注');
$font = [
'font' => [
'bold' => true,
'size' => 13,
],
];
$objSheet->getStyle('A2:M2')->applyFromArray($font);
header("content-type:text/html;charset=utf-8");
$dataCount = count($gongyis);
$k = 7;
if($dataCount == 0){
exit;
}else{
for ($i=0;$i<$dataCount;$i++){
$k = $k + 1;
$objSheet->setCellValue('A' . $k, $gongyis[$i]['order_sn'])
->setCellValue('B' . $k, $gongyis[$i]['gongyi'])
->setCellValue('C' . $k, $gongyis[$i]['guige'])
->setCellValue('D' . $k, $gongyis[$i]['danwei'])
->setCellValue('E' . $k, $gongyis[$i]['num'])
->setCellValue('F' . $k, $gongyis[$i]['danjia'])
->setCellValue('G' . $k, $gongyis[$i]['order_money'])
->setCellValue('H' . $k, $gongyis[$i]['remark']);
}
}
$objSheet->getStyle('A8:A13')->getNumberFormat()->setFormatCode('000000');
$objSheet->getStyle('A7:H13')->getBorders()->getAllBorders()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
$objSheet->mergeCells('G14:H14');
$objSheet->setCellValue('G14', '合计金额:'.$jiner)
;
$objSheet->mergeCells('A15:A16');
$objSheet->mergeCells('E15:H16');
$objSheet->setCellValue('A15', '送货经办人:')
->setCellValue('E15', '收货人签名盖章:')
;
$objSheet->getDefaultRowDimension()->setRowHeight(22);
$objSheet->getColumnDimension('A')->setWidth(25);
ob_end_clean();
ob_start();
$format='Xlsx';
if ($format == 'Xlsx') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
} elseif ($format == 'Xls') {
header('Content-Type: application/vnd.ms-excel');
}
header("Content-Disposition: attachment;filename=" . $name . '.' . strtolower($format));
header('Cache-Control: max-age=0');
$objWriter = IOFactory::createWriter($newExcel, $format);
$file=ROOT_PATH . 'public' .DS . 'uploads' . DS . date('Ymd'). DS ;
if(!file_exists($file)){
mkdir($file);
}
$ret=$objWriter->save($file.$name . '.' . strtolower($format));
Db::table('fg_orders_all')->where('id',$order_id)->update(['files'=>DS . 'uploads' . DS . date('Ymd'). DS.$name . '.' . strtolower($format)]);
return true;
}
生成的表格如下生成的表格如下](https://i-blog.csdnimg.cn/blog_migrate/f859ad3e550cc1fb1d740446ccb9f3fe.png)