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

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


Juozas Kaziukenas' Blog:
Creating Excel documents on the fly
Feb 17, 2009 @ 12:55:45

Juozas Kaziukenas has a new blog post that looks at creating Excel documents on the fly with the Spreadsheet_Excel_Writer PEAR package.

In one of my projects, we need to send emails to our suppliers with new orders as Excel spreadsheets. We used to do it by hand, but when you have 10 suppliers and you make 200 products orders per week, after some time you are just tired of creating Excel spreadsheets, mailing them and so on. So, my task was to implement automatic spreadsheets creation with PHP.

His example creates a basic file with a set of "Items", some formatting and a set of code/title items to fill out each row. The end result can then be packaged up and sent out as a complete Excel XLS file.

tagged: excel document create tutorial spreadsheelexcelwriter pear package spreadsheet