Tag Archives: Excel

PHPExcel: How to Bold a Cell’s Value CodeUnit 14 JUL 2010

PHPExcel is a phenomenal Excel generating library for PHP which I have mentioned on this site numerous times before, and which is currently my defacto way for quickly knocking out all those annoying Excel downloads that clients always seem to MUST have!

Today’s quick code tip is on how to embolden a cell’s value – in other words how to replace that big B bold button with some code! ;)

Now the recommended method for applying styles to cells or cell ranges is by making use of style arrays to set a number of styles all at once, mostly because setting styles one by one can turn out to be a little resource intensive.

So in keeping with this then, here is how you would make a cell’s contents bold:


$workbook = new PHPExcel;
$sheet = $workbook->getActiveSheet();
$sheet->setCellValue('A1', 'Hello World');
$styleArray = array(
'font' => array(
'bold' => true
)
);
$sheet->getStyle('A1')->applyFromArray($styleArray);
$writer = new PHPExcel_Writer_Excel5($workbook);
header('Content-type: application/vnd.ms-excel');
$writer->save('php://output');

And there you go, as simple as that!

Related Link: http://phpexcel.codeplex.com

PHPExcel: How to Set the Width of Cells CodeUnit 12 JUL 2010

PHPExcel is a phenomenal Excel generating library for PHP which I have mentioned on this site numerous times before, and which is currently my defacto way for quickly knocking out all those annoying Excel downloads that clients always seem to MUST have! ;)

Today’s quick code tip is on how to specify or set the width of individual columns in your worksheet.

In order to do this, we make use of the handy setWidth function that allows us to specify the width of our targeted column using the standard Excel column width units.

So let’s see this in code then:

$workbook = new PHPExcel;
$sheet = $workbook->getActiveSheet();
$sheet->getColumnDimension('A')->setWidth(15);
$sheet->getColumnDimension('B')->setWidth(30);
$sheet->getColumnDimension('C')->setWidth(45);
$writer = new PHPExcel_Writer_Excel5($workbook);
header('Content-type: application/vnd.ms-excel');
$writer->save('php://output');

And there you go, as simple as that!

Related Link: http://phpexcel.codeplex.com

PHPExcel: How to Merge Cells CodeUnit 09 JUL 2010

PHPExcel is a phenomenal Excel generating library for PHP which I have mentioned on this site numerous times before, and which is currently my defacto way for quickly knocking out all those annoying Excel downloads that clients always seem to MUST have!

Today’s quick code hint deals with how one sets about merging a cell range using PHPExcell. Unfortunately for me though, the solution is so simple that this will only take one line of my time! :P

So what is the solution then?

Well PHPExcel features a nifty little function named mergeCells, and feeding it a range results in that range being merged into one great big cell.

Nifty.

In code it would look a little something like this:

$workbook = new PHPExcel;
$sheet = $workbook->getActiveSheet();
$sheet->setCellValue('A1','A pretty long sentence that deserves to be in a merged cell');
$sheet->mergeCells('A1:C1');
$writer = new PHPExcel_Writer_Excel5($workbook);
header('Content-type: application/vnd.ms-excel');
$writer->save('php://output');

And there you go, as simple as that!

Related Link: http://phpexcel.codeplex.com

Setting Your Worksheet Printing Layout Options in PHPExcel CodeUnit 18 APR 2010

I’ve gone on and on in length over just how awesome the PHPExcel OpenXML spreadsheet reader/writer project is over the last bit here in this blog, and today is no exception as I take a quick look at how one can set the page printing layout when generating an Excel spreadsheet.

Now we all know how annoying it is when we right-click on an Excel spreadsheet and select print, only to recoil in horror when we realise that the columns don’t exactly fit nicely on a single page, meaning a spill-over effect and thus a lot of wasted paper!

So ideally what one wants to do for a spreadsheet is preset the document’s printing preferences in order to avoid handing over this quick print annoyance to our spreadsheet users.

Naturally, being as awesome a system as what it is, PHPExcel comes bundled with a number of methods that allow us to specify page size/type and orientation, as well as scaling options.

For this example, we’re going to set our spreadsheet to print in A4 size with a portrait layout and scaled so that it fits to 1 page wide and infinite pages tall.

// Redirect output to a client’s web browser (Excel5)
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=demo.xls");
header('Cache-Control: max-age=0');

// PHPExcel
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set Orientation, size and scaling
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToPage(true);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToWidth(1);
$objPHPExcel->getActiveSheet()->getPageSetup()->setFitToHeight(0);

// Generate spreadsheet
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

As you can see above, setting the various options are pretty self explanatory. There are a number or preset orientation and papersizes to choose from (see ClassesPHPExcelWorksheetPageSetup.php for the options) and then the main full page scaling option is dealt with by the setFitToPage boolean flag. If set to true, you can then specify by how many pages wide you wish to force the layout and also how many pages tall you want it. Specifying a 0 means infinite pages.

If however you set FitToPage to false but still want to scale, you can make use of the setScale() function which allows you to set the percentage scaling you wish to apply.

And that’s precisely how simple all of this is!

Related Link: http://phpexcel.codeplex.com/

PHPExcel: How to Underline a Cell’s Text CodeUnit 16 APR 2010

The CodePlex project PHPExcel is an absolutely brilliant implementation around Microsoft’s OpenXML standard, giving us a powerful PHP object driven engine that is capable of creating and reading from Excel 2007, PDF, HTML and even Excel 2003 spreadsheets and documents.

Today’s little hint is on how one can go about underlining the value of a cell (for example, if you want to use that text as a link in your worksheet).

So let’s go then.

At this point you might of course like to know which underline options you do have available to you. The easiest way to see this is by checking up which PHPExcel constants have been set up for the underline type and to do this we browse to the Font.php file located in the ClassesPHPExcelStyle folder of your PHPExcel installation/folder structure.

Opening this file we find:

const UNDERLINE_NONE = 'none';
const UNDERLINE_DOUBLE = 'double';
const UNDERLINE_DOUBLEACCOUNTING = 'doubleAccounting';
const UNDERLINE_SINGLE = 'single';
const UNDERLINE_SINGLEACCOUNTING = 'singleAccounting';

Looking at this list, we can see that we pretty much have all the usual underline options that Excel offers us, so let’s go ahead and put this knowledge to action!

First we initiate our PHPExcel object in the following fashion:

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=example.xls");
header('Cache-Control: max-age=0');

require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();

Then we set the active sheet to the default first sheet in the workbook and give it a title, in this case ‘Example’. Then we populate a cell with some data so that we’ve got something to underline:

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Example');

$objPHPExcel->getActiveSheet()->setCellValue('A1','Underline me!');

Okay, so let’s apply a normal underlining to that text we just inserted:

$styleArray = array(
  'font' => array(
    'underline' => PHPExcel_Style_Font::UNDERLINE_SINGLE
  )
);

$objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styleArray);
unset($styleArray);

Okay. So from above you can see that we first create a style array that holds our underline information. We’ve opted for the ‘single line’ style. Then we simply use the useful applyFromArray function to apply this style to our selected cell.

And that is pretty much it. Finish off the spreadsheet generation by using the code below and then open up your freshly generated spreadsheet. As you can see, you should have a nicely underlined cell of text to admire!

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

Nifty.

PHPExcel: How to Place a Border Around a Cell Range CodeUnit 14 APR 2010

The CodePlex project PHPExcel is an absolutely brilliant implementation around Microsoft’s OpenXML standard, giving us a powerful PHP object driven engine that is capable of creating and reading from Excel 2007, PDF, HTML and even Excel 2003 spreadsheets and documents.

Today’s little hint is on how one can go about putting a border around a whole range of cells (or just one cell at that).

So let’s go then.

At this point you might of course like to know which border options you do have available to you. The easiest way to see this is by checking up which PHPExcel constants have been set up for the border type and to do this we browse to the Border.php file located in the ClassesPHPExcelStyle folder of your PHPExcel installation/folder structure.

Opening this file we get:

const BORDER_NONE = 'none';
const BORDER_DASHDOT = 'dashDot';
const BORDER_DASHDOTDOT = 'dashDotDot';
const BORDER_DASHED = 'dashed';
const BORDER_DOTTED = 'dotted';
const BORDER_DOUBLE = 'double';
const BORDER_HAIR = 'hair';
const BORDER_MEDIUM = 'medium';
const BORDER_MEDIUMDASHDOT = 'mediumDashDot';
const BORDER_MEDIUMDASHDOTDOT = 'mediumDashDotDot';
const BORDER_MEDIUMDASHED = 'mediumDashed';
const BORDER_SLANTDASHDOT = 'slantDashDot';
const BORDER_THICK = 'thick';
const BORDER_THIN = 'thin';

Looking at this list, we can see that we pretty much have all the usual border options that Excel offers us, so let’s go ahead and put this knowledge to action!

First we initiate our PHPExcel object in the following fashion:

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=example.xls");
header('Cache-Control: max-age=0');

require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();

Then we set the active sheet to the default first sheet in the workbook and give it a title, in this case ‘Example’. Then we populate four cells with data so that we’ve got something to border:

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Example');

$objPHPExcel->getActiveSheet()->setCellValue('A1','Text 1');
$objPHPExcel->getActiveSheet()->setCellValue('A2','Text 3');
$objPHPExcel->getActiveSheet()->setCellValue('B1','Text 2');
$objPHPExcel->getActiveSheet()->setCellValue('B2','Text 4');

Okay, so let’s apply a normal border that will border each and every cell on each and every wall:

$styleArray = array(
  'borders' => array(
    'allborders' => array(
      'style' => PHPExcel_Style_Border::BORDER_THIN
    )
  )
);

$objPHPExcel->getActiveSheet()->getStyle('A1:B2')->applyFromArray($styleArray);
unset($styleArray);

Okay. So from above you can see that we first create a style array that holds our border information. We’ve opted for the ‘allborders’ switch and have selected the classic thin border style. Then we simply use the useful applyFromArray function to apply this style to our selected cell range.

And that is pretty much it. Finish off the spreadsheet generation by using the code below and then open up your freshly generated spreadsheet. As you can see, you should have a nicely bordered little table of four to admire!

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

Nifty.

PHPExcel: Avoid Exceeding Maximum Execution Time CodeUnit 12 APR 2010

The CodePlex project PHPExcel is an absolutely brilliant implementation around Microsoft’s OpenXML standard, giving us a powerful PHP object driven engine that is capable of creating and reading from Excel 2007, PDF, HTML and even Excel 2003 spreadsheets and documents.

Recognise this?

Fatal error: Maximum execution time of 20 seconds exceeded in…

Today’s little hint is on how one can go about avoiding hitting the dreaded maximum execution time fata error that PHP so loves spitting out at us while generating a PHPExcel excel worksheet.

So let’s go then.

As we know, generating an excel spreadsheet using PHPExcel is remarkably simple. The following code below shows you just how simple it really is:

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=example.xls");
header('Cache-Control: max-age=0');

require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Example');
$objPHPExcel->getActiveSheet()->setCellValue('A1','Hello World');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

And that’s it, all done. However, generating a big Excel file takes a lot of time and memory and can quite often cause either Apache or PHP to simply timeout or run out of resources and spit a half-baked spreadsheet back at you.

Now to fix the memory allocation problem we can skip through by using PHP’s script cheat ini_set(‘memory_limit’, ‘-1’); which basically frees our script from the normal constraints in terms of memory usage and allocation enforced upon it.

In order to solve the maximum execution time exceeded problem we do pretty much the same thing.

Using the incredibly useful cheat of set_time_limit(20);, we basically identify the big looping structures that are involved in the generation of our spreadsheet and attach the time limit reset function inside each loop, basically now forcing the script to keep adding time to it’s execution timer and thus allowing it to essentially run forever until the loops complete their work.

In practise, you code would look like this:

foreach ($dataset as $dataitem)
{
    set_time_limit(20);
    //create the necessary worksheet cells for this particular item
}

And that is pretty much that. Implementing this approach should now free you from all those nasty, unexpected timeout fatal errors!

PHPExcel: How to Link to another Worksheet CodeUnit 11 APR 2010

The CodePlex project PHPExcel is an absolutely brilliant implementation around Microsoft’s OpenXML standard, giving us a powerful PHP object driven engine that is capable of creating and reading from Excel 2007, PDF, HTML and even Excel 2003 spreadsheets and documents.

Today’s little hint is on how one can go about creating a link on one worksheet in your spreadsheet that will then take a user to another worksheet in your spreadsheet by clicking the link, using the PHPExcel engine. (Of course, we’re not limited to linking to another worksheet, the same process will allow you to link even to web sites from within your Excel spreadsheet!)

So let’s go then.

First we initiate our PHPExcel object in the following fashion:

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=example.xls");
header('Cache-Control: max-age=0');

require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();

Then we set the active sheet to the default first sheet in the workbook and give it a title, in this case ‘Result’. The next step is then to create a second sheet which will be the one you are linking to. I’ve named it ‘ResultDetails’ for this example.

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Results');

$objPHPExcel->createSheet()->setTitle('ResultDetails');

Now we go back to our original base sheet and add in the cell text which will act as the link. With that done, we then apply the hyperlink to it:

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1','Click to see details');
$objPHPExcel->getActiveSheet()->getCell('A1')
  ->getHyperlink()
  ->setUrl("sheet://'ResultDetails'!A1");

And that is pretty much it. Finish off the spreadsheet generation by using the code below and then open up your freshly generated spreadsheet. As you can see, you should have at least two worksheets in the workbook, the first of which contains a link that by clicking on it will take you straight to the second worksheet.

Very nifty indeed.

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

If of course if you would rather just have that link you created open up a normal webpage address instead, you simply change the setUrl function call to point to a normal http:// address! Simple as that! :)

Create Excel 2007 Spreadsheets using PHP CodeUnit 10 APR 2010

For the quickest implementation time in terms of churning out Excel 2007 spreadsheets in your PHP-based project, look no further than the brilliant PHPExcel project!

Built around Microsoft’s OpenXML standard and PHP, PHPExcel is a brilliant little spreadsheet engine that can read and write from a variety of different file formats like Excel 2007, PDF and HTML, with the added bonus of being able to even generate the binary format used for Excel 2003 spreadsheets.

Installing PHPExcel is a simple matter of downloading the zip file and unzipping all of the class files to a location which you then reference in your script file. A simple hello world example which generates an Excel 2003 spreadsheet is as follows:

header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=example.xls");
header('Cache-Control: max-age=0');

require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Example');
$objPHPExcel->getActiveSheet()->setCellValue('A1','Hello World');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;

And that’s really it. The system allows you to manipulate spreadsheet meta data like author, title and description, etc., work with multiple worksheets containing different fonts, font styles, cell borders, fills, gradients and even the ability to add in images into your Excel workbooks!

Taken from the project site’s feature list, PHPExcel is able to:

...