Analysis of MySQL's method of exporting to Excel

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

Recommend

Install redis and MySQL on CentOS

1|0MySQL (MariaDB) 1|11. Description MariaDB data...

Methods for defragmenting and reclaiming space in MySQL tables

Table of contents Causes of MySQL Table Fragmenta...

Implementation of forced line breaks and non-line breaks in div, td, p and other containers in HTML

1. Force no line break and end with an ellipsis. C...

Detailed explanation of MYSQL log and backup and restore issues

This article shares MYSQL logs and backup and res...

CSS complete parallax scrolling effect

1. What is Parallax scrolling refers to the movem...

Vue implements the digital thousands separator format globally

This article example shares the specific code for...

Vue+elementUI component recursively implements foldable dynamic rendering multi-level sidebar navigation

The function has been implemented a long time ago...

Detailed explanation of replace into example in mysql

Detailed explanation of replace into example in m...

CentOS 7 configuration Tomcat9+MySQL solution

Configure Tomcat First install Tomcat Installing ...

Understand the usage of Vue2.x and Vue3.x custom instructions and the principle of hook functions

Table of contents Vue2.x Usage Global Registratio...

Methods and steps for Etcd distributed deployment based on Docker

1. Environmental Preparation 1.1 Basic Environmen...

Ubuntu Server 16.04 MySQL 8.0 installation and configuration graphic tutorial

Ubuntu Server 16.04 MySQL 8.0 installation and co...

Simple summary of tomcat performance optimization methods

Tomcat itself optimization Tomcat Memory Optimiza...

Practical way to build selenium grid distributed environment with docker

Recently, I needed to test the zoom video confere...

Three ways to use CSS inline styles, embedded styles, and external reference styles

A simple example of how to use the three methods ...