While working on a long-standing CMS (Content Management System) project I needed to find a way to export database results into a working Excel document. For years I have been creating functions which exported .CSV files which were then easily imported into Excel, but for this project a working Excel document was needed and I was lucky enough to stumble across PHPExcel. I am very pleased with this code and I have spent the last week learning it and writing an export function. You can download your own copy here https://phpexcel.codeplex.com/.
The functionality of PHPExcel is very in-depth and I only scratched the surface of what it can do. Basically it can do just about anything Excel can do. One thing it didn’t do was save number formats in the Excel accounting format, which was having the $ sign on the left of the cell while the numbers would be aligned right. Luckily it is very easy to modify and contribute to PHPExcel and I managed to figure out a way to add the accounting format.
This is how you add accounting format to PHPExcel
First open PHPExcel_1 > Classes > PHPExcel > Style > NumberFormat.php
Within this class add the bolded text.
class PHPExcel_Style_NumberFormat extends PHPExcel_Style_Supervisor implements PHPExcel_IComparable
{
// Add this
const FORMAT_ACCOUNTING = ‘_(“$”* #,##0.00_);_(“$”* \(#,##0.00\);_(“$”* “-“??_);_(@_)’;
}
Then when writing your export code you now have a new number format
$objPHPExcel->getActiveSheet()->getStyle(‘D’.$current_row.’:E’.$current_row.”)->getNumberFormat()->applyFromArray(
array(
‘code’ => PHPExcel_Style_NumberFormat::FORMAT_ACCOUNTING
)
);
One very important Item to note, this only works in Excel itself, not Open Office! If you are using Open Office it will not display the number format like Excel does, this is because of Open Office limitations, not PHPExcel.
While I haven’t found an official WordPress plugin for this, this site is a WordPress site and as you can see I have integrated the functionality into my website. Please use the form below to test out and see how it works. I hope you find this functionality as useful as I do.
Test it out yourself and export an Excel file