mysql将数导入数据库-mysql 数据导入

看过我之前分享的朋友应该知道，我之前用过如何快速使用vue-element-admin创建管理后台，需要导入大量数据，所以也研究了如何批量导入大量数据数据在后台。 由于源数据存储在excel文件中，数据结构比较复杂，一般的CSV导入方式有点力不从心。 在网上找了很多前后台的解决方案mysql将数导入数据库，但是后台解决方案中将excel导入mysql数据的方法主要是用Pythonmysql将数导入数据库，也有的直接用mysql可视化工具解决，比如用navicat导入. 但是因为这次的数据结构有些特殊，需要一个可编程的方案来定制，所以方案就更加局限了。 最后，我尝试了两种不同的 PHP 库，一种使用电子表格阅读器库，另一种使用 PhpSpreadsheet。 经过几次尝试，我选择了功能更强大的PhpSpreadsheet。

Php电子表格

什么是 PhpSpreadsheet

PhpSpreadsheet 是一个纯 PHP 编写的库。 它通过提供的类处理不同类型的电子表格文件读写功能，PhpSpreadsheet支持的电子表格格式比较丰富，是电子表格阅读器中最大的专注于数据读取的。 不同之处在于支持阅读和写作的能力。 以下是 PhpSpreadsheet 支持的电子表格格式。 虽然还有一些不支持写入，但支持所有主流文件格式：

PhpSpreadsheet 支持的电子表格类型

PhpSpreadsheet的环境支持与安装

PhpSpreadsheet对PHP版本要求稍高，需要7.2以上版本。 相对于电子表格阅读器的PHP 5.3版本要求，可能需要更新服务器的PHP版本，但是PHP7确实提供了很多不错的功能升级，所以将PHP版本更新到是一个不错的选择7 甚至更新的 8。

安装 PhpSpreadsheet

可以直接通过Composer安装PhpSpreadsheet，将PhpSpreadsheet的依赖库一步到位管理，直接composer require即可：

composer require phpoffice/phpspreadsheet

让我们从一个 Hello World 开始：

getActiveSheet();
$sheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');
?>

直接执行这个PHP后，会在同一个文件夹下生成一个hello world.xlsx。 推荐使用PHP命令行直接执行

php path/to/helloworld.php

指示

加载excel文件

在PhpSpreadsheet中，需要通过IOFactory的静态方法createReader创建一个reader对象。 创建时需要设置读取文件的格式，然后直接通过读取器对象的load方法加载需要的文件。 例如下面是xlsx文件的加载代码：

$inputFileType = 'Xlsx';
//    $inputFileType = 'Xls';
//    $inputFileType = 'Xml';
//    $inputFileType = 'Ods';
//    $inputFileType = 'Slk';
//    $inputFileType = 'Gnumeric';
//    $inputFileType = 'Csv';
$inputFileName = './sampleData/example1.xls';
/**  根据$inputFileType生成一个阅读器对象  **/
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
/**  加载 $inputFileName 并生成一个Spreadsheet对象  **/
$spreadsheet = $reader->load($inputFileName);

当然也可以通过更直接的方式加载文件，即通过IOFactory的静态方法load，该方法会尝试识别文件类型，然后加载对应的reader实例：

$inputFileName = './sampleData/example1.xls';

/** 加载 $inputFileName 并生成一个Spreadsheet对象  **/
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);

另一种方式是直接通过new创建一个reader对象，然后通过reader的load方法加载文件，如下：

$inputFileName = './sampleData/example1.xls';
/** 创建一个Xlsx阅读器对象  **/
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xml();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Ods();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Slk();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Gnumeric();
//    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
/** 加载 $inputFileName 并生成一个Spreadsheet对象 **/
$spreadsheet = $reader->load($inputFileName);

PhpSpreadsheet也提供了一些阅读器设置，比如设置文件数据只读，只加载自定义的工作表等，具体可以参考文档

其中，Spreadsheet对象是电子表格文件对应的可操作对象，后面可以直接调用该对象中的方法实现不同的目的。

获取对应的工作表

首先，对于包含多个工作表的电子表格文件，可以先获取工作表的个数，然后循环遍历所有的工作表。 当然，你也可以根据工作表的标题加载指定的工作表，或者获取当前活动的工作表，即在Excel中打开电子表格时当前显示的工作表。

/** 获取工作表数量 **/
$sheetCount = $spreadsheet->getSheetCount();

for($i=0; $i<$sheetCount; $i++)
{
	/** 根据下标来获取对应的工作表 **/
	$sheet = $spreadsheet->getSheet($i);
	/** 对每个工作表的操作 **/
}
/** 根据工作表名称获取工作表 **/
$sheet = $spreadsheet->getSheetByName('Sheet1');
/** 直接获取活动的工作表 **/
$sheet = $spreadsheet->getActiveSheet();

读取单元格数据

获取Sheet对象后，可以获取工作表中每个单元格的对象，最终获取单元格中的数据。 因为我这里主要是导入大量的数据，所以我先用它遍历所有有数据的单元格，完成所有的数据导入。 要遍历整个工作表中的数据，首先需要通过以下代码获取工作表中的总列数以及可以传递多少行数据：

// 获取相应工作表内，最高的行数和列数
$highestRow = $sheet->getHighestRow(); // 如： 10
$highestColumn = $sheet->getHighestColumn(); // 如： 'F'
// 最后需要将列数转换成数字，因为绝大多数电子表格列数是用字母来表示的
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // 如 5

一旦我们拥有最多的行数和列数，我们就可以使用一个简单的嵌套 For 循环来遍历整个工作表中的每个单元格：

for($row = 1;$row<=$highestRow;$row++)
{
	for($col = 1;$col<=$highestColumnIndex;$col++)
	{

		/** 获取单元格的对象 **/
		$cell = $sheet->getCellByColumnAndRow($col,$row);
		/** 获取单元格的数值 **/
		$value = $cell->getValue();
		/** 也可以是将方法串联起来 **/
		// $value = $sheet->getCellByColumnAndRow($col,$row)->getValue();
		/** 如果数据是通过一个公式得出的话，可以使用getCalculatedValue() **/
		// $calculatedValue = $sheet->getCellByColumnAndRow($col,$row)->getCalculatedValue();
		/** 如果需要格式化相应单元格内的数据，例如日期等，可以使用getFormattedValue() **/
		// $formatedValue = $sheet->getCellByColumnAndRow($col,$row)->getFormattedValue();
	}
} 

根据我的习惯，我直接使用下标来遍历这个工作表中的单元格。 其实PhpSpreadsheet也提供了遍历迭代器（Iterator）的方法，这样可以让代码更简洁。 代码如下：

foreach($sheet->getRowIterator() as $row)
{
	$cellIterator = $row->getCellIterator();
	/** 设置遍历所有单元格，就算单元格是空的 **/
	$cellIterator->setIterateOnlyExistingCell(FALSE);

	foreach($cellIterator as $cell)
	{
		$value = $cell->getValue();
	}
}

对于其他应用场景，直接通过工作表中的坐标值或行列对获取单元格的对象可能更直接，例如：

/** 获取坐标为A1的单元格内的值 **/
$value=$sheet->getCell('A1')->getValue();
/** 获取第1列 第5行的单元格内的值 **/
$value=$sheet->getCellByColumnAndRow(1,5)->getValue();

最后一种方式是通过一个范围获取范围内单元格的值，然后输出到数组中，如下：

/** 获取C3至E5内的数据
$dataArray = $sheet->rangeToArray(
        'C3:E5',     // 工作表内的单元格坐标范围
        NULL,        // 当单元格为空时，返回的值
        TRUE,        // 如果是公式，是否要计算值，等同使用getCalcuatedValue()
        TRUE,        // 是否需要使用格式化的数据，等同使用getFormattedValue()
        TRUE         // 输出数组是否使用表格单元格的行数和列数作为下标
    );

数据库操作

因为本次读取的数据需要插入到对应的数据库中，而且每个工作表中的数据列所代表的属性也不相同，所以还需要在第一行添加对应属性的名称表格，也就是遍历整个工作表，当它在第一行时，需要将这一行单元格的内容保存为key，然后可以使用对应的列数得到将后续行中对应的key添加到data数组中，最后，每行数据读取完毕后，处理数据库插入操作。 当然，如果导入的数据结构比较直观，也可以通过insert语句导入，可以大大提高数据写入的效率。

总结

这次主要分享PhpSpreadsheet数据读取的部分，也是这次自己项目中用到的部分。 总之，PhpSpreadsheet的功能比较齐全，文档也很齐全，包含了很多例子，但是目前核心的文档还是英文的，中文文档比较零散。 如果大家有兴趣了解更多PhpSpreadsheet的其他功能，欢迎在评论区讨论！

如果你对PhpSpreadsheet或者其他开发话题感兴趣，可以关注我！ 谢谢阅读！