Shulou(Shulou.com)06/01 Report--

In this paper, an example is given to describe the method of exporting excel by MySQL. Share with you for your reference, the details are as follows:

It's easy for MySQL to export to an excel file, and execute commands like this:

Select * from a table into outfile'dvv / filename .xls'

If the above command is executed on the server, it will be directed to the server D: disk. If it is executed on the client command line, it will be directed on the client side D: disk. If it is executed on the client side through POST mode, it will be directed on the server D: disk. The prerequisite for successful guidance is that you have login permission, select permission, and file permission. Without file permission, you will not be able to execute select....into outfile and load data infile. Or something like that.

As for adding a button, click the prompt to save as an excel file, I think you may mean on the web page? If operations on MySQL are generally blocked on a web page, MySQL is generally operated through server-side scripting languages (such as PHP,JSP,ASP). Take PHP and JavaScript as examples to save database query results as xls (I have practiced both methods and are feasible):

1.PHP method:

Call the header () function with the following parameters:

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

These two functions are placed in the PHP file before the database query operation is performed.

Generally, after the database query operation is finished, the query results can be displayed to the client through functions such as echo () and printf (). If you add the above two lines before performing the database operation, the client will no longer display the results, but a dialog box will appear to download and save as xls, and the path and file name can be freely selected. Button or link you can put it on page 1, point it to page 2, page 1 is used to display the results to the client, page 2 (PHP file) put the above two lines, and then write the code to connect to the database and perform the same query operation as page 1. So that you click this link on page 1 will pop up a download and save as xls dialog box, save the content is you see on page 1 of the query results, save the format as xls, save you can use Excel to open it for operation.

PHP is a server-side script, using the above method, the same data results are actually transmitted from the server to the client twice, the first to display, and the second to package into a xls file for download.

2.JavaScript method:

First, define a JS function in the part of the page where the data resides:

Function xSaveAsxls (xResult, xFileName) {var ow=window.open (); with (ow) {[xss_clean] (Result); document.execCommand ('Saveas', true, xFileName); close ();}}

Then put it before displaying the data and put it later.

Then make a button below:

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

JS is a browser-side script. In this way, the display result is saved as xls, and its data is transmitted only once from the server to the client, saving transmission resources.

Another way is to consider passing the query result set from PHP to JS array variables, bringing it to the client, and then calling FileObjectSystem to operate, which is just an idea, which I haven't practiced.

Please correct the irregularities.

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

Select convert (dname using gb2312), email from d_dealerinfo into outfile'dVOUGANG d.xls'

Where dname is in Chinese.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection", "MySQL Database Lock related skills Summary" and "MySQL Common function Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.