在ThinkPHP 6.0 中实现 excel 导入导出功能,需要使用第三方库或扩展,例如 phpspreadsheet。
目录
安装
项目目录下打开命令行,执行命令:
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导出完整实现》