Introduction to how PHPExcel is replaced by PHP_XLSXWriter (picture)

PHPExcel is an open source framework for processing Excel and CVS files. Unfortunately, PHPExcel officially no longer maintains this project. The official team has started a new project on github called PhpSpreadsheet. So this article mainly introduces you to examples of how to use PHP_XLSXWriter instead of PHPExcel. Friends in need can refer to it.

Preface

This article mainly introduces to you the method of using PHP_XLSXWriter instead of PHPExcel, and shares it for your reference and study. I won’t say anything below. Let’s talk more, let’s take a look at the detailed introduction:

What’s the difference between the two?

PHPExcel is a process for Excel, An open source framework for CVS files, based on Microsoft's OpenXML standard and PHP language. You can use it to read and write spreadsheets in different formats. This is also the most versatile Excel processing tool for PHP so far, but it has a very fatal shortcoming: it takes up a lot of memory and is almost tiring for large batches of table data. Love it or not, the processing speed is very slow, but it is very rich in functions and has many APIs, so when exporting Excel tables in complex formats, you often have to use it, which is really a love-hate relationship.

Unfortunately, PHPExcel officially no longer maintains this project. The official team has started a new project on github called PhpSpreadsheet. The new project uses a lot of new PHP features, such as Namespace , PSR standard, and its performance is much higher than PHPExcel. However, the project is still in development status (2017-07-12). The minimum stable version has not yet been released. It is estimated that there will be more bugs, so it is not recommended. Use, the picture below is the project migration instructions:

Compared with PHPExcel, PHP_XLSXWriter is a small but powerful Excel reading and writing plug-in. It does not have as many advanced features as PHPExcel. Operations such as freezing table headers are not available, but its export speed is very fast, which is very suitable for export requirements where the amount of data is particularly large and the report format is not very complex. The following picture is the official speed and memory test:

How to use PHP_XLSXWriter?

Download

This is the github address of PHP_XLSXWriter. Of course, you can also download it locally. You can click to download Download it. After decompression, you can see that it has only one core file: xlswriter.class.php. The examples directory is the code sample directory. There are many examples in it for you to refer to.

Use

For most daily reporting needs, PHP_XLSXWriter is competent. Let's use an example to get familiar with the use of API.

Suppose we want to export the report in the picture below and download it through the browser:

Code implementation:

//writer 类
$writer = new XLSXWriter();
 //文件名
$filename = "example.xlsx";
//设置 header，用于浏览器下载
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');

//导出的数据
$string = array (
 0 => 
 array (
 'payc_bill_time' => '2017-07-12 16:40:44',
 'payt_received_date' => '2017-07-12',
 'ci_name' => '租金',
 'payt_num' => 'YRZB(2012)A0047',
 'payt_scsr_name' => '李巧红',
 'payt_received' => '300.00',
 'paytd_type' => '现金',
 'emp_name' => '郑振标',
 ),
 1 => 
 array (
 'payc_bill_time' => '2017-07-12 16:39:55',
 'payt_received_date' => '2017-07-12',
 'ci_name' => '租金',
 'payt_num' => 'YRZB(2012)A0046',
 'payt_scsr_name' => '22222',
 'payt_received' => '45.00',
 'paytd_type' => '现金',
 'emp_name' => '郑振标',
 )
 );
 //每列的标题头
$title = array (
 0 => '开单时间',
 1 => '收款时间',
 2 => '开票项目',
 3 => '票据编号',
 4 => '客户名称',
 5 => '实收金额',
 6 => '收款方式',
 7 => '收款人',
);
//工作簿名称
$sheet1 = 'sheet1';

//对每列指定数据类型，对应单元格的数据类型
foreach ($title as $key => $item){
 $col_style[] = $key ==5 ? 'price': 'string';
}

//设置列格式，suppress_row: 去掉会多出一行数据；widths: 指定每列宽度
$writer->writeSheetHeader($sheet1, $col_style, ['suppress_row'=>true,'widths'=>[20,20,20,20,20,20,20,20]] );
//写入第二行的数据，顺便指定样式
$writer->writeSheetRow($sheet1, ['xxx财务报表'],
['height'=>32,'font-size'=>20,'font-style'=>'bold','halign'=>'center','valign'=>'center']);

/*设置标题头，指定样式*/
$styles1 = array( 'font'=>'宋体','font-size'=>10,'font-style'=>'bold', 'fill'=>'#eee',
'halign'=>'center', 'border'=>'left,right,top,bottom');
$writer->writeSheetRow($sheet1, $title,$styles1);
// 最后是数据，foreach写入
foreach ($data as $value) {
 foreach ($value as $item) { $temp[] = $item;}
 $rows[] = $temp;
 unset($temp);
}
$styles2 = ['height'=>16];
foreach($rows as $row){
 $writer->writeSheetRow($sheet1, $row,$styles2);
}

//合并单元格，第一行的大标题需要合并单元格
$writer->markMergedCell($sheet1, $start_row=0, $start_col=0, $end_row=0, $end_col=7);
//输出文档
$writer->writeToStdOut();
exit(0);

The above Each line of code is commented. If you don’t understand it, you can check out the code samples in the example folder and the documentation on the official website, but the documentation is relatively short;

Guide to jumping into pits :

I have also stepped on some pitfalls during use. Here is a list, I hope it will be helpful to you:

File name and class The name does not correspond to

. This is not actually a problem when using require or require_once, but when using automatic loading, the two do not correspond. It cannot be identified. You may want to introduce the xlsxwriter.class.php file into your project and add a namespace so that it can be automatically loaded. What you need to do at this time is to change the file name to the class name XLSXWriter.class.php (introduced into TP here). For example, if I put it in the Component directory, then add the namespace Component; to the file. At this time, There is also a Zip class in the file that has not introduced a namespace, and you need to add use ZipArchive;

After these are completed, you can use it elsewhere in the project:

use Component；
$writer = new XLSXWriter();

How to set columns Format?

Different columns may need to be displayed in different formats. The default ones are text formats, but sometimes they need to be displayed as numeric columns, which is more convenient for using functions in excel, such as the table above. The amount column in must have two decimal points, thousandths, and numeric format.

Look at the code above. The number format is actually set in the writeSheetHeader method. The column with type price is the amount column. If you need other formats, the commonly used formats are listed on the homepage of the official website.

Can I set the value of a certain cell individually?

This has not been implemented yet. Currently, data is written row by row, and such fine granularity is not supported. However, a compromise is to write null into the cells that do not need to be filled;

Summarize

The above is the detailed content of Introduction to how PHPExcel is replaced by PHP_XLSXWriter (picture). For more information, please follow other related articles on the PHP Chinese website!