Export to Excel


This is a demonstration of the creation of a dynamic Excel workbook with data taken from a live online database using PHPSpreadsheet.




PHPSpreadsheet Solution



If your company regularly creates Excel workbooks from data exported from your CRM or company database, you will understand how time consuming it can be to compile them.

It may be the case that CRM or database reports are not always suitable for the reporting data you require. Sometimes the reports don't always match your company needs and you may prefer the dynamic flexibility of a workbook. For example, you may want to model the impact of a price increase or decrease in your data which you cannot do in an exported PDF or CSV file.

Some software packages can export data to Excel and offer formatting for headings and some number formats. This solution aims to go a step further by dynamically building in Excel formulae and user-defined formatting.

Demonstration



This live demonstration generates a workbook containing:


This is an example of using the PHPExcel library for generating and outputting data from a server running PHP/MySQL to a formatted Excel workbook. The live data is taken from database tables I set up for my data project.

Additional code has been created for the customisation of the output and for dynamic functionality. The output and formulae will adapt to the amount of data exported, so it doesn't matter if there are five records or five thousand records found, the formulae will adapt and still be accurate.

Try It Out!



This test demonstrates interaction with the live database and produces four sets of data into one workbook, along with formatting and additional total calculations.

The workbook itself is fairly simple in regards to the formulae in use, but can be more complex and also use multiple worksheets within the workbook.

To demonstrate that this is live, the choice is given below to allow you to select a staff member along with a selection of both reporting colours and number formats to output their sales data.

Please select your preferences from the form below and click the 'Submit' button.









You may find that this could help your company.

In the example given above, there are eight sales staff to choose from. Each sales staff member would require their own report, containing only their individual sales figures. If you wanted to generate eight reports from Excel, this would be time consuming and you may need to be very careful that you didn't include other sales staff data in the spreadsheets.

For this example, you could export one set of data from your CRM or database and import it directly. You could then generate the multiple separate worksheets as above, just by selecting criteria from drop-down boxes and then simply click on a button to generate each workbook you need, complete with a staff members' name.

Automated Steps



Description details are added to the workbook, which can be viewed by clicking on the file properties once it has been saved to your computer. These are:

  • Title
  • Subject
  • Tags
  • Categories
  • Comments

Properties


The following steps are carried out within the code for each of the four created tables:

  • Data is extracted from a database for the selected staff member, using a live MySQL query
  • A table title is added
  • Headings are added to the Excel worksheet
  • The table heading cells are formatted in bold, coloured text with the chosen coloured background
  • The data extracted is output to a PHPSpreadsheet object as a dynamic 2D table
  • The table is formatted with a thin border
  • Number cells containing financial information are formatted using the selected number format
  • SUM formulae are added dynamically to the relevant cells to show calculated totals

Two named ranges have been dynamically applied to the Name Manager in the Excel workbook and are named 'widgetSales' and 'SalesTable'. These relate to column G and the range A to G for the first table respectively.


Excel name manager screenshot


The range widgetSales is used for populating the MAX, MIN and AVERAGE formulae underneath the first table.

Once the data has been loaded into the spreadsheet, the columns are set to auto-width.


Also the following actions are processed:

  • The worksheet is named as 'Widget Sales'
  • The worksheet tab is given a colour
  • A new worksheet tab is created with the name of 'filtered'
  • A sales table is inserted into it
  • A filter is applied to the table

The completed Excel workbook will then download to your web browser and the staff member's name that you selected will be included as part of the filename.


The demonstration above does not include a datepicker control for selecting dates. This is so that the date range is wide enough to ensure that grouped data is always available in the generated Excel worksheets in these examples. This should make the difference between the tables obvious. A datepicker can be added to the form in a corporate environment for the flexibility of selecting date ranges.

The web form above where options are available for selection ensures that there are a possible 112 permutations. This should be enough to demonstrate that the results are generated live and are not pre-built worksheets.

Note that this approach is suited for regularly issued Excel-based reports where only the data or criteria range changes. For Excel reports that are subject to continuous structural changes, manually created Excel files may be more time effective.