


PHP method to import Excel into database and export database data to Excel, excel data export_PHP tutorial
Jul 13, 2016 am 09:48 AMHow to import Excel into the database and export database data to Excel with PHP, and export excel data
This article describes the method of importing Excel into the database and exporting database data to Excel with PHP. Share it with everyone for your reference. The specific implementation method is as follows:
1. Import
Importing requires the use of components that can read Excel. There are also better components on the Internet. Here I share the ones I use: Download Extraction code: vxyn. (Note that the two files are referenced)
<?php //傳入要導(dǎo)入的Excel的文件名 function import_to_DB($filename) { require_once'reader.php'; $data = new Spreadsheet_Excel_Reader(); //創(chuàng)建讀取Excel的對象 $data->setOutputEncoding('utf-8'); //設(shè)置讀取Excel內(nèi)容后輸出的字符編碼 $data->read("data/Excel/{$filename}.xls"); $db = mysql_connect('localhost', '用戶名', '密碼') or die("Could not connect to database."); //連接數(shù)據(jù)庫 mysql_query("set names 'uft8'"); //輸出中文 mysql_select_db('數(shù)據(jù)庫名'); //選擇數(shù)據(jù)庫 error_reporting(E_ALL ^ E_NOTICE); for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) { echo $data->sheets[0]['cells'][$i][列數(shù)]; //這里可以把每一行相應(yīng)列的值插到數(shù)據(jù)庫中,如: /* $sql="insert "表名" values(對應(yīng)項...)"; mysql_query($sql); 可加上錯誤判斷 */ } ?>
In short, if you can read the value of the corresponding column $data->sheets[0][row][column] in each row of the table, the insertion operation will be easy.
2. Export
Export can easily export table files using the MIME protocol without relying on any components. Set the header in the following format to export to Excel, and the browser will download it at the same time
header('Content-type: text/html; charset=utf-8'); header("Content-type:application/vnd.ms-excel;charset=UTF-8"); //application/vnd.ms-excel指定輸出Excel格式 header("Content-Disposition:filename=表格文件名.xls"); //輸出的表格名稱
The complete code is as follows:
<?php header('Content-type: text/html; charset=utf-8'); header("Content-type:application/vnd.ms-excel;charset=UTF-8"); header("Content-Disposition:filename=表格文件名.xls"); $conn = mysql_connect("localhost","root","數(shù)據(jù)庫密碼") or die("不能連接數(shù)據(jù)庫"); mysql_select_db("數(shù)據(jù)庫名", $conn); mysql_query("set names 'UTF-8'"); $sql="select * from 表名 where 條件"; $result=mysql_query($sql); echo "表頭1\t表頭2\t表頭3\n"; while($row=mysql_fetch_array($result)){ echo $row[0]."\t".$row[1]."\t".$row[2]."\n"; } ?>
Here, t is actually a format change, and n is a line break. Set the link to this php file in a web page, and when clicked, the browser will automatically save the passed stream as an Excel file.
I hope this article will be helpful to everyone’s PHP programming design.

Hot AI Tools

Undress AI Tool
Undress images for free

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

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

std::chrono is used in C to process time, including obtaining the current time, measuring execution time, operation time point and duration, and formatting analysis time. 1. Use std::chrono::system_clock::now() to obtain the current time, which can be converted into a readable string, but the system clock may not be monotonous; 2. Use std::chrono::steady_clock to measure the execution time to ensure monotony, and convert it into milliseconds, seconds and other units through duration_cast; 3. Time point (time_point) and duration (duration) can be interoperable, but attention should be paid to unit compatibility and clock epoch (epoch)

ToaccessenvironmentvariablesinPHP,usegetenv()orthe$_ENVsuperglobal.1.getenv('VAR_NAME')retrievesaspecificvariable.2.$_ENV['VAR_NAME']accessesvariablesifvariables_orderinphp.iniincludes"E".SetvariablesviaCLIwithVAR=valuephpscript.php,inApach

PHPhasthreecommentstyles://,#forsingle-lineand/.../formulti-line.Usecommentstoexplainwhycodeexists,notwhatitdoes.MarkTODO/FIXMEitemsanddisablecodetemporarilyduringdebugging.Avoidover-commentingsimplelogic.Writeconcise,grammaticallycorrectcommentsandu

The key steps to install PHP on Windows include: 1. Download the appropriate PHP version and decompress it. It is recommended to use ThreadSafe version with Apache or NonThreadSafe version with Nginx; 2. Configure the php.ini file and rename php.ini-development or php.ini-production to php.ini; 3. Add the PHP path to the system environment variable Path for command line use; 4. Test whether PHP is installed successfully, execute php-v through the command line and run the built-in server to test the parsing capabilities; 5. If you use Apache, you need to configure P in httpd.conf

The basic syntax of PHP includes four key points: 1. The PHP tag must be ended, and the use of complete tags is recommended; 2. Echo and print are commonly used for output content, among which echo supports multiple parameters and is more efficient; 3. The annotation methods include //, # and //, to improve code readability; 4. Each statement must end with a semicolon, and spaces and line breaks do not affect execution but affect readability. Mastering these basic rules can help write clear and stable PHP code.

1. Close Edge search suggestions: Go to Settings > Privacy, Search and Services > Address Bar and Search, and turn off display search suggestions; 2. Close other recommended content: Close use the search box to obtain shortcuts and new tab page interest content; 3. Clear historical data: delete browsing history and cookies. Through the above steps, Edge can effectively prevent automatically popping up search suggestions and improve privacy protection.

The key to writing Python's ifelse statements is to understand the logical structure and details. 1. The infrastructure is to execute a piece of code if conditions are established, otherwise the else part is executed, else is optional; 2. Multi-condition judgment is implemented with elif, and it is executed sequentially and stopped once it is met; 3. Nested if is used for further subdivision judgment, it is recommended not to exceed two layers; 4. A ternary expression can be used to replace simple ifelse in a simple scenario. Only by paying attention to indentation, conditional order and logical integrity can we write clear and stable judgment codes.

The steps to install PHP8 on Ubuntu are: 1. Update the software package list; 2. Install PHP8 and basic components; 3. Check the version to confirm that the installation is successful; 4. Install additional modules as needed. Windows users can download and decompress the ZIP package, then modify the configuration file, enable extensions, and add the path to environment variables. macOS users recommend using Homebrew to install, and perform steps such as adding tap, installing PHP8, setting the default version and verifying the version. Although the installation methods are different under different systems, the process is clear, so you can choose the right method according to the purpose.
