Tutorial

Hyperlinks and formulas

Deutsche Version

Hyperlinks

The code is shown only partly, the complete code is in the zip-file.

Hyperlinks can link to a cell in the current file, to another file or a folder.

If the target of the link is outside the currrent file you have to give different formats for Excel and OpenOffice (see differences).

DECLARE
  -- test_40.sql
  v_fileHandle            utl_file.FILE_TYPE;

  tab_columns             xml_spreadsheet.t_tab_columns;
  tab_caption             xml_spreadsheet.t_tab_caption;

  r_font                  xml_spreadsheet.t_rec_font;
  r_customStyles          xml_spreadsheet.t_rec_customStyles;

  tab_customStyles        xml_spreadsheet.t_tab_customStyles;

BEGIN

  r_customStyles.id           := 'Hyperlink';
  r_customStyles.type         := 'Text';
  r_font.fColor               := xml_spreadsheet.c_blue;
  r_font.fUnderline           := 's';
  r_customStyles.font         := r_font;
  tab_customStyles(1)         := r_customStyles;

  -- New File
  v_fileHandle   := xml_spreadsheet.createNewFile('DOC','test_40.xml');
  ...

  -- Write a data row
  xml_spreadsheet.writeData(
      v_fileHandle
     ,'Link same page F2'
     ,'Hyperlink'
     ,NULL
     ,NULL
     ,NULL
     ,'#page_1!F2'
     );
  xml_spreadsheet.writeData(
      v_fileHandle
     ,'#page_1!F2'
     );
...

  -- close worksheet
  xml_spreadsheet.closeWorksheet(
       v_fileHandle
      ,0
      );

  -- Close file
  xml_spreadsheet.closeFile(v_fileHandle);

END;
/

Results for Test 40

Excel	OO-Calc
Click on images to enlarge

Differences

• OO-Calc by default gives hyperlinks a grey background

Link syntax

Link Type	Excel	OO-Calc
same page	#<worksheet>!<cell reference in A1 notation> e.g. #page_1!F2	#<worksheet>!<cell reference in A1 notation> e.g. #page_1!F2
another worksheet	#<worksheet>!<cell reference in A1 notation> e.g. #page_2!B3	#<worksheet>!<cell reference in A1 notation> e.g. #page_2!B3
another file or folder, absolute path	Windows style with backslash e.g. C:\temp\test_30.xml	Unix style with slash e.g. /C:/temp/test_30.xml
another file or folder, relative Path	Windows style with backslash e.g. \test_30.xml seen from the root of the current drive	Unix style with slash e.g. ../test_30.xml seen from the current file

Formulas

The code is shown only partly, the complete code is in the zip-file.

Basically you can use every formula allowed in Excel.

The easiest way to find the correct syntax is to create an empty file in Excel or Oo-Calc, to save it in XML-format and to view the output in a text editor.

Important: you have to write cell references relatively to the current cell, contrary to hyperlinks. To find the correct String you can use the function xml_spreadsheet.getRelativeCellReference.

Cell references

Type	Syntax
single cell	R[x]C[y]
Area	R[x1]C[y1]:R[x2]C[y2]
More than one cell	R[x1]C[y1],R[x2]C[y2],R[x3]C[y3],...

DECLARE
  -- test_41.sql
  v_fileHandle            utl_file.FILE_TYPE;

  tab_columns             xml_spreadsheet.t_tab_columns;

BEGIN

  tab_columns(1).cWidth       := 60;
  tab_columns(2).cWidth       := 250;
  tab_columns(3).cWidth       := 80;

  -- New File
  v_fileHandle   := xml_spreadsheet.createNewFile('DOC','testFormula.xml');
  ...

  -- Write a data row
  xml_spreadsheet.writeData(
      v_fileHandle
     ,1
     ,xml_spreadsheet.c_integer
     );
  xml_spreadsheet.writeData(
      v_fileHandle
     ,'Summe A1 - A20'
     );
  xml_spreadsheet.writeData(
      v_fileHandle
     ,TO_NUMBER(NULL)
     ,xml_spreadsheet.c_integer
     ,NULL
     ,'=SUM(RC[-2]:R[19]C[-2])'
     );
  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(
      v_fileHandle
     ,TO_NUMBER(NULL)
     ,xml_spreadsheet.c_integer
     ,TO_NUMBER(NULL)
     ,'=R[-1]C+1'
     );
  xml_spreadsheet.writeData(
      v_fileHandle
     ,'Summe A1 - A20 auf Blatt 2'
     );
  xml_spreadsheet.writeData(
      v_fileHandle
     ,TO_NUMBER(NULL)
     ,xml_spreadsheet.c_integer
     ,NULL
     ,'=SUM(page_2!R[-1]C[-2]:R[18]C[-2]))'
     );
    xml_spreadsheet.newDatarow(v_fileHandle);
...

  -- close worksheet
  xml_spreadsheet.closeWorksheet(
       v_fileHandle
      ,0
      );

  -- Close file
  xml_spreadsheet.closeFile(v_fileHandle);

END;
/

Results for Test 41

Excel	OO-Calc
Click on images to enlarge

Differences

• None except the known difference in standard date formats

oracle@matzberger.de Impressum