Examples of common excel export methods in php and phpexcel export Methods

Examples of common excel export methods in php and phpexcel export Methods

I. general export Methods

There are many ways to export excel on the Internet. It is common to export excel in crm or OA systems. People who have done this function know that the main operation is actually a circular data list, add data to a fixed cell in one cell. Once done, you only need to copy and modify the relevant code, and the export function will be completed elsewhere.

However, there are two problems:

1. When the list data field is modified, a large amount of code needs to be modified, which makes maintenance difficult and uncomfortable;

2. When the export function appears multiple times, you need to copy a large number of redundant code in multiple places, and it looks uncomfortable;

Therefore, it is necessary to unify an excel export method. When using this method, you only need to input the data header, header field name, data list, and data table name to export the excel file.

Ii. Use a common export Method

As shown in, as long as four parameters are passed in, the export can be completed, and the use and maintenance become much simpler.

Okay, the goal is clear, and the next step is code implementation.

Obviously, the biggest challenge to implement this function is that, based on the index of a single piece of data and the order of the header fields, automatically calculates the coordinate values (such as A1 and B3) of each field in each data record in excel ).

Then let's analyze the cell coordinates of excel. Starting from A1, the numeric value is increased vertically, and the letter is increased horizontally. When the horizontal letter is changed to Z, the next letter is AA, then AB ,..., ZZ ,..., AAA...

In this case, we will know that the vertical coordinates are simple and can be calculated based on the index value of each data. What is difficult is the horizontal coordinates. How can we calculate them? After careful analysis of the horizontal coordinates, we can find that it is A 26-digit letter number. If A is regarded as 0, then Z represents 25. However, this number is not the same as the common hexadecimal and Octal numbers, because when Z is carried, the next number is AA instead of BA. In common hexadecimal systems, such as decimal 9 decimal places, it is changed to 10 instead of 00. in hexadecimal notation 0 x F places, it is changed to 0x10 instead of 0x00.

Therefore, you can refer to the hexadecimal conversion algorithm and then change it to obtain the method for calculating the horizontal coordinate of excel (10-to-26-to-26 ):

// AAA converts public static function toAAA ($ dec) {if ($ dec <0) return ''; $ y = $ dec % 26; $ x = floor ($ dec/26); return self: toAAA ($ x-1 ). chr ($ y + 65 );}

Complete code is attached.

The framework is Yii2, And the 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 => chr (65) foreach ($ titles as $ k => $ v) {$ keys [] = s Elf: 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 and style foreach ($ titles as $ key => $ title) {$ activeSheet-> setCellValue ($ keys [$ key]. '2', $ title); $ activeSheet-> 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 // Transcoder", $ fileName); header ('content-Type: APPLICATION/OCTET-STREAM '); ob_clean (); ob_start (); $ objWriter-> save ('php: // output'); ob_end_flush ();} // convert public static function toAAA ($ dec) {if ($ dec <0) return ''; $ y = $ dec % 26; $ x = floor ($ dec/26); return self: toAAA ($ x-1 ). chr ($ y + 65 );}

Iii. Export result example

Export result:

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.