Export to CSV or XLSX in the admin screens

Export to CSV or XLSX in the admin screens

User Export Button

After a few weeks of hiatus we’re back with useful WordPress tutorials. We all need vacation, and I told to myself: you need to recharge. This meant not touching any business related emails or doing any work on the site as well. It’s a good thing to unwind and rest your brain a bit. But now that we’re back it’s time for a new tutorial.

When you work with WordPress long enough, and on a various projects, you get asked to do certain things that fall off of the classic blogging platform spectrum. And then you get to see all the cool stuff you can actually do with WordPress. I’ve covered a lot in my tutorials that is not something you’d usually use on your site. And I’ve barely scratched the surface. One thing that I hope to learn to work in the future with is the REST API.

Since its beginnings WordPress grew out from the blogging platform into a full fledged CMS that you can use on various projects. You can have a full shop with customer base, a forums with a user base, or some other client base website (or app). One thing that you might want to keep track of are users. In other cases, you might want to see all the subscribers that you have (assuming that you’re using a plugin that stores them in a database as a custom post type). In the earlier article, I added the code with which you can export your users as a comma separated value (.csv) file

/********* Export to csv ***********/
add_action('admin_footer', 'mytheme_export_users');

function mytheme_export_users() {
    $screen = get_current_screen();
    if ( $screen->id != "users" )   // Only add to users.php page
        return;
    ?>
    <script type="text/javascript">
        jQuery(document).ready( function($)
        {
            $('.tablenav.top .clear, .tablenav.bottom .clear').before('<form action="#" method="POST"><input type="hidden" id="mytheme_export_csv" name="mytheme_export_csv" value="1" /><input class="button button-primary user_export_button" style="margin-top:3px;" type="submit" value="<?php esc_attr_e('Export All as CSV', 'mytheme');?>" /></form>');
        });
    </script>
    <?php
}

add_action('admin_init', 'export_csv'); //you can use admin_init as well

function export_csv() {
    if (!empty($_POST['mytheme_export_csv'])) {

        if (current_user_can('manage_options')) {
            header("Content-type: application/force-download");
            header('Content-Disposition: inline; filename="users'.date('YmdHis').'.csv"');

            // WP_User_Query arguments
            $args = array (
                'order'          => 'ASC',
                'orderby'        => 'display_name',
                'fields'         => 'all',
            );

            // The User Query
            $blogusers = get_users( $args );
            // Array of WP_User objects.
            foreach ( $blogusers as $user ) {
                $meta = get_user_meta($user->ID);
                $role = $user->roles;
                $email = $user->user_email;

                $first_name = ( isset($meta['first_name'][0]) && $meta['first_name'][0] != '' ) ? $meta['first_name'][0] : '' ;
                $last_name  = ( isset($meta['last_name'][0]) && $meta['last_name'][0] != '' ) ? $meta['last_name'][0] : '' ;

                echo '"' . $first_name . '","' . $last_name . '","' . $email . '","' . ucfirst($role[0]) . '"' . "\r\n";
            }

            exit();
        }
    }
}

This code will add a blue export button that will call the export_csv() function, which will then download the .csv file with the data we specified. Notice that we can control where we want the button to appear, by specifying $screen->id name. So in this way you can put it in your custom post type screen and download the info about the custom post type (just one of applications). All of this is nice, but .csv files are so old fashioned. These days we have a much more powerful tool to keep our tabbed values in order – excel spreadsheets.

Exporting excel spreadsheets

To be able to export your data to excel spreadsheets, you’re going to need to have an external library to turn your php to excel. One of such library is PHPExcel. You can get your source code for that library here. You’ll need to download the code, and extract the zip file, and then put the contetns of Classes (a PHPExcel.php file and folder) in your theme folder so that you can call it. The zip contains documentation, as well as examples, so I’d recommend that you scour it a bit to see all it can do. For our case the code would look something like this:

/********* Export to xlsx ***********/
add_action('admin_footer', 'mytheme_export_users');

function mytheme_export_users() {
    $screen = get_current_screen();
    if ( $screen->id != "users" )   // Only add to users.php page
        return;
    ?>
    <script type="text/javascript">
        jQuery(document).ready( function($)
        {
            $('.tablenav.top .clear, .tablenav.bottom .clear').before('<form action="#" method="POST"><input type="hidden" id="mytheme_export_xlsx" name="mytheme_export_xlsx" value="1" /><input class="button button-primary user_export_button" style="margin-top:3px;" type="submit" value="<?php esc_attr_e('Export All as XLSX', 'mytheme');?>" /></form>');
        });
    </script>
    <?php
}

add_action('admin_init', 'export_xlsx'); //you can use admin_init as well

function export_xlsx() {
    if (!empty($_POST['mytheme_export_xlsx'])) {

        if (current_user_can('manage_options')) {

            /** PHPExcel */
			include 'PHPExcel.php';

			/** PHPExcel_Writer_Excel2007 */
			include 'PHPExcel/Writer/Excel2007.php';

			// Create new PHPExcel object
			$objPHPExcel = new PHPExcel();

			// Set properties
			$objPHPExcel->getProperties()->setTitle( esc_html__('Test xlsx document', 'mytheme') );
			$objPHPExcel->getProperties()->setSubject( esc_html__('Test xlsx document', 'mytheme') );
			$objPHPExcel->getProperties()->setDescription( esc_html__('Test export users document for XLSX, generated using PHP classes.', 'mytheme') );

			// WP_User_Query arguments
            $args = array (
                'order'   => 'ASC',
                'orderby' => 'display_name',
                'fields'  => 'all',
            );

            // The User Query
            $blogusers = get_users( $args );
            $cell_counter = 1;

            //Set up the labels of the columns
            $objPHPExcel->getActiveSheet()->SetCellValue('A1', esc_html__('First Name', 'mytheme'));
			$objPHPExcel->getActiveSheet()->SetCellValue('B1', esc_html__('Last Name', 'mytheme'));
			$objPHPExcel->getActiveSheet()->SetCellValue('C1', esc_html__('Email', 'mytheme'));
			$objPHPExcel->getActiveSheet()->SetCellValue('D1', esc_html__('User Role', 'mytheme'));

            foreach ( $blogusers as $user ) {
                $cell_counter++;

                $meta = get_user_meta($user->ID);
                $role = $user->roles;
                $email = $user->user_email;

                $first_name = ( isset($meta['first_name'][0]) && $meta['first_name'][0] != '' ) ? $meta['first_name'][0] : '' ;
                $last_name  = ( isset($meta['last_name'][0]) && $meta['last_name'][0] != '' ) ? $meta['last_name'][0] : '' ;

				// Add data
				$objPHPExcel->setActiveSheetIndex(0);
				$objPHPExcel->getActiveSheet()->SetCellValue('A'.$cell_counter.'', $first_name);
				$objPHPExcel->getActiveSheet()->SetCellValue('B'.$cell_counter.'', $last_name);
				$objPHPExcel->getActiveSheet()->SetCellValue('C'.$cell_counter.'', $email);
				$objPHPExcel->getActiveSheet()->SetCellValue('D'.$cell_counter.'', ucfirst($role[0]));

            }

            // Set column data auto width
            for($col = 'A'; $col !== 'E'; $col++) {
			    $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
			}

			// Rename sheet
			$objPHPExcel->getActiveSheet()->setTitle(esc_html__('Users', 'mytheme'));

			header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
			header('Content-Disposition: attachment;filename="users.xlsx"');
			header('Cache-Control: max-age=0');

			// Save Excel file
			$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
			$objWriter->save('php://output');

			exit();
        }
    }
}

Now when you click on ‘Export All as XSLX’ button, you’ll download a users.xlsx file with all users in their respective cells, all neat and easy to edit. Much better than comma separated values.

This is especially useful if you have a CRM system (which you can also implement with WordPress), when you need to handle a lot of information. Manipulating it in excel is much easier.

User Export Button
User export button added in the top nav (same is added in the bottom nav helpers)

Alternatives?

One of the things that came to mind that you could do is to export your data as .pdf file. You have a good library for that here (including examples). I haven’t done anything like that yet, but I recon some could find exporting to pdf useful. The other alternatives you have is to avoid coding altogether and use a plugin like Export WordPress data to XML/CSV which has a ton of options for you to use.

But if you need something simple, or if you want to implement the export data option to your plugin, the above code (with some modifications) will do just fine.

Hope you’ll find this tutorial useful, if you have any questions or comments, leave them in the comment section below, and as always: happy coding :)

6 comments

  1. Scott Thornton

    on

    Reply

    I have been trying to make this export data for specific roles only. Any ideas where to start? Thanks.

    • Denis Žoljom

      on

      Reply

      Hi!
      On the code where you have if ( $screen->id != "users" ) you can add the check for the user permissions as well. That way the button you add using JavaScript won’t be added if the user doesn’t have the certain permissions. Aside from that, looking back at the code, I’d probably try to see which filter I can use to place a button there using just php. This code that I wrote over a year ago could use some refactoring :D

  2. Adrian Simpson

    on

    Reply

    Thanks Denis for your quick reply. It will take me a bit longer to figure this out I am still a bit new to WordPress development. I will let you know when I get it working. Thanks again for a great tutorial.

  3. Adrian Simpson

    on

    Reply

    Hi Denis, thanks for the tutorial. I really like to use this code to export a custom post type contact form I setup. The fields are Full Name, Message, Email & Phone. I will able to get the export button show up by doing if($screen->post_type !=”the name of the post type”).. the area I am having problem with is foreach ($blogusers as $user), $meta = get_user_meta($user->ID); etc. I just can’t figure out the correct code for my custom post type instead of user. Any help I would really appreciate it.

    • Denis Žoljom

      on

      Reply

      Well that depends on what your data is saved as in the custom post type – a taxonomy, a post meta, or some other field (from ACF even). You could replace the `get_users()` query with the custom `WP_Query()` with specifications for your custom post type. And then you’d do the same thing I did with `foreach`, but in your case you’d do it in a `while` loop, like when you want to show your custom post type in a custom template for instace. Once you have the query, you can access any of the aforementioned data. And then just echo that data out like in the article for csv, or setting it as a data in the excel table :)

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.