ThinkPHP6 excel 导入功能完整实现

在ThinkPHP 6.0 中实现 excel 导入导出功能，需要使用第三方库或扩展，例如 phpspreadsheet。

目录

安装

加载类库

添加路由

实现代码

加载文件

获取xls文件内容

整理数据

完整代码

打印输出

总结

---

安装

项目目录下打开命令行，执行命令：

composer require phpoffice/phpspreadsheet

安装过程

$ composer require phpoffice/phpspreadsheet
Using version ^1.25 for phpoffice/phpspreadsheet
./composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 8 installs, 0 updates, 0 removals
  - Installing psr/http-factory (1.0.1): Loading from cache
  - Installing psr/http-client (1.0.1): Loading from cache
  - Installing markbaker/matrix (3.0.1): Downloading (100%)
  - Installing markbaker/complex (3.0.2): Downloading (100%)
  - Installing myclabs/php-enum (1.8.4): Downloading (100%)
  - Installing maennchen/zipstream-php (2.1.0): Downloading (100%)
  - Installing ezyang/htmlpurifier (v4.16.0): Downloading (100%)
  - Installing phpoffice/phpspreadsheet (1.25.2): Downloading (100%)
ezyang/htmlpurifier suggests installing cerdic/css-tidy (If you want to use the filter 'Filter.ExtractStyleBlocks'.)
ezyang/htmlpurifier suggests installing ext-tidy (Used for pretty-printing HTML)
phpoffice/phpspreadsheet suggests installing ext-intl (PHP Internationalization Functions)
phpoffice/phpspreadsheet suggests installing mpdf/mpdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing dompdf/dompdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing tecnickcom/tcpdf (Option for rendering PDF with PDF Writer)
phpoffice/phpspreadsheet suggests installing mitoteam/jpgraph (Option for rendering charts, or including charts with PDF or HTML Writers)
Writing lock file
Generating autoload files
> @php think service:discover
Succeed!
> @php think vendor:publish
File D:\tp_solve\config\trace.php exist!
Succeed!

加载类库

use PhpOffice\PhpSpreadsheet\IOFactory;

添加路由

上传文件改用post提交

Route::post('productImport', 'ProductOrder/importExcel');

实现代码

加载文件

通过上传文件的真实路径，来加载读取文件。

$spreadsheet = IOFactory::load($file->getRealPath());

获取xls文件内容

$sheet = $spreadsheet->getActiveSheet();

整理数据

$data = [];
foreach ($sheet->getRowIterator() as $row) {
    $rowIndex = $row->getRowIndex();
    // 不读取第一行 标题
    if ($rowIndex == 1) {
        continue;
    }
    $cellIterator = $row->getCellIterator();
    $row = [];
    foreach ($cellIterator as $cell) {
       $row[] = $cell->getValue();
    }
    $data[] = $row;
}

完整代码

/**
 * excel 导入
 * @return false|string
 */
public function importExcel()
{
    $file = request()->file('file');
    if (!$file) {
        print_r('请选择需要导入的文件');die;
    }

    // 加载文件
    $spreadsheet = IOFactory::load($file->getRealPath());
    $sheet = $spreadsheet->getActiveSheet();

    // 处理文件数据
    $data = [];
    foreach ($sheet->getRowIterator() as $row) {
        $rowIndex = $row->getRowIndex();
        // 不读取第一行 标题
        if ($rowIndex == 1) {
            continue;
        }
        $cellIterator = $row->getCellIterator();
        $row = [];
        foreach ($cellIterator as $cell) {
            $row[] = $cell->getValue();
        }
        $data[] = $row;
    }

    // 数据入库处理


    print_r($data);die;
}

打印输出

总结

使用PHPSpreadsheet对excel导入就到目前为止了，很简单就实现了读取文件中的信息转化为数据，剩下的就是对数据进行入库操作处理。

但是要注意，在实际使用中，可能需要对数据进行更多处理，并加以适当的验证。

导出如何做，点击《ThinkPHP6 excel导出完整实现》