XL.Sheet.SetCol

Sets column width and format for all columns from colFirst to colLast.

Parameters

Parameter	Description	Example	Flags
bookRef	The reference to the workbook. Please use XL.LoadBook to load a file.	$ref
sheetIndex	The index of the sheet. This number ranges from 0 to XL.Book.SheetCount-1.	0
ColFirst	The first column.	0
ColLast	The last column.	0
Width	The new column width measured as the number of characters of the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the normal style's font. Value -1 is used for autofit column widths.	10
Format	The format for the cell. This is index from 0 to XL.GetFormatCount-1 in the book's list of formats.	0	Optional
Hidden	Whether to hide columns.	0	Optional

Result

Returns OK or error message.

Description

Examples

Create sheet with column format:

# create new xls file
Set Variable [ $book ; Value: MBS( "XL.NewBook"; 1 ) ]
# load logo picture
Set Variable [ $logoID ; Value: MBS( "XL.Book.AddPictureContainer"; $book; Writing data::Logo) ]
# create fonts
# create format for column
Set Variable [ $colFormat ; Value: MBS( "XL.Book.AddFormat"; $book) ]
Set Variable [ $r ; Value: MBS( "XL.Format.SetWrap"; $book; $colFormat; 1) // enable wrap ]
Set Variable [ $r ; Value: MBS( "XL.Format.SetAlignH"; $book; $colFormat; 1) // left ]
#
# create specialized format for one cell with lock
Set Variable [ $lockFormat ; Value: MBS( "XL.Book.AddFormat"; $book; $colFormat) ]
Set Variable [ $r ; Value: MBS( "XL.Format.SetLocked"; $book; $lockFormat; 1) // on ]
#
# create sheet
Set Variable [ $sheet ; Value: MBS( "XL.Book.AddSheet"; $book; "Sales Receipt") ]
# set default format for whole column
Set Variable [ $r ; Value: MBS( "XL.Sheet.SetCol"; $book; $sheet; 1; 1; 12; $colFormat ) ]
#
# put some text in using column format
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteText"; $book; $sheet; 2; 1; "Long text wraps due to column format") ]
#
# and locked format
Set Variable [ $r ; Value: MBS( "XL.Sheet.CellWriteText"; $book; $sheet; 3; 1; "And this is locked"; $lockFormat) ]
#
# save and export
Set Field [ Writing data::Output ; MBS("XL.Book.Save"; $book; "test.xlsx") ]
Set Variable [ $r ; Value: MBS("XL.Book.Release"; $book) ]
#
Export Field Contents [ Writing data::Output ; Create folders: On ]

See also

• XL.LoadBook
• XL.NewBook
• XL.Sheet.CellWriteText
• XL.Sheet.GetZoom
• XL.Sheet.InsertCol
• XL.Sheet.SetCellFormat
• XL.Sheet.SetName
• XL.Sheet.SetRowPx
• XL.Sheet.SetRows
• XL.Sheet.SetZoom

Release notes

• Version 13.3
  • Added XL.Sheet.SetColPx and XL.Sheet.SetRowPx functions.
• Version 13.0
  • Fixed XL.Sheet.SetColumn and XL.Sheet.SetRow to better.

Example Databases

• XL/Writing data

This function checks for a license.

Created 18th August 2014, last changed 22nd August 2022

XL.Sheet.SetCellFormats - XL.Sheet.SetColHidden