This article mainly introduces the example of uploading Excel files to MySQL database using PHP. You can write Excel data into the MySQL database. Interested students can learn about it.
Recently I am importing data from Excel files into the database. If the website wants to support batch insertion of data, it can create a small program that uploads Excel files and imports the data content into the MySQL database.
Tools to be used:
ThinkPHP: lightweight domestic PHP development framework. It can be downloaded from ThinkPHP official website.
PHPExcel: A PHP class library for Office Excel documents, which is based on Microsoft's OpenXML standard and PHP language. It can be downloaded from the CodePlex official website. ,
1. Design MySQL database product
Create product database
CREATE DATABASE product DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Create pro_info table, table structure
CREATE TABLE pro_info( pId int(4) NOT NULL PRIMARY KEY AUTO_INCREMENT, pName varchar(20) NOT NULL, pPrice float NOT NULL, pCount float NOT NULL );
2. Generate the project
First create a new index.php file in the same directory as ThinkPHP and generate the project Home.
<?php define('APP_NAME', 'Home'); //项目名称 define('APP_PATH', './Home/'); //项目路径 define('APP_DEBUG', true); //开启DEBUG require './ThinkPHP/ThinkPHP.php'; //引入ThinkPHP核心运行文件 ?>
3. Upload File form
Create a new Index folder under the Home/Tpl folder, and create a new index.html file inside it
<!DOCTYPE html>
<html>
<head>
<title>上传文件</title>
<meta charset="UTF-8">
</head>
<body>
<form id="upload" action="__URL__/upload/" method="post" enctype="multipart/form-data">
<label for="file">上传文件:</label>
<input type="file" name="file" id="file"><br />
<input type="submit" name="submit" value="上传" />
</form>
</body>
</html>4. Write in /Home/Lib/Action/IndexAction.class.php Methods to display the upload form page, upload Excel files, and import Excel files (if there is no expansion package under ThinkPHP/Extend, you need to download it from the ThinkPHP official website, and then unzip the expansion package and put it in)
<?php
/**
*
* 导入Excel文件数据到MySQL数据库
*/
class IndexAction extends Action {
/**
* 显示上传表单html页面
*/
public function index() {
$this->display();
}
/**
* 上传Excel文件
*/
public function upload() {
//引入ThinkPHP上传文件类
import('ORG.Net.UploadFile');
//实例化上传类
$upload = new UploadFile();
//设置附件上传文件大小200Kib
$upload->mixSize = 2000000;
//设置附件上传类型
$upload->allowExts = array('xls', 'xlsx', 'csv');
//设置附件上传目录在/Home/temp下
$upload->savePath = './Home/temp/';
//保持上传文件名不变
$upload->saveRule = '';
//存在同名文件是否是覆盖
$upload->uploadReplace = true;
if (!$upload->upload()) { //如果上传失败,提示错误信息
$this->error($upload->getErrorMsg());
} else { //上传成功
//获取上传文件信息
$info = $upload->getUploadFileInfo();
//获取上传保存文件名
$fileName = $info[0]['savename'];
//重定向,把$fileName文件名传给importExcel()方法
$this->redirect('Index/importExcel', array('fileName' => $fileName), 1, '上传成功！');
}
}
/**
*
* 导入Excel文件
*/
public function importExcel() {
header("content-type:text/html;charset=utf-8");
//引入PHPExcel类
vendor('PHPExcel');
vendor('PHPExcel.IOFactory');
vendor('PHPExcel.Reader.Excel5');
//redirect传来的文件名
$fileName = $_GET['fileName'];
//文件路径
$filePath = './Home/temp/' . $fileName . '.xlsx';
//实例化PHPExcel类
$PHPExcel = new PHPExcel();
//默认用excel2007读取excel，若格式不对，则用之前的版本进行读取
$PHPReader = new PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($filePath)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filePath)) {
echo 'no Excel';
return;
}
}
//读取Excel文件
$PHPExcel = $PHPReader->load($filePath);
//读取excel文件中的第一个工作表
$sheet = $PHPExcel->getSheet(0);
//取得最大的列号
$allColumn = $sheet->getHighestColumn();
//取得最大的行号
$allRow = $sheet->getHighestRow();
//从第二行开始插入,第一行是列名
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
//获取B列的值
$name = $PHPExcel->getActiveSheet()->getCell("B" . $currentRow)->getValue();
//获取C列的值
$price = $PHPExcel->getActiveSheet()->getCell("C" . $currentRow)->getValue();
//获取D列的值
$count = $PHPExcel->getActiveSheet()->getCell("D" . $currentRow)->getValue();
$m = M('Info');
$num = $m->add(array('pName' => $name, 'pPrice' => $price, 'pCount' => $count));
}
if ($num > 0) {
echo "添加成功！";
} else {
echo "添加失败！";
}
}
}
?>5. Test
Related recommendations:
ThinkPHP3.2.2 plug-in controller function
The above is the detailed content of PHP uploads Excel files to import data into MySQL database. For more information, please follow other related articles on the PHP Chinese website!
What is the difference between unset() and session_destroy()?May 04, 2025 am 12:19 AMThedifferencebetweenunset()andsession_destroy()isthatunset()clearsspecificsessionvariableswhilekeepingthesessionactive,whereassession_destroy()terminatestheentiresession.1)Useunset()toremovespecificsessionvariableswithoutaffectingthesession'soveralls
What is sticky sessions (session affinity) in the context of load balancing?May 04, 2025 am 12:16 AMStickysessionsensureuserrequestsareroutedtothesameserverforsessiondataconsistency.1)SessionIdentificationassignsuserstoserversusingcookiesorURLmodifications.2)ConsistentRoutingdirectssubsequentrequeststothesameserver.3)LoadBalancingdistributesnewuser
What are the different session save handlers available in PHP?May 04, 2025 am 12:14 AMPHPoffersvarioussessionsavehandlers:1)Files:Default,simplebutmaybottleneckonhigh-trafficsites.2)Memcached:High-performance,idealforspeed-criticalapplications.3)Redis:SimilartoMemcached,withaddedpersistence.4)Databases:Offerscontrol,usefulforintegrati
What is a session in PHP, and why are they used?May 04, 2025 am 12:12 AMSession in PHP is a mechanism for saving user data on the server side to maintain state between multiple requests. Specifically, 1) the session is started by the session_start() function, and data is stored and read through the $_SESSION super global array; 2) the session data is stored in the server's temporary files by default, but can be optimized through database or memory storage; 3) the session can be used to realize user login status tracking and shopping cart management functions; 4) Pay attention to the secure transmission and performance optimization of the session to ensure the security and efficiency of the application.
Explain the lifecycle of a PHP session.May 04, 2025 am 12:04 AMPHPsessionsstartwithsession_start(),whichgeneratesauniqueIDandcreatesaserverfile;theypersistacrossrequestsandcanbemanuallyendedwithsession_destroy().1)Sessionsbeginwhensession_start()iscalled,creatingauniqueIDandserverfile.2)Theycontinueasdataisloade
What is the difference between absolute and idle session timeouts?May 03, 2025 am 12:21 AMAbsolute session timeout starts at the time of session creation, while an idle session timeout starts at the time of user's no operation. Absolute session timeout is suitable for scenarios where strict control of the session life cycle is required, such as financial applications; idle session timeout is suitable for applications that want users to keep their session active for a long time, such as social media.
What steps would you take if sessions aren't working on your server?May 03, 2025 am 12:19 AMThe server session failure can be solved through the following steps: 1. Check the server configuration to ensure that the session is set correctly. 2. Verify client cookies, confirm that the browser supports it and send it correctly. 3. Check session storage services, such as Redis, to ensure that they are running normally. 4. Review the application code to ensure the correct session logic. Through these steps, conversation problems can be effectively diagnosed and repaired and user experience can be improved.
What is the significance of the session_start() function?May 03, 2025 am 12:18 AMsession_start()iscrucialinPHPformanagingusersessions.1)Itinitiatesanewsessionifnoneexists,2)resumesanexistingsession,and3)setsasessioncookieforcontinuityacrossrequests,enablingapplicationslikeuserauthenticationandpersonalizedcontent.
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Mac version
God-level code editing software (SublimeText3)
mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.
MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.
