Manipulate Excel Spreadsheets with PHP on Linux

Have you ever faced a situation when you need to manipulate Excel spreadsheets with PHP on the server that is running Linux? If you had a Windows Server you could use PHP COM extensions. However they are unavailable on Linux.

Hopefully, there is a solution. It is called Open XML. It’s a new format of Microsoft Office documents introduced in Microsoft Office 2007. Basically, an Open XML file is a ZIP archive that contains XML files that represent the document mark-up. You can view it yourself or read some documentation, my article isn’t about Open XML, but about the PHP library for working with Excel 2007 files. It also supports Excel 97 format by incorporating a PEAR library.

The library is called PHPExcel. It allows you to read/write Excel spreadsheets, save them in many formats including PDF and HTML. It supports formulas,styles and etc.

It requires:

• PHP 5.2+
• GD extension
• XML extension
• ZIP extension

I have these two simple examples that show you how to create an Excel spreadsheet and save it in several formats and how to read a spreadsheet and display it on the HTML page.

How to import csv file to mysql using PhpMyAdmin

Here's one way to get your Excel file into MySQL via phpMyAdmin.

Reading/Parsing Excel Spreadsheet using PHP

In this tutorial we will see, how we can easily parse or read an Excel sheet and display the parsed data to user. Even though we are going to display the output, the other extension of this technique is that we can parse an excel sheet filled with data, in set format, and convert it into SQL statement and insert the data into database. Now this is an easy task on an windows based server, to do the same thing in Linux we need to use some other libraries.

This tutorial assumes that you know PHP.

Reuse Excel business logic with PHPExcel

In many companies, business logic resides in Excel. This business logic is sometimes created by business analysts and sometimes by business users who want to automate parts of their everyday job using Excel. This same Excel-based business logic is often copied into an application (i.e. a website) and is maintained on 2 places: if the Excel logic changes, the application should also be modified. Did you know you can use PHPExcel to take advantage of the Excel-based business logic without having to worry about duplicate business logic?

Reading and Writing Spreadsheets with PHP

In this article, I'll introduce you to two packages that make it surprisingly easy to hook your PHP scripts up to a Microsoft Excel spreadsheet and extract the data contained therein. I'll also show you how to dynamically create a new spreadsheet from scratch, complete with formulae and formatting, and import data from a spreadsheet into a database. So come on it, and let's get started!