Reference

Release 1.5
09 February 2004 		Developer's reference PB2XLS for PowerBuilder
Reference

Reference

Objects

n_xls_workbook_v5 | An object-oriented interface to an Excel5 workbook
n_xls_workbook_v97 | An object-oriented interface to an Excel97 workbook
n_xls_worksheet | An object-oriented interface to an Excel worksheet
n_xls_format | This class is used for cell formating
n_cst_unicode | This class is used for unicode/ansi conversion

Examples Code samples with screenshots

Your suggestions | Send us your suggestions and ideas towards the improvement of Pb2xls library.
Outsource your project! | You can place an order to develop some special component based on our pb2xls or another component or program. Write us your software requirements and we shall do our best to accommodate your needs.
© 2003-2004 Desta Ltd. All rights reserved. SupportDesk


N_XLS_WORKBOOK_V5 object

Reference > N_xls_workbook_v5

n_xls_workbook_v5 object

The n_xls_workbook_v5 class provides an object-oriented interface to a new Excel5 workbook. The following methods are available through a new workbook:

Methods

of_create()
of_close()
of_add_worksheet()
of_add_format()
of_set_custom_color()
of_set_codepage()

Your suggestions | Send us your suggestions and ideas towards the improvement of Pb2xls library.


of_create method

Reference > N_xls_workbook_v97 > Of_create

of_create() method

Creates new workbook with the file name you specify.

Syntax

Workbook.of_create ( filename )

Parameters

Filename A string whose value is the name of the file you want to create. If filename is not on the operating system's search path, you must enter the fully qualified name

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs. If argument value is NULL, of_create returns NULL.

Examples

n_xls_workbook book5
book5 = create n_xls_workbook_v5    //Excel5
book5.of_create ( 'C:\DOC\MY_BOOK5.XLS' )     //create excel workbook

n_xls_workbook book97
book97 = create n_xls_workbook_v97    //Excel97
book97.of_create ( 'C:\DOC\MY_BOOK97.XLS' )     //create excel workbook


of_close method

Reference > N_xls_workbook_v97 > Of_close

of_close() method

The of_close() method can be used to explicitly close an Excel file.

Syntax

workbook.of_close ( )

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

book.of_close ( )


of_set_custom_color method

Reference > N_xls_workbook_v97 > Of_set_custom_color

of_set_custom_color() method

Override one of the built-in palette values with a more suitable color.

Syntax

workbook.of_set_custom_color(index, red, green, blue)

Parameters

Index | A long identifying the color index which you want to override
Red | The integer value of the red component of the desired color
Green | The integer value of the green component of the desired color
Blue | The integer value of the blue component of the desired color

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

A new color is set using its RGB (red green blue) components. The red, green and blue values must be in the range 0..255.

Examples

book.of_set_custom_color(32, 200, 200, 200) //set custom color with index 32
format.of_set_color ( 32 ) //using defined color


of_set_codepage method

Reference > N_xls_workbook_v97 > Of_set_codepage

of_set_codepage() method

Set the code page for workbook. The default code page is ANSI.
This option is ignored for Excel97

Syntax

workbook.of_set_codepage(codepage)

Parameters

Codepage The unsigned integer whose value is the number of the code page you want to set

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

book.of_set_codepage ( 1252 ) //set ANSI code page
book.of_set_codepage (  32768 ) //set Apple Macintosh code page


N_XLS_WORKBOOK_V97 object

Reference > N_xls_workbook_v97

n_xls_workbook_v97 object

The n_xls_workbook_v97 class provides an object-oriented interface to a new Excel97 workbook. The following methods are available through a new workbook:

Methods

of_create()
of_close()
of_add_worksheet()
of_add_format()
of_set_custom_color()
of_set_codepage()


N_XLS_WORKSHEET object

Reference > N_xls_worksheet

n_xls_worksheet object

A new worksheet is created by calling the of_add_worksheet() method from a workbook object.
The following methods are available through a new worksheet:

Methods

of_activate()
of_center_horizontally()
of_center_vertically()
of_fit_to_pages()
of_freeze_panes()
of_get_name()
of_get_name_unicode()
of_hide_gridlines()
of_insert_bitmap()
of_merge_write()
of_print_area()
of_print_row_col_headers()
of_protect()
of_repeat_columns()
of_repeat_rows()
of_select()
of_set_column_format()
of_set_column_width()
of_set_column_hidden()
of_set_first_sheet()
of_set_footer()
of_add_h_pagebreak()
of_set_header()
of_set_landscape()
of_set_margin_bottom()
of_set_margin_left()
of_set_margin_right()
of_set_margin_top()
of_set_margins()
of_set_margin_lr()
of_set_margin_tb()
of_set_paper()
of_set_portrait()
of_set_print_scale()
of_set_row_format()
of_set_row_height()
of_set_row_hidden()
of_set_selection()
of_add_v_pagebreak()
of_set_zoom()
of_thaw_panes()
of_write()


of_activate method

Reference > N_xls_worksheet > Of_activate

of_activate() method

Specify which worksheet is initially visible in a multi-sheet workbook.

Syntax

Worksheet.of_activate ( )

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

worksheet.of_activate ( )


of_center_horizontally method

Reference > N_xls_worksheet > Of_center_horizontally

of_center_horizontally() method

Center the worksheet data horizontally between the margins on the printed page.

Syntax

Worksheet.of_center_horizontally ( pages )

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

worksheet.of_center_horizontally ( )


of_center_vertically method

Reference > N_xls_worksheet > Of_center_vertically

of_center_vertically() method

Center the worksheet data vertically between the margins on the printed page.

Syntax

Worksheet.of_center_vertically ( )

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

worksheet.of_center_vertically ( )


of_fit_to_pages method

Reference > N_xls_worksheet > Of_fit_to_pages

of_fit_to_pages() method

Fit the printed area to a specific number of pages both vertically and horizontally. If the printed area exceeds the specified number of pages it will be scaled down to fit. This guarantees that the printed area will always appear on the specified number of pages even if the page size or margins change.

Syntax

worksheet.of_fit_to_pages (width, height)

Parameters

Width | The integer whose value is the number of pages horizontally
Height | The integer whose value is the number of pages vertically

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Note that although it is valid to use both of_fit_to_pages() and of_set_print_scale() on the same worksheet only one of these options can be active at a time. The last method call made will set the active option.
Note that of_fit_to_pages() will override any manual page breaks that are defined in the worksheet.

Examples

worksheet.of_fit_to_pages ( 1, 1 ) //fit the worksheet's printed area to one page


of_freeze_panes method

Reference > N_xls_worksheet > Of_freeze_panes

of_freeze_panes() method

Divide a worksheet into horizontal or vertical regions known as panes and to also "freeze" these panes so that the splitter bars are not visible

Syntax

worksheet.of_freeze_panes (row, col, top_row, left_col)

Parameters

Row | The unsigned integer whose value is the location of the split
Col | The unsigned integer whose value is the location of the split
Top_row | The unsigned integer whose value is the top-most visible row in the scrolling region of the panes
Left_row | The unsigned integer whose value is the left-most visible column in the scrolling region of the panes

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

The parameters Row and Col are used to specify the location of the split. It should be noted that the split is specified at the top or left of a cell and that the method uses zero based indexing. Therefore to freeze the first row of a worksheet it is necessary to specify the split at row 2 (which is 1 as the zero-based index).
You can set one of the row and col parameters as zero if you do not want either a vertical or horizontal split.

Examples

worksheet.of_thaw_panes ( 0, 1, 0, 1)	//First column
worksheet.of_thaw_panes ( 1,  0, 1, 0 )	//First row
worksheet.of_thaw_panes( 1, 1, 1, 1 )	// First row and column


of_get_name method

Reference > N_xls_worksheet > Of_get_name

of_get_name() method

This method is used for retrieving worksheet's name.

Syntax

Worksheet.of_get_name ( )

Return value

String. Returns the worksheet's name.

Examples

string ls_name
ls_name = worksheet.of_get_name ( )


of_get_name_unicode method

Reference > N_xls_worksheet > Of_get_name_unicode

of_get_name_unicode() method

This method is used for retrieving worksheet's name as unicode.

Syntax

Worksheet.of_get_name_unicode ( )

Return value

Blob. Returns the worksheet's name as unicode.

Examples

blob lblb_name
lblb_name = worksheet.of_get_name_unicode ( )


of_hide_gridlines method

Reference > N_xls_worksheet > Of_hide_gridlines

of_hide_gridlines() method

Hide the gridlines on the screen and printed page. Gridlines are the lines that divide the cells on a worksheet. Screen and printed gridlines are turned on by default in an Excel worksheet. If you have defined your own cell borders you may wish to hide the default gridlines.

Syntax

Worksheet.of_hide_gridlines ( Option )

Parameters

Option The following values of option are valid:
0 : Don't hide gridlines
1 : Hide printed gridlines only
2 : Hide screen and printed gridlines

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

worksheet.of_hide_gridlines ( 2 )


of_insert_bitmap method

Reference > N_xls_worksheet > Of_insert_bitmap

of_insert_bitmap() method

This method can be used to insert a bitmap into a worksheet. The bitmap must be a 24 bit, true color bitmap without optimization. See Example09.

Syntax

Worksheet.of_insert_bitmap ( Row, Col, Filename{, X, Y{, ScaleWidth, ScaleHeight}})

Parameters

Row | The unsigned integer whose value is the row where top left corner of the image will be placed
Col | The unsigned integer whose value is the column where top left corner of the image will be placed
Filename | A string whose value is the name of the bitmap file you want to insert. If filename is not on the operating system's search path, you must enter the fully qualified name
X (optional) | The unsigned integer whose value is the horizontal offset from the top left hand corner of the the cell specified by Row and Col. The offset value is in pixels. Default 0.
Y (optional) | The unsigned integer whose value is the vertical offset from the top left hand corner of the the cell specified by Row and Col. Default 0.
ScaleWidth (optional) | The double whose value is the horizontal scale factor. Default 1.
ScaleHeight (optional) | The double whose value is the vertical scale factor. Default 1.

Description

Note: you must call of_set_row_height() or of_set_column_width() before of_insert_bitmap() if you wish to change the default dimensions of any of the rows or columns that the images occupies

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

worksheet.of_insert_bitmap( 0, 3, 'c:\images\image.bmp')
worksheet.of_insert_bitmap( 1, 5, 'c:\images\image.bmp', 10, 13)
worksheet.of_insert_bitmap( 1, 5, 'c:\images\image.bmp', 0, 0, 2, 2)


of_merge_write method

Reference > N_xls_worksheet > Of_merge_write

of_merge_write() method

Write data to the merged cells

Syntax

worksheet.of_merge_write (first_row, first_col, last_row, last_col{, Value}, format)

Parameters

First_row | The unsigned integer whose value is the number of first row in the range
First_col | The unsigned integer whose value is the number of first column in the range
Last_row | The unsigned integer whose value is the number of last row in the range
Last_col | The unsigned integer whose value is the number of last column in the range
Value (optional) | The value to which you want to set the data at the specified location. The data type of the value must be: integer, long, double, decimal, string, blob (unicode string), datetime, date or time
Format | A reference to the n_xls_format object

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

The of_merge_write() method doesn't work with Excel versions before Excel 97. The format object that is used with a of_merge_write() method call is marked internally as being associated with a merged range. As such, it shouldn't be used for other formatting.

Examples

n_cst_unicode lnvo_unicode_srv
worksheet.of_merge_write ( 0, 0, 1, 0, 123, format1 )	//write number to A1:A2 cells
worksheet.of_merge_write ( 0, 1, 0, 3, 'Hello world', format2 )	//write string to B1:D1 cells
//write unicode string to F1:H2 cells
worksheet.of_merge_write ( 0, 5, 1, 7,&
          lnvo_unicode_srv.of_ansi2unicode('English value') +&
          lnvo_unicode_srv.of_ansi2unicode(ls_greek_value, 1253) +&
          lnvo_unicode_srv.of_ansi2unicode(ls_russian_value, 1251),&
          format2)


of_print_area method

Reference > N_xls_worksheet > Of_print_area

of_print_area() method

Specify the area of the worksheet that will be printed.

Syntax

worksheet.of_print_area (first_row, first_col, last_row, last_col)

Parameters

First_row | The unsigned integer whose value is the number of first row in the range
First_col | The unsigned integer whose value is the number of first column in the range
Last_row | The unsigned integer whose value is the number of last row in the range
Last_col | The unsigned integer whose value is the number of last column in the range

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

worksheet.of_print_area ( 0, 0, 9, 9 )   //A1:J10 print area


of_print_row_col_headers method

Reference > N_xls_worksheet > Of_print_row_col_headers

of_print_row_col_headers() method

Set the option to print the row and column headers on the printed page.

Syntax

worksheet.of_print_row_col_headers ( Option )

Parameters

Option (optional) A boolean value that controls whether Worksheet prints the row and column headers. Values are:
TRUE - (Default) Print the row and column headers
FALSE - Do not print the row and column headers

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

worksheet1.of_print_row_col_headers ( true )


of_protect method

Reference > N_xls_worksheet > Of_protect

of_protect() method

Protect a worksheet from modification.

Syntax

worksheet.of_protect ( password )

Parameters

Password A string whose value is the password

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

The of_protect() method also has the effect of enabling a cell's locked and hidden properties if they have been set. A "locked" cell cannot be edited. A "hidden" cell will display the results of a formula but not the formula itself. In Excel a cell's locked property is on by default.

Examples

worksheet.of_protect ( 'mypass' )


of_repeat_columns method

Reference > N_xls_worksheet > Of_repeat_columns

of_repeat_columns() method

Set the columns to repeat at the left hand side of each printed page.

Syntax

worksheet.of_repeat_columns ( first_column, last_column )

Parameters

First_column | The unsigned integer whose value is the number of first column
Last_column | The unsigned integer whose value is the number of last column

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

For large Excel documents it is often desirable to have the first column or columns of the worksheet print out at the left hand side of each page. This can be achieved by using the of_repeat_columns() method. The parameters first_column and last_column are zero based.

Examples

worksheet1.of_repeat_columns(0, 0)     //Repeat the first column
worksheet2.of_repeat_columns(0, 2)     //Repeat the first three columns


of_repeat_rows method

Reference > N_xls_worksheet > Of_repeat_rows

of_repeat_rows() method

Set the number of rows to repeat at the top of each printed page.

Syntax

worksheet.of_repeat_rows ( first_row, last_row )

Parameters

First_row | The unsigned integer whose value is the number of first row
Last_row | The unsigned integer whose value is the number of last row

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

For large Excel documents it is often desirable to have the first row or rows of the worksheet print out at the top of each page. This can be achieved by using the of_repeat_rows() method. The parameters first_row and last_row are zero based.

Examples

worksheet1.of_repeat_rows(0, 0)     //Repeat the first row
worksheet2.of_repeat_rows(0, 2)     //Repeat the first three rows


of_select method

Reference > N_xls_worksheet > Of_select

of_select() method

Specify which worksheet is initially selected in a multi-sheet workbook.

Syntax

Worksheet.of_select ( )

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

A selected worksheet has its tab highlighted. Selecting worksheets is a way of grouping them together so that, for , several worksheets could be printed in one go. A worksheet that has been activated via the of_activate() method will also appear as selected.


of_set_column_format method

Reference > N_xls_worksheet > Of_set_column_format

of_set_column_format() method

Set the default format of a single column

Syntax

worksheet.of_set_column_format ( column, format )

Parameters

column | The long whose value is the number of column
format | A reference to the n_xls_format object

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

If you wish to define a column format in this way you should call the method before any calls to of_write(). If you call it afterwards it won't have any effect.
A default row format takes precedence over a default column format.

Examples

worksheet.of_set_column_format( format1 )	// Set format for first column
worksheet.of_write( 0, 0, 'Hello world' )	//Defaults to format1
worksheet.of_write( 1,  0, 'Hello world', format2 )	//Keeps format2

worksheet.of_set_row_format(0, format1)	//Set format for first row
worksheet.of_set_column_format(0, format2)	//Set format for first column
worksheet.of_write( 0, 0, 'Hello world')	// Defaults to format1
worksheet.of_write( 1, 0, 'Hello world')	//Defaults to format2


of_set_column_width method

Reference > N_xls_worksheet > Of_set_column_width

of_set_column_width() method

Set the width of a single column

Syntax

worksheet.of_set_column_width ( column, width )

Parameters

column | The long whose value is the number of column
width | The long or double whose value is the width

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

The width corresponds to the column width value that is specified in Excel. It is approximately equal to the length of a string in the default font of Arial 10.

Examples

worksheet.of_set_column_width( 0, 10 )    	 // Set width for first column
worksheet.of_set_column_width( 1, 10.5 )    	 // Set width for second column


of_set_column_hidden method

Reference > N_xls_worksheet > Of_set_column_hidden

of_set_column_hidden() method

Set the option to hide a column.

Syntax

worksheet.of_set_column_hidden ( column, hidden )

Parameters

column | The long whose value is the number of column
hidden | A boolean value that controls whether column is hidden

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

This can be used, for example, to hide intermediary steps in a complicated calculation.

Examples

worksheet.of_set_column_hidden( 0, true )    	 // Hide first column


of_set_first_sheet method

Reference > N_xls_worksheet > Of_set_first_sheet

of_set_first_sheet() method

Specify which worksheet is the leftmost visible.

Syntax

Worksheet.of_set_first_sheet ( )

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

The of_activate() method determines which worksheet is initially selected. However, if there are a large number of worksheets the selected worksheet may not appear on the screen. To avoid this you can select which is the leftmost visible worksheet using of_set_first_sheet()


of_set_footer method

Reference > N_xls_worksheet > Of_set_footer

of_set_footer() method

Set the page footer.

Syntax

worksheet.of_set_footer ( footer, margin )

Parameters

footer | A string or a blob (unicode string) whose value is the page footer
margin | a double whose value is the footer margin in inches

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

The footer is a combination of plain text and control characters (See header and footer string).
Note, the header string must be less than 255 characters. Strings longer than this will not be written and a warning will be generated. The default footer margin is 0.50 inch.

Examples

worksheet.of_set_footer( 'Footer' , 0.75)    	 // set the page footer and footer margin


of_add_h_pagebreak method

Reference > N_xls_worksheet > Of_add_h_pagebreak

of_add_h_pagebreak() method

Add horizontal page break to a worksheet. A page break causes all the data that follows it to be printed on the next page.

Syntax

worksheet.of_add_h_pagebreak ( pagebreak )

Parameters

Pagebreak The unsigned integer whose value is the row of page break

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Horizontal page breaks act between rows. To create a page break between rows 24 and 25 you must specify the break at row 25. However in zero index notation this is actually row 24.
Note: If you specify the "fit to page" option via the of_fit_to_pages() method it will override all manual page breaks.

Examples

worksheet.of_add_h_pagebreak ( 10 )    	 //Ten rows on the first page


of_set_header method

Reference > N_xls_worksheet > Of_set_header

of_set_header() method

Set the page header.

Syntax

worksheet.of_set_header ( header, margin )

Parameters

header | A string or a blob (unicode string) whose value is the page header
margin | a double whose value is the header margin in inches

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

The header is a combination of plain text and control characters (See header and footer string).
Note, the header string must be less than 255 characters. Strings longer than this will not be written and a warning will be generated. The default header margin is 0.50 inch.

Examples

worksheet.of_set_header( 'Header' , 0.75)    	 // set the page header and header margin


of_set_landscape method

Reference > N_xls_worksheet > Of_set_landscape

of_set_landscape() method

Set the orientation of a worksheet's printed page to landscape.

Syntax

worksheet.of_set_landscape ( )

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

worksheet.of_set_landscape (  )


of_set_margin_bottom method

Reference > N_xls_worksheet > Of_set_margin_bottom

of_set_margin_bottom() method

Set the bottom margin of worksheet's printed page.

Syntax

worksheet.of_set_margin_bottom ( margin )

Parameters

margin a double whose value is the margin in inches

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

This method take a distance in inches as a parameter.
Note: 1 inch = 25.4mm.
The default bottom margin is 1.00 inch.

Examples

worksheet.of_set_margin_bottom ( 2.00 )	// set the bottom margin 2 inch


of_set_margin_left method

Reference > N_xls_worksheet > Of_set_margin_left

of_set_margin_left() method

Set the left margin of worksheet's printed page.

Syntax

worksheet.of_set_margin_left ( margin )

Parameters

margin a double whose value is the margin in inches

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

This method take a distance in inches as a parameter.
Note: 1 inch = 25.4mm.
The default left margin is 0.75 inch.

Examples

worksheet.of_set_margin_left ( 1.00 )	// set the left margin 1 inch


of_set_margin_right method

Reference > N_xls_worksheet > Of_set_margin_right

of_set_margin_right() method

Set the right margin of worksheet's printed page.

Syntax

worksheet.of_set_margin_right ( margin )

Parameters

margin a double whose value is the margin in inches

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

This method take a distance in inches as a parameter.
Note: 1 inch = 25.4mm.
The default right margin is 0.75 inch.

Examples

worksheet.of_set_margin_right ( 1.00 )	// set the right margin 1 inch


of_set_margin_top method

Reference > N_xls_worksheet > Of_set_margin_top

of_set_margin_top() method

Set the top margin of worksheet's printed page.

Syntax

worksheet.of_set_margin_top ( margin )

Parameters

margin a double whose value is the margin in inches

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

This method take a distance in inches as a parameter.
Note: 1 inch = 25.4mm.
The default top margin is 1.00 inch.

Examples

worksheet.of_set_margin_top ( 2.00 )	// set the top margin 2 inch


of_set_margins method

Reference > N_xls_worksheet > Of_set_margins

of_set_margins() method

Set the all worksheet margins to the same value.

Syntax

worksheet.of_set_margins ( margin )

Parameters

margin a double whose value is the margin in inches

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

This method take a distance in inches as a parameter.
Note: 1 inch = 25.4mm.
The default left and right margin is 0.75 inch. The default top and bottom margin is 1.00 inch.

Examples

worksheet.of_set_margins ( 2.00 ) 	// set the all margins 2 inch


of_set_margin_lr method

Reference > N_xls_worksheet > Of_set_margin_lr

of_set_margin_lr() method

Set the left and right worksheet margins to the same value.

Syntax

worksheet.of_set_margin_lr ( margin )

Parameters

margin a double whose value is the margin in inches

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

This method take a distance in inches as a parameter.
Note: 1 inch = 25.4mm.
The default left and right margin is 0.75 inch.

Examples

worksheet.of_set_margin_lr ( 1.00 )	// set the left and right margin 1 inch


of_set_margin_tb method

Reference > N_xls_worksheet > Of_set_margin_tb

of_set_margin_tb() method

Set the top and bottom worksheet margins to the same value.

Syntax

worksheet.of_set_margin_tb ( margin )

Parameters

margin a double whose value is the margin in inches

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

This method take a distance in inches as a parameter.
Note: 1 inch = 25.4mm.
The default top and bottom margin is 1.00 inch.

Examples

worksheet.of_set_margin_tb ( 2.00 )	// set the top and bottom margin 2 inch


of_set_paper method

Reference > N_xls_worksheet > Of_set_paper

of_set_paper() method

Set the paper format for the printed output of a worksheet

Syntax

worksheet.of_set_paper ( {index} )

Parameters

index (optional) The unsigned integer whose value is the index of paper style. Default 0

The following paper styles are available:

Index | Paper format | Paper size
0 | Printer default | -
1 | Letter | 8 1/2 x 11 in
2 | Letter Small | 8 1/2 x 11 in
3 | Tabloid | 11 x 17 in
4 | Ledger | 17 x 11 in
5 | Legal | 8 1/2 x 14 in
6 | Statement | 5 1/2 x 8 1/2 in
7 | Executive | 7 1/4 x 10 1/2 in
8 | A3 | 297 x 420 mm
9 | A4 | 210 x 297 mm
10 | A4 Small | 210 x 297 mm
11 | A5 | 148 x 210 mm
12 | B4 | 250 x 354 mm
13 | B5 | 182 x 257 mm
14 | Folio | 8 1/2 x 13 in
15 | Quarto | 215 x 275 mm
16 | - | 10x14 in
17 | - | 11x17 in
18 | Note | 8 1/2 x 11 in
19 | Envelope 9 | 3 7/8 x 8 7/8
20 | Envelope 10 | 4 1/8 x 9 1/2
21 | Envelope 11 | 4 1/2 x 10 3/8
22 | Envelope 12 | 4 3/4 x 11
23 | Envelope 14 | 5 x 11 1/2
24 | C size sheet | -
25 | D size sheet | -
26 | E size sheet | -
27 | Envelope DL | 110 x 220 mm
28 | Envelope C3 | 324 x 458 mm
29 | Envelope C4 | 229 x 324 mm
30 | Envelope C5 | 162 x 229 mm
31 | Envelope C6 | 114 x 162 mm
32 | Envelope C65 | 114 x 229 mm
33 | Envelope B4 | 250 x 353 mm
34 | Envelope B5 | 176 x 250 mm
35 | Envelope B6 | 176 x 125 mm
36 | Envelope | 110 x 230 mm
37 | Monarch | 3.875 x 7.5 in
38 | Envelope | 3 5/8 x 6 1/2 in
39 | Fanfold | 14 7/8 x 11 in
40 | German Std Fanfold | 8 1/2 x 12 in
41 | German Legal Fanfold | 8 1/2 x 13 in

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Note, it is likely that not all of these paper types will be available to the end user since it will depend on the paper formats that the user's printer supports. Therefore, it is best to stick to standard paper types.
If you do not specify a paper type the worksheet will print using the printer's default paper.

Examples

worksheet.of_set_paper ( 9 )	// set the A4 paper style


of_set_portrait method

Reference > N_xls_worksheet > Of_set_portrait

of_set_portrait() method

Set the orientation of a worksheet's printed page to portrait.

Syntax

worksheet.of_set_portrait ( )

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

worksheet.of_set_portrait (  )


of_set_print_scale method

Reference > N_xls_worksheet > Of_set_print_scale

of_set_print_scale() method

Set the scale factor of the printed page.

Syntax

worksheet.of_set_print_scale ( scale )

Parameters

scale The unsigned integer whose value is the scale factor. Scale factors in the range 10 <= scale <= 400 are valid

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

The default scale factor is 100.
Note, of_set_print_scale() does not affect the scale of the visible page in Excel. For that you should use of_set_zoom().
Note also that although it is valid to use both of_fit_to_pages() and of_set_print_scale() on the same worksheet only one of these options can be active at a time. The last method call made will set the active option.

Examples

worksheet.of_set_print_scale ( 200 )


of_set_row_format method

Reference > N_xls_worksheet > Of_set_row_format

of_set_row_format() method

Set the default format of a single row

Syntax

worksheet.of_set_row_format ( row, format )

Parameters

row | The long whose value is the number of row
format | A reference to the n_xls_format object

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

If you wish to define a row format in this way you should call the method before any calls to of_write(). If you call it afterwards it will overwrite any format that was previously specified. A default row format takes precedence over a default column format.

Examples

worksheet.of_set_row_format ( 0, format1 )	// Set format for the first row
worksheet.of_write ( 0, 0, 'Hello world' )	//Defaults to format1
worksheet.of_write ( 0, 1, 'Hello world', format2 )	//Keeps format2

worksheet.of_set_row_format ( 0, format1 )	//Set format for first row
worksheet.of_set_column_format ( 0, format2 )	//Set format for first column
worksheet.of_write ( 0, 0, 'Hello world' )	// Defaults to format1
worksheet.of_write ( 1, 0, 'Hello world' )	//Defaults to format2


of_set_row_height method

Reference > N_xls_worksheet > Of_set_row_height

of_set_row_height() method

Set the height of a single row

Syntax

worksheet.of_set_row_height ( row, height )

Parameters

row | The long whose value is the number of row
height | The long or double whose value is the height of row

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

worksheet.of_set_row_height( 0, 20 )    	 // Set height for the first row
worksheet.of_set_row_height( 1, 10.5 )    	 // Set height for the second row


of_set_row_hidden method

Reference > N_xls_worksheet > Of_set_row_hidden

of_set_row_hidden() method

Set the option to hide a row.

Syntax

worksheet.of_set_row_hidden ( row, hidden )

Parameters

row | The long whose value is the number of row
hidden | A boolean value that controls whether row is hidden

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

This can be used, for example, to hide intermediary steps in a complicated calculation.

Examples

worksheet.of_set_row_hidden( 0, true )    	 // Hide first row


of_set_selection method

Reference > N_xls_worksheet > Of_set_selection

of_set_selection() method

Specify which cell or cells are selected in a worksheet.

Syntax

worksheet.of_set_selection ( first_row, first_col{, last_row, last_col} )

Parameters

First_row | The unsigned integer whose value is the number of first row in the range
First_col | The unsigned integer whose value is the number of first column in the range
Last_row(optional) | The unsigned integer whose value is the number of last row in the range
Last_col(optional) | The unsigned integer whose value is the number of last column in the range

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

The most common requirement is to select a single cell, in which case last_row and last_col can be omitted. The active cell within a selected range is determined by the order in which first and last are specified.

Examples

worksheet1.of_set_selection( 0, 0 )    	 // select cell A1
worksheet2.of_set_selection( 0, 0, 2, 0 )    	 // select cells A1:A3


of_add_v_pagebreak method

Reference > N_xls_worksheet > Of_add_v_pagebreak

of_add_v_pagebreak() method

Add vertical page break to a worksheet. A page break causes all the data that follows it to be printed on the next page.

Syntax

worksheet.of_add_v_pagebreak ( pagebreak )

Parameters

Pagebreak The unsigned integer whose value is the column of page break

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Vertical page breaks act between columns. To create a page break between columns 7 and 8 you must specify the break at column 8. However in zero index notation this is actually column 7.
Note: If you specify the "fit to page" option via the of_fit_to_pages() method it will override all manual page breaks.

Examples

worksheet.of_add_v_pagebreak ( 10 )    	 //Ten columns on the first page


of_set_zoom method

Reference > N_xls_worksheet > Of_set_zoom

of_set_zoom() method

Set the worksheet zoom factor in the range 10 <= scale <= 400.

Syntax

worksheet.of_set_zoom ( scale )

Parameters

scale The unsigned integer whose value is the zoom factor. Zoom factors in the range 10 <= scale <= 400 are valid

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

The default zoom factor is 100.
Note, of_set_zoom() does not affect the scale of the printed page. For that you should use of_set_print_scale().

Examples

worksheet.of_set_zoom ( 200 )


of_thaw_panes method

Reference > N_xls_worksheet > Of_thaw_panes

of_thaw_panes() method

Divide a worksheet into horizontal or vertical regions known as panes. This method is different from the of_freeze_panes() method in that the splits between the panes will be visible to the user and each pane will have its own scroll bars.

Syntax

worksheet.of_thaw_panes (x, y, top_row, left_col)

Parameters

Y | The double whose value is the vertical position of the split
X | The double whose value is the horizontal position of the split
Top_row | The unsigned integer whose value is the top-most visible row in the scrolling region of the panes
Left_row | The unsigned integer whose value is the left-most visible column in the scrolling region of the panes

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

The units for Y and X are the same as those used by Excel to specify row height and column width. However, the vertical and horizontal units are different from each other. Therefore you must specify the Y and X parameters in terms of the row heights and column widths that you have set or the default values which are 12.75 for a row and 8.43 for a column.
You can set one of the Y and X parameters as zero if you do not want either a vertical or horizontal split.

Examples

worksheet.of_thaw_panes ( 0, 8.43, 0, 1)	//First column
worksheet.of_thaw_panes ( 12.75,  0, 1, 0 )	//First row
worksheet.of_thaw_panes( 12.75, 8.43, 1, 1 )	// First row and column


of_write method

Reference > N_xls_worksheet > Of_write

of_write() method

Write data to the cell

Syntax

worksheet.of_write ( row, col{, value}, format )

Parameters

Row | The unsigned integer whose value is the number of row
Col | The unsigned integer whose value is the number of column
Value (optional) | The value to which you want to set the data at the specified location. The data type of the value must be: integer, long, double, decimal, string, blob (unicode string), datetime, date or time
Format | A reference to the n_xls_format object

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

The maximum string value size is 255 characters.

Examples

worksheet.of_write ( 0, 0, 123, format1 )	//write number to A1 cell
worksheet.of_write ( 0, 1, 'Hello world', format2)   //write string to B1 cell
worksheet.of_write ( 0, 2, today(), format3)            //write date to C1 cell


N_XLS_FORMAT object

Reference > N_xls_format

n_xls_format object

Cell formatting is defined through a n_xls_format object. n_xls_format objects are created by calling the workbook of_add_format() method. A n_xls_format object can be used in methods: of_write(), of_merge_write(), of_set_column_format(), of_set_row_format().

Methods

of_set_font ( )
of_set_size ( )
of_set_color ( )
of_set_bold ( )
of_set_italic ( )
of_set_num_format ( )
of_set_align ( )
of_set_text_wrap ( )
of_set_bg_color ( )
of_set_border ( )
of_set_border_color ( )
of_set_bottom ( )
of_set_right ( )
of_set_left ( )
of_set_top ( )
of_set_bottom_color ( )
of_set_left_color ( )
of_set_right_color ( )
of_set_top_color ( )
of_set_underline ( )
of_set_rotation ( )
of_set_script ( )
of_set_locked ( )
of_set_strikeout ( )
of_set_text_justlast ( )
of_set_pattern ( )
of_set_fg_color ( )
of_set_merge ( )
of_copy ( )


of_set_font method

Reference > N_xls_format > Of_set_font

of_set_font() method

Specify the font.

Syntax

format.of_set_font ( fontname )

Parameters

Fontname A string or a blob (unicode string) whose value is the name of the font

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs. If argument value is NULL, of_create returns NULL.

Description

Default font is Arial.
Excel can only display fonts that are installed on the system that it is running on. Therefore it is best to use the fonts that come as standard such as 'Arial', 'Times New Roman' and 'Courier New'.

Examples

format.of_set_font ( 'Times New Roman' )


of_set_size method

Reference > N_xls_format > Of_set_size

of_set_size() method

Set the font size.

Syntax

format.of_set_size ( fontsize )

Parameters

Fontsize The integer whose value is the font size

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs. If argument value is NULL, of_create returns NULL.

Description

Default font size is 10.
Excel can only display fonts that are installed on the system that it is running on. Therefore it is best to use the fonts that come as standard such as 'Arial', 'Times New Roman' and 'Courier New'.

Examples

format.of_set_size ( 14 )


of_set_color method

Reference > N_xls_format > Of_set_color

of_set_color() method

Set the font color.

Syntax

format.of_set_color ( color )

Parameters

color Integer from 8..63 (index of color. See of_set_custom_color() ) or the string whose value is the color (See valid colors)

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs. If argument value is NULL, of_create returns NULL.

Description

Default font color is black.

Examples

format1.of_set_color ( 16 )
format2.of_set_color ( 'red' )


of_set_bold method

Reference > N_xls_format > Of_set_bold

of_set_bold() method

Set the bold property of the font.

Syntax

format.of_set_bold ( Option )

Parameters

Option A boolean value that controls whether font is bold.

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

format.of_set_bold ( true )


of_set_italic method

Reference > N_xls_format > Of_set_italic

of_set_italic() method

Set the italic property of the font.

Syntax

format.of_set_italic ( Option )

Parameters

Option A boolean value that controls whether font is italic.

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

format.of_set_italic ( true )


of_set_num_format method

Reference > N_xls_format > Of_set_num_format

of_set_num_format() method

Define the numerical format of a number in Excel. It controls whether a number is displayed as an integer, a floating point number, a date, a currency value or some other user defined format

Syntax

format.of_set_num_format ( format )

Parameters

Format A string or a blob (unicode string) whose value is the numerical format or an integer whose value is the index of built-in format

Excel's built-in formats are shown in the following table:

Index | Format String
0 | General
1 | 0
2 | 0.00
3 | #,##0
4 | #,##0.00
5 | ($#,##0_);($#,##0)
6 | ($#,##0_);[Red]($#,##0)
7 | ($#,##0.00_);($#,##0.00)
8 | ($#,##0.00_);[Red]($#,##0.00)
9 | 0%
10 | 0.00%
11 | 0.00E+00
12 | # ?/?
13 | # ??/??
14 | m/d/yy
15 | d-mmm-yy
16 | d-mmm
17 | mmm-yy
18 | h:mm AM/PM
19 | h:mm:ss AM/PM
20 | h:mm
21 | h:mm:ss
22 | m/d/yy h:mm
.. | ...........
37 | (#,##0_);(#,##0)
38 | (#,##0_);[Red](#,##0)
39 | (#,##0.00_);(#,##0.00)
40 | (#,##0.00_);[Red](#,##0.00)
41 | _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
42 | _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)
43 | _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
44 | _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
45 | mm:ss
46 | [h]:mm:ss
47 | mm:ss.0
48 | ##0.0E+0
49 | @

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs. If argument value is NULL, of_create returns NULL.

Description

Default format is General.
You should ensure that the format string is valid in Excel prior to using it in WriteExcel.

Examples

format1.of_set_num_format ( 'd mmm yyyy' ) // Format string
format2.of_set_num_format ( 15 )         //built-in format
format3.of_set_num_format ( '0.000' )
format4.of_set_num_format ( '#,##0' )
format5.of_set_num_format ( '#,##0.00' )
format6.of_set_num_format ( '$0.00' )
format7.of_set_num_format ( 'mm/dd/yy' )


of_set_align method

Reference > N_xls_format > Of_set_align

of_set_align() method

Set the horizontal and vertical text alignment within a cell.

Syntax

format.of_set_align ( alignment )

Parameters

Alignment A string whose value is the alignment method

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Valid horizontal alignment: 'left', 'center', 'right', 'fill', 'justify', 'merge'.
Valid vertical alignment: 'top', 'vcenter', 'bottom', 'vjustify'.
The vjustify (vertical justify) option can be used to provide automatic text wrapping in a cell. The height of the cell will be adjusted to accommodate the wrapped text. To specify where the text wraps use the of_set_text_wrap ( ) method.

Examples

format = workbook.of_add_format ( )
format.of_set_align ( 'center' )
format.of_set_align ( 'vcenter' )
worksheet.of_set_row ( 0, 40 )
worksheet.of_write ( 0, 0, 'A', format )


of_set_text_wrap method

Reference > N_xls_format > Of_set_text_wrap

of_set_text_wrap() method

Set the text wrap property.

Syntax

format.of_set_text_wrap ( option )

Parameters

option A boolean value that controls whether text is wrapped

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Excel will adjust the height of the row to accommodate the wrapped text. A similar effect can be obtained without newlines using the of_set_align ( 'vjustify' ) method.

Examples

format.of_set_text_wrap( true )


of_set_bg_color method

Reference > N_xls_format > Of_set_bg_color

of_set_bg_color() method

Set the background color of a pattern. Patterns are defined via the of_set_pattern() method. If a pattern hasn't been defined then a solid fill pattern is used as the default.

Syntax

format.of_set_bg_color ( color )

Parameters

color Integer from 8..63 (index of color. See of_set_custom_color() ) or the string whose value is the color (See valid colors)

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

format1.of_set_bg_color ( 16 )
format2.of_set_bg_color ( 'yellow' )


of_set_border method

Reference > N_xls_format > Of_set_border

of_set_border() method

Set the top, bottom, left and right border to the same style.

Syntax

format.of_set_border ( border_style )

Parameters

Border_style The integer whose value is the border style

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

A cell border is comprised of a border on the bottom, top, left and right.
See border styles.

Examples

format.of_set_border ( 1 )


of_set_border_color method

Reference > N_xls_format > Of_set_border_color

of_set_border_color() method

Set the same color for top, bottom, left and right borders.

Syntax

format.of_set_border_color ( color )

Parameters

color Integer from 8..63 (index of color. See of_set_custom_color() ) or the string whose value is the color (See valid colors)

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Default border color is black.

Examples

format1.of_set_border ( 1 )
format1.of_set_border_color ( 'red' )


of_set_bottom method

Reference > N_xls_format > Of_set_bottom

of_set_bottom() method

Set the bottom border style.

Syntax

format.of_set_bottom ( border_style )

Parameters

Border_style The integer whose value is the border style

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

See border styles.

Examples

format.of_set_bottom ( 3 )


of_set_right method

Reference > N_xls_format > Of_set_right

of_set_right() method

Set the right border style.

Syntax

format.of_set_right ( border_style )

Parameters

Border_style The integer whose value is the border style

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

See border styles.

Examples

format.of_set_right ( 2 )


of_set_left method

Reference > N_xls_format > Of_set_left

of_set_left() method

Set the left border style.

Syntax

format.of_set_left ( border_style )

Parameters

Border_style The integer whose value is the border style

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

See border styles.

Examples

format.of_set_left ( 2 )


of_set_top method

Reference > N_xls_format > Of_set_top

of_set_top() method

Set the top border style.

Syntax

format.of_set_top ( border_style )

Parameters

Border_style The integer whose value is the border style

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

See border styles.

Examples

format.of_set_top ( 5 )


of_set_bottom_color method

Reference > N_xls_format > Of_set_bottom_color

of_set_bottom_color() method

Set the color of bottom border.

Syntax

format.of_set_bottom_color ( color )

Parameters

color Integer from 8..63 (index of color. See of_set_custom_color() ) or the string whose value is the color (See valid colors)

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Default border color is black.

Examples

format1.of_set_border ( 1 )
format1.of_set_bottom_color ( 'red' )
format1.of_set_top_color ( 'yellow )
format1.of_set_left_color ( 'green )
format1.of_set_right_color ( 'blue )


of_set_left_color method

Reference > N_xls_format > Of_set_left_color

of_set_left_color() method

Set the color of left border.

Syntax

format.of_set_left_color ( color )

Parameters

color Integer from 8..63 (index of color. See of_set_custom_color() ) or the string whose value is the color (See valid colors)

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Default border color is black.

Examples

format1.of_set_border ( 1 )
format1.of_set_bottom_color ( 'red' )
format1.of_set_top_color ( 'yellow' )
format1.of_set_left_color ( 'green' )
format1.of_set_right_color ( 'blue' )


of_set_right_color method

Reference > N_xls_format > Of_set_right_color

of_set_right_color() method

Set the color of right border.

Syntax

format.of_set_right_color ( color )

Parameters

color Integer from 8..63 (index of color. See of_set_custom_color() ) or the string whose value is the color (See valid colors)

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Default border color is black.

Examples

format1.of_set_border ( 1 )
format1.of_set_bottom_color ( 'red' )
format1.of_set_top_color ( 'yellow )
format1.of_set_left_color ( 'green )
format1.of_set_right_color ( 'blue )


of_set_top_color method

Reference > N_xls_format > Of_set_top_color

of_set_top_color() method

Set the color of top border.

Syntax

format.of_set_top_color ( color )

Parameters

color Integer from 8..63 (index of color. See of_set_custom_color() ) or the string whose value is the color (See valid colors)

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Default border color is black.

Examples

format1.of_set_border ( 1 )
format1.of_set_bottom_color ( 'red' )
format1.of_set_top_color ( 'yellow )
format1.of_set_left_color ( 'green )
format1.of_set_right_color ( 'blue )


of_set_underline method

Reference > N_xls_format > Of_set_underline

of_set_underline() method

Set the underline property of the font

Syntax

format.of_set_underline ( style )

Parameters

style The integer whose value is underline style

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Excel's underline styles are shown in the following table:

Style | Description
0 | No underline
1 | Single underline
2 | Double underline
33 | Single accounting underline
34 | Double accounting underline

Examples

format.of_set_underline ( 1 )


of_set_rotation method

Reference > N_xls_format > Of_set_rotation

of_set_rotation() method

Set the rotation of the text in a cell.

Syntax

format.of_set_rotation ( style )

Parameters

style The integer whose value is rotation style

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Excel's rotation styles are shown in the following table:

Style | Description
0 | No rotation
1 | Letters run from top to bottom
2 | 90° anticlockwise
3 | 90° clockwise

Examples

format.of_set_rotation ( 2 )


of_set_script method

Reference > N_xls_format > Of_set_script

of_set_script() method

Set the superscript/subscript property of the font.

Syntax

format.of_set_script ( style )

Parameters

style The integer whose value is script style

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Excel's script styles are shown in the following table:

Style | Description
0 | Normal
1 | Superscript
2 | Subscript

Examples

format.of_set_script ( 1 )


of_set_locked method

Reference > N_xls_format > Of_set_locked

of_set_locked() method

Prevent modification of a cells contents

Syntax

format.of_set_locked ( option )

Parameters

option A boolean value that controls whether cell is locked

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Cell locking is turned on by default. However, it only has an effect if the worksheet has been protected. See of_protect() method.

Examples

format.of_set_locked ( false )


of_set_strikeout method

Reference > N_xls_format > Of_set_strikeout

of_set_strikeout() method

Set the strikeout property of the font.

Syntax

format.of_set_strikeout ( option )

Parameters

option A boolean value that controls whether font is strikeout

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

format.of_set_strikeout ( true )


of_set_text_justlast method

Reference > N_xls_format > Of_set_text_justlast

of_set_text_justlast() method

Set the justify last text option.

Syntax

format.of_set_text_justlast ( option )

Parameters

option A boolean value that controls justify last option

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Only applies to Far Eastern versions of Excel

Examples

format.of_set_text_justlast ( true )


of_set_pattern method

Reference > N_xls_format > Of_set_pattern

of_set_pattern() method

Set cell's pattern style.

Syntax

format.of_set_pattern ( style )

Parameters

style The integer whose value is pattern style
Valid pattern styles: 0..18

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

Pattern 1 is a solid fill of the background color.

Examples

format.of_set_pattern ( 1 )


of_set_fg_color method

Reference > N_xls_format > Of_set_fg_color

of_set_fg_color() method

Set the foreground color of a pattern.

Syntax

format.of_set_fg_color ( color )

Parameters

color Integer from 8..63 (index of color. See of_set_custom_color() ) or the string whose value is the color (See valid colors)

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Examples

format1.of_set_fg_color ( 16 )
format2.of_set_fg_color ( 'yellow' )


of_set_merge method

Reference > N_xls_format > Of_set_merge

of_set_merge() method

Align text across two or more adjacent cells.

Syntax

format.of_set_merge ( )

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

This only allows simple Excel5 style horizontal merging which Excel refers to as "center across selection". The of_merge_write() method allows you to do Excel97+ style formatting where the cells can contain other types of alignment in addition to the merging.

Examples

format.of_set_merge ( )
worksheet.of_write (0, 0, 'Merged cells', format )
worksheet.of_write (0, 1, format )


of_copy method

Reference > N_xls_format > Of_copy

of_copy() method

Copy all of the properties from one Format object to another

Syntax

format.of_copy ( src_format )

Parameters

src_format n_xls_format object which is copied

Return value

Integer. Returns 1 if it succeeds and -1 if an error occurs.

Description

This is not a copy constructor, both objects must exist prior to copying.

Examples

format1 = workbook.of_add_format (  )
format1.of_set_font ( 'Times New Roman' )
format1.of_set_size ( 20 )
format1.of_set_bold ( true )
format2 = workbook.of_add_format (  )
format2.of_copy ( format1 )   //format2 is bold, Times New Roman, 20


Examples

Reference > Examples

Examples

Example01 | How to create workbook, add worksheets, write data into merged cells, apply formatting for cells etc.
Example02 | How to create multisheets workbook, set active, selected and first sheet, specify selected cells
Example03 | How to divide a worksheet into horizontal or vertical regions known as panes
Example04 | How to divide a worksheet into horizontal or vertical regions known as frozen panes
Example05 | How to specify page properties: size of paper, orientation, margins, header and footer, scale. This example demonstraite also using the custom colors in workbook.
Example06 | How to specify rows to repeat at the top of each printed page
Example07 | How to define the numerical format for the cells
Example08 | How to specify the print area of the worksheet. This example demonstraite also how to add horizontal and vertical page breaks to a worksheet
Example09 | How to insert bitmap image into a worksheet.

Your suggestions | Send us your suggestions and ideas towards the improvement of Pb2xls library.


example01

Reference > Examples > Example01

Example01

n_xls_workbook book
n_xls_worksheet sheet
n_xls_format format1
n_xls_format format2
n_xls_format format3

//for Excel97:
book = create n_xls_workbook_v97

//for Excel5:
//book = create n_xls_workbook_v5

book.of_create("example01.xls")
sheet = book.of_add_worksheet('sheet 1')
format1 = book.of_add_format()
format1.of_set_align('center')
sheet.of_merge_write ( 0, 0, 0, 3, 'Hello world', format1 )

format2 = book.of_add_format()
format2.of_set_align('vcenter')
format2.of_set_align('center')
format2.of_set_num_format('#,##0.00')
sheet.of_merge_write ( 1, 0, 3, 0, 100, format2 )

format3 = book.of_add_format()
format3.of_set_align('vcenter')
format3.of_set_align('center')
format3.of_set_num_format('dd.mm.yyyy hh:mm:ss')
sheet.of_merge_write ( 1, 1, 3, 1, datetime(today(), now()), format3 )

sheet.of_set_column_width(1, 25)

book.of_close()

Screenshot


example02

Reference > Examples > Example02

Example02

n_xls_workbook book
n_xls_worksheet sheet1
n_xls_worksheet sheet2
n_xls_worksheet sheet3
n_xls_worksheet sheet4

//for Excel97:
book = create n_xls_workbook_v97

//for Excel5:
//book = create n_xls_workbook_v5

book.of_create("example02.xls")

sheet1 = book.of_add_worksheet('first')
sheet2 = book.of_add_worksheet('second')
sheet3 = book.of_add_worksheet('third')
sheet4 = book.of_add_worksheet('forth')

sheet1.of_select()
sheet2.of_select()

sheet4.of_activate()

sheet1.of_set_first_sheet()

sheet4.of_set_selection ( 1, 1, 3, 3 )

book.of_close()

Screenshot


example03

Reference > Examples > Example03

Example03

n_xls_workbook book
n_xls_worksheet sheet1

//for Excel97:
book = create n_xls_workbook_v97

//for Excel5:
//book = create n_xls_workbook_v5

book.of_create("example03.xls")

sheet1 = book.of_add_worksheet('1')
sheet1.of_thaw_panes(25, 15, 5, 10)
book.of_close()

Screenshot


example04

Reference > Examples > Example04

Example04

n_xls_workbook book
n_xls_worksheet sheet1

//for Excel97:
book = create n_xls_workbook_v97

//for Excel5:
//book = create n_xls_workbook_v5

book.of_create("example04.xls")

sheet1 = book.of_add_worksheet('1')
sheet1.of_freeze_panes(1, 1, 5, 10)
book.of_close()

Screenshot


example05

Reference > Examples > Example05

Example05

n_xls_workbook book
n_xls_worksheet sheet1
n_xls_format format1

//for Excel97:
book = create n_xls_workbook_v97

//for Excel5:
//book = create n_xls_workbook_v5

book.of_create("example05.xls")
book.of_set_custom_color(36, 240, 240, 240)

sheet1 = book.of_add_worksheet('sheet 1')

sheet1.of_set_paper(11)
sheet1.of_set_landscape()
sheet1.of_set_margins(2)
sheet1.of_set_header('&LLeft header&C&20Center header', 0.5)
sheet1.of_set_footer('&LLeft footer&C&8Center footer', 0.5)
sheet1.of_set_print_scale(140)

format1 = book.of_add_format()
format1.of_set_bg_color(36)

sheet1.of_write(0,0, 'Check  print preview...', format1)
sheet1.of_set_column_width(0, 60)
book.of_close()

Screenshot


example06

Reference > Examples > Example06

Example06

n_xls_workbook book
n_xls_worksheet sheet1
n_xls_format format1
uint i, j

//for Excel97:
book = create n_xls_workbook_v97

//for Excel5:
//book = create n_xls_workbook_v5

book.of_create("example06.xls")

sheet1 = book.of_add_worksheet('sheet 1')
format1 = book.of_add_format()
for i = 1 to 100
   for j = 1 to 5
     sheet1.of_write(i - 1, j - 1, i * j, format1)
   next
next

sheet1.of_repeat_rows(0, 0)
sheet1.of_print_row_col_headers(true)

book.of_close()

Screenshot


example07

Reference > Examples > Example07

Example07

yn_xls_workbook book
n_xls_worksheet sheet1
n_xls_format format1
n_xls_format format2
n_xls_format format3

//for Excel97:
book = create n_xls_workbook_v97

//for Excel5:
//book = create n_xls_workbook_v5

book.of_create("example07.xls")

sheet1 = book.of_add_worksheet('sheet 1')
format1 = book.of_add_format()
format1.of_set_color ( 'red' )
format1.of_set_bold ( true )
sheet1.of_write(0, 0, 'HELLO', format1)

format2 = book.of_add_format()
format2.of_set_color ( 'blue' )
format2.of_set_italic ( true )
format2.of_set_num_format ( '#,##0.00' )
sheet1.of_write(1, 0, 25456.4567, format2)

format3 = book.of_add_format()
format3.of_set_num_format ( 'dd.mm.yyyy hh:mm:ss' )
sheet1.of_write(2, 0, datetime(today(),now()), format3)

book.of_close()

Screenshot


example08

Reference > Examples > Example08

Example08

n_xls_workbook book
n_xls_worksheet sheet1
n_xls_format format1
uint i, j

//for Excel97:
book = create n_xls_workbook_v97

//for Excel5:
//book = create n_xls_workbook_v5

book.of_create("example08.xls")

sheet1 = book.of_add_worksheet('sheet 1')
format1 = book.of_add_format()
for i = 1 to 30
    for j = 1 to 8
        sheet1.of_write(i - 1, j - 1, i * j, format1)
    next
next

sheet1.of_print_area(1, 2, 5, 7)
sheet1.of_add_h_pagebreak(3)
sheet1.of_add_v_pagebreak(4)

book.of_close()

Screenshot


example09

Reference > Examples > Example09

Example09

n_xls_workbook book
n_xls_worksheet sheet

//for Excel97:
book = create n_xls_workbook_v97

//for Excel5:
//book = create n_xls_workbook_v5

book.of_create("example09.xls")

sheet = book.of_add_worksheet('Sheet with logo')

sheet.of_write(0, 0, 'PB2XLS Logo')
sheet.of_insert_bitmap( 1, 0, 'pb2xls.bmp')

book.of_close()

Screenshot


Border styles

Reference > N_xls_format > Border styles

Border styles

Excel's border styles are shown in the following table:

Border style | Description
0 | No border
1 | Thin single border
2 | Medium single border
3 | Dashed border
4 | Dotted border
5 | Thick single border
6 | Double line border
7 | Hair border

Your suggestions | Send us your suggestions and ideas towards the improvement of Pb2xls library.


Your suggestions | Send us your suggestions and ideas towards the improvement of Pb2xls library.


Header and footer string

Reference > N_xls_worksheet > Header and footer string

Header and footer string

The header and footer is a combination of plain text and control characters.

Control | Description
&L | Left justification
&C | Center justification
&R | Right justification
&P | Page number
&N | Total number of pages
&D | Date
&T | Time
&F | File name
&A | Worksheet name
&fontsize | Font size
&"font,style" | Font name and style
&U | Single underline
&E | Double underline
&S | Strikethrough
&X | Superscript
&Y | Subscript
&& | Literal ampersand &

Text in headers and footers can be justified (aligned) to the left, center and right by prefixing the text with the control characters &L, &C and &R.
For example:

worksheet1.of_set_header( '&RHeader', 0.50 ) //Header justified to the right
worksheet2.of_set_header( '&LHeader', 0.50 ) //Header justified to the left
worksheet3.of_set_header( '&CHeader', 0.50 ) //Header justified to the center

For simple text, if you do not specify any justification the text will be centred. However, you must prefix the text with &C if you specify a font name or any other formatting.
You can have text in each of the justification regions:

worksheet.of_set_header( '&Rright text&Ccenter text&Lleft text', 0.50 )

You can specify the font size of a section of the text by prefixing it with the control character &n where n is the font size:

worksheet1.of_set_header('&L&40Big header', 0.50 )
worksheet2.of_set_header('&L&9Small header', 0.50 )

You can specify the font of a section of the text by prefixing it with the control sequence &"font,style" where font is a font name such as "Arial" or "Times New Roman" and style is one of the standard Windows font descriptions: "Regular", "Bold" , "Italic"or "Bold Italic":

worksheet.of_set_header('&L&"Arial,Bold"Header', 0.50 )


N_CST_UNICODE object

Reference > N_cst_unicode

n_cst_unicode object

This class provides functions to convert ANSI strings to Unicode and Unicode strings to ANSI

Methods

of_ansi2unicode ( )
of_unicode2ansi ( )


of_add_format method

Reference > N_xls_workbook_v97 > Of_add_format

of_add_format() method

Create new format object which are used to apply formatting to a cell.

Syntax

workbook.of_add_format ( )

Return value

n_xls_format. Returns a reference to the format object. If the format couldn't be created, of_add_format returns NULL.

Examples

n_xls_worksheet sheet
n_xls_format format
sheet = book.of_add_worksheet ( 'sheet 1' ) //create worksheet with name 'sheet 1'
format = book.of_add_format ( ) //create format
format.of_set_bold ( true ) //customize the format


of_add_worksheet method

Reference > N_xls_workbook_v97 > Of_add_worksheet

of_add_worksheet() method

Add new worksheet into workbook. You can specify the name of worksheet.

Syntax

workbook.of_add_worksheet ( { worksheetname } )

Parameters

Worksheetname A string or a blob (unicode) whose value is the name of the worksheet you want to create. Length of worksheetname should be less then 31 symbols and it cannot contain any of the following characters: , : * ? / \

In addition, you cannot use the worksheetname for more than one worksheet

Return value

n_xls_worksheet. Returns a reference to the worksheet object. If the worksheet couldn't be created, of_add_worksheet returns NULL.

Examples

n_xls_worksheet sheet
sheet = book.of_add_worksheet( 'sheet 1' ) //create worksheet with name 'sheet 1'


of_ansi2unicode method

Reference > N_cst_unicode > Of_ansi2unicode

of_ansi2unicode() method

Convert ANSI strings to Unicode

Syntax

n_cst_unicode.of_ansi2unicode(as_value{, ai_codepage})

Parameters

as_value | A string whose value is the ANSI string
ai_codepage(optional) | The unsigned integer whose value is the number of the code page . Default 0.

Return value

Blob. Return unicode string.

Examples

n_cst_unicode lnvo_unicode
blob lblb_unicode_value1
blob lblb_unicode_value2

lblb_unicode_value1 = lnvo_unicode.of_ansi2unicode(as_ansi_value)
lblb_unicode_value2 = lnvo_unicode.of_ansi2unicode(as_greek_value, 1253)


of_unicode2ansi method

Reference > N_cst_unicode > Of_unicode2ansi

of_unicode2ansi() method

Convert Unicode strings to ANSI

Syntax

n_cst_unicode.of_unicode2ansi(ablb_value{, ai_codepage})

Parameters

ablb_value | A blob whose value is the Unicode string
ai_codepage(optional) | The unsigned integer whose value is the number of the code page . Default 0.

Return value

Blob. Return unicode string.

Examples

n_cst_unicode lnvo_unicode
string ls_value1
string ls__value2

ls_value1 = lnvo_unicode.of_unicode2ansi(ablb_unicode_value)
ls_value2 = lnvo_unicode.of_unicode2ansi(ablb_unicode_value, 1253)


Reference

Reference

Reference

Objects

n_xls_workbook_v5 | An object-oriented interface to an Excel5 workbook
n_xls_workbook_v97 | An object-oriented interface to an Excel97 workbook
n_xls_worksheet | An object-oriented interface to an Excel worksheet
n_xls_format | This class is used for cell formating
n_cst_unicode | This class is used for unicode/ansi conversion

Examples Code samples with screenshots


Valid colors

Reference > N_xls_format > Valid colors

Valid colors

The following colors are available:
'black', 'blue', 'brown', 'cyan', 'gray', 'green', 'lime', 'magenta', 'navy', 'orange',
'purple', 'red', 'silver', 'white', 'yellow'