PSSpreadsheet Class Methods

Syntax

CreateSheet(sheetname)

Description

Use the CreateSheet method to create and name a new spreadsheet within the open Excel workbook.

Parameters

Parameter	Description
`sheetname`	Specifies a String value representing the name of the new spreadsheet.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example sets Addresses as the name of the new spreadsheet.

&sheetname = "Addresses";
&ssObject.CreateSheet(&sheetname);

Syntax

DeleteSheet(sheetname)

Description

Use the DeleteSheet method to delete the specified sheet within an open Excel workbook.

Parameters

Parameter	Description
`sheetname`	Specifies a String value representing the name of the sheet to be deleted.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example deletes the Addresses sheet.

&sheetname = "Addresses";
&ssObject.DeleteSheet(&sheetname);

Syntax

GetFileContent(filename)

Description

Use the GetFileContent method to read the Excel workbook file content.

Parameters

Parameter	Description
`filename`	Specifies a string representing the name of the Excel workbook file from which you want to read the data.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example gets the content of the specified Excel workbook file.

&filename = "filedata.xlsx";
&ssObject.GetFileContent(&filename);

Syntax

InsertColumns(startcol, colcount)

Description

Use the InsertColumns method to insert one or more columns into the active spreadsheet.

Parameters

Parameter	Description
`startcol`	Specifies the starting column number.
`colcount`	Specifies the number of columns to be inserted.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example inserts three columns into the spreadsheet.

&startcol = 2;
&colcount = 3;
&ssObject. InsertColumns(&startcol, &colcount);

Syntax

InsertRows(startrow, rowcount)

Description

Use the InsertRows method to insert one or more rows into the active spreadsheet.

Parameters

Parameter	Description
`startrow`	Specifies the starting row number.
`rowcount`	Specifies the number of rows to be inserted.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example inserts three rows into the spreadsheet.

&startrow = 2;
&rowcount = 3;
&ssObject.InsertRows(&startrow, &rowcount);

Syntax

Open(filename, enable_rowsetprocessing)

Description

Use the Open method to open or create an Excel workbook file.

Parameters

Parameter	Description
`filename`	Specifies the name of the Excel workbook file to be opened or created.
`enable_rowsetprocessing`	Specifies a Boolean value indicating whether to enable rowset processing or disable rowset processing.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example opens an existing or creates a new Excel workbook file.

&filename = "myfile.xlsx";
&ssObject.Open(&filename, True);

Syntax

RangeApplyStyle(StyleName, startrow, rowcount, startcol, colcount)

Description

Use the RangeApplyStyle method to set the style of the range of cells.

Note: Due to Apache POI restrictions for user defined style lookup, the implementation of the RangeApplyStyle method is based on the following:

Every workbook should contain a sheet named PSSTYLESHEET.

The STYLESHEET sheet must create unique names on the first row in different cells.

The custom style sheet must be applied to the cell that contains the unique names.

Parameters

Parameter	Description
`StyleName`	Specifies the style for the Excel sheet. Possible style names are: PSChar - Character fields (such as names). PSDate - Dates. PSDec - Numbers with decimal places, such as dollar amounts. PSInt - Integers (such as years). PSHeading - Column headings. PSSpacer - Space between data columns.
`startrow`	Specifies the starting row number.
`rowcount`	Specifies the number of rows.
`startcol`	Specifies the starting column number.
`colcount`	Specifies the number of columns.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

&StyleName ="PSInt";
&startrow = 2;
&rowcount = 3;
&startcol = 2;
&colcount = 3;
&ssObject.RangeApplyStyle(&StyleName, &startrow, &rowcount,&startcol, &colcount);

Syntax

RangeColOutline(startcol, colcount, collapse)

Description

Use the RangeColOutline method to expand or collapse a range of columns in the active spreadsheet.

Parameters

Parameter	Description
`startcol`	Specifies the starting column number.
`colcount`	Specifies the number of columns to be expanded or collapsed.
`collapse`	Specifies a Boolean value indicating whether to collapse or expand the columns.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example collapses three columns.

&startcol = 2;
&colcount = 3;
&b_collapse = True;
&ssObject.RangeColOutline(&startcol, &colcount, &b_collapse);

Syntax

RangeRowOutline(startrow, rowcount, collapse)

Description

Use the RangeRowOutline method to expand or collapse a range of rows in the active spreadsheet.

Parameters

Parameter	Description
`startrow`	Specifies the starting row number.
`rowcount`	Specifies the number of rows to be expanded or collapsed.
`collapse`	Specifies a Boolean value indicating whether to collapse or expand the rows.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example collapses three rows.

&startrow = 2;
&rowcount = 3;
&b_collapse = True;
&ssObject.RangeRowOutline(&startrow, &rowcount, &b_collapse);

Syntax

Save()

Description

Use the Save method to save the Excel workbook file.

Parameters

None.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example saves the Excel workbook file.

&ssObject.Save();

Syntax

SaveAs(filename)

Description

Use the SaveAs method to save the Excel workbook file with a new file name.

Parameters

Parameter	Description
`filename`	Specifies the new name for the Excel workbook file.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example saves the Excel workbook file with a new name.

&filename = "newfile.xlsx";
&ssObject.SaveAs(&filename);

Syntax

SetActiveSheetName(sheetname)

Description

Use the SetActiveSheetName method to rename the active sheet within the Excel workbook file.

Parameters

Parameter	Description
`sheetname`	Specifies the new name for the active sheet within the Excel workbook file.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example sets the name of the active sheet to Accounts.

&sheetname = "Accounts";
&ssObject.SetActiveSheetName(&sheetname);

Syntax

SetCellDate(rownum, colnum, date, locale, timezone, format)

Description

Use the SetCellDate method to set a date value and format for the specified cell in the active spreadsheet.

Parameters

Parameter	Description
`rownum`	Specifies the row number for the cell.
`colnum`	Specifies the column number for the cell.
`date`	Specifies the date value to be set in the cell.
`locale`	Specifies the locale for the date cell value.
`timezone`	Specifies the time zone for the date cell value.
`format`	Specifies the date format for the cell value.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example sets a date value and format for the cell within the spreadsheet.

&rownum = 2;
&colnum = 2;
&data = 120223323;
&locale = "EN";
&timezone = "Americas/Los_Angeles";
&format = "m/d/yy h:mm";
&ssObject. SetCellDate(&rownum, &colnum, &data, &locale, &timezone, &format);

Syntax

SetCellFormula(rownum, colnum, data)

Description

Use the SetCellFormula method to set a formula value for the specified cell in the active spreadsheet.

Parameters

Parameter	Description
`rownum`	Specifies the row number for the cell.
`colnum`	Specifies the column number for the cell.
`data`	Specifies the formula value to be set in the cell.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example sets a formula as the value for the specified cell within the spreadsheet.

&rownum = 2;
&colnum = 2;
&data = "=2+4";
&ssObject.SetCellFormula(&rownum, &colnum, &data);

Syntax

SetCellHeaderStyle(rownum, colnum, redvalue, greenvalue, bluevalue, boldweight, color, fontheight, fontname)

Description

Use the SetCellHeaderStyle method to set the format for the specified cell in the active spreadsheet.

Parameters

Parameter	Description
`rownum`	Specifies the row number for the cell.
`colnum`	Specifies the column number for the cell.
`redvalue`	Specifies the red color value (from 0 to 256) for the specified cell.
`greenvalue`	Specifies the green color value (from 0 to 256) for the specified cell.
`bluevalue`	Specifies the blue color value (from 0 to 256) for the specified cell.
`boldweight`	Specifies the bold weight for the specified cell.
`color`	Specifies the color value for the specified cell.
`fontheight`	Specifies the font height for the specified cell.
`fontname`	Specifies the name of the font for the specified cell.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example sets the format for the specified cell within the spreadsheet.

&rownum = 2;
&colnum = 2;
&red = 100;
&green = 100;
&blue = 100;
&boldweight = 10;
&color = 100;
&fontheight = 10;
&fontname = "Helvetica";
&ssObject.SetCellHeaderStyle(&rownum, &colnum, &red, &green, &blue, &boldweight, &color, &fontheight, &fontname);

Syntax

SetCellHyperlink(rownum, colnum, link)

Description

Use the SetCellHyperlink method to set a hyperlink for the specified cell in the active spreadsheet.

Parameters

Parameter	Description
`rownum`	Specifies the row number for the cell.
`colnum`	Specifies the column number for the cell.
`link`	Specifies the hyperlink value to be set for the specified cell.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example sets the hyperlink value of the specified cell within the spreadsheet.

&rownum = 2;
&colnum = 2;
&link = "http://www.oracle.com";
&ssObject.SetCellHyperlink(&rownum, &colnum, &link);

Syntax

SetCellNumber(rownum, colnum, data, format)

Description

Use the SetCellNumber method to set a double precision floating point value for the specified cell in the active spreadsheet.

Parameters

Parameter	Description
`rownum`	Specifies the row number for the cell.
`colnum`	Specifies the column number for the cell.
`data`	Specifies the double precision floating point value to be set for the specified cell.
`format`	Specifies the numeric format of the data.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example sets a numeric value and format for the specified cell within the spreadsheet.

&rownum = 2;
&colnum = 2;
&data = 120;
&format = "0.00";
&ssObject.SetCellNumber(&rownum, &colnum, &data, &format);

Syntax

SetCellString(rownum, colnum, string)

Description

Use the SetCellString method to set a string value for the specified cell in the active spreadsheet.

Parameters

Parameter	Description
`rownum`	Specifies the row number for the cell.
`colnum`	Specifies the column number for the cell.
`string`	Specifies the string value to be set in the cell.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example sets a string value for the cell.

&rownum = 2;
&colnum = 2;
&data = "Accounting Department";
&ssObject.SetCellString(&rownum, &colnum, &data);

Syntax

SetColumnWidth(colnum, width)

Description

Use the SetColumnWidth method to set the width of the specified column in the active spreadsheet.

Parameters

Parameter	Description
`colnum`	Specifies the column number. Column number must be zero-based.
`width`	Specifies the width of the column. Set the width in units of 1/256th of a character width.

Parameter

Description

colnum

Specifies the column number.

Column number must be zero-based.

width

Specifies the width of the column. Set the width in units of 1/256th of a character width.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example sets the column width within the spreadsheet.

&colnum = 2;
&width = 10;
&ssObject.SetColumnWidth(&colnum, &width);

Syntax

SetRowSetData(&rowset)

Description

Use the SetRowSetData method to read the data from a standalone rowset into the spreadsheet.

Parameters

Parameter	Description
`&rowset`	Specifies an already instantiated standalone Rowset object.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example sets the PeopleSoft rowset data into the spreadsheet.

Local number &idx = 1;

Local Rowset &rs;
&rs = CreateRowset(Record.MY_DATA_TBL);
Local integer &rowNum, &colNum, &cellType;
Local string &cellData;

While (&qSQL.Fetch(&rowNum, &colNum, &cellType, &cellData))
   If &idx <> 1 Then
      &rs.InsertRow(&rs.ActiveRowCount);
   End-If;
   
   &rs(&idx).MY_DATA_TBL.ROWNUM.Value = &rowNum;
   &rs(&idx).MY_DATA_TBL.COLNUM.Value = &colNum;
   &rs(&idx).MY_DATA_TBL.CELLDATA.Value = &cellData;
   &rs(&idx).MY_DATA_TBL.CELLTYPE.Value = &cellType;
   &idx = &idx + 1;
End-While;

&ssObject.SetRowSetData(&rs);

Syntax

SetSheetActivate(sheetnum)

Description

Use the SetSheetActivate method to activate the specified sheet in the Excel workbook.

Parameters

Parameter	Description
`sheetnum`	Specifies the number of sheet to be activated in the Excel workbook.

Returns

(Optional) A Boolean value: True if the method completes successfully, False otherwise.

Example

The following example activates sheet 2 within the Excel workbook.

&sheetnum = 2;
&ssObject.SetSheetActivate(&sheetnum);