Analysis of MySQL's method of exporting to Excel

This article describes how to use MySQL to export data to Excel. Share with you for your reference, the details are as follows:

It is very simple to export MySQL into an excel file. Just execute a command like this:

select * from a table into outfile 'd:/filename.xls';

If you execute the above command on the server, it will be exported to the server's D: disk. If you execute it in the client command line mode, it will be exported to the client's D: disk. If you execute it in the client through POST mode, it will be exported to the server's D: disk. The prerequisite for successful import is that you have login permission, select permission, and file permission. If you do not have file permission, you will not be able to perform operations such as select....into outfile and load data infile....

As for adding a button that prompts you to save as an excel file, I think you may mean on the web page? If the operation of MySQL is generally blocked on the web page, MySQL is usually operated through server-side scripting languages (PHP, JSP, ASP, etc.). The following uses PHP and JavaScript as examples to save the database query results as xls (I have tried both methods and they are feasible):

1.PHP method:

Call header() function with the following parameters:

header("content-type:application/msexcel");
header("content-disposition:filename=file001.xls");

These two functions are placed before executing database query operations in the PHP file.

Generally, after the database query operation is completed, the query results can be displayed to the client through functions such as echo() and printf() . If the above two lines are added before executing the database operation, the client will no longer display the results, but a dialog box for downloading and saving as xls will appear, and the path and file name can be freely selected. You can put a button <input type=button ...> or a link <a href=...> on page 1, pointing it to page 2. Page 1 is used to display the results to the client, and page 2 (PHP file) has the above two lines, and then write the code to connect to the database and perform the same query operation as page 1. In this way, when you click this link on page 1, a dialog box will pop up to download and save as xls. The saved content is the query result you see on page 1. The saved format is xls. After saving, you can open it with Excel for operation.

PHP is a server-side script. Using the above method, the same data result is actually transmitted from the server to the client twice. The first time is used for display, and the second time is used to package it into an xls file for download.

2. JavaScript method:

First, define a JS function in the <head> section of the page where the data is located:

<head>
<script language="javascript">
function xSaveAsxls( xResult , xFileName ){
var ow=window.open();
with(ow){
 document.write(Result);
 document.execCommand('Saveas', true, xFileName);
 close();
}
}
</script>
</head>

Then put <div id=div_id> before the <table> that displays the data, and put </div> after </table>.

Then make a button below:

<input type="button" value="Save as Excel file" onclick=xSaveAsxls
(document.all.div_id.innerHTML,'filename.xls')>

That's it. This is displayed as "Save as Excel file". When you click it, a save dialog box will pop up. The saved content is the data result set in the xls format.

JS is a browser-side script. This method is used to save the displayed results as xls. The data is only transmitted once from the server to the client, saving transmission resources.

Another way is to consider passing the query result set from PHP to a JS array variable, which is brought to the client, and then calling FileObjectSystem to operate. This is just an idea and I have not put it into practice.

Please point out any inappropriateness.

If there is a garbled problem: you can use the following statement

select convert(dname using gb2312),email from d_dealerinfo into outfile 'd:/d.xls';

Where dname is in Chinese

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:

Detailed explanation of Linux mysqldump exporting database, data, and table structure
A simple method to export table structure script using Navicat for MySQL
mysql mysqldump only exports table structure or only exports data implementation method
A brief discussion on the datetime format when exporting table data from MySQL to Excel
Python3 implements mysql export excel method
Problems and solutions when exporting mysql select statement results to excel files
How to solve the problem of MySQL Chinese garbled characters and exporting to SQL statements and Excel [Pictures and text]
PHP exports MySQL data to Excel file (fputcsv)
How to export mysql table structure to excel

<<: jQuery plugin to achieve seamless carousel

>>: Zabbix's psk encryption combined with zabbix_get value