Export mysql Data to excel and related computing, and export mysql Data

Export mysql Data to excel and related computing, and export mysql Data

After mysql queries the data, you can select to export the file as a csv file by default. If the data type is integer, you can add multiple values in CONCAT ('\ '', filed) to convert it into text, then, open the csv file in the text editor and replace the 'and other characters with an empty string.

Create an excel file, find the data-self text, and select the next format as text.

Some basic excel functions:

Calculate the number of characters, numbers, and so on: = COUNTIF (A: A, "test") is the number of 'test' strings in column.

Gender: = IF (MOD (MID (A1, IF (LEN (A1) = 15,15, 17), 1), 2) = 1, "male ", "female ")

Then formatted and calculated the entire computation of a result, for example, if the gender column selects the first calculated result of this column, press Ctrl + the plus sign in this column, and double-click it to calculate all the data in this column.

Age: = DATEDIF (TEXT (MID (A1, 7, LEN (A1)/2-1), "0-00-00"), TODAY (), "Y ")

Count age groups: column C for age statistics = IF (C1> 50, "50 +", IF (C1> = 40, "40-50 ", IF (C1> = 28, "28-40", IF (C1> = 23, "23-28", IF (C1> = 18, "18-22 ", "less than 18 ")))))

OK. The above are some basic computing functions. Of course, there are many functions in excel. Check the information when necessary.

How can I export MYSQL Data to an EXCEL table?

Select * into outfile 'd:/test.xls 'from Table Name

Who is eager to export mysql data into an excel table?

Method 1:
Import to SQL and export excel from SQL
Method 2:
$ File_type = "vnd. ms-excel"; // fixed expression in the excel Header
$ File_ending = "xls"; // Suffix of the excel table
Header ("Content-Type: application/$ file_type ");
Header ("Content-Disposition: attachment; filename = list. $ file_ending"); // name of the table everywhere in agentfile
Header ("Pragma: no-cache"); // cache
Header ("Expires: 0 ");

Echo mb_convert_encoding ("name", "GBK", "UTF-8 "). "\ t ". mb_convert_encoding ("Age", "GBK", "UTF-8 "). "\ t ". mb_convert_encoding ("time", "GBK", "UTF-8 ");
Print ("\ n ");
// Cyclic data ~ Print ("\ n") for line breaks ");
Exit;
--------------------------
That's all.
Basically, define the mimetype and output content. It will be automatically downloaded to excel. Note: Do not use the download tool.
Method 3:
1. Export
Select * from test outfile '/tmp/reg.xls'
Store exported data in the/tmp directory
2. encoding conversion
Because excel is encoded as GB3212 by default, the data contains Chinese characters which need to be converted. Conversion command
Iconv-futf8-tgb2312 -oreg2.xls reg.xls
If conversion fails:
Iconv: illegal input sequence at position 1841 due to this error, download reg.xls first. at this time, the file is UTF-8 encoded and opened in excel with garbled characters. Open reg.xls in text format, save it as, select ANSI encoding in encoding, and save. The garbled problem is solved.