In order to export data to Excel, you need to connect PHPExcel.php. This library is needed download, and upload to your theme folder.
We connect it with the following code in the functions.php of your theme:
1 | require_once 'Classes/PHPExcel.php'; |
Next we need to call the export function, I used the GET request:
1 2 3 | if (isset($_GET['excel-export'])) { } |
You also need to think about security, for example, so that this request can only be performed by the administrator:
1 2 3 | if (current_user_can('manage_options')) { } |
In the example, I will consider exporting user data, in principle, this option can be altered to fit your needs, because he is universal. The entire request code will look like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 | if (isset($_GET['excel-export'])) { if (current_user_can('manage_options')) { add_action('init', function () { error_reporting(E_ALL); if (PHP_SAPI == 'cli') die('This example should only be run from a Web Browser'); require_once 'Classes/PHPExcel.php'; $objPHPExcel = new PHPExcel(); // Set properties $objPHPExcel->getProperties()->setTitle(esc_html__('Results', 'mytheme')); $objPHPExcel->getProperties()->setSubject(esc_html__('Results', 'mytheme')); $objPHPExcel->getProperties()->setDescription(esc_html__('Export users document for XLSX.', 'mytheme')); // WP_User_Query get users by specified criteria $args02 = array( 'role' => 'subscriber', 'order' => 'ASC', 'orderby' => 'display_name', 'fields' => 'all', ); // The User Query $blogusers02 = get_users($args02); $cell_counter = 1; // Paint the background with our headlines. $objPHPExcel->getActiveSheet()->getStyle('A1:AM1')->getFill() ->setFillType(PHPExcel_Style_Fill::FILL_SOLID) ->getStartColor()->setARGB('a5b6ca'); // Sign the columns $objPHPExcel->getActiveSheet()->SetCellValue('A1', esc_html__('Name', 'mytheme')); $objPHPExcel->getActiveSheet()->SetCellValue('B1', esc_html__('E-mail', 'mytheme')); $objPHPExcel->getActiveSheet()->SetCellValue('C1', esc_html__('Profession', 'mytheme')); $objPHPExcel->getActiveSheet()->SetCellValue('D1', esc_html__('Role', 'mytheme')); $objPHPExcel->getActiveSheet()->SetCellValue('E1', esc_html__('Description', 'mytheme')); // Actually the cycle itself: foreach ($blogusers02 as $user) { $cell_counter++; // The counter, it is needed to move to the next line. $meta = get_user_meta($user->ID); // Fetching data from standard fields $role = $user->roles; $email = $user->user_email; // Fetching data from custom fields $first_name = (isset($meta['user_first_name'][0]) && $meta['user_first_name'][0] != '') ? $meta['user_first_name'][0] : ''; $profession = (isset($meta['user_profession'][0]) && $meta['user_profession'][0] != '') ? $meta['user_profession'][0] : ''; $description = (isset($meta['user_description'][0]) && $meta['user_description'][0] != '') ? $meta['user_description'][0] : ''; // Fill the table cells $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->SetCellValue('A' . $cell_counter . '', $first_name); $objPHPExcel->getActiveSheet()->SetCellValue('B' . $cell_counter . '', $email); $objPHPExcel->getActiveSheet()->SetCellValue('C' . $cell_counter . '', $profession); $objPHPExcel->getActiveSheet()->SetCellValue('D' . $cell_counter . '', $description); } // Set automatic width for cells for ($col = 'A'; $col !== 'D'; $col++) { $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true); } // Give the tab name $objPHPExcel->getActiveSheet()->setTitle(esc_html__('Results', 'mytheme')); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="SbmChampions2019Survey.xlsx"'); header('Cache-Control: max-age=0'); // Save the file $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); exit(); }); } } |
As a result, an Excel document will be saved on your PC with user data.
In principle, instead of selecting user data, you can select anything, whether it is a page, post or product, and substitute the desired data.
If you need to get a document with two or more tabs, then you need to write the following code after the rename code of the first tab:
1 2 | $objPHPExcel->createSheet(); $objPHPExcel->setActiveSheetIndex(1); |
This code creates a new tab and makes it active.