使用PHP把excel(xls)文件导入mysql数据库
昨晚一个客户联系我做网站,提出的要求是需要把客户提供的excel文件里面的数据导入到mysql数据库,最常用的方法就是先把xls文件导出为csv格式的文件,然后在解析csv格式的文件导入到mysql数据库。方法比较冗余,而且分好几步进行,很不方便,断桥残雪今天介绍一种方法是直接跳过csv的中间环节,直接把excel文件导入mysql数据库。
首先我们需要下载PHP-ExcelReader这是一个开源的项目,主要是来解析excel的文件,下载地址:http://sourceforge.net/projects/phpexcelreader,下载之后解压,主要用到excel文件夹里面的两个文件reader.php和oleread.php(这个文件默认的是oleread.inc,不清楚为啥,一堆e文,没看,直接改名即可)。
在reader.php文件中找到以下类似代码(第一行既是),改成正确的oleread.php路径即可:require_once 'oleread.php';
然后新建一个php文件引入reader.php,代码如下:
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('gbk');\\此处设置编码,一般都是gbk模式
$data->read('Book1.xls');//文件路径
error_reporting(E_ALL ^ E_NOTICE);
//这里我就只循环输出excel文件的内容了,要入库,只要把输出的地方,写一段mysql语句即可~
for ($i = 1; $i sheets[0]['numRows']; $i++) {
for ($j = 1; $j sheets[0]['numCols']; $j++) {
echo "\"".$data->sheets[0]['cells'][$i][$j]."\",";
}
echo "\n";
}
?>
注意:请不要使用PHP-ExcelReader压缩包里面的xls进行测试,断桥残雪发现,那个文件既是使用excel也打不开,所以是错误的。
断桥残雪使用上面的方法解析了一个1.4M的数据,都显示正常,所以大家可以放心使用
原文:http://www.js8.in/618.html

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 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

PHP is mainly procedural programming, but also supports object-oriented programming (OOP); Python supports a variety of paradigms, including OOP, functional and procedural programming. PHP is suitable for web development, and Python is suitable for a variety of applications such as data analysis and machine learning.

The core benefits of PHP include ease of learning, strong web development support, rich libraries and frameworks, high performance and scalability, cross-platform compatibility, and cost-effectiveness. 1) Easy to learn and use, suitable for beginners; 2) Good integration with web servers and supports multiple databases; 3) Have powerful frameworks such as Laravel; 4) High performance can be achieved through optimization; 5) Support multiple operating systems; 6) Open source to reduce development costs.

PHP is suitable for web development and rapid prototyping, and Python is suitable for data science and machine learning. 1.PHP is used for dynamic web development, with simple syntax and suitable for rapid development. 2. Python has concise syntax, is suitable for multiple fields, and has a strong library ecosystem.

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

PHP originated in 1994 and was developed by RasmusLerdorf. It was originally used to track website visitors and gradually evolved into a server-side scripting language and was widely used in web development. Python was developed by Guidovan Rossum in the late 1980s and was first released in 1991. It emphasizes code readability and simplicity, and is suitable for scientific computing, data analysis and other fields.

PHP is a server-side scripting language used for dynamic web development and server-side applications. 1.PHP is an interpreted language that does not require compilation and is suitable for rapid development. 2. PHP code is embedded in HTML, making it easy to develop web pages. 3. PHP processes server-side logic, generates HTML output, and supports user interaction and data processing. 4. PHP can interact with the database, process form submission, and execute server-side tasks.
