PSSpreadsheet Class Methods
In this section, the PSSpreadsheet class methods are presented in alphabetical order.
Syntax
CreateSheet(sheetname)
Description
Use the CreateSheet method to create and name a new spreadsheet within the open Excel workbook.
Parameters
Field or Control |
Definition |
---|---|
sheetname |
Specfies 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 the Addresses as the name of the new spreadsheet.
&sheetname = "Addresses";
&ssObject.CreateSheet(&sheetname);
Syntax
GetFileContent(filename)
Description
Use the GetFileContent method to read the Excel workbook file content.
Parameters
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
StyleName |
Specifies the style for the Excel sheet. Possible style names are:
|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
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
Field or Control |
Definition |
---|---|
&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
Field or Control |
Definition |
---|---|
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);