thinkphp6导入和导出exl数据

安装

使用thinkphp6导入和导出exl需要安装phpoffice/phpspreadsheet

composer require phpoffice/phpspreadsheet

路径

安装完成后文件路径为/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet

控制器

<?php
namespace app\index\controller;

use app\BaseController;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;

use app\index\model\Order;

class Index extends BaseController
{


}

生成exl表格

public function index()
{
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setCellValue('A1','hello word ');
    $writer = new Xlsx($spreadsheet);
    $writer->save('hello.xlsx');
    return '生成exl表格成功！';
}

结果：

public/hello.xlsx

模型

<?php
namespace  app\index\model;
use think\Model;
class Order extends Model{

}

数据库

order表sql：

CREATE TABLE `order` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`order_number` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '订单编号',
	`total_amount` decimal(10,2) DEFAULT '0.00' COMMENT '订单总价',
	`consignee` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '收货人',
	`user_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '用户名称',
	PRIMARY KEY(`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

导入exl

public function import_db() {
	$reader = IOFactory::createReader('Xlsx');
	$reader->setReadDataOnly(TRUE);
	$spreadsheet = $reader->load('excel/order.xlsx');
	$worksheet = $spreadsheet->getActiveSheet();
	$highestRow = $worksheet->getHighestRow();
	//总行数
	for ($row = 2;$row<=$highestRow;$row++) {
		$order_number = $worksheet->getCellByColumnAndRow(1,$row)->getValue();
		$total_amount = $worksheet->getCellByColumnAndRow(2,$row)->getValue();
		$consignee = $worksheet->getCellByColumnAndRow(3,$row)->getValue();
		$user_name = $worksheet->getCellByColumnAndRow(4,$row)->getValue();
		$data = [
		                'order_number'=>$order_number,
		                'total_amount'=>$total_amount,
		                'consignee'=>$consignee,
		                'user_name'=>$user_name
		            ];
		Order::create($data);
	}
	echo 'import success!';
	exit;
}

导出exl

public function export_tb(){
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();

        //表格名字
        $worksheet->setTitle('订单表');
        //单元内容
        $worksheet->setCellValueByColumnAndRow(1,1,'订单列表');
        $worksheet->setCellValueByColumnAndRow(1,2,'订单标号');
        $worksheet->setCellValueByColumnAndRow(2,2,'订单总价');
        $worksheet->setCellValueByColumnAndRow(3,2,'收货人');
        $worksheet->setCellValueByColumnAndRow(4,2,'用户名称');

        //合并单元格
        $worksheet->mergeCells('A1:D1');
        $styleArray = [
            'font'=>[
                'bold'=>true
            ],
            'alignment'=>[
                'horizontal'=>Alignment::HORIZONTAL_CENTER,
            ],
        ];
        //设置单页样式
        $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);
        $worksheet->getStyle('A2:D2')->applyFromArray($styleArray)->getFont()->setSize(14);
        $rows = Order::select();

        //数据总条数
        $len = Order::count();//$rows->count();

        //表数据
        $rows = $rows->toArray();

        $j = 0;
        for($i = 0;$i<$len;$i++){
            $j = $i+3;//从表格第3行开始
            $worksheet->setCellValueByColumnAndRow(1,$j,$rows[$i]['order_number']);
            $worksheet->setCellValueByColumnAndRow(2,$j,$rows[$i]['total_amount']);
            $worksheet->setCellValueByColumnAndRow(3,$j,$rows[$i]['consignee']);
            $worksheet->setCellValueByColumnAndRow(4,$j,$rows[$i]['user_name']);
        }
        $styleArrayBody = [
            'borders'   =>[
                'allBorders'    =>[
                    'borderStyle'=>Border::BORDER_THIN,//边框
                    'color'=>['argb'=>'666666'],
                ],
                'alignment'=>[
                    'horizontal'=>Alignment::HORIZONTAL_CENTER,//居中
                ]
            ]
        ];
        //设置列宽
        $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);
        $total_rows = $len+2;
        //添加所有边框并字体设置居中
        $worksheet->getStyle('A1:D'.$total_rows)->applyFromArray($styleArrayBody);

        $filename = '订单表.xlsx';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
}