Trying to Strike Back!

INTRO

As a web application developer we often displaying data or displaying report of data from database to our page. Example, sales data, statistical data, and many more as you has done. Now, how if our users want to get data with different format? Maybe users will want to analyze the data in different way. Because, impossible to analyze data in HTML format. It’s easy if we analyze the data in Excel format.

WHAT IS Spreasheet_Excel_Writer

Spreadsheet_Excel_Writer is a set of class from PEAR repository. We can generate Excel document, complete with excel functions and formatting. We can using formula, working in multipl sheet, and much more tasks that often you has done usually with Excel. You can find Spreadsheet_Excel_Writer at PEAR repository site, or if you are using all in one web development package such as XAMPP, there available complete enough classes from PEAR repository, and one from the among is Spreadsheet_Excel_Writer class. You can download the package here ( Spreadsheet_Classess ) after download the package,now you can use Spreadsheet class without depend to PEAR repository structure, you can use the class everywhere.

THE CLASS INFORMATION

The library contains three other classes of which you should be aware, although you may not find yourself having to work with them directly:

• Spreadsheet_Excel_Writer_Validator makes it possible to add cell validation rules. Right now, there’s basically no documentation for this class. It seems to be experimental code, so I’ll be avoiding it here. Basically, it appears to provide the ability to perform basic validation on data entered into an Excel cell by an end user. More complex rules, such as validating against a list of cells, can be implemented by extending the class. The Spreadsheet_Excel_Writer_Workbook class provides the method addValidator() to create an instance of the validation while the Spreadsheet_Excel_Writer_Worksheet allows validators to be assigned to cells with the setValidation() method.
• Spreadsheet_Excel_Writer_Parser, which is a parser for Excel spreadsheet functions that allows you to check whether a function is valid Excel syntax. This may help you trap errors when adding functions to the spreadsheet within PHP.
• Finally, Spreadsheet_Excel_Writer_BIFFwriter is used to generate the Binary File Format for storing Excel files. If you’re interested in Excel hacking, it may be interesting to study what it’s doing but, otherwise, the library hides you from this class completely, so you don’t need to worry about it.

And also another important supporter class that i had to group into one directory.

Let me introduce some basic method that we usually use for generating excel document.

• addWorkSheet()
  This method used for creating new worksheet
• write()
  used for put a data to cell.
• writeRow()
  used for put data to cell. The different between write() and writeRow() is, write() just render data once into selected cell each we call, and if writeRow(), will render data depend on how many data passed into the writeRow(). The data in writeRow() saved as array()
• send()
  used for telling browser the type of raw data will come from server
• close()
  used for erasing class data in memory that we had to instatiate before.

And next, i will explain in more detail only this two important method (because this method is most frequently used) the method is write() and writeRow() method.

write()
what kind of things we passed to this method? first you must imagine we are in front of Excel window now. Illustrated as below :

As you can see above, letter A,B,C,D,etc is named column. And number 1,2,3,etc is named row. It’s funny if i tell you something like this but, no problem because this will relate with the methods.

In Excel, rows starting from ” 1 ” as u can see above. But in PHP, in the Spreadsheet class, rows is starting from zero. And the starting column is start from zero in Excel, and also in PHP. The letter A equal Zero, B equal 1, C equal 2 and so on..

basic uses of write() method is like this :
$worksheet->write(which_row,which_column,’the data’,cell_style);

which_row : is an integer value that represent the position of row
which_column : is an integer value that represent the position of column
the data : is an string value
cell_style : is a object data type that contain many property of cell style.

example use : $worksheet->write(0,0,’Hello iam in first row and first column’,$xls->addFormat(array(’color’ => ‘red’)));

The above explanation will look like the picture below :

And next, the writeRow() method.

basic uses of writeRow() method is like this :
$worksheet->write(which_row,start_from_which_column,the_data_in_array,cell_style);

which_row : is an integer value that represent the position of row
start_from_which_column : is an integer value that represent the start position of column
the_data_in_array : is an array data type that contain data
cell_style : is a object data type that contain many property of cell style.
example use :
$column = array(’Name’, ‘E-Mail’,'Address’,'Contact Number’);
$worksheet->write(0,0,$column,$xls->addFormat(array(’color’ => ‘red’)));

Do you remember the different of write() and writeRow() method? i hope you remember . The above explanation is look like this :

Ho ho… i think that’s enough for you to begin to Strike it!

LET’S STRIKE !

As you had to read above, now we will making some basic and simple excel document. Let’s write following code in your favourite editor.

Just for information, my directory structure is like this : C:\xampp\htdocs\Excel\ExcelWriter , ExcelWriter directory contain all of classes.

xls1.php

Just run, and you will get pure xls / Excel document format with data that we have to put with write() method. You must know it, why we call write() method from object variable $worksheet ? and why we call addWorksheet() method from object variable $xls ?
That’s because relate with class structure. object $xls handling all tasks outside of worksheet ( creating new worksheet, setting our worksheet cells,etc) and object $worksheet handling all tasks inside of our worksheet ( putting data to cell, adding formula to cell, changing cells color, etc ) . What object $xls doing is affect globally our worksheet, and $worksheet just handling the internal cells tasks. Woww… it’s difficult enough to explain, try to understand by yourself . And, you must remember, we must keep track our rows, if we want to put data in a new row
Ok, now we can to generate very simple excel document

SETTING THE CELL

Now i introduce some common method for setting our cells. With this methods, we can setting height, width, foreground color, text color, and merging the cells.Below is list of the methods that we will use :

• setColumn()
  Used for setting the width of a single column or a range of columns.
  Basic use : $worksheet->setColumn(whichFirst_column_on_the_range,whichLast_column_on_the_range,width);
• setRow()
  This method is used to set the height and format for a row.
  Basic use :
  $worksheet->setRow(which_row,height_for_the_row,row_styles);
• setMerge()
  Sets a merged cell range.
  Basic use : $worksheet->setMerge(whichFirst_row,whichFirst_column,whichLast_row,whichLast_column);
• setCustomColor()
  Setting custom color from limited alternative from fgcolor properties. With this method we can set the foregroung color of our cells with our favourite color from our favourite color palette. All colors is in RGB Format.
  Basic use : $xls->setCustomColor(index,red_value,green_value,blue_value);
• addFormat()
  Adding format ( cell properties ) to a or range of cell. The cell properties is saved as array()
  Basic use :
  $worksheet->write(0,0,”TITLE”,$xls->addFormat(array(’align’ => ‘center’,'bold’ => 1,’size’ => 13)));

setCustomColor() method must have a index, and the index is defined by ourself. The value is up to you, and must keep in range of number from 8 up to 64. And must have a call at the top of writing tasks with write() method. Call setCustomColor() method before you call methods for writing because its index will be use in addFormat() method properties.

Let’s try to setting our cell height,width,foreground color, adding format and merging the cells.