Excel File Connector

MCWLibXL is an Omnis Studio external object that wraps the LibXL library, providing a straightforward interface for reading and writing Excel files (.xls and .xlsx) directly from Omnis applications.

!Requirement for a one time purchase perpetual licence (LibXL!) https://www.libxl.com/home.html

MCWLibXL

MCWLibXL is an Omnis Studio external object that wraps the LibXL library, providing a straightforward interface for reading and writing Excel files (.xls and .xlsx) directly from Omnis applications.

Original Project
https://github.com/javlacerda/mcwlibxl

 

Features

• Create and save Excel workbooks in XLS and XLSX formats
• Read existing Excel files
• Read and write strings, numbers, booleans, dates, times, and date/times
• Write formulas to cells
• Manage multiple sheets (add, select by name or index, list all)
• Insert rows and columns
• Full cell formatting support: fonts, colours, alignment, borders, fill patterns
• Control gridlines display and text rotation
• Navigate data ranges (first/last row and column)

Methods

Workbook

Method	Signature	Description
$createbook	$createbook()	Creates a new Excel book in XLS format
$createxmlbook	$createxmlbook()	Creates a new Excel book in XLSX format
$loadbook	$loadbook(name)	Reads an Excel book from a file
$savebook	$savebook(name)	Saves the Excel book to a file
$releasebook	$releasebook()	Destroys the Excel book and frees memory
$geterrormessage	$geterrormessage()	Returns the last error message

Sheets

Method	Signature	Description
$addsheet	$addsheet(name)	Creates a new sheet in the Excel book
$selectsheet	$selectsheet(name)	Selects a sheet by name
$selectsheetbyindex	$selectsheetbyindex(index)	Selects a sheet by its index
$getsheetnames	$getsheetnames(list)	Returns a list with all sheet names
$getactivesheet	$getactivesheet(name)	Returns the name of the current sheet
$getactivesheetindex	$getactivesheetindex(index)	Returns the index of the current sheet

Reading Cells

Method	Signature	Description
$readstr	$readstr(row, col, text)	Reads a string from a cell
$readnum	$readnum(row, col, num)	Reads a numeric value from a cell
$readbool	$readbool(row, col, bool)	Reads a boolean value from a cell
$readdate	$readdate(row, col, day,  month,year)	Reads a date from a cell
$readtime	$readtime(row, col, hours, minutes, seconds)	Reads a time from a cell
$readdatetime	$readdatetime(row, col, year, month, day, hours, minutes, seconds)	Reads a date/time from a cell
$getcelltype	$getcelltype(row,col,celltype)	Returns the data type of a cell (see Cell Type Constants)

Writing Cells

Method	Signature	Description
$writestr	$writestr(row,col,text)	Writes a string to a cell
$writenum	$writenum(row, col, num)	Writes a numeric value to a cell
$writebool	$writebool(row, col, bool)	Writes a boolean value to a cell
$writeformula	$writeformula(row, col, formula)	Writes a formula to a cell
$writedate	$writedate(row, col, day, month, year)	Writes a date to a cell
$writetime	$writetime(row, col, hours, minutes, seconds)	Writes a time to a cell
$writedatetime	$writedatetime(row, col, year, month, day, hours, minutes, seconds)	Writes a date/time to a cell

Navigation

Method	Signature	Description
$getfirstrow	$getfirstrow(row)	Returns the first row containing data
$getlastrow	$getlastrow(row)	Returns the last row containing data
$getfirstcol	$getfirstcol(col)	Returns the first column containing data
$getlastcol	$getlastcol(col)	Returns the last column containing data
$insertrow	$insertrow(start,end)	Inserts one or more rows between start and end
$insertcol	$insertcol(start,end)	Inserts one or more columns between start and end

Formatting

Formats and fonts are created by name, activated, configured, and then applied to subsequent write operations.

Method	Signature	Description
$addformat	$addformat (name)	Creates a format with the specified name
$setformat	$setformat (name)	Selects and activates a format by name
$unsetformat	$unsetformat ()	Clears the current format
$setalignh	$setalignh (type)	Sets horizontal alignment (see Alignment Constants)
$setalignv	$setalignv (type)	Sets vertical alignment (see Alignment Constants)
$setborder	$setborder (type)	Sets the border style for all sides
$setbordercolor	$setbordercolor (color)	Sets the border color
$setbordersides	$setbordersides (top, bottom, left, right)	Sets border style individually per side
$setpatternforegroundcolor	$setpatternforegroundcolor (color)	Sets the foreground pattern color
$setpatternbackgroundcolor	$setpatternbackgroundcolor (color)	Sets the background pattern color
$setfillpattern	$setfillpattern (pattern)	Sets the fill pattern (see Fill Pattern Constants)
$setrotation	$setrotation (value)	Sets text rotation: 0–90 clockwise, 91–180 counter-clockwise, 255 vertical
$setdisplaygridlines	$setdisplaygridlines (activate)	Enables/disables gridlines display
$addfont	$addfont (name)	Creates a font with the specified name
$setfont	$setfont (name)	Selects and activates a font by name
$unsetfont	$unsetfont ()	Clears the current font
$setfontname	$setfontname (name)	Sets the font face name
$setfontsize	$setfontsize (size)	Sets the font size
$setfontcolor	$setfontcolor (color)	Sets the font color (see Color Constants)
$setfontitalic	$setfontitalic (activate)	Enables/disables italic
$setfontbold	$setfontbold (activate)	Enables/disables bold
$setfontunderline	$setfontunderline (activate)	Enables/disables underline
$setformatfont	$setformatfont (fontname)	Associates a font with the current format

 

Also implemented are various constants for easier user such as for cell types, alignment, border styles, fill patterns and colours.