2019-03-05

继昨天完成的Excel表导出功能今天完成了Excel表导入功能，也是通过phpspreadsheet完成的附上代码：

<?php

require('../vendor/autoload.php');

require('./mysql.php');

set_time_limit(0);

$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');

$reader->setReadDataOnly(TRUE);

$url = $_FILES['inputExcel']['tmp_name'];

$spreadsheet = $reader->load($url);

$worksheet = $spreadsheet->getActiveSheet();

$highestRow = $worksheet->getHighestRow();

$highestColumn = $worksheet->getHighestColumn();

$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

if($highestRow < 2) {

exit('Excel表格中没有数据');

}

$tableId = $_POST['tableId'];

$sql = "SELECT id FROM customized_field WHERE fieldname = ? AND tableId = ?";

$stmt = $pdo->prepare($sql);

$arrFieldId = [];

for ($i=1; $i <= $highestColumnIndex; $i++) {

$fieldname = $worksheet->getCellByColumnAndRow($i, 1)->getValue();

$stmt->execute(array($fieldname, $tableId));

array_push($arrFieldId, $stmt->fetch(FN)[0]);

}

$sql = "INSERT INTO customized_data (fielddata, fieldId) VALUES (?,?)";

$stmt = $pdo->prepare($sql);

for ($i=2; $i <= $highestRow+1; $i++) {

for ($j=1; $j < $highestColumnIndex+1; $j++) {

$data = $worksheet->getCellByColumnAndRow($j,$i)->getValue();

$stmt->execute(array($data, $arrFieldId[$j-1]));

}

}

echo "SUCCESS";

由于数据大的时候需要显示进步条，所以今天在完成进步条功能，但是目前遇到了挺多问题正在解决中