Examples of common Excel export methods in PHP

This article mainly introduces you to the PHPThe relevant data of the general Excel export method, through the method introduced in this paper, to learn or use PHPHave a certain reference learning value, need to learn PHPHow to export Excel friends below with the small series to learn together.

I. GENERAL EXPORT methods

Excel Export method Online There are many, in the CRM or OA system to export Excel is often the thing, did this function people know that its main operation is actually a circular data list, and then a lattice to add data to a fixed cell. Just do it once, and then just copy the relevant code to modify the changes, the other local export function is completed.

But there are two problems with this:

1. When the list data field changes, need to modify a lot of code, maintenance difficult, change uncomfortable;

2. When the export function occurs multiple times, it is necessary to copy a large number of duplicated redundant code in many places and look uncomfortable;

Therefore, it is necessary to unify a method of exporting Excel, when used, only need to pass in the data Header, table header field name, data list, and data table name, you can export Excel.

Two. Using a common export method

As shown, it is much easier to export, use and maintain as long as 4 parameters are passed in.

OK, the goal is clear, and then the code is implemented.

Obviously, one of the biggest challenges in achieving this is to automatically calculate the coordinate values (such as A1,B3) in Excel for each field in each piece of data, based on the index of the individual data and the header field order.

So let's analyze the cell coordinates of Excel, start with A1, increment the number vertically, increment the letter horizontally, and when the horizontal letter becomes z, the next letter is AA, then ab,...,zz,...,aaa ...

In this way, we know that the longitudinal coordinate is simple, according to each data index value can be calculated, difficult is the horizontal coordinate, how to calculate? Further careful analysis of the horizontal coordinates, you can find that is a similar to the 26-digit alphanumeric, a if viewed as 0, that Z represents 25. But this number is not the same as our common 16-binary, 8-binary, because when Z-carry, the next number is not BA, but AA. Common in-system, such as decimal 9 carry, becomes 10 instead of 00;16 binary 0xF, which becomes 0x10 instead of 0x00.

Therefore, it is possible to refer to the algorithm of the conversion, and then change the method to calculate the horizontal coordinates of Excel (10 binary to pseudo 26 binary):

AAA convert public static function Toaaa ($DEC) {if ($dec < 0) return ', $y = $dec%; $x = Floor ($dec/26), return self: : Toaaa ($x-1). Chr ($y + 65);}

Finally, attach the complete code

The framework for the Yii2,excel export component is moonlandsoft/yii2-phpexcel;

Other similar

Export xlspublic static function Exportxls ($array) {set_time_limit (0); include (Url::to (' @vendor/moonland/phpexcel/ Phpexcel.php ')); Include (Url::to (' @vendor/moonland/phpexcel/phpexcel/writer/excel2007.php ')); $titles = $array [' titles ']; $fields = $array [' Fields ']; $list = $array [' list ']; $name = $array [' name ']; $count = count ($titles);  $keys = [];//A=&GT;CHR] foreach ($titles as $k = = $v) {$keys [] = SELF::TOAAA ($k);} $objPHPExcel = new \phpexcel (); $objWriter = new \phpexcel_writer_excel2007 ($objPHPExcel); $objPHPExcel->setactivesheetindex (0); $activeSheet = $objPHPExcel->getactivesheet (); $activeSheet->settitle ($name); $activeSheet->getstyle ("a1:{$keys [$count -1]}1")->getalignment ()->sethorizontal (\phpexcel_style_ Alignment::horizontal_center); $activeSheet->mergecells ("a1:{$keys [$count -1]}1"); $activeSheet->setcellvalue (' A1 ', $name); Set title, Style foreach ($titles as $key + = $title) {$activeSheet->setcellvalue ($keys [$key]. ' 2 ', $title); $activeSheet-&Gt;getcolumndimension ($keys [$key])->setwidth (20); $activeSheet->getrowdimension (($key + 1))->setrowheight (18); } $i = 3; foreach ($list as & $item) {foreach ($keys as $k = $v) {$val = Isset ($item [$fields [$k]])? $item [$fields [$k]].  ' ' : ' '; $activeSheet->setcellvalue ($v. $i, $val); } $i + +; } $fileName = $name. "_" . Date (' Y_m_d_his '). '. xlsx '; Header ("Cache-control:public"); Header ("Pragma:public"); Header ("Content-type:application/vnd.ms-excel"); Header ("Content-disposition:attachment;filename=". Iconv ("Utf-8", "Gb2312//translit", $fileName)); Header (' Content-type:application/octet-stream '); Ob_clean (); Ob_start (); $objWriter->save (' php://output '); Ob_end_flush ();} AAA convert public static function Toaaa ($DEC) {if ($dec < 0) return ', $y = $dec%; $x = Floor ($dec/26), return self: : Toaaa ($x-1). Chr ($y + 65);}

Three. Examples of export results

Export results: