Developing 'off-device' is an important part of any programming langauge. Almost every C++ or Java prorgammer of smartphones will do the majority of their work on a PC. It's a much larger screen, it's easier to see what's going on and type in a lot of information. And when things go wrong (which they will) all of that will let you focus on the real problem in the spreadsheet.
Developing on Your Desktop Computer
Using Microsoft Excel
Quickoffice, on both UIQ and Series 60 smartphones, can read in spreadsheets saved in the latest Microsoft Excel formats (mHTML). And while the 9500 and 9300 Communicators can read in the Excel format, dragging and dropping Excel files onto these devices using their PC Suites will automatically convert them into the much more efficient Symbian Sheet format. If you can, you should always try to convert them as you move them over.
If you have a copy of Microsoft Office (available from all good computer retailers), then you should have Excel available. Quickoffice's spreadsheet saves and reads files in the standard Microsoft format of mHTML, which features in Office XP and Office 2003. Earlier versions of Office will require an Excel Plug-in (freely downloadable with Quickoffice) that adds a "Quickoffice" menu to your version of Excel.
Keep It Simple
Excel has a huge number of functions, graphical options, charting and other options. You may not find it surprising, but many of these are not part of the spreadsheet applications in your smartphone. So always follow the KISS rule - Keep It Simple, Stupid.
Stupid in this sense doesn't mean do a simple spreadsheet, but to keep what you do in the spreadsheets within the realms of what your smartphone can do. So feel free to format the colour of cells, but don't go overlaying jpeg impages along the top line showing a fancy title. If you start using some wierd mathematical functions, then check that these commands are available on your smartphone.
Timesheet / Hours Calculator
Let's build our first spreadsheet using your desktop computer. Open up your spreadsheet program on your desktop, and use "File... New..." to create an empty spreadsheet. It might be an idea for you to check out how to add in information, edit the content of the cells, and do the basic formatting that you practiced on the Sales Tax calculator in part one.
Wages Spreadsheet
Let's look at another spreadsheet close to everyone's hearts. How much you get paid for your work. The aim here is to have a spreadsheet where we put in the number of hours that you work, and it then works out various deductions, to arrive at your net pay for that week. As with any program, it's important to consider the information you are going to put into the spreadsheet, information that will change each time you want to do a calculation (and which numbers will stay the same), and what information we want to get out.
Information Going In - Variables
- We'll be telling the spreadsheet how many hours a week I have worked. Now I have fixed shifts every week of four hours per shift, plus casual hours, so to save me some maths, I'll have two entries. The first will be the number of fixed shifts I work, and the second will be the number of extra hours I work.
Information Going In - Constants
- What is my rate of pay?
- Deduction 1: The rate of Income tax (22% of my wage)
- Deduction 2: National Insurance Class 2 (8% of my wage)
- Deduction 3: National Insurance Class 4 (£2.08 per week of my wage).
Obviously these are geared to my wages in the UK, but you will be able to adapt these to your own circumstances - which is one of the great things about programming something specifically for yourself.
Information Coming Out - More Variables
- How much money I receive after all those deductions.
Constructing the Spreadsheet
With our blank spreadsheet now in front of us on the PC, we can add in all these variables and constants into column A, so we can see which number is which.
As well as our yellow bar to explain what each column is for, we have the first 'section' where I can input my hours, the second section where the deductions are carried out, and a final section of one line, showing the money I actually recieve.
Hours and Gross Wage
We work out the total hours by multiplying the number of shifts by the length of shifts (the variable is the hours in a single shift), and then adding in the number of single hours. The formula doing this is in cell B6, and is =(B4*C4)+B5. The brackets here are used to make sure the calculation inside the cell is done before any calculations. Just like you learned in your Maths classes. Then we take the hours worked and multiply this by the hourly rate (the variable in C7) to get the pay without any deductions. Note that we're using the result of a formula in B6, and it is treated as if it was a number.
Deductions
Percentage deductions act on the gross wage, so we can take the value in B7 and apply some percentage changes to it - just as we did in the VAT example in part one. We have two percentage deductions here, one for 22% and one for 8%: B9=B7*C9, and B10=B7*C10. The third deduction here is a fixed deduction, so we simply copy over the value from the variable into the B column.
Now let's add all these deductions together. How would you do it? If you said that cell B12 would be =B9+B10+B11, you'd be right. But imagine if we had to add up one hundred cells in a column. That's a lot of typing. But we can use a function. This is a special type of formula that has a command and some arguments (normally cell references). To add up a column or row of cells, we can type =SUM(B9:B11). This adds up the value of all the cells from the first cell referenced (B9 here) to the last cell (B11).
The Final Wage
So now we have my total wage earned (in cell B7) and the amount of deductions (B12) we can go =B7-B12 to get the wage I'll end up with after a week of work.
Transferring Files
- Communicators
The easiest way to move your spreadsheet over to a communicator is using the bundled PC Suite, and its file transfer capability. This will convert the Excel spreadsheet into the Symbian Sheet format. Alternatively you can right click the spreadsheet file on your PC, and send via IR or Bluetooth. - Series 60 and UIQ
While the PC Suites and various add on modules allow you to see the drives of your UIQ or Series 60 smartphones, the easiest way to move these files over is using IR or Bluetooth directly. Right click the spreadsheet file, and you can send it to your smartphone. It will appear in the Messaging inbox and you can then save it to disk, to appear in the main file lists of Quicksheet.
That's it for this week - you'll probably want to enter the spreadsheet, then tailor it for your own use. You might want to play around with the insert rows and insert column options on your PC spreadsheet when adding in cells. In part three we'll look at a much larger spreadsheet, and start to build up a simple Bank Account Tracker in a spreadsheet.