How to load phpspreadsheet for excel exports

The amr-users-plus export to excel feature uses the phpoffice/phpspreadsheet library. The library must be accessible on the server. Here are two ways to make it available:

Option 1: the easy low tech way: use the plugin cbxphpspreadsheet

Add the plugin https://github.com/codeboxrcodehub/cbxphpspreadsheet to your wordpress installation. The plugin does not need to be active, it.only needs to exist in your wordpress plugins folder. It may be safer to activate the plugin so that you don’t accidental delete it thinking it is not in use.

The amr-users-plus plugin will check for it’s code and load the necessary files if the files are found. Then in the plugin list settings, simple tick the checkbox to show the ‘export to xls’ option in the lists you want.

Option 2: use composer to load the spreadsheet.

if you have the composer dependency manager available to you, follow the instructions on the phpspreadsheet documentation. Many hosts like mine make composer available. I have used icdsoft since well before 2009. Basically on the command line type:

composer require phpoffice/phpspreadsheet

If you want to install composer, please see instructions available on the internet, such as:

If that all looks too hard, please see option 1 above.

The amr-users-plus plugin will look for phpspreadsheet as follows:

It first looks for the composer files: 

dirname(ABSPATH).'/vendor/autoload.php' 

and uses that if found.  If that is not found, then it looks for

WP_PLUGIN_DIR.'/cbxphpspreadsheet/lib/vendor/autoload.php' ;   

The amr-users-plus XLS export feature will not function if phpspreadsheet and the writer class are not found. It will give you an error message instead.

Before and After text for fields

It is possible to add text to the field value before or after the field value. There are not many good reasons to do this:

  • Repeated static text just clogs up the screen, headings are better.
  • Adding html for styling reasons is not suggested.
  • Better to style with css.

However let us assume for example that you wish to add the text ‘Born: ‘ to every Birthdate field with content. You can add it to the ‘before’ text of the Configure list> Before & After tab. See screen shots below.

Note that:

  • This ‘before’ text is not shown if the field is empty.
  • Note that the text becomes part of the field value.The full text is included in the cache and the csv and excel extracts.
  • It is strongly suggested that you do NOT use this feature for styling. CSS is a better way to style fields. There are a lot of meaningful css selectors offered in the user lists so that one can target almost any field.
  • It is also possible to keep the field values clean for csv and excel and to use css to add the extra text. This can be done using the before and after pseudo elements. These pseudo elements can also be used to hide and replace content as you can see on this user list demo page where the field content can be replaced by text or an image.
Specifying the text to display before a field
The ‘before’ text is displayed when there is a value in the field
In the current ‘cache’ versions the ‘before’ and ‘after’ text is included in the cache and the csv (historical reasons).
An alternative to adding text in the configuration, is to add the extra text via css, so that it does not appear in the csv or excel extracts.

New formatting options

In a recent amr-users-plus update, a variety of new formatting options were added. This was prompted by a request to handle buddypress uploaded files, like resumes or images that are stored with a relative url.. In responding to this, the ability to choose a variety of formats for urls, telephone numbers, emails etc was added to amr-users-plus. See the buddypress user directory demo site for some examples. The wordpress dashicons font is used, enabling the icons to take on the colours of your theme. In addition, since it is possible for you to change the icons used as they are delivered using css.

As always I am happy to entertain requests for such features. I try to deliver them in ways that offer generic consistent use. Further updates are on the way to refresh other add-ons in line with a big refresh of the main amr-users plugin.

Example of various formats for files, urls, telephone numbers
Dashicon font allows the icons to take on the styling of your theme
Some settings

Excel user list export

Excel Download from Custom user list

amr users plus version 3.6 up includes the ability to offer a direct excel export of the user list data, instead of, or as well as the .csv export.   It needs the phpoffice/phpspreadsheet library to be available (see below)

Demo

excel wordpress user export demo.

Instructions:

  1. Make the phpspreadsheet library available. There is an easy low tech way to do that by adding the cbx plugin https://codeboxr.com/php-spreadsheet-library-wordpress-plugin/ or via composer. For instructions click here.
  2. Tick the ‘xls export’ checkbox in the userslists overview settings. (Similar to the existing .csv option). and then view the list and test it!
excel icon or text link
In the general settings you can specficy the icon or text link for the excel export
Option to display excel export or not
To offer the excel export option in a user list on the front end, tick the checkbox

The XLS export will not function if phpspreadsheet and the writer class are not found.

On demand filtering on a new field

How to filter by a field not currently displayed?

(requires amr-users-plus

  1. Enter some data for some users.  Ideally all users should have a value for that field.  Plugin will show a ‘-‘ for users with a blank value.
  2. Display the field (possibly temporarily)
  3. Define the filtering
  4. Rebuild cache  (will soon no longer be needed)
  5. Location of filter html

Display the field

  • Enter a column number in the display order column for the “First Role”
  • Click update field settings
  • Click Rebuild cache to be sure field shows

This tells the plugin you want to use that field in that list and where to display it.

Adding field to list for filtering

Specify that field should be displayed for now

Check that field works, some values exist:

Define Filtering

  • Click “Filtering”,
  • Select an html input type from the dropdown.  Note some options may require special add-ons.
  • Click Update
  • Click Rebuild cache now (for now, coming version will remove this)

In current version of plugin the list will show with filter but the values will not be there – plugin hasn’t had a chance to  ‘save’ them for filtering yet.

Rebuild!

Cache versions of plugin  will use this rebuild to save the possible values of role in your db, including any custom roles, but excluding any for which there are NO  users.

If  values (eg roles) and users with those values (roles) are added over time – a rebuild cache will find and add them.  The filter should now be visible with values for existing users.

Rebuild the cache (Realtime Query version coming)

  • just making sure….. if you do not see the values, then rebuild the cache!
  • still don’t see all values you expect to see ? Is there a user with that value ? No ? Create/update and rebuild the cache.

Filtering Html Location

 
 

Setting the location of the filters

In the configure screen in the overview section, under f’iltering’.
 
  • Select
    • in table or
    • above or
    • none (to switch off while saving settings)
  • Click Update.
FilteringSetup
FilteringSetup

GDPR Compliance and the plugins

The amr-users and amr-users-plus* suite of programs enable websites to list their user data from the wordpress user database and related tables (eg CIMY user tables).  See the individual addon plugin descriptions to see the tables they access. The plugins do not add or store any other personal user data – they are aimed at simplifying the development of lists or reports of user data.

The design of the user data and the decision to display the data in a user list generated by the plugins is the website’s responsibility. The base plugin currently caches user lists to simplify querying the website user data for the designed user list. The cache content is determined by the website – it is the user list content. These caches are temporary. They are stored in the wordpress database and are subject to the same protections as other website data in that websites wordpress database. If a user record is updated or deleted, the next cache run will delete all existing user data for that list and create a new cache.

Privacy and consent to the display of any personal data in a user list is the website’s responsibility. The base plugin offers a setting to determine whether a user list is can be shown publicly or not. The website is responsible for determining what goes into any such user list and whether these lists are made publicly available or simply run in admin by an appropriately authorised person.

The plugin offers the feature of a csv extract of a user list. If a list is public, the csv extract is stored as a file on the websites server. Security and safety of this file is the website’s responsibility. If the list is not public, the csv extract can be generated real time by a user who has the appropriate security capability.

When the plugin is deleted, the cache tables and plugin options are also deleted.

Many of the add-on plugins merely format the user data or offer searching, filtering and navigation features unrelated to the capture or tracking of the data.  In some cases they are merely example plugins and it is the website’s responsibility as to how these plugins are modified and what they data they might then make it possible to store.

The above applies to the following user related plugins:

  • amr-users
  • amr-users-plus
  • amr-users-multisite
  • amr-users-agequery
  • amr-users-plus-buddypress
  • amr-users-plus-cimy
  • amr users plus contexture groups
  • amr users plus date ranges
  • amr users plus edd
  • amr users plus gravity
  • amr users plus groupings
  • amr users plus itthinxgroups
  • amr users plus magicmembers
  • amr users plus mytreasures
  • amr users plus pmp
  • amr users plus post counts by dates
  • amr users ranking
  • amr users plus s2member
  • amr users plus social
  • amr users plus subscribe2
  • amr users plus symposium
  • amr users plus upme
  • amr users plus video link
  • amr users plus wishlist
  • amr users list users like me
  • amr user to user emails

 

Field types and formats

A range of formats are available for each type of field.  The plugin will try work out what type each field is.  If it doesn’t, you can set it yourself in the ‘Fields & Nice Names settings.

Then if the ‘Format’ section of each list, formats can be chosen for that field.

Dates

For example:  Date Time fields and Unix time-stamp fields (and even an ‘ambiguous date in case you have inconsistent data) can be formatted as:

  • Date and time
  • Time
  • Date
  • Ago (human time difference)
  • Age
  • Birth month

The actual format of the date, time and datetime options can also be customised.  The options are the usual wordpress options AND the custom options from your general settings screen.

Email example:

user_email field will be formatted with the ‘mailto’ linktype and then each site can tailor this with their own css.  Perhaps use wordpress dashicons to show a email icon? 

You should ensure that your data adheres to the field type specified.  Any invalid data will generally be shown unformatted / raw.

See the screenshots below and the demo sites for more possibilities.

Some Field Types
More Field Types
More Field Types
Image Field Formats
Image Field Formats
Post Id Formats
Post Id Formats
Boolean Formats
Boolean Formats
Date Formats
Date Formats
Url Formats
Url Formats
Email Formats
Email Formats
Phone Formats
Phone Formats
Formatting Post ids – they might be images & they might not!

Change the displayed text in a plugin or theme

Changing the text in a plugin or theme without editing the plugin or theme files is surprisingly easy IF the plugin or theme has made the text translateable.

Using a plugin like https://wordpress.org/plugins/loco-translate/, ensure the language chosen matches your wordpress language, and choose a location for your files (loco translate helps) that
will be accessed by the plugin or theme (some help with this) and
will not be overwritten by updates – loco translates helps and some plugins/themes help too by offering another lookup location. The recommended custom language file location is /wp-content/languages/.

For more information see http://icalevents.com/3445-an-accent-for-your-website/

change wordpress plugin text

Search users text replaced using translation tools – translating english into english!

Controlling access to user lists

Define whether lists may be shown in public to non logged in users and whether they may be shown in the admin menu to users who do not have manage users capability.

Summary of access control options:

  • List overview settings:
    • ‘public’ lists (ie lists ticked ‘public’ in the settings) can be viewed by anyone
    • non-public lists can only be viewed by people with ‘list_users’ capability
    • ‘show in menu’ (v 4.48 upwards with matching amr-users-plus) will cause  that list to be shown separately in the admin menu under users for users with ‘manage_options’ and ‘list_users’ capabilities
    • if some lists are not shown in the menu, an ‘all lists’ submenu will show for users who have ‘manage_options’ capability
  • Users with ‘manage_options‘ or ‘manage_user_lists’ can create and edit user lists.

Define public lists for front end

User list overview settings
Who can see list with setting:PublicNot Public
Non logged in User
Subscriber
user with ‘list_users’ capability
user with ‘manage_options’
User List public setting

Define which lists appear in the admin menu

  • As per the screen shot above, use overview settings available when amr-users-plus is active to remove or select which lists appear in the admin side menu.
  • An all lists menu option is shown to users with ‘manage_options’ or ‘manage_user_lists’ capability.
Organise, sort with drag & drop, rename and renumber list
A menu option will list all lists

Define which roles see which lists in admin menu:

Who can see list in menu with setting:Show in menuDo not show in menu
Non logged in User
Subscriber
user with ‘list_users’ capability
user with ‘manage_options’
User List ‘show in menu’ setting (admin menu) available with amr-users-plus v3.2 upwards. If addon not acrtive, all lists will show in menu by default.