thinkphp6 导出Excel表单

发布日期:2022-06-01浏览次数:0 所属栏目: 常见问题

 1.下载安装地址 https://github.com/PHPOffice/PhpSpreadsheet


2. 引用 

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

3.实例化

$phpexcel=new Spreadsheet();
$phpexcel->setActiveSheetIndex(0);
$sheet=$phpexcel->getActiveSheet();

4.数据组合

$sheet->setTitle('订单明细');
$sheet->setCellValue('A1','姓名')
    ->setCellValue('B1','手机号')
    ->setCellValue('C1','订单号')
    ->setCellValue('D1','收货信息')
    ->setCellValue('F1','属性')
    ->setCellValue('G1','金额')
    ->setCellValue('D2','收货人')
    ->setCellValue('E2','地址')
    ->setCellValue('G2','支付金额')
    ->setCellValue('H2','优惠劵')
    ->setCellValue('I2','会员优惠')
    ->setCellValue('J1','订单生成时间')
    ->setCellValue('K1','完成时间')
->setCellValue('L1','订单状态')
    ->setCellValue('M1','会员号')
    ->setCellValue('N1','商品名称')
    ->setCellValue('O1','商品规格');


$sheet->getStyle('A1:O2')->getAlignment()->setHorizontal( \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$sheet->getStyle('A1:O2')->getAlignment()->setVertical( \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$sheet->mergeCells('A1:A2')
    ->mergeCells('B1:B2')
    ->mergeCells('C1:C2')
    ->mergeCells('D1:E1')
    ->mergeCells('F1:F2')
    ->mergeCells('G1:I1')
    ->mergeCells('J1:J2')
    ->mergeCells('K1:K2')
    ->mergeCells('M1:M2')
    ->mergeCells('N1:N2')
    ->mergeCells('O1:O2')
->mergeCells('L1:L2');
$sheet->getStyle('A1:O2')->getFont()->setBold(true);
$sheet->getStyle('G1:I2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);


$sheet->getRowDimension(1)->setRowHeight(18);
$sheet->getRowDimension(2)->setRowHeight(18);
$sheet->getColumnDimension('A')->setWidth(12);
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setAutoSize(true);
$sheet->getColumnDimension('J')->setAutoSize(true);
$sheet->getColumnDimension('K')->setAutoSize(true);
$sheet->getColumnDimension('L')->setWidth(12);
$sheet->getColumnDimension('D')->setWidth(12);
$sheet->getColumnDimension('E')->setWidth(30);
$sheet->getStyle('E')->getAlignment()->setWrapText(true);
$sheet->getColumnDimension('O')->setWidth(25);
$sheet->getColumnDimension('N')->setWidth(30);
$sheet->getStyle('N')->getAlignment()->setWrapText(true);
$currow=0;
foreach ($res as $key=>$v){
    $currow=$key+3;
    $sheet->setCellValue('A'.$currow,$v['address']['name'])
        ->setCellValue('B'.$currow,$v['address']['phone'])
        ->setCellValue('C'.$currow,' '.$v['out_trade_no'])
        ->setCellValue('D'.$currow,$v['address']['name'])
        ->setCellValue('E'.$currow,$v['address']['address'].$v['address']['detail'])
        ->setCellValue('F'.$currow,$v['attr'])
        ->setCellValue('G'.$currow,$v['amount'])
        ->setCellValue('H'.$currow,$v['goods_tag'])
        ->setCellValue('I'.$currow,$v['price_popu'])
        ->setCellValue('J'.$currow,$v['time'])
        ->setCellValue('K'.$currow,$v['status_time'])
        ->setCellValue('L'.$currow,status($v['status']))
        ->setCellValue('M'.$currow,$v['payer'])
        ->setCellValue('N'.$currow,$v['description'])
        ->setCellValue('O'.$currow,attr($v['attr']).'-'.$v['uid'].'-'.$v['pid'].'-'.'购买件数'.$v['num']);
}

$sheet->setCellValue('A'.($currow+1),"合计");
$sheet->setCellValue('B'.($currow+1),"=sum(G3:G".$currow.")");
$sheet->setCellValue('C'.($currow+1),"完成交易额");
$sheet->setCellValue('D'.($currow+1),$this->status(3));
$sheet->setCellValue('E'.($currow+1),"待发货");
$sheet->setCellValue('F'.($currow+1),$this->status(1));
$sheet->setCellValue('G'.($currow+1),"退款金额");
$sheet->setCellValue('H'.($currow+1),$this->status('4,5,6,7'));
$sheet->setCellValue('I'.($currow+1),"待签收");
$sheet->setCellValue('J'.($currow+1),$this->status('2'));

$style_array = array(

    'borders' => array(

        'allborders' => array(

            'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN

        )

    )                                                                                                                                                       );


$sheet->getStyle('A1:O'.($currow+1))->applyFromArray($style_array);

$time=date('Y-m-d', time());
$filename="订单详情".$time;
$this->excelsave($phpexcel,$filename,'Xls');

5.浏览器下载

protected function excelsave($phpexcel,$filename,$format){
    // $format只能为 Xlsx 或 Xls
    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="
        . $filename . date('Y-m-d') . '.' . strtolower($format));
    header('Cache-Control: max-age=0');
    $objWriter = IOFactory::createWriter($phpexcel, $format);

    $objWriter->save('php://output');



}


上一篇:

下一篇:网站制作

导出Excel

如果您有什么问题,欢迎咨询技术员 点击QQ咨询