Author: JT Smith
KSpread offers the standard spreadsheet elements: columns and rows of cells
which can contain data or formulas, an entry box for putting information into the cells, and a set of toolbars and menus for formatting and general control of the application.
If you only have a small amount of data, you can type it into KSpread or even KWord (KWord allows you to create tables, just like Microsoft Word). However, you can also retrieve spreadsheet data from a database. KSpread provides a wizard that guides you through the process with a minimum of effort. It sets up the connection to the database, allows you to select tables, lets you choose fields from the tables, and helps you create filters on the data. The wizard also allows you to modify the SQL that it creates if you need to so that you can get the actual output that you require. Once you confirm that the SQL is correct the wizard pastes the data into the spreadsheet.
Having entered the data into KSpread you can add formulas and format the spreadsheet.
The data import wizard does not store the SQL once it finishes its job. This is fine for one-off reports, but not so good if you are going to be updating a spreadsheet regularly, especially if the SQL is complicated (if, for instance, you’re deriving data from multiple tables). However, you can create a text file containing the SQL, run this against the database, save the output to a file, and then use KSpread’s data import wizard to load the data from the file rather than from the database.
What if some of your data would be better displayed as a chart than in a table? Simply select the area containing the relevant data and use KSpread’s charting wizard to create your graph.
In KSpread data in a column is always drawn against the Y-axis and data in a row is always drawn against the X-axis. There is no way to define columns as containing data for the X-axis, and KSpread lacks a cross-tabulation or transpose function. A SQL statement such as:
select date, sum(hits) from websitedata group by date;
will produce output something like:
|
15-MAR-05 |
200 |
|
16-MAR-05 |
320 |
|
17-MAR-05 |
400 |
However, to graph it in KSpread it must be formatted as:
|
15-MAR-05 |
16-MAR-05 |
17-MAR-05 |
|
200 |
320 |
400 |
Although KSpread can’t do the job for us, a simple shell script can:
#!/bin/ksh #File name: crosstab while read COLUMN1 COLUMN2;do XLINE1="$XLINE1 $COLUMN1" XLINE2="$XLINE2 $COLUMN2" done echo $XLINE1 echo $XLINE2
You can, of course, modify the script for as many columns as necessary.
If you save the original SQL as rawdata.sql, then you can create a file suitable for importing into KSpread as follows:
cat rawdata.sql|mysql -u{user} -p{password}
{database}|crosstab>crosstab.txt
With the data loaded and formatted, the final step is to decide whether any further work is required. KSpread is a good spreadsheet, but a lousy word processor. Use KWord to add the professional details for the final presentation. You can incorporate KWord documents into a KSpread spreadsheet by using Insert — Object Frame — Spreadsheets.
