How Phpexcel is introduced by Php_xlsxwriter substitution method

Phpexcel is an open-source framework for working with EXCEL,CVS files, but unfortunately Phpexcel officially no longer maintains the project, and the official team has started a new project on GitHub called Phpspreadsheet. Then this article mainly introduces you to the use of Php_xlsxwriter to replace the Phpexcel Method example, the need for friends can refer to.

Objective

This article is mainly about the use of Php_xlsxwriter to replace the Phpexcel method, sharing out for everyone to reference the study, the following words do not say, come together to see the detailed introduction:

What is the difference between the two?

Phpexcel is an open-source framework for processing Excel,cvs files based on Microsoft's OPENXML Standard and PHP language. It can be used to read and write spreadsheets in different formats, which is the most common Excel processing tool in PHP so far, but it has a very deadly disadvantage: especially in memory, the large amount of tabular data will almost make people tired of love, processing speed is very slow, but it is very rich, the API is very many, So when you export a complex format Excel table, you often have to use it, it's really love and hate.

Unfortunately, Phpexcel officially no longer maintained the project, the official team on GitHub on a new project, called Phpspreadsheet, the new project uses a lot of new PHP features, such as namespaces, PSR Standard, performance is much higher than the phpexcel, However, the project so far (2017-07-12) or development status, the minimum stable version has not come out, the estimated bug will be more, so it is not recommended to use, is the project migration description:

Compared to Phpexcel,php_xlsxwriter is a small and powerful Excel read-write plug-in, it does not have phpexcel feature rich, many advanced operations such as frozen table header, does not have, but it is exported very fast, very suitable for the data volume is particularly large, The report format is not a very complex export requirement, it is the official speed and memory test:

How is php_xlsxwriter used?

Download

This is Php_xlsxwriter's GitHub address, of course, you can also download it locally, you can download it by clicking Download. After decompression you can see, its core file only one: Xlswriter.class.php,examples directory for the code sample directory, there are many examples you can refer to.

Use

For the majority of the daily reporting requirements, php_xlsxwriter is capable of, following an example to familiarize yourself with the use of the API.

Let's say we want to export the report in and download it through the browser:

Code implementation:

Writer class $writer = new Xlsxwriter (); File name $filename = "EXAMPLE.XLSX";//Set header for browser download header (' content-disposition:attachment; Filename= '. Xlsxwriter::sanitize_filename ($filename). "'); Header ("Content-type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); Header (' Content-transfer-encoding:binary '); header (' cache-control:must-revalidate '); header (' pragma:public ');//Exported data $ String = Array (0 = = Array (' payc_bill_time ' = ' 2017-07-12 16:40:44 ', ' payt_received_date ' = ' 2017-07-12 ', ' Ci_name ' = ' rent ', ' payt_num ' = ' yrzb ' (+) A0047 ', ' payt_scsr_name ' = ' Li Chaohong ', ' payt_received ' = ' 300.00 ', ' PA Ytd_type ' + ' cash ', ' emp_name ' = ' Zheng ', ', ' 1 ' = ' Array (' payc_bill_time ' = ' 2017-07-12 16:39:55 ', ' payt_rece Ived_date ' = ' 2017-07-12 ', ' ci_name ' and ' rent ', ' payt_num ' + ' yrzb ', ' A0046 ', ' 22222 ', ' payt_received ' = ' 45.00 ', ' paytd_type ' and ' cash ', ' emp_name ' = ' Zheng ',); Header header for each column $title = Array (0 = ' Billing time ', 1 = ' payment time ', 2 = ' Invoicing item ', 3 = ' Ticket number ', 4 ' Customer name ', 5 = ' paid Amount ', 6 = ' payment method ', 7 ' payee ', and ' beneficiary '; /Workbook Name $sheet1 = ' Sheet1 ';//Specifies the data type for each column, the data type of the corresponding cell is foreach ($title as $key = = $item) {$col _style[] = $key ==5? ' Price ': ' String ';} Set the column format, Suppress_row: Remove a row of data, widths: Specify the width of each column $writer->writesheetheader ($sheet 1, $col _style, [' Suppress_row ' = >true, ' widths ' =>[20,20,20,20,20,20,20,20]]);//write the second row of data, by the way specify the style $writer->writesheetrow ($sheet 1, [' XXX financial statements '],[' height ' =>32, ' font-size ' =>20, ' font-style ' = ' bold ', ' halign ' = ' center ', ' valign ' and ' = ' */* Set header, specify style */$styles 1 = array (' font ' = ' Arial ', ' Font-size ' =>10, ' font-style ' = ' bold ', ' fill ' = ') #eee ', ' halign ' = ' center ', ' border ' = ' left,right,top,bottom '); $writer->writesheetrow ($sheet 1, $title, $ STYLES1);//Last is data, foreach writes foreach ($data as $value) {foreach ($value as $item) {$temp [] = $item;} $rows [] = $temp; unse T ($temp);} $styles 2 = [' Height ' =>16];foreach ($rows as $row) {$writer->writEsheetrow ($sheet 1, $row, $styles 2);} Merge cells, the first row of the large headings need to merge cells $writer->markmergedcell ($sheet 1, $start _row=0, $start _col=0, $end _row=0, $end _col=7);// Output document $writer->writetostdout (); exit (0);

Each line of code above is commented, if you do not know the words can go to see the code in the example folder sample and the official homepage of the document, but the document is relatively short;

Jump Pit Guide:

Oneself in the use of the process also stepped on some pits, here listed, hope to help you:

File name does not correspond to class name

This is not a problem when using require or require_once, but it is not recognized when using automatic loading because they are not. You may want to introduce the xlsxwriter.class.php file into your project and add namespaces so that you can automate the loading. What you need to do now is change the file name to the class name XLSXWriter.class.php (which is introduced into the TP), such as I put in the Component directory, then add the namespace namespace Component in the file; At this point, there is a zip class in the file does not introduce a namespace, need to add use ziparchive;

Once this is done, it can be used elsewhere in the project:

Use Component; $writer = new Xlsxwriter ();

How do I format columns?

Different columns may need to show different formatting, default is text format, but sometimes need to display as a number column, than easy to use functions in Excel, such as the amount column in the above table, must be two decimal points, thousand digits, number format.

Look at the above code, the number format is actually set in the Writesheetheader method, the type of price of the column is the amount of the column, if you need other formats, the official website on the home page lists the commonly used format.

Can I set the value of a cell individually?

This is not currently implemented, and now the data is written in line-by-row, does not support such a fine granularity, but the tradeoff is to do not need to fill the cell to write null;

Summarize