Webquery : Importing HTML tables

With the help of the Web Page Query OpenOffice.org Calc import filter, you can insert tables from HTML documents in a OpenOffice.org Calc spreadsheet.

Let us step through the relatively straightforward process of importing data from tables in a HTML document.

The data for this example was obtained from an excellent website with tons of population data from all over the world : citypopulation.de

Here is what the source HTML file looks like : swiss.html

For this example, we will be importing awebpage from the local disk.

The External Data dialog is activated by Insert-External Data.

Once we fill in the path to the local copy of the HTML file and the table inside this file we wish to import – we hit OK and we are done – it is that easy.

Read More

Advanced Functions: INDIRECT

The INDIRECT function converts a string into a cell reference. With examples, I will show how useful this can be in certain applications.

First, let us look at how INDIRECT works with the help of a few examples.

D3:D6 is an array of numbers. The values in B3:B6 will be used in the different INDIRECT examples.

B10 =SUM(INDIRECT(B5)) This is equivalent to =SUM(D3:D6)

B12 =SUM(INDIRECT(“D”&B3;&”:D”&B4;)) A more elaborate example of assembling a string from different sources to be converted by INDIRECT to a range reference. B3 and B4 contain the start and end rows respectively of the array wewish to SUM

B14 =INDIRECT(ADDRESS(3;4;4)) The ADDRESS function also can be combined with INDIRECT.ADDRESS takes row and column numbers as arguments – converting them to a string – just what INDIRECT needs.

B15 =SUM(INDIRECT(“Sheet”&B6;&”.”&B5;)) An indirect reference to another sheet in the same document.

The one major difference between OOo Calc and Excel with regard to the INDIRECT function is the handling of named ranges. Excel allows you to make an indirect reference to a named range. This is not the case with OOo Calc.

Read More

Webquery : Importing HTML tables

With the help of the Web Page Query OpenOffice.org Calc import filter, you can insert tables from HTML documents in a OpenOffice.org Calc spreadsheet.

Let us step through the relatively straightforward process of importing data from tables in a HTML document.

The data for this example was obtained from an excellent website with tons of population data from all over the world : citypopulation.de

Here is what the source HTML file looks like : swiss.html

For this example, we will be importing awebpage from the local disk.

The External Data dialog is activated by Insert-External Data.

Once we fill in the path to the local copy of the HTML file and the table inside this file we wish to import – we hit OK and we are done – it is that easy.

Read More

Charting: Editing charts : part 1

In this series of tutorial, we will take a newly created chart and make use of the various chart editing tools provided by OOo Calc to adjust the appearance of the chart to our satisfaction.

In the example below, the chart has just been created from the data in B2:C11 However, we would like to make some changes – making the chart background transparent, adjusting the orientation of the x-axis labels, changing the format of the y-axis numbers and correcting the label on the right hand side.

The first task is the chart background. We would like to make it transparent. We first select the chart by double (left) clicking inside it’s borders. Then we invoke the Chart Area dialog by (right) clicking and selecting Chart Area as shown below.

With the Transparency tab selected, we make the desired change as shown below. Click OK

The end result is much more pleasing to the eye. But we are not finished yet with our chart modifications. Next, we will adjust the orientation of the x-axis labels.

Read More