Export MySQL query to Excel file

Export MySQL query to Excel file (XLS or XLSX file)

Tools

one step conversion: ex MySQL query -> XLS/XLSX file

• PHPExcel
• other MySQL client softwares

two steps conversion: ex: (1) MySQL query -> CSV/XLS file (2) CSV --> XLSX or XLS --> XLSX

• PHPExcel or MySQL client softwares + MS Excel
• PHPExcel or MySQL client softwares + LibreOffice Calc

Technical issues: If it costs too much time to convert to Excel file

The important steps before generating CSV file. These steps reduce the problems when the CSV file was converted to Excel file.

• Add delimiter character between different column ex: comma symbol (,) or tab symbol
• Add enclosure character ex: double quote symbol (") or single quote symbol (')
• Add escape character escape the column value if contains the enclosure character
• Remove return symbol:

  SELECT REPLACE(REPLACE(REPLACE(`my_column`, '\r\n', '   '), '\n', '   '), '\r', '   ')