The easiest way to generate xls in PHP
In general, it would seem that the task placed in the header should not be difficult, and Google gives a lot of links, including Habr, but in order to unload the database from the site to Excel, I had to suffer a little.
I preferred not to get involved with the Spreadsheet_Excel_Writer module, due to the lack of the php modules it needs on the three servers I have available for testing, I still want to make the code dragged between servers easily and simply.
php_write_excel pushed aside the complete lack of documentation if necessary to do the work quickly (although in the future I want to sort out this module).
As a result, I chose the simplest method found on the Internet - the derivation of a standard html table under the guise of an xls file. On this path, I personally had a problem with the encoding, Excel stubbornly did not want to see the Cyrillic script in honest Windows-1251. As a result, the following construction turned out to be working.
')
Accordingly, iconv is needed if the data in the database is written in utf-8, htmlentities translates into a format that is accessible to Excel. An attempt to apply htmlentities to the text in utf-8 led to a very large number of Chinese characters in Excel.
This method allows using the same html tags to set bold and italic text, but so far it has not been possible to understand whether it is possible to fill cells with color. However, the method is still quite suitable for generating a simple .xls file. The data is then normally viewed, edited and stored in Excel.
I hope this post will help someone in solving a similar problem.
I preferred not to get involved with the Spreadsheet_Excel_Writer module, due to the lack of the php modules it needs on the three servers I have available for testing, I still want to make the code dragged between servers easily and simply.
php_write_excel pushed aside the complete lack of documentation if necessary to do the work quickly (although in the future I want to sort out this module).
As a result, I chose the simplest method found on the Internet - the derivation of a standard html table under the guise of an xls file. On this path, I personally had a problem with the encoding, Excel stubbornly did not want to see the Cyrillic script in honest Windows-1251. As a result, the following construction turned out to be working.
')
header('Content-Type: text/html; charset=windows-1251'); header('P3P: CP="NOI ADM DEV PSAi COM NAV OUR OTRo STP IND DEM"'); header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); header('Cache-Control: no-store, no-cache, must-revalidate'); header('Cache-Control: post-check=0, pre-check=0', FALSE); header('Pragma: no-cache'); header('Content-transfer-encoding: binary'); header('Content-Disposition: attachment; filename=list.xls'); header('Content-Type: application/x-unknown'); echo<<<HTML <table border="1"> <tr><td> htmlentities(iconv("utf-8", "windows-1251", $val),ENT_QUOTES, "cp1251")); </td></tr> </table> HTML; Accordingly, iconv is needed if the data in the database is written in utf-8, htmlentities translates into a format that is accessible to Excel. An attempt to apply htmlentities to the text in utf-8 led to a very large number of Chinese characters in Excel.
This method allows using the same html tags to set bold and italic text, but so far it has not been possible to understand whether it is possible to fill cells with color. However, the method is still quite suitable for generating a simple .xls file. The data is then normally viewed, edited and stored in Excel.
I hope this post will help someone in solving a similar problem.
Source: https://habr.com/ru/post/133093/
All Articles