The easiest way to generate xls in PHP

    In general, it would seem that the task put in the header should not be difficult, and Google gives a bunch of links, including to the Habr, however, in order to do the unloading of the database from the site to Excel, I had to suffer a little.

    I preferred not to mess with the Spreadsheet_Excel_Writer module, due to the lack of the necessary php modules on the three servers I have available for testing, I still want to make the code draggable between the servers easy and simple.
    php_write_excel pushed out a complete lack of documentation if necessary to do the job quickly (although in the future I want to deal with this module).


    As a result, I chose the simplest method found on the Internet - displaying a standard html table under the guise of an xls file. On this way, I personally had a problem with the encoding, Excel persistently did not want to see the Cyrillic alphabet 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<<
    
    htmlentities(iconv("utf-8", "windows-1251", $val),ENT_QUOTES, "cp1251"));
    
    
    HTML;


    Accordingly, iconv is needed if the data in the database is written in utf-8, htmlentities translates into an excel-accessible format. An attempt to apply htmlentities to text in utf-8 resulted in a very large number of Chinese characters in excel.

    This method allows using standard 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 saved in Excel.

    I hope this post helps someone in solving a similar problem.

    Also popular now: