DEDE How to import excel data into the background in batches?
DEDE development tutorial for batch importing excel data into the backend article system
For some corporate websites or Taobao customer websites, it is often necessary to import some excel data Batch import to the website, saving time through batch import instead of entering and adding data one by one from the website background, has a certain reference value, interested friends can refer to
Recommended learning: Dream Weaver cms
For some corporate websites or Taobao customer websites, it is often necessary to import some excel data into the website in batches. This saves time through batch import instead of entering and adding one by one from the website background. data. This tutorial uses the example of importing excel data into the Dreamweaver system (dedecms) to develop batch data import. It is assumed that the excel data is imported into the Dreamweaver main table dede_archives (there are also micro tables and additional tables, and the main table is used here to represent these three tables) inside.
The principle of batch importing excel data: by querying excel, read the data one by one, and then import the data into the table through the php program we developed.
Through the above principles, we know that we can divide the batch import of excel data into two parts. One part is to develop a program to read data from excel, and the other part is to develop a program to insert the read data into Inside the database table dede_archives.
Development of program to read data from excel
In fact, a foreigner has already helped us develop this step. This foreigner developed a set of programs specifically for reading excel data tables called the PHPExcel class library.
This excel class library also includes some other functions, which are not introduced one by one here. You can download them and take a look. Here we only explain reading excel.
Insert the read data into the main table dede_archives
After reading the data through the class provided in the PHPExcel class library, this step is to insert the data into the database table, so , the development of this tutorial is actually developing this part; the development of the program to insert the data read from excel into the main table.
For the convenience of explanation, we call the file name of the data inserted into the database table excelinert.php. Next, we will enter the program development in this file and insert the read data into the database table.
Insert data development principle
1) Preparation before development:
Add a header encoding to excelinsert.php in order to unify the encoding to avoid garbled characters: header("Content-type:text/html;charset=utf8");
Introduce the public file common.inc.php of the DreamWeaver system. Why should we introduce this file? Before we insert the database table, the home page You need to connect to the database of the website. If you do not connect, you will not be able to insert database data. Please note that the path of the imported file must be changed to your own, because I installed the Dreamweaver system in dedecms, so the directory name dedecms is in the path. If you install it in the root directory, remove this path.
Set the error report, set the time zone, and set the program running time. Of course, this running time does not need to be set. Set the class inventory path of the PHPExcel class. Then, introduce the IOFactory.php file. This file is the most important. This function is processed through this file. The setting codes are as follows:
These codes are all provided in the PHPExcel class, so no further explanation is needed here.
2) Develop the data insertion code:
means to insert the data named test1 in excel through the file excelinert.php, and insert these data into the Dreamweaver database table dede_archvies, column In the table with ID 6, dopost=exdata means inserting data password, because if you make a judgment, then anyone can insert data into your database table. This is very scary, just like a website without The same goes for the backend. Is it very possible that anyone can enter the backend of the website? Similarly, if you don't add this password, anyone can insert data, which is unsafe.
So, before inserting data, first obtain the values of typeid, do and n through the $GET super array, and then compare to see if you have permission to insert into the database. The first judgment should be to judge the password. Is it correct? If this is not correct, there will be no further operations.
For the convenience of explanation, the complete code for excelinsert.php development is provided below:
<?php
header("Content-type:text/html;charset=utf8");
require_once('/../dedecms/include/common.inc.php');
error_reporting(E_ALL);
set_time_limit(0);
date_default_timezone_set('Europe/London');
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../../../Classes/');
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
if(!empty($_GET)){
$typeid = $_GET['typeid'];
$dopost = $_GET['do'];
if($dopost == "exdata"){
if(!empty($_GET['n'])){
$inputFileName = './'.$_GET['n'].'.xlsx';
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
$rowarr=array();
$dsql->Execute('all',"select bio2,bio1 from `dede_addonshop`");
while ($rowall = $dsql->GetArray('all')) {
$rowarr[]=$rowall;
}
foreach ($sheetData as $v) {
foreach ($rowarr as $vt) {
if(in_array($v['B'],$vt)&&$v['A']==$vt['bio1']){
echo "货号为：".$v['B']."<br>厂商为：".$v['A']."<br>的数据已在表中，请在excel文件里面删除这一条后再添加！";
exit;
}
}
}
//附加表插入数据前处理
$row = $dsql->GetOne("select aid,bio2 from `dede_addonshop` order by aid desc");
if(!empty($row)){
$aid = $row['aid'];
$bio2 = $row['bio2'];
}else{
$aid = 0;
$bio2 = '';
}
//处理重复问题
if($bio2==$sheetData[count($sheetData)]['B']){
ShowMsg("不能重复添加内容",'javascript:;');
exit;
}
//主表插入数据前处理
$arcrow = $dsql->GetOne("select id from `dede_archives` order by id desc");
if(!empty($arcrow)){
$arcid = $arcrow['id'];
}else{
$arcid = 0;
}
//微表插入数据前处理
$tinyrow = $dsql->GetOne("select id from `dede_arctiny` order by id desc");
if(!empty($tinyrow)){
$tinyid = $tinyrow['id'];
}else{
$tinyid = 0;
}
//找出最大的id
$id = max($aid,$arcid,$tinyid);
$alphalpha = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
//字段数量
$fieldnum = count($sheetData[1]);
$fields = $fieldvalue = '';
for ($i=0; $i < $fieldnum; $i++) {
$fields .= $sheetData[1][$alphalpha[$i]]. ',';
}
$fields = substr($fields, 0,-1);
//遍历数组
foreach ($sheetData as $value) {
$pubdate = GetMkTime(GetDateTimeMk(time()));
$click = mt_rand(50, 200);
if($value['A']=='bio1'|$value['A']=='厂商'){
continue;
}
$id = $id+1;
//获取字段值$value['A'];
for ($i=0; $i < $fieldnum; $i++) {
$fieldvalue .= " ,'".$value[$alphalpha[$i]]."' ";
}
//标题
$C = trim($value['C']);
//保存到主表
$senddate = time();
$arcquery = "INSERT INTO `dede_archives`(id,typeid,title,mid,channel,pubdate,senddate,click,ismake)VALUES ('$id','$typeid','$C','1','6','$pubdate','$senddate','$click','-1');";
$dsql->ExecuteNoneQuery($arcquery);
//保存到附近加表
$query = "INSERT INTO `dede_addonshop`(aid,typeid,$fields)
VALUES ('$id','$typeid'{$fieldvalue});";
$dsql->ExecuteNoneQuery($query);
$fieldvalue = '';
//保存到微表
$tinyquery = "INSERT INTO `dede_arctiny`(id,typeid,channel,mid,senddate)VALUES ('$id','$typeid','6','1','$senddate');";
$dsql->ExecuteNoneQuery($tinyquery);
}
$num = count($sheetData)-2;
ShowMsg("恭喜，成功插入 ".$num." 条数据！",'javascript:;');
}
}
}else{
echo "密码或文件名错误！您无权做任何操作！";
}
?>The above is the complete code for inserting the data in excel into the main database table dede_archives, which has been actually tested. There is no problem at all. If you can't make it, there may be a problem with the common.inc.php path you introduced and the PHPExcel class library Classess class you introduced. If there is any problem, please check these two places.
Code Analysis
1）第一步先判断问号后面是不是为空，即dopost=exdata&typeid=6&n=test1字符串是否为空，如果这个字符串都是空的话，其它，就不用操作了，直接通过出程序，显示：echo "密码或文件名错误！您无权做任何操作！";
只有当if(!empty($_GET))条件成立时，才有可能进入插入操作。
然后，获取typeid和do的值：
$typeid = $_GET['typeid']; $dopost = $_GET['do'];
因为下面要对要插入到数据库表中数据时还要进行口令的判断，所以，这里获取do的值是必须的，但是，这里为什么一起要获取栏目id的值呢？因为，这里基本上要到最后一步才使用，为什么不等到使用时再获取呢？如果密码不对或其它原因的话，不能进行插入操作，那不是白白获取了$typeid的值，从代码优化的角度，这样不是最优的，但是，大家请注意，因为，下面要对获取到的$GET的数组进行操作，所以，为了保险起见，这时大这个地方先获取到栏目id的值，这样可以保证不会在后面出错，当然，如果您在开发时，能保证不会出错可以在使用$typeid时再获取也可以。
然后，判断口令是否正确，即if($dopost=="exdata")是否为真，若为真说明我们在浏览器地址栏输入的口令就是正确的，否则，就是错误的，如果出错出退出程序不做任何操作。
如果正确，则获取n的值，文件excel的文件名$inputFileName。
然后，通过PHPExcel类获取到excel文件里面的数据保存到数组$sheetData里面。
2)判断要插入的数据是否已经存在于表dede_addonshop里面。
查询附加表dede_addonshop里面的字段bio2,bio1，并把查询出来的一维数组放到二维数组$rowall里面。
因为，把一条数据插入到织梦系统里面后，其实，是在织梦的主表、附加表和微表进入了插入操作，所以，在插入前都要先判断一下插入的数据是不是在这三个表中都已经存在了，如果已经存在了就不要进入插入操作了。
3）插入数据库表时最大数据id的判断
当向主表或附加表或微表加插入数据库前，还要判断一下这时主表或附加表或微表里面最大的id，把这个id查询出来，进入对比即：$id = max($aid,$arcid,$tinyid);
这样对比以后获取到的$id的值就是最大的，获取这个最大的$id后，就可以在这个$id的基本上增加$id，例如，当前表中最大的id为9,那么，下面我们在插入数据时对应的id值就是从10开始，这是非常重要的，如果不做这个判断，那么，就会把原来的数据给覆盖掉。
3）遍历数组$sheetData插入数据
因为，通过PHPExcel获取到的数据是保存在$sheetData二维数据里面，类似array(array('a','b'),array('b','c'))，所以，在遍历这个数组，然后，把这个数组里面的数据插入到织梦的主表、微表和附加表里面。
这里要注意$id = $id+1，这行代码为什么要做这行代码，是因为，如果没有这行代码那么，在插入数据时id的值一直是同一个，例如，$id=9，这样一直是9，这是不可能的，因为，一个网站面的每一篇文章的id都是不同的，如果全一样的话，可能网站里面显示数据就只有一条了，其它的全部被覆盖掉了。
The above is the detailed content of How does DEDE batch import excel data to the backend?. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
