composer require phpoffice/phpspreadsheet

composer require phpoffice/phpspreadsheet --prefer-source

<?php/** * Created by PhpStorm. * Created by OwenZhang at 2021/11/8 14:39 */
namespace App\Common;
use Hyperf\HttpMessage\Stream\SwooleStream;use Hyperf\HttpServer\Response;
class Csv{    /**    $head = ['name'=>'名字','score'=>'得分'];    $data = [                ['name' => '张三', 'score' => '80'],                ['name' => '李四', 'score' => '90'],                ['name' => '王五', 'score' => '60'],            ];     $fileName = '测试'     */    /**     * Describe:   导数数据 （csv 格式）     * @param array $head     * @param array $body     * @param string $fileName '测试.csv','测试.xlsx'     * @return \Psr\Http\Message\ResponseInterface     * Created by lkz at 2021/11/8 14:47     */    static function export(array $head, array $body, string $fileName)    {        $head_keys = array_keys($head);        $head_values = array_values($head);        $fileData = self::utfToGbk(implode(',', $head_values)) . "\n";
        foreach ($body as $value) {            $temp_arr = [];            foreach ($head_keys as $key) {                $temp_arr[] = $value[$key] ?? '';            }            $fileData .= self::utfToGbk(implode(',', $temp_arr)) . "\n";        }        $response = new Response();        $contentType = 'text/csv';        return $response->withHeader('content-description', 'File Transfer')            ->withHeader('content-type', $contentType)            ->withHeader('content-disposition', "attachment; filename={$fileName}")            ->withHeader('content-transfer-encoding', 'binary')            ->withHeader('pragma', 'public')            ->withBody(new SwooleStream($fileData));    }
    /**     * 字符转换（utf-8 => GBK）     * @param $data     * @return false|string     */    static function utfToGbk($data)    {        return mb_convert_encoding($data,"GBK","UTF-8");       # return iconv('utf-8', 'GBK', $data);    }
}

/** * Describe: 列表导出 * Route: get /admin/badword_list_export * Created by OwenZhang at 2021/12/13 10:14 */public function getBadwordListExport(): ResponseInterface{    $page = (int)$this->request->input('page', 1);    $pageSize = (int)$this->request->input('page_size', 15);    $word = (string)$this->request->input('word', '');    $type = (string)$this->request->input('type', '');
    $container = ApplicationContext::getContainer();    $exportArray = $container->get(BadwordServiceInterface::class)->getBadwordListExport($page, $pageSize, $word, $type);    //$exportArray 数组，每个里面键值和$header一样           set_time_limit(0);    ini_set('memory_limit', '5048M');    $filename = '敏感词数据' . date('ymdHis');
    $header = [        'badword_id' => '敏感词id',        'word' => '敏感词',        'type' => '分类',        'style_name' => '应用区域',        'replace_word' => '替换词',    ];
    return Csv::export($header, $exportArray, $filename);}

<?php/** * Created by PhpStorm. * Created by OwenZhang at 2021/12/28 14:39 */
namespace App\Common;
use Hyperf\HttpMessage\Stream\SwooleStream;use Hyperf\HttpServer\Response;use PhpOffice\PhpSpreadsheet\IOFactory;use PhpOffice\PhpSpreadsheet\Spreadsheet;
class ExportExcelHandle{    private $sheet;    private $spreadsheet;    private $row;
    //构造函数 创建一个PhpSpreadsheet实例    public function __construct()    {        // Create new Spreadsheet object        $this->spreadsheet = new Spreadsheet();        // Set document properties        $this->spreadsheet->getProperties()->setCreator('Maarten Balliauw')            ->setLastModifiedBy('Maarten Balliauw')            ->setTitle('Office 2007 XLSX Test Document')            ->setSubject('Office 2007 XLSX Test Document')            ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')            ->setKeywords('office 2007 openxml php')            ->setCategory('Test result file');        // Add some data        $this->spreadsheet->setActiveSheetIndex(0);        $this->sheet = $this->spreadsheet->getActiveSheet();        // Rename worksheet        $this->spreadsheet->getActiveSheet()->setTitle('Sheet1');    }
    //设置表头    public function setHeader($title)    {        foreach ($title as $key => $item) {            $this->sheet->setCellValue(chr($key + 65) . '1', $item);        }        $this->row = 2; // 从第二行开始        return $this;    }
    //添加表内容    public function addData($data)    {        foreach ($data as $item) {            $dataCol = 'A';            foreach ($item as $value) {                // 单元格内容写入                $this->sheet->setCellValue($dataCol . $this->row, $value);                $dataCol++;            }            $this->row++;        }        return $this;    }
    //保存到服务器本地    public function saveToLocal($fileName)    {        // Set active sheet index to the first sheet, so Excel opens this as the first sheet        $this->spreadsheet->setActiveSheetIndex(0);
        $fileName = $fileName . '.xlsx';        $url = '/storage/' . $fileName;        $outFilename = BASE_PATH . $url;        $writer = IOFactory::createWriter($this->spreadsheet, 'Xlsx');        $writer->save($outFilename);        $this->spreadsheet->disconnectWorksheets();        unset($this->spreadsheet);        return ['path' => $outFilename, 'filename' => $fileName];    }
    //直接从浏览器下载到本地，有问题，不使用    // php://output 目前PhpSpreadsheet插件有问题，PhpSpreadsheet插件作者还在修复    public function saveToBrowser($fileName)    {        $fileName = $fileName . '.xlsx';        //xls='application/vnd.ms-excel'        //xlsx='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        $writer = IOFactory::createWriter($this->spreadsheet, 'Xlsx');        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');        header('Content-Disposition: attachment;filename="' . $fileName . '"');        header('Cache-Control: max-age=0');
        return $writer->save('php://output');    }
    //保存临时文件在从浏览器自动下载到本地    public function saveToBrowserByTmp($fileName)    {        $fileName = $fileName . '.xlsx';
        $writer = IOFactory::createWriter($this->spreadsheet, "Xlsx");        //保存到服务器的临时文件下        $writer->save("./tmp.xlsx");
        //将文件转字符串        $content = file_get_contents('./tmp.xlsx');
        //删除临时文件        unlink("./tmp.xlsx");
        $response = new Response();        //xls='application/vnd.ms-excel'        //xlsx='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'        $contentType = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
        return $response->withHeader('content-description', 'File Transfer')            ->withHeader('content-type', $contentType)            ->withHeader('content-disposition', "attachment; filename={$fileName}")            ->withHeader('content-transfer-encoding', 'binary')            ->withHeader('pragma', 'public')            ->withBody(new SwooleStream((string)$content));    }
}

 /**     * Describe: 列表导出     * Route: get /admin/badword_list_export     * Created by OwenZhang at 2021/12/13 10:14     */    public function getBadwordListExport()    {        set_time_limit(0);        ini_set('memory_limit', '5048M');
        $fileName = '敏感词数据' . date('YmdHis');        //表头        $title = [            '敏感词id',            '敏感词',            '分类',            '应用区域',            '替换词',        ];        //表体数据        $list = ($this->applicationContext::getContainer())->get(BadwordServiceInterface::class)->getBadwordListExport();        $data = [];        foreach ($list as $value) {            $data[] = [                $value['badword_id'],                $value['word'],                $value['type'],                $value['style_name'],                $value['replace_word'],            ];        }        $exportService = new ExportExcelHandle();//        //保存到服务器本地//        $result = $exportService->setHeader($title)->addData($data)->saveToLocal($fileName);//        //下载服务器的文件到本地//        return $this->response->download($result['path'], $result['filename']);
        //保存临时文件在从浏览器自动下载到本地        return $exportService->setHeader($title)->addData($data)->saveToBrowserByTmp($fileName);    }

/** * Describe: 列表导入 * Route: get /admin/badword_list_import * Created by OwenZhang at 2021/12/13 10:14 */public function getBadwordListImport(): ResponseInterface{    $file = $this->request->file('import_file')->toArray();    //获取上传文件的临时路径    if (!isset($file['tmp_file'])) {        throw new BusinessException(ErrorCode::BUSINESS_ERROR, "文件上传失败");    }    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file['tmp_file']);    //指定第一个工作表为当前    $data = $spreadsheet->getSheet(0)->toArray();            $container = ApplicationContext::getContainer();    $result = $container->get(BadwordServiceInterface::class)->getBadwordListImport($data);
    return $this->success($result);}
/** * Describe: 列表导入-批量添加数据到mysql * Route: get /admin/badword_list_import * @param array $data 数据 * @return string * Created by OwenZhang at 2021/12/16 10:14 */public function getBadwordListImportToMysql(array $data): string{    $insertData = [];    $badwordId = (int)$this->ZucaiBadwordModel->orderBy('badword_id', 'desc')->value('badword_id');
    foreach ($data as $key => $datum) {        //第一个数据是表头        if ($key != 0) {            $insertData[] = [                'badword_id' => $badwordId + $key + 1,                'word' => $datum[1],                'type' => $datum[2],                'style' => 1,                'replace_word' => '',                'app' => Context::get('app') ?? 1,                'created_at' => date('Y-m-d H:i:s'),                'updated_at' => date('Y-m-d H:i:s'),            ];        }    }
    if (!$insertData) {        return '添加失败';    }
    return $this->ZucaiBadwordModel->insert($insertData) ? '添加成功' : '添加失败';}