PhpSpreadsheet
使用前,使用composer引入PhpSpreadsheet
composer require phpoffice/phpspreadsheet
方法:
- 导入转数组
- 导出为文件
- 导入文件存入数据库
<?php
/**
* Created by PhpStorm.
* User: WuNai
* Date: 2019/4/2/002
* Time: 16:57
*/
namespace common\helpers;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
class FileProcess
{
/**
* 文件 转 Array
* @param string $filePath 文件路径
* @return array
* @throws \PhpOffice\PhpSpreadsheet\Exception
*/
public static function toArray($filePath)
{
$spreadsheet = IOFactory::load($filePath);// 载入excel表格
$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数
$highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);
$data = [];
for ($row = 2; $row <= $highestRow; ++$row) { // 从第二行开始
$row_data = [];
for ($column = 1; $column <= $highestColumnIndex; $column++) {
$row_data[] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
}
$data[] = $row_data;
}
return $data;
}
/**
* Array 转 文件
* @param array $title 标题 格式['title1', 'title2']
* @param array $data 导出数据 ['A1' => '***', 'B2' => '***']
* @param string $fileName 导出文件名称
* @param array $options 配置 [ 'fileType' => 'xls', 导出文件后缀 默认 xls
* 'savePath' => '/web' 自定义保存地址
* ]
* @return bool
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
public static function toFile($title, $data, $fileName, $options = [])
{
/** 设置转义格式 */
if (isset($options['fileType']) && !in_array($options['fileType'], ['xls', 'xlsx', 'ods', 'csv', 'html', 'tcpdf', 'dompdf', 'mpdf'])) {
return false;
}
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
//设置标题名称
$worksheet->setTitle($fileName);
foreach ($title as $key => $value) {
$worksheet->setCellValueByColumnAndRow($key + 1, 1, $value);
}
$row = 2; //第二行开始
foreach ($data as $item) {
$column = 1;
foreach ($item as $value) {
$worksheet->setCellValueByColumnAndRow($column, $row, $value);
$column++;
}
$row++;
}
/** 设置文件后缀名 */
if (!isset($options['fileType'])) {
$fileName = $fileName . '.xls';
} else {
$fileName = $fileName . '.' . $options['fileType'];
}
/** 设置文件路径 */
if (!isset($options['savePath'])) {
$savePath = \Yii::$app->basePath . '/web/' . $fileName;
} else {
$savePath = $options['savePath'] . $fileName;
}
/** @var string $writerType 转义成PhpSpreadsheet能识别的后缀 */
$writerType = ucfirst($options['fileType']);
$writer = IOFactory::createWriter($spreadsheet, $writerType);
$writer->save($savePath);
/* 释放内存 */
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
ob_end_flush();
return true;
}
/**
* 导入文件插入数据库
* @param string $tableName 表名
* @param array $filed 字段名 [ 'create_id', 'create_time' ]
* @param string $filePath 文件路径
* @return bool|int
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \yii\db\Exception
*/
public static function toSql($tableName, $filed, $filePath)
{
$params = self::toArray($filePath);
$result = \Yii::$app->db->createCommand()->batchInsert($tableName, $filed, $params)->execute();
return $result;
}
}