常見問題

thinkphp6 導(dǎo)出Excel表單

常見問題

2653

字體:

 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.數(shù)據(jù)組合

$sheet->setTitle('訂單明細(xì)');
$sheet->setCellValue('A1','姓名')
    ->setCellValue('B1','手機號')
    ->setCellValue('C1','訂單號')
    ->setCellValue('D1','收貨信息')
    ->setCellValue('F1','屬性')
    ->setCellValue('G1','金額')
    ->setCellValue('D2','收貨人')
    ->setCellValue('E2','地址')
    ->setCellValue('G2','支付金額')
    ->setCellValue('H2','優(yōu)惠劵')
    ->setCellValue('I2','會員優(yōu)惠')
    ->setCellValue('J1','訂單生成時間')
    ->setCellValue('K1','完成時間')
->setCellValue('L1','訂單狀態(tài)')
    ->setCellValue('M1','會員號')
    ->setCellValue('N1','商品名稱')
    ->setCellValue('O1','商品規(guī)格');


$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'].'-'.'購買件數(shù)'.$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),"待發(fā)貨");
$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');



}


[聲明]原創(chuàng)不易,請轉(zhuǎn)發(fā)者備注下文章來源(hbsjsd.cn)【速建時代】。