geexel example 1: a simple time sheet

To show how to use geexel (if this is possible at it's current stage) we look at a simple time sheet. We suppose starting on an empty geexel sheet (new -> other -> geexel -> geexel sheet). As you will see, the simple use depends on knowing waht you do. A look for the javadocs is most propably necessary.

1. Headlines

First you type your headlines in the first row and you do the formatting stuff (switch to the geexel perspective!). The result might look like this:

We assume the columns date, begin (time), end (time), duration and comment in the following steps.

2. Initialize using a script

We want to initialize the date column. To do this we use a script and execute it using the "execute script" command. The script we use is the following:

import java.util.Calendar;
import java.util.Locale;

import org.eclipse.swt.graphics.RGB;

import de.jaret.geexel.model.*;
import de.jaret.util.date.JaretDate;
import de.jaret.util.date.holidayenumerator.HolidayEnumerator;
import de.jaret.util.date.holidayenumerator.HolidayEnumeratorFactory;
import de.jaret.util.date.holidayenumerator.HolidayEnumerator_de_DE;
import de.jaret.util.ui.table.renderer.ICellStyle;

CellIterator it = new CellIterator(ctx.getModel(), "a2", "a45");
JaretDate date = new JaretDate();
HolidayEnumerator en = HolidayEnumeratorFactory.getHolidayEnumeratorInstance(Locale.GERMANY, HolidayEnumerator_de_DE.DE_DE_REGION_NRW);

while (it.hasNext()) {
	GeexelCell cell = it.next();
	cell.setValue(date.toDisplayStringDate());
	if (en.getDayName(date.getDate()) != null) {
		cell.setComment(en.getDayName(date.getDate()));
	}
	
	if (en.isHoliday(date.getDate()) || date.getDayOfWeek() == Calendar.SATURDAY || date.getDayOfWeek() == Calendar.SUNDAY) {
		String curLoc = EvalHelper.getLoc(ctx.getModel(), cell);
		String destLoc = EvalHelper.getDiffLoc(curLoc, 4, 0);
		CellIterator markIt = new CellIterator(ctx.getModel(), curLoc, destLoc);
		while (markIt.hasNext()) {
			GeexelCell markCell = markIt.next();
			ICellStyle style = EvalHelper.getCellStyle(ctx.getModel(), markCell);
			style.setBackgroundColor(new RGB(255, 200, 200));
		}
	}
	
	date.advanceDays(1);
}
      

See the following:

  • It's plain java. You get an EvalContext as ctx. BeanShell conventions apply.
  • Cells are adressed in an excel style ([$]<col>[$]<row>). The $ will fix the value when transferring the script code to another cell.
  • You sure need to know java ...

The result is a filled first column including weekend marking and holidays for Germany(NRW) set as comments.:

3. Add the calculation

The next step is to fill the duration column with the calculate script. To do so double click on the topmost cell in the duration column and paste the the following script: (Note the prepended equals sign!)

=import org.eclipse.swt.graphics.RGB;

import de.jaret.geexel.model.*;
import de.jaret.util.date.TimeHelper;
import de.jaret.util.ui.table.renderer.ICellStyle;

GeexelCell c1 = EvalHelper.getCell(ctx.getModel(), "b2");
GeexelCell c2 = EvalHelper.getCell(ctx.getModel(), "c2");
int secs1 = c1.getValueSeconds();
int secs2 = c2.getValueSeconds();

if (secs1 != -1 && secs2 != -1) {
	int diff = secs2 - secs1;
	value = TimeHelper.secondsToString(diff, false);
	ICellStyle style = EvalHelper.getCellStyle(ctx.getModel(), ctx.getCell());
	if (diff > 10*60*60) {
		style.setBackgroundColor(new RGB(0, 255, 0));
	} else {
		style.setBackgroundColor(new RGB(255, 255, 255));
	}
} else {
	value = "###";
}

Since no begin and end time are entered the result will be "###". Try entering times in th format hh:mm. Now Mark the cells beginning with the one you et the script to and use fill -> down from the geexel menu.

See the following:

  • You "return" the new value for the cell in the varaibale "value" (Object).

4. Add a sum script

To sum up the times the following script can be used:

import de.jaret.geexel.model.*;
import de.jaret.util.date.TimeHelper;

CellIterator it = new CellIterator(ctx.getModel(), "d2", "d14");
int sum = 0;
while (it.hasNext()) {
	GeexelCell cell = it.next();
	int secs = cell.getValueSeconds();
	if (secs != -1) {
		sum = sum + secs;
	}
}
value = TimeHelper.secondsToString(sum, false);	

At last you might like to print the timesheet: Printed timesheet.

The example is downloadable: timesheet.gxl