首先我们创建抽象工具类Import:
namespace app\common\tool;
abstract class Import
{
private static $instance = [];
/**
* @return static
*/
public static function getInstance()
{
$class = get_called_class();
if(!isset(self::$instance[$class]) || !self::$instance[$class] instanceof $class){
self::$instance[$class] = new static();
}
return self::$instance[$class];
}
}
然后我们定义一个抽象方法,要求子类必须返回和Excel表格中数据对应的字段
abstract protected function getFieldNames(): array;
实现Excel数据导入:
fileName: Excel 的地址
extraData: 额外的数据,和合并到每一行的数据内
public function import($fileName, $extraData = [])
{
if (!is_file($fileName)) {
throw new ToolException('文件不存在');
}
$spreadSheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fileName);
$importData = $this->getImportData($spreadSheet);
$importImages = $this->getImportImages($spreadSheet, $importData);
if (!empty($importImages)) {
foreach ($importImages as $image) {
$importData[$image['row']][$image['column']] = $image['image'];
}
}
return $this->formatData($importData, $extraData);
}
获取导入的图片数据:
private function getImportImages($spreadsheet)
{
$importImages = [];
foreach ($spreadsheet->getActiveSheet()->getDrawingCollection() as $drawing) {
list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());
$imageFileName = md5(microtime(true));
$fileName = Upload::getSavePath(strtolower(Request::instance()->controller()), 'img', false, true);
if(!is_dir(Upload::$rootPath.DS.$fileName)){
mkdir(Upload::$rootPath.DS.$fileName, 0777, true);
}
switch ($drawing->getExtension()) {
case 'jpg':
case 'jpeg':
$imageFileName .= '.jpg';
$source = imagecreatefromjpeg($drawing->getPath());
imagejpeg($source, $fileName .DS. $imageFileName);
break;
case 'gif':
$imageFileName .= '.gif';
$source = imagecreatefromgif($drawing->getPath());
imagegif($source, $fileName .DS. $imageFileName);
break;
case 'png':
$imageFileName .= '.png';
$source = imagecreatefrompng($drawing->getPath());
imagepng($source, $fileName.DS. $imageFileName);
break;
}
$startColumn = $this->ABC2decimal($startColumn);
$importImages[] = [
'row' => $startRow - 1,
'column' => $startColumn,
'image' => $fileName .DS. $imageFileName,
];
}
return $importImages;
}
public function ABC2decimal($abc)
{
$ten = 0;
$len = strlen($abc);
for ($i = 1; $i <= $len; $i++) {
$char = substr($abc, 0 - $i, 1);//反向获取单个字符
$int = ord($char);
$ten += ($int - 65) * pow(26, $i - 1);
}
return $ten;
}
获取导入的Excel 数字、文字数据:
private function getImportData($spreadSheet)
{
return $spreadSheet->getActiveSheet()->toArray(null, true, true, false);
}
对应Excel文件内的字段数据并合并入ExtraData:
protected function formatData($importData, $extraData)
{
$formatData = [];
foreach ($importData as $index => $import) {
$data = [];
if ($index == 0) {
continue;
}
foreach ($this->getFieldNames() as $index => $field) {
$data[$field] = isset($import[$index]) ?$import[$index]: '';
}
if (!empty($extraData)) {
$data = array_merge($extraData, $data);
}
$formatData[] = $data;
}
return $formatData;
}
我们来实现一个商品Excel导入的功能:
namespace app\common\tool\import;
use app\common\tool\Import;
class GoodsImport extends Import
{
protected function getFieldNames():array
{
return ['brand_name', 'top_cate_name', 'cate_name', 'import_product_name', 'goods_img', 'goods_spec', 'goods_unit', 'sale_price', 'on_sale'];
}
protected function formatData($importData, $extraData)
{
$data = parent::formatData($importData, $extraData);
foreach ($data as $index => $goods)
{
$data[$index]['goods_img'] = empty($goods['goods_img']) ? 'static/img/default.png' : $goods['goods_img'];
}
return $data;
}
}