Tag Archives: spreadsheet

Google Spreadsheet: How To Sum on Conditional Tips, Tricks and Tutorials 21 AUG 2013

google docs iconI’m busy scripting a quick and dirty spreadsheet to keep track of Chantelle’s various incomes and expenditures, and in the process adding a view as to the split between her business and personal finances.

Now the sum of the expenditure and income columns is obviously easy, just a simple =SUM function call, but the next little step is slightly more tricky: Given two columns, I only want to sum the values of the first column provided the value in the second column matches a certain condition.

As an example, consider the following data:

A B
1 Expense Business
2 150.00 1
3 100.00 0
4 125.00 1

If I want to get the total expenses that are marked as being business related, then I need to run a =SUM function on column A but only include values when the associated value in column B is 1. To achieve this we make use of the =SUMIF function which essentially allows us to sum values on condition. The syntax is as follows:

=SUMIF(Range, Criteria, Sum Range)

where Range is the group of cells the function is to search on (i.e. run the conditional criteria against), Criteria is the conditional that determines whether the cell is to be summed or not, and Sum Range which is the data range that is to be summed if the first range meets the specified criteria. Note, that if Sum Range is omitted, the first range is then summed instead.

So we can solve our example below by running =SUMIF(B2:B4,’=1′,A2:A4), which would give us a value of 275.00

Nifty.

How to Hide a Spreadsheet Column in LibreOffice Calc Software & Sites 06 APR 2012

Seeing as Ubuntu ships with LibreOffice (forked from OpenOffice) as its default office suite these days, I guess I should try and get a little more familiar with its various time-saving tips and tricks, like this one noted here today.

(And If you don’t know what I’m talking about, then: “LibreOffice is the power-packed free, libre and open source personal productivity suite for Windows, Macintosh and GNU/Linux, that gives you six feature-rich applications for all your document production and data processing needs: Writer, Calc, Impress, Draw, Math and Base.”)

Anyway, Calc is LibreOffice’s spreadsheet application (similar to Microsoft Office’s Excel), and today’s handy little user interface tip shows us how to quickly and efficiently hide unwanted columns which you still want present, but just not visible in order to save on screen real estate.

To hide a column, grab with your mouse pointer the targeted column’s right hand edge, and begin resizing it like you normally would. In this case you want to shrink it, so in other words, drag towards the left. If you stop and release the mouse button before reaching the column’s left hand edge, the column will be shrunk to a smaller width.

And here is where it gets fun. Keep on resizing until you drag the right hand edge past the left hand edge! Releasing the mouse at this point will result in the column instantly hiding itself!

Which of course is pretty nifty! :)

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:

...

Updated PHP Excel Reader Project CodeUnit 07 DEC 2009

For a long time there has been the excellent SourceForge Excel Reader PHP class available to developers wishing to add Excel parsing to their offerings, but unfortunately active development on the code stopped absolutely months ago already.

Thankfully for us though, a guy by the name of Matt Kruse picked up this dropped project, put it up on Google Code and continued to update and make the code a little bit more usable in the process.

Implementing Excel parsing (only up to Office 2003 format, i.e. .xls files) is as simple as including a single file and then pointing a valid Excel document at its constructor function:

$data = new Spreadsheet_Excel_Reader(“test.xls”);

From there it is a matter of accessing the spreadsheet data by using a number of methods available to you. For example, to retrieve formatted value of a cell you have the following options available to you:

Retrieve formatted value of cell (first or only sheet): 

$data->val($row,$col)

Or using column names: 

$data->val(10,‘AZ’)

From a sheet other than the first: 

$data->val($row,$col,$sheet_index)

You can also retrieve cell info such as: 

$data->type($row,$col);
$data->raw($row,$col);
$data->format($row,$col);
$data->formatIndex($row,$col);

or get the active sheet size: 

$data->rowcount();
$data->colcount();

There’s a fair bit of functionality available to you, all neatly stored in the help documentation, meaning that now there is absolutely no excuse for not supporting Excel spreadsheet parsing in your PHP projects any more! :P

excel-screenshots

Related Link: http://code.google.com/p/php-excel-reader/