PhpSpreadsheet 读取excel文件
安装
composer require phpoffice/phpspreadsheet
例子
class Vcard
{
public function index()
{
$file_name = "1";
$uploadwork = "C:\Users\Administrator\Desktop\城市\二线城市（30个）27575条\\";
$uploadfile = $uploadwork.$file_name.'.xlsx';
$reader = \PHPExcel_IOFactory::createReader('excel2007'); //设置以Excel5格式(Excel97-2003工作簿)
$PHPExcel = $reader->load($uploadfile); // 载入excel文件
$sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumm = $sheet->getHighestColumn(); // 取得总列数
$data = [];
for ($row = 2; $row <= $highestRow; $row++) //行号从1开始
{
for ($column = 'A'; $column <= $highestColumm; $column++) //列数是以A列开始
{
if (empty($sheet->getCell($column . $row)->getValue()) == false) {
if (empty($data[$row]) == false) {
$str = $sheet->getCell($column . $row)->getValue();
if (strlen($str)>11) {
$pieces = explode("|", $str);
$data[$row]['mobile'] = $pieces[0];
}else{
$data[$row]['mobile'] = $str;
}
} else {
$data[$row]['name'] = $sheet->getCell($column . $row)->getValue();
}
}
}
}
// dump($data);
$myfile = fopen($uploadwork.$file_name.".vcf", "w") or die("Unable to open file!");
foreach ($data as $key => $value) {
$all_str = $this->make_vcard($value['name'], $value['mobile']);
fwrite($myfile, $all_str);
}
fclose($myfile);
}
}
解析excel协程实例
<?php
namespace App\Store\Service\Client;
use App\System\Model\BillCommissJd;
use App\System\Model\BillCommissJdLog;
use App\System\Model\BillCommissJdtzLog;
use Mine\Exception\MineException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
/**
* 京东对账单
*
* @author jcleng
*/
class StaJDService
{
/**
* 导入京东对账明细
*
* @param string $tempFilePath 完整文件路径
* @param array $params 额外参数
* @return bool
* @author jcleng
*/
public function importJd($tempFilePath, $params = [])
{
if (empty($params['period'])) {
throw new MineException('请选择帐期!');
}
$check = BillCommissJd::where('period', $params['period'])->first();
if (!empty($check['status'])) {
throw new MineException('帐期完成, 不能再导入!');
}
$importId = time() . rand(1000, 9999);
if (strpos($tempFilePath, '.xlsx') !== false) {
$reader = IOFactory::createReader(IOFactory::READER_XLSX);
} else {
$reader = IOFactory::createReader(IOFactory::READER_XLS);
}
$PHPExcel = $reader->load($tempFilePath); // 载入excel文件
$sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumm = $sheet->getHighestColumn(); // 取得总列数
$result = [];
$head = [];
for ($row = 1; $row <= $highestRow; $row++) // 行号从1开始
{
for ($column = 'A'; $column <= $highestColumm; $column++) //列数是以A列开始
{
if ($row == 1) {
$head[] = (string)$sheet->getCell($column . $row)->getValue();
} else {
$column_idnex = Coordinate::columnIndexFromString($column); // chong 1
$string = $sheet->getCell($column . $row)->getValue();
if (is_object($string)) {
$string = (string)$string;
}
$result[$row - 1][$head[$column_idnex - 1]] = $string;
}
}
}
$head_val = array_values($head);
if (
!in_array('服务工单号', $head_val) ||
!in_array('服务工单完成/取消时间', $head_val) ||
!in_array('服务工单状态', $head_val) ||
!in_array('金额', $head_val)
) {
throw new MineException('表格内容异常!');
}
$row_space = [];
$jump = 1000; // 单个数组的最大值
$i = 0;
$datetime = date('Y-m-d H:i:s');
$wg = new \Swoole\Coroutine\WaitGroup();
$err = 0;
$err_msg = [];
try {
foreach ($result as $key => $row) {
$i = $i + 1;
// if ($i == 1) {
// continue;
// }
if ($row['服务工单状态'] != '已完工') {
continue;
}
$date_arr = $this->yMonth2arr($params['period'], true);
$time_str = $row['服务工单完成/取消时间'] ?? '';
if (is_numeric($time_str)) {
$toTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($time_str, date_default_timezone_get());
$time_str = date("Y-m-d H:i:s", $toTimestamp);
}
$period_date = date("Y-m-d", strtotime($time_str));
$period = date("Y-m", strtotime($time_str));
if (strtotime($time_str) >= strtotime($date_arr[0]) && strtotime($time_str) <= strtotime($date_arr[1])) {
// ok
} else {
continue;
}
// ! 入库数据
$row_space[] = [
'period_date' => $period_date,
'period' => $period,
'no' => $row['服务工单号'],
'status_str' => $row['服务工单状态'],
'time_str' => $time_str,
'amount' => $row['金额'],
'import_id' => $importId,
'created_at' => $datetime,
];
if (count($row_space) % $jump == 0) {
// 去使用
go(function () use ($wg, &$row_space, &$err, &$err_msg) {
$wg->add();
try {
BillCommissJdLog::upsert($row_space, 'no');
} catch (\Throwable $th) {
$err = $err + 1;
$err_msg[] = $th->getMessage();
throw $th;
} finally {
$wg->done();
}
});
$row_space = [];
}
}
if (!empty($row_space)) {
// 去使用
go(function () use ($wg, &$row_space, &$err, &$err_msg) {
$wg->add();
try {
BillCommissJdLog::upsert($row_space, 'no');
} catch (\Throwable $th) {
$err = $err + 1;
$err_msg[] = $th->getMessage();
throw $th;
} finally {
$wg->done();
}
});
}
$wg->wait();
} catch (\Throwable $e) {
while ($wg->count() > 0) {
\Swoole\Coroutine\System::sleep(1);
}
BillCommissJdLog::where('import_id', $importId)->delete();
throw new MineException('导入失败,' . $e->getMessage(), 500);
} finally {
// 尝试回滚
if (!empty($err)) {
// 删除已经入库的数据
while ($wg->count() > 0) {
\Swoole\Coroutine\System::sleep(1);
}
BillCommissJdLog::where('import_id', $importId)->delete();
if (!empty($err_msg[0])) {
preg_match("/Duplicate entry '([^']+)'/", $err_msg[0], $matches);
if (isset($matches[1])) {
throw new MineException('导入失败,' . $matches[1] . '存在重复!', 500);
}
}
throw new MineException('导入失败,可能存在重复-' . json_encode($err_msg), 500);
}
}
if (empty($row_space)) {
throw new MineException('无效订单数据，请核实后重试!');
}
$this->periodMake($params['period']);
return true;
}
/**
* 导入台账明细
*
* @param string $tempFilePath 完整文件路径
* @param array $params 额外参数
* @return bool
* @author jcleng
*/
public function importTz($tempFilePath, $params = [])
{
$importId = time() . rand(1000, 9999);
if (strpos($tempFilePath, '.xlsx') !== false) {
$reader = IOFactory::createReader(IOFactory::READER_XLSX);
} else {
$reader = IOFactory::createReader(IOFactory::READER_XLS);
}
$PHPExcel = $reader->load($tempFilePath); // 载入excel文件
$sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumm = $sheet->getHighestColumn(); // 取得总列数
$result = [];
$head = [];
for ($row = 1; $row <= $highestRow; $row++) // 行号从1开始
{
for ($column = 'A'; $column <= $highestColumm; $column++) //列数是以A列开始
{
if ($row == 1) {
$head[] = (string)$sheet->getCell($column . $row)->getValue();
} else {
$column_idnex = Coordinate::columnIndexFromString($column); // chong 1
$result[$row - 1][$head[$column_idnex - 1]] = (string)$sheet->getCell($column . $row)->getValue();
}
}
}
$head_val = array_values($head);
if (
!in_array('日期', $head_val) ||
!in_array('鉴定量', $head_val)
) {
throw new MineException('表格内容异常!');
}
if (count($result) > 1000) {
throw new MineException('数据数量异常!');
}
$row_space = [];
$jump = 1000; // 单个数组的最大值
$i = 0;
$datetime = date('Y-m-d H:i:s');
$wg = new \Swoole\Coroutine\WaitGroup();
$err = 0;
$err_msg = [];
try {
foreach ($result as $key => $row) {
$i = $i + 1;
// if ($i == 1) {
// continue;
// }
if (empty($row['日期'])) {
continue;
}
if ($row['鉴定量'] == "") {
continue;
}
if (is_numeric($row['日期'])) {
$toTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($row['日期'], date_default_timezone_get());
$row['日期'] = date("Y-m-d", $toTimestamp);
}
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $row['日期'])) {
throw new MineException("日期格式错误: YYYY-MM-DD");
}
// ! 理来说数据很少, 每条进行验证一下
$bill_commiss_status = BillCommissJd::where('period', date('Y-m', strtotime($row['日期'])))
->value('status');
if ($bill_commiss_status == 1) {
// 已经完成, 跳过
continue;
}
$has_jd = BillCommissJdLog::where('period', date('Y-m', strtotime($row['日期'])))
->count();
if (empty($has_jd)) {
throw new MineException("请先导入京东对账单!");
}
// ! 入库数据
$row_space[] = [
'period_date' => $row['日期'],
'period' => date('Y-m', strtotime($row['日期'])),
'num' => $row['鉴定量'] ?? 0,
'import_id' => $importId,
'created_at' => $datetime,
];
if (count($row_space) % $jump == 0) {
// 去使用
go(function () use ($wg, &$row_space, &$err, &$err_msg) {
$wg->add();
try {
BillCommissJdtzLog::upsert($row_space, 'period_date');
} catch (\Throwable $th) {
$err = $err + 1;
$err_msg[] = $th->getMessage();
throw $th;
} finally {
$wg->done();
}
});
$row_space = [];
}
}
if (!empty($row_space)) {
// 去使用
go(function () use ($wg, &$row_space, &$err, &$err_msg) {
$wg->add();
try {
BillCommissJdtzLog::upsert($row_space, 'period_date');
} catch (\Throwable $th) {
$err = $err + 1;
$err_msg[] = $th->getMessage();
throw $th;
} finally {
$wg->done();
}
});
}
$wg->wait();
} catch (\Throwable $e) {
while ($wg->count() > 0) {
\Swoole\Coroutine\System::sleep(1);
}
BillCommissJdtzLog::where('import_id', $importId)->delete();
throw new MineException('导入失败,' . $e->getMessage(), 500);
} finally {
// 尝试回滚
if (!empty($err)) {
// 删除已经入库的数据
while ($wg->count() > 0) {
\Swoole\Coroutine\System::sleep(1);
}
BillCommissJdtzLog::where('import_id', $importId)->delete();
if (!empty($err_msg[0])) {
preg_match("/Duplicate entry '([^']+)'/", $err_msg[0], $matches);
if (isset($matches[1])) {
throw new MineException('导入失败,' . $matches[1] . '存在重复!', 500);
}
}
throw new MineException('导入失败,可能存在重复-' . json_encode($err_msg), 500);
}
}
if (empty($row_space)) {
throw new MineException('无效订单数据，请核实后重试!');
}
return true;
}
/**
* 计算对账误差率
*
* @param int $tz_num 台账数量
* @param int $jd_num 京东订单数量
* @return float 4位小数不带百分比
* @author jcleng
*/
public function recPoint($tz_num, $jd_num)
{
if (empty($tz_num)) {
return 0;
}
$point = (($tz_num - $jd_num) / $tz_num);
return number_format($point, 4, '.', '');
}
/**
* 通过传入账期(年月)生成/更新数据
*
* @param string $period 账期(年月)
* @return bool
* @author jcleng
*/
public function periodMake($period)
{
if (!preg_match('/^\d{4}-\d{2}$/', $period)) {
throw new MineException("日期格式错误: YYYY-MM");
}
$check = BillCommissJd::where('period', $period)->count();
if (empty($check)) {
BillCommissJd::insert([
'period' => $period,
'created_at' => date('Y-m-d H:i:s'),
]);
}
return true;
}
/**
* 获取当前年月的第一天和最后一天
*
* @param string $yearMonth 2022-01
* @return array
* @author jcleng
*/
public function yMonth2arr($yearMonth, $and_time = false)
{
// 检查输入格式是否正确
if (!preg_match('/^\d{4}-\d{2}$/', $yearMonth)) {
throw new MineException("日期格式错误: YYYY-MM");
}
// 创建 DateTime 对象
$firstDay = new \DateTime("$yearMonth-01");
// 获取最后一天
$lastDay = clone $firstDay;
$lastDay->modify('last day of this month');
// 返回结果
return [
$firstDay->format('Y-m-d') . ($and_time ? ' 00:00:00' : ''),
$lastDay->format('Y-m-d') . ($and_time ? ' 23:59:59' : '')
];
}
}