Looking for more information on how to do PHP the right way? Check out PHP: The Right Way

Handling Intensive Tasks with Laravel
Apr 05, 2016 @ 12:48:36

The Toptal blog has a post for the Laravel users out there showing you how to handling intensive tasks in your applications. In this case it's creating a background job (run via cron) to import information from Excel spreadsheet formatted file into the database.

When dealing with time consuming resource intensive tasks, most PHP developers are tempted to choose the “quick hack route.” Don’t deny it! We’ve all used ini_set('max_execution_time', HUGE_INT); before, but it doesn’t have to be this way.

In today’s tutorial, I demonstrate how an application’s user experience may be improved (with minimal developer effort) by separating long-running tasks from the main request flow using Laravel. By making use of PHP’s ability to spawn separate processes that run in the background, the main script will respond faster to user action.

He starts with why PHP isn't particularly a good choice for long running requests and how making users wait for it to complete is a bad thing. He then walks you through the setup of a basic Laravel application that includes the maatwebsite/excel library for the Excel handling. He shows his configuration setup, both in the Nginx and Laravel side, to handle serving up the app. He uses Laravel migrations to set up the database and the models, routing and logic (controller) to handle the incoming Excel file for import. With this in place he then creates the command type to process the file and save the information it contains to the database. Finally, he ends the post with the cron configuration you'll need to handle the import, running it nightly at midnight.

tagged: tutorial intensive task excel import database laravel

Link: https://www.toptal.com/laravel/handling-intensive-tasks-with-laravel

BitExpert Blog:
Think About It: PHP/PostgreSQL Bulk Performance (Part 3)
Jul 24, 2015 @ 10:46:06

On the bitExpert blog they've continued their "Think About It" series of posts looking at optimizations that can be made to different technologies in their stack to increase performance. In this third part of the series they focus in on the changes made to help speed things up with the PostgreSQL database backend.

This article is the last of a three-part series and describes how we optimized the persistence process of bulk data in our code in combination with PostgreSQL. Make sure you covered the first article about how we tweaked PHPExcel to run faster while reading Excel and CSV files and the second article about how we optimized our data processing and reached performance improvements tweaking our code.

They work from the example code provided at the end of part two and update the "update" handling to optimize it a bit. By default it executes an update query for each record so, instead, they modified it to perform a bulk update with an "update from values" format. They could then migrate to a "save all" handler with the complete set of records to save.

tagged: performance postgresql bulk series part3 tutorial phpexcel excel csv

Link: https://blog.bitexpert.de/blog/think-about-it-php-postgresql-bulk-performance-part-3/

SitePoint PHP Blog:
Generate Excel Files and Charts with PHPExcel
Sep 11, 2014 @ 09:48:55

SitePoint's PHP blog has a new post that walks you through the creation of Excel files with the help of the PHPExcel tool, a part of the overall PHPOffice package.

After my article “How To Make Microsoft Word Documents with PHP” (using Interop capability under Windows), there were quite a few comments urging a pure PHP implementation, i.e., only using a universal PHP library to manipulate Office files. In this article, we will see how to use a PHPExcel library to provide an “Export to Excel” function in a web app so that the user can export the data into an Excel 2007/2013 file for further analysis.

His example spreadsheet shows game information for an NBA team (the LA Lakers) including some charting and analytic data in the exported output. He sets up a simple Silex application and both a base and export endpoints. He also shows you how to create the PHPExcel instance, configure it with properties like creator, title and a set of keywords. With this object in place he moves into setting data for each of the rows/cells, populating it from a database table. He also includes examples of creating more than one sheet of results, inserting formulas and creating the charts mentioned earlier.

tagged: phpexcel tutorial excel microsoft chart formular export

Link: http://www.sitepoint.com/generate-excel-files-charts-phpexcel/

Best in Class Libraries to Generate Excel Reports
Aug 14, 2013 @ 09:18:55

WebDeveloperJuice.com has posted a list of several PHP tools that you can use to create Microsfot Excel reports from your data (some a bit more feature-packed than others).

Every time somebody does something unique with PHP that is far beyond the standards and norms of PHP applications. One must say you will truly be overwhelmed by the capabilities and features of PHP language. Microsoft Excel is widely used by most of the companies as a report generation tool and data analysis tool. [...] In this post we will have a look at the various Libraries that the PHP developers can use for generating Excel Reports in any PHP Application and for exporting the data to Microsoft Excel Spreadsheets.

The tools that made their list are:

  • PHP Export Data
  • PHP Excel Library
  • Excel Writer (XML) PHP Library
  • Php-Excel
  • Pear Excel Writer Library
tagged: microsoft excel report generate library project

Link: http://www.webdeveloperjuice.com/2013/07/05/best-in-class-libraries-to-generate-excel-reports-in-php-applications/

How to Generate a Complete Excel Spreadsheet From MySQL
Nov 23, 2011 @ 17:52:54

On NetTuts there's a new tutorial showing how to take data from a MySQL database and translate it into a usable Excel file with the help of some simple PHP.

A CSV (Comma Separated Value) file is usually sufficient for exporting MySQL data as an Excel Spreadsheet. These CSV files are data only though. A real Excel spreadsheet has formatting, formulas and perhaps even graphics - the difference between a simple method of data transfer and a professional report. This tutorial shows how to use open source PHP components to create "real" Excel spreadsheets from MySQL SELECT statements.

With the help of the PHPExcel tool, making Excel-formatted files is a simple process. They show how to label columns, pull data out with a "quick and dirty" SQL statement, formatting the results to something a bit more clean and iterate through the pages of data to push them into the spreadsheet (including formulas). You can download the full source to get everything in one shot.

tagged: excel spreadsheet tutorial phpexcel mysql database


Brian Swan's Blog:
Rendering SQL Server Reports as Excel Documents with PHP
Sep 27, 2010 @ 10:15:51

Brian Swan has a new post to his blog that looks at a method for pulling back the reports from a SQL Server instance in something a bit more readable/useful - an Excel document.

One of the most common questions [from his previous post] has been "How do I render a report as an Excel document?" I've been telling folks that this is easy with the SSRS SDK for PHP (and it is easy), but when I sat down to do it, I ran into a problem. So, in this post, I'll show you how to render a SSRS report as an Excel document and how to avoid the one problem that caused me headaches.

This post's a short one with a code snippet (and download) showing how to connect to the server and create a "RenderAsEXCEL" object and request the report information with that in the rendering function. Then it's just as simple as pushing that information out to a file as a ".xls". The included download will also let you pull down the report as HTML or as a PDF.

tagged: sqlserver report excel format pdf html


Ilia Alshanetsky's Blog:
PHP Excel Extension
Aug 02, 2010 @ 13:12:02

In a new post to his blog today Ilia Alshanetsky talks about a PHP Excel extension to help with some output generation problems he was seeing (based on the LibXL libraries).

As we are doing more & more Excel output generation, this became a bigger and bigger problem. At first we've tried solving the problem via a newer PHP based Excel library, PHPExcel. Unfortunately, it is massive beast, that is not only slower than the old Spreadsheet Excel Writer/Reader, but memory hog too. [...] So, I turned to Google and found LibXL, which is a small, C++ (with C, C++ interfaces) library that promised really fast Excel reading & writing.

His extension gives you an object-oriented interface to the library that can generate Excel files with a lot less resources than some of the other PHP Excel extensions out there. He includes a sample benchmarking script he used to generate these stats.

tagged: excel extension libxl phpexcel github


Import and export data using PHPExcel
Jul 15, 2010 @ 10:21:12

On the ThinkPHP blog today there's a new post looking at their experience with PHPExcel to open, modify, save, etc Microsoft Excel files directly from PHP.

EAR's Spreadsheet_Excel_Writer combined with the project Spreadsheet_Excel_Reader on SourceForge was a good helper in the past - but only for BIFF5. BIFF8 support in spreadsheet excel writer has been a problem for a long time, and according to the authors, is still somewhat kludgy in the current version. So I needed an alternative. After a short research I stumbled upon PHPExcel which supports reading and writing of many formats in one API.

He lists some of these input and output formats and includes a simple example of how to use the tool. He creates a basic Excel file with a few different attributes (title, body, keywords) and show how to read in a document to extract the document's content.

tagged: import phpexcel export tutorial excel microsoft


Pablo Viquez's Blog:
Export Excel Spreadsheets using Zend Framework
Aug 25, 2009 @ 07:57:30

New on his blog today Pablo Viquez has this post looking at code that will let your Zend Framework applications export to Microsoft Excel spreadsheets (via the Spreadsheet Excel Writer PEAR component).

Last week, I had to allow the user to export a given report into an MS Excel file format. The application uses Zend Framework 1.9.1 and so far ZF does not support for "Office" formats, so after searching for a nice implementation, I found a PEAR module called Spreadsheet Excel Writter, which looked pretty good, it had very good documentation and the code was clean and well structured so I wanted I give it a shot.

He lays out the structure of the sample application and, using a context switch on the request, he creates a controller that responds to the "/report" by calling a special model/view combination that uses the PEAR package to push out the custom "report.xls" file.

tagged: zendframework pear excel spreadsheet


Vinu Thomas' Blog:
Saving Data into Excel the Easy Way using PHP
Jul 02, 2009 @ 10:18:51

Vinu Thomas has a new post to his blog looking at using the MS-Excel Stream Handler class to push your data out to Excel (in more than just a CSV file).

If you’re looking for an easy way to output your data from a PHP script into a Excel file, you’ve got to check out this script which I came across at the PHP Classes site. What this script essentially does is to implement a stream handler interface to write Microsoft Excel spreadsheet files.

Code snippets are included showing how to structure the data for the import and how to push that generated Excel file out to the user's waiting client.

tagged: class excel save