在 Symfony Command中自定义脚本把Excel数据导入到数据库中
// 注:只是在此做下记录,有兴趣的可以参考,不做实际教程文档
<?php
/**
* Created by IntelliJ IDEA.
* User: davis
* Date: 2019-03-12
* Time: 16:01
*/
namespace CommonBundle\Command;
use AdminBundle\Entity\LiteList;
use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Wiz\AdminTools\Bundle\AdminToolsBundle\Entity\DictionaryTranslation;
class ImportLiteListCommand extends ContainerAwareCommand
{
protected function configure()
{
$this
->setName('isa:import-lite-list')
->addArgument('path', InputArgument::OPTIONAL, '键入Excel文件路径!')
->setDescription('import lite List data');
}
protected function execute(InputInterface $input, OutputInterface $output)
{
$em = $this->getContainer()->get('doctrine.orm.entity_manager');
$path = $input->getArgument('path');
if (file_exists($path) && is_dir($path) == false && pathinfo($path, PATHINFO_EXTENSION) == 'xlsx') {
//导入数据前,先清空表,并把自增id重置
$emConn = $this->getContainer()->get('doctrine.orm.entity_manager')->getConnection();
$emConn->executeQuery("DELETE FROM `isa_lite_list`");
$emConn->executeQuery("alter table `isa_lite_list` auto_increment =1;");
$emConn->executeQuery("alter table `isa_lite_list_translation` auto_increment =1;");
$excel = new \PHPExcel();
try {
$inputFileType = \PHPExcel_IOFactory::identify($path);
$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
$excel = $objReader->load($path);
} catch (\Exception $e) {
$output->writeln('<error>文件加载失败!</error>');
die;
}
$sheet = $excel->getSheet(0);
$highestRow = $sheet->getHighestRow();
//读取到excel中数据
for ($i = 1; $i <= $highestRow; $i++) {
$title = $excel->getActiveSheet()->getCell("A" . $i)->getValue();
$energy = sprintf("%.2f", $excel->getActiveSheet()->getCell("B" . $i)->getValue());
$water = sprintf("%.2f", $excel->getActiveSheet()->getCell("C" . $i)->getValue());
//写入table中
$liteList = new LiteList();
$trans = new DictionaryTranslation();
$liteList->setSlug('Lite-' . $i);
$liteList->setStatus(2);
$liteList->setEnergySaving($water);
$liteList->setWaterSaving($energy);
$liteList->setInputAt(new \DateTime("-{$i} second"));
$trans->setLocale('en');
$trans->setTranslatable($liteList);
$liteList->translate('en')->setTitle($title);
$liteList->mergeNewTranslations();
$em->persist($liteList);
$em->flush();
if ($i == 1)
$output->writeln('<comment>数据导入中...</comment>');
}
$output->writeln('<info>Success!!</info>');
} else {
$output->writeln('<error>文件类型或路径有错,请检查!!</error>');
}
}
}
//help
//在命令行执行脚本,数据导入成功
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· 单线程的Redis速度为什么快?
· SQL Server 2025 AI相关能力初探
· 展开说说关于C#中ORM框架的用法!
· AI编程工具终极对决:字节Trae VS Cursor,谁才是开发者新宠?