Tutorial

Column formatting

Deutsche Version

Set column format

Columns are defined by a record of type t_rec_columns

TYPE t_rec_columns IS RECORD(
     cWidth             PLS_INTEGER
    ,cFormat            VARCHAR2(50)
    ,cAutowidth         BOOLEAN
    ,cHidden            BOOLEAN
    ,cIndex             PLS_INTEGER
    );

cWidth

column width. Between 0 and 255 or NULL.

cFormat

ID of a user defined cell format. Set as default for cells in this column.

cAutowidth

set the width of a column automatically. If a width is defined then this value will be the minimal width of the column. Width is only adjusted for date and number columns.

cHidden

Hide column.

cIndex

Specifies the position of this column within the table. If this tag is not specified, the first instance has an assumed Index="1". Each additional Column element has an assumed Index that is one higher. Indices must appear in strictly increasing order. Failure to do so will result in an XML Spreadsheet document that is invalid. Indices do not need to be sequential, however. Omitted indices are formatted with the default style's format.

If one of this values is omitted, the column uses the Default of Excel or OO-Calc.

DECLARE
  -- test_10.sql
  v_fileHandle utl_file.FILE_TYPE;
  tab_columns  xml_spreadsheet.t_tab_columns;

BEGIN
  -- set columns  
  tab_columns(1).cWidth       := 100;
  tab_columns(2).cWidth       := 50;
  tab_columns(3).cWidth       := 25;
  tab_columns(4).cHidden      := TRUE;
  tab_columns(5).cAutowidth   := TRUE;

  -- New File
  v_fileHandle   := xml_spreadsheet.createNewFile('DOC','test_10.xml');
  -- open first worksheet
  xml_spreadsheet.newWorksheet(
       v_fileHandle
      ,'Columns'
      ,xml_spreadsheet.g_tab_caption -- Dummy
      ,tab_columns
      );

  -- Write a data row
  xml_spreadsheet.writeData(v_fileHandle,'Width 100');
  xml_spreadsheet.writeData(v_fileHandle,'Width 50');
  xml_spreadsheet.writeData(v_fileHandle,'Width 25');
  xml_spreadsheet.writeData(v_fileHandle,'Hidden');
  xml_spreadsheet.writeData(v_fileHandle,'Autowidth');
  -- New data row
  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_date);
  xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_date);
  xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_date);
  xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_date);
  xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_date);
  -- New data row
  xml_spreadsheet.newDatarow(v_fileHandle);
  xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_dateTime);
  xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_dateTime);
  xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_dateTime);
  xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_dateTime);
  xml_spreadsheet.writeData(v_fileHandle,SYSDATE,xml_spreadsheet.c_dateTime);

  -- close worksheet
  xml_spreadsheet.closeWorksheet(
           v_fileHandle
          ,0 -- fix no rows in first worksheet when scrolling
          );

  -- Close file
  xml_spreadsheet.closeFile(v_fileHandle);

END;
/

Results for Test 10

Excel	OO-Calc
Click on images to enlarge

Differences

• Excel and OO-Calc show date and datetime with different formattings
• Ecel ignores column width if the content does not fit. Maybe autowidth is set by default
• OO-Calc does size the column width if specified. The width for autowidth seems to be set to the column default, as datetime is wrapped

oracle@matzberger.de Impressum