Tip & Tricks

PHP, Tip & Tricks - Friday, May 30, 2008 3:20 - 0 Comments

Reuse Excel business logic with PHPExcel

n 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?

Here’s a scenario: You are working in a company which sells “dream cars”. For every model, the company has created an Excel spreadsheet which is used to calculate the car’s price based on customer preferences. These spreadsheets are updated frequently in order to reflect the car manufacturer’s pricing schemes.

Your manager asks you to create a small website which accepts some input fields (Does the customer want automatic transmission? What colour should the car be painted? Does the customer want leather seats? Does the customer want sports suspension?). Based on these questions, the car’s price should be calculated. Make sure all prices on the website are in sync with this Excel sheet!

Download example source code
View live example

1. Create the Excel sheet containing business logic

First of all, we’ll create an Excel sheet containing business logic. If you’re lazy, download my example here. To make things easy for yourself when scripting, make sure you add some defined names on each field you want to use as input/output. Of course it’s possible to work with the sheet’s cell references later on, but if you want to be able to change the location of cells within the worksheet later, these defined names are much easier!

2. Download the latest PHPExcel version

You can find PHPExcel on www.phpexcel.net. If you want a stable version, download an official release. The source code tab on CodePlex reveals the latest Subversion source code if you want to use it.

3. Create the web based front-end

Next thing we’ll do is creating a simple webpage containing an HTML form which corresponds woth the parameters you want to pass to the Excel sheet.

4. Let’s do some PHP coding!

Since you came here for the good stuff, here it is! What we’ll do is load the Excel sheet, pass in the parameters and use some calculated values on our resultign page. First things first: include the necessary class references:

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . ‘./PHPExcel/Classes/’);

/** Class requirements */
require_once(’PHPExcel.php’);
require_once(’PHPExcel/Reader/Excel2007.php’);

PHPExcel is the base library which represents an in-memory spreadsheet. Since we need to interface with an Excel2007 file, we also include the required reader class.

Now load the Excel sheet into a PHPExcel object:

// Load price calculation spreadsheet
$objReader = new PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load(”price_calculation.xlsx”);

All data from the web form is passed in via the $_REQUEST array. Let’s pass these to the Excel sheet. I named all form fields equal to my defined names in Excel which makes coincidence of all array keys and cell names being the same intentional.

// Set active sheet
$objPHPExcel->setActiveSheetIndex(0);

// Assign data
$objPHPExcel->getActiveSheet()->setCellValue(’automaticTransmission’, $_REQUEST[’automaticTransmission’]);
$objPHPExcel->getActiveSheet()->setCellValue(’carColor’, $_REQUEST[’carColor’]);
$objPHPExcel->getActiveSheet()->setCellValue(’leatherSeats’, $_REQUEST[’leatherSeats’]);
$objPHPExcel->getActiveSheet()->setCellValue(’sportsSeats’, $_REQUEST[’sportsSeats’]);

PHPExcel is great success! This is actually about it. The only thing left is to fetch the formula’s calculated values and we’re done!

// Perform calculations
$_VIEWDATA[’totalPrice’] = $objPHPExcel->getActiveSheet()->getCell(’totalPrice’)->getCalculatedValue();
$_VIEWDATA[’discount’] = $objPHPExcel->getActiveSheet()->getCell(’discount’)->getCalculatedValue();
$_VIEWDATA[’grandTotal’] = $objPHPExcel->getActiveSheet()->getCell(’grandTotal’)->getCalculatedValue();

You can use these values to print the result on your web page:

Based on your chosen preferences, your car will cost <?php echo number_format($_VIEWDATA[’grandTotal’], 2); ?> EUR.

5. Summary

Embedding business logic in Excel and re-using it in PHP is not that hard. The PHPExcel library helps you simplify development: your application logic and business logic is separated. Business logic can be maintained by a business expert or key user in Excel. As an application developer, you can easily pass data in the sheet and make use of PHPExcel’s calculation engine.

100 Ways to Save The Environment

- Tuesday, May 27, 2008 9:51 - 0 Comments

100 Ways to Save The Environment

In Your Home – Conserve Energy

Clean or replace air filters on your air conditioning unit at least once a month.
If you have central air conditioning, do not close vents in unused rooms.
Lower the thermostat on your water heater to 120.
Wrap your water heater in an insulated blanket.
Turn down or shut off your water heater when you will be away for extended periods.
Turn off unneeded lights even when leaving a room for a short time.
Set your refrigerator temperature at 36 to 38 and your freezer at 0 to 5 .
When using an oven, minimize door opening while it is in use; it reduces oven temperature by 25 to 30 every time you open the door.
Clean the lint filter in your dryer after every load so that it uses less energy.
Unplug seldom used appliances.
Use a microwave when- ever you can instead of a conventional oven or stove.
Wash clothes with warm or cold water instead of hot.
Reverse your indoor ceiling fans for summer and winter operations as recommended.
Turn off lights, computers and other appliances when not in use.
Purchase appliances and office equipment with the Energy Star Label; old refridgerators, for example, use up to 50 more electricity than newer models.
Only use electric appliances when you need them.
Use compact fluorescent light bulbs to save money and energy.
Keep your thermostat at 68 in winter and 78 in summer.
Keep your thermostat higher in summer and lower in winter when you are away
Insulate your home as best as you can.
Install weather stripping around all doors and windows.
Shut off electrical equipment in the evening when you leave work.
Plant trees to shade your home.
Shade outside air conditioning units by trees or other means.
Replace old windows with energy efficient ones.
Use cold water instead of warm or hot water when possible.
Connect your outdoor lights to a timer.
Buy green electricity - electricity produced by low - or even zero-pollution facilities (NC Greenpower for North Carolina - www.ncgreenpower.org). In your home-reduce toxicity.

In Your Home – Reduce Toxicity

Eliminate mercury from your home by purchasing items without mercury, and dispose of items containing mercury at an appropriate drop-off facility when necessary (e.g. old thermometers).
Learn about alternatives to household cleaning items that do not use hazardous chemicals.
Buy the right amount of paint for the job.
Review labels of household cleaners you use. Consider alternatives like baking soda, scouring pads, water or a little more elbow grease.
When no good alternatives exist to a toxic item, find the least amount required for an effective, sanitary result.
If you have an older home, have paint in your home tested for lead. If you have lead-based paint, cover it with wall paper or other material instead of sanding it or burning it off.
Use traps instead of rat and mouse poisons and insect killers.
Have your home tested for radon.
Use cedar chips or aromatic herbs instead of mothballs.
In Your Yard
Avoid using leaf blowers and other dust-producing equipment.
Use an electric lawn- mower instead of a gas-powered one.
Leave grass clippings on the yard-they decompose and return nutrients to the soil.
Use recycled wood chips as mulch to keep weeds down, retain moisture and prevent erosion.
Use only the required amount of fertilizer.
Minimize pesticide use.
Create a wildlife habitat in your yard.
Water grass early in the morning.
Rent or borrow items like ladders, chain saws, party decorations and others that are seldom used.
Take actions that use non hazardous components (e.g., to ward off pests, plant marigolds in a garden instead of using pesticide).
Put leaves in a compost heap instead of burning them or throwing them away. Yard debris too large for your compost bin should be taken to a yard-debris recycler.

In Your Office

Copy and print on both sides of paper.
Reuse items like envelopes, folders and paper clips.
Use mailer sheets for interoffice mail instead of an envelope.Use mailer sheets for interoffice mail instead of an envelope.
Set up a bulletin board for memos instead of sending a copy to each employee.
Use e-mail instead of paper correspondence.
Use recycled paper.
Use discarded paper for scrap paper.
Encourage your school and/or company to print documents with soy-based inks, which are less toxic.
Use a ceramic coffee mug instead of a disposable cup.
Ways To Protect Our Air
Ask your employer to consider flexible work schedules or telecommuting.
Recycle printer cartridges.
Shut off electrical equipment in the evening when you leave work.
Report smoking vehicles to your local air agency.
Don’t use your wood stove or fireplace when air quality is poor.
Avoid slow-burning, smoldering fires. They produce the largest amount of pollution.
Burn seasoned wood - it burns cleaner than green wood.
Use solar power for home and water heating.
Use low-VOC or water-based paints, stains, finishes and paint strippers.
Purchase radial tires and keep them properly inflated for your vehicle.
Paint with brushes or rollers instead of using spray paints to minimize harmful emissions.
Ignite charcoal barbecues with an electric probe or other alternative to lighter fluid.
If you use a wood stove, use one sold after 1990. They are required to meet federal emissions standards and are more efficient and cleaner burning.
Walk or ride your bike instead of driving, whenever possible.
Join a carpool or vanpool to get to work.

Ways to Use Less Water

Check and fix any water leaks.
Install water-saving devices on your faucets and toilets.
Don’t wash dishes with the water running continuously.
Wash and dry only full loads of laundry and dishes.
Follow your community’s water use restrictions or guidelines.
Install a low-flow shower head.
Replace old toilets with new ones that use a lot less water.
Turn off washing machine’s water supply to prevent leaks.

Ways to Protect Our Water
Revegetate or mulch disturbed soil as soon as possible.
Never dump anything down a storm drain.
Have your septic tank pumped and system inspected regularly.
Check your car for oil or other leaks, and recycle motor oil.
Take your car to a car wash instead of washing it in the driveway.
Learn about your watershed.
Create Less Trash
Buy items in bulk from loose bins when possible to reduce the packaging wasted.
Avoid products with several layers of packaging when only one is sufficient. About 33 of what we throw away is packaging.
Buy products that you can reuse.
Maintain and repair durable products instead of buying new ones.
Check reports for products that are easily repaired and have low breakdown rates.
Reuse items like bags and containers when possible.
Use cloth napkins instead of paper ones.
Use reusable plates and utensils instead of disposable ones.
Use reusable containers to store food instead of aluminum foil and cling wrap.
Shop with a canvas bag instead of using paper and plastic bags.
Buy rechargeable batteries for devices used frequently.
Reuse packaging cartons and shipping materials. Old newspapers make great packaging material.
Compost your vegetable scraps.
Buy used furniture - there is a surplus of it, and it is much cheaper than new furniture.

15 Handy Google Search Tricks
- Tuesday, May 27, 2008 9:28 - 0 Comments
15 Handy Google Search Tricks

Most of us use Google search to search on various topics, products, tips, etc. No doubt it’s one of the best search engines available out there. In order to search something we simply enter the keywords in the search bar and search for stuff but did you ever know that there are several built in functions within Google which makes searching stuff even easier. Some of you’ll know about them. But well if you don’t, then here they are:

Google calculator:
Google search can be used as a calculator. It can calculate anything from the simplest math to the most complex equation. All you need to do is just enter the math in the search box and hit enter. You can use the following functions:
+ : add
- :subtract
* :multiply
/ :divide
^ :to the power of
sqrt : square root of a number

Dictionary Definitions:
You can define any word by entering “define:” following the word. For example define: ignoble
Google will display a list of dictionary definitions for that word.

Search for a specific title:
Suppose you came across a great article which you really liked but unfortunately forgot to bookmark it then you can find it easily with the help of Google provided you know the title of the article. Here’s how you do it:
Let’s say you read an article “Women talk three times as much as men” then you can search for it by typing “intitle: Women talk three times as much as men”

Google Convertor:
Using this feature you can convert units like millimeter into inches, kilometers into miles, liters into ml, etc. You can do so by typing ‘25km in miles’ for converting km in to miles, ‘50litres in ml’ for converting litres into ml, and so on.

Compare currencies:
Google’s search has an inbuilt currency converter. It allows you to perform any currency conversion. All you need to do is just enter the conversion you’d like done into the Google search box and hit “Enter” or click the Google Search button. For instance: “1 USD in INR”

Weather Updates:
You can easily know the weather condition of a specific place. In order to find out, all you need to do is just enter the place name followed by “weather”. For ex: “london weather”

Search for specific file types:
There are various kinds of files available over the net. Searching for a specific file type isn’t really easy. But using this tip you can easily search for a specific file type.
For instance if you want to search for a guide, let’s say a ‘Guide to Building a Successful IT Career’ in ppt format. You can make use of the “filetype:” function. So in order to search for the PPT version of the guide you can enter in search bar ‘Guide to Building a Successful IT Career filetype:ppt’

Search on a particular website:
If you want to search for something from a particular site using Google search then you can do so by using the “site:” feature. Here’s how you do it:
Let’s say you want to search for cloverfield DVD on Amazon, you can do so by typing “cloverfield DVD site:www.amazon.com”

Get the local time anywhere:
Wanna know what time is it in London now? You can ask Google by typing “what time is it London” in the search bar. You can also enter “time (location)” without the quotes.

Remove unwanted search results:
Suppose you wanted to search a Harry potter book review but you ended up in getting reviews of the movie rather than the book. Well in this case you can make Google to exclude the movie results from the search. You can do so by adding “-movie” at the end of the term. For instance: “Harry potter Chamber of secrets -movie” This will give search results excluding the movie- The chamber of secrets.

Search for URLs:
You can even search for URLs in Google search.For instance: “Women_talk_three_times_as_much_as_men”. You can also use “.” “-” instead of “_“. The search will bring up results with the URLs containing the above words entered.

Track flight status:
You can also track a particular flight status. All you need to do is just enter the airline and flight number into the search box and hit enter. You’ll get the arrival and departure time of the flight right inside Google’s search results.

Search Google groups by the subject line:
You need to be in the google groups search page before attempting this query; otherwise it might not work correctly. Using the “insubject:” function you can search google groups by the subject line. For instance “insubject:windows xp fast shutdown”

Find related sites:
Suppose you find an interesting website and you would like to find some other alternative to this site then you can do so by using “related:” function. For instance if you want to search an alternative for google.com you can do so by entering “related:www.google.com”

Find links to a specific URL:
You can find the webpages which have a link to a specific URL using the “link:” function. This is really great as it will help you if you own a website, you can easily find out the pages linking to a page in your site. To use it, you need to enter the whole URL after “link:”
For instance “link:http://www.cnn.com”
Well that’s it for now, hope you liked them. If you know more feel free to mention them here.

Whois Lookup service:

You can also search for information on a particular domain name. Just type in “whois domain.com” and hit enter. You’ll get to see the creation and expiry dates of the domain.

Suggests words, phrases and their results:

This is really helpful if you aren’t able to spell a particular word or you would like to know the results a particular word would contain when searched. Click here to do a search with suggestions.
Multiple live CDs in one DVD
- Tuesday, May 27, 2008 8:43 - 0 Comments
Multiple live CDs in one DVD

Live CDs do a great job of advertising Linux distributions. In addition to general-purpose live CD distributions, there are lots of task-oriented live CDs. Wouldn’t it be great if you could carry multiple live CDs on one DVD disc? Nautopia.net has put up a script that you can use to make a custom DVD to boot multiple live CDs.

Continue…
Install Ubuntu 8.04 “Hardy Heron” Beta In Windows
- Saturday, May 24, 2008 9:47 - 1 Comment
Install Ubuntu 8.04 “Hardy Heron” Beta In Windows

There are various reasons why people don’t try out Linux. The most common reason, is that they can’t bring themselves to remove the familiar Windows system and replace it with a totally new environment. While there are ways to boot into a Linux environment without uninstalling Windows, most people either don’t have the patience or the time to learn how.

With the Ubuntu 8.04 “Hardy Heron” beta, Windows users will be able to install Ubuntu onto their systems without having to worry about affecting their Windows installation. The Hardy Heron beta includes an install option for Windows users to install and uninstall Ubuntu just like your normal Windows app. Continue…
How I built an electricity producing Solar Panel
- Saturday, May 24, 2008 4:53 - 0 Comments
How I built an electricity producing Solar Panel

It was easy. You can do it too Several years ago I bought some remote property in Arizona. I am an astronomer and wanted a place to practice my hobby far away from the sky-wrecking light pollution found near cities of any real size. I found a great piece of property. The problem is, it’s so remote that there is no electric service available. That’s not really a problem. No electricity equals no light pollution. However, it would be nice to have at least a little electricity, since so much of life in the 21st century is dependant on it. I built a wind turbine to provide some power on the remote property. It works great, when the wind blows. However, I wanted more power, and more dependable power. The wind seems to blow all the time on my property, except when I really need it too. I do get well over 300 sunny days a year on the property though, so solar power seems like the obvious choice to supplement the wind turbine. Solar panels are very expensive though. So I decided to try my hand at building my own. I used common tools and inexpensive and easy to acquire materials to produce a solar panel that rivals commercial panels in power production, but completely blows them away in price. Read on for step by step instructions on how I did it.

Continue…
Fetching a Web Page From Your PHP Code
- Tuesday, May 6, 2008 23:28 - 0 Comments
To fetch a web page from your PHP application, you could use curl functions or simply open the page with fopen(). But these have some limitations. Your server needs to have PHP with curl enabled, or the PHP configuration should allow scripts to open an URL with fopen().

If that is not the case, you can still fetch a web page by opening a socket connection to the remote host and make HTTP request.

Continue…
Make Windows Genuine by a simple trick
- Tuesday, May 6, 2008 1:12 - 2 Comments
Windows genuine advantage notifications occur when your computer hasn’t passed the validation test. The validation test can be failed due to being sold a pirated (non-genuine) copy of XP, or because you have changed your XP product key to a software-generated key, or sometimes it just happens for no reason at all. The failed validation installs three types of notifications on your computer: one on the log in screen, one log in timer, and one balloon. It also stops updates from Microsoft and disables your ability to install IE7 and Windows Media Player 11.

Continue…
Bad Domain Names
- Sunday, March 23, 2008 23:46 - 0 Comments
All of these are companies that didn’t spend quite enough time considering how their online names might appear - and be misread…
1. Who Represents is where you can find the name of the agent that represents any celebrity. Their Web site is
  www.whorepresents.com/
2. Experts Exchange is a knowledge base where programmers can exchange Advice and views at
  www.expertsexchange.com/
3. Looking for a pen? Look no further than Pen Island at
  www.penisland.net/
Continue…
Popular Desktop Applications And Their Online Alternatives
- Sunday, March 23, 2008 12:04 - 6 Comments
Due soaring software costs, computer users are being pushed away from original software and towards piracy. However, internet has changed the scenario considerably with a lot of applications being available online. Microsoft Office has given way to Google Docs and Zoho, more and more internet users are using Meebo instead of all the instant messengers, and what’s more - all these online alternatives are free

Go ahead, check out this list of more than 25 online alternatives for your native desktop applications and save money!

Continue…