Excel Integration

Source: source/function/fwxlsxlb.prg, source/function/drxl.prg, source/function/readxlsx.prg

FiveWin provides three complementary strategies for Excel file integration, ranging from high-speed native generation using the xlsxlibhb C library (based on libxlsxwriter), to OLE automation via TOleAuto for full Excel COM control, and direct XML parsing for reading .xlsx files without Excel.

Strategy Overview

Strategy	Source	Use Case
Native XLSX (TWorkBook/TWorkSheet)	`fwxlsxlb.prg`	High-performance creation, no Excel required, supports formatting and images
OLE Automation (FWDrXLSX)	`drxl.prg`	Full Excel COM object model for legacy formatting and advanced features
XML Reader (TXlsxReader)	`readxlsx.prg`	Reading `.xlsx` files via ZIP/XML parsing, no Excel required

TWorkBook / TWorkSheet - Native XLSX Generation

The TWorkBook and TWorkSheet classes provide an object-oriented interface to the xlsxlibhb C library. This is the preferred method for generating large spreadsheets quickly without any dependency on Microsoft Excel.

TWorkBook Class

Method	Description
`New( cFile )`	Create a new workbook. If the file exists, it is erased before creating a new one. Returns nil on failure.
`AddSheet( cName )`	Add a new worksheet with the given name. Returns a `TWorkSheet` object.
`Format( cPicture, cStyles, nAlign )`	Create a `TFormat` object for cell formatting.
`Close()`	Finalize and save the workbook to disk via `workbook_close()`.

TWorkSheet Class

Method	Description
`Say( nRow, nCol, uValue, oFormat, cPicture )`	Write a value to a cell. Automatically detects Harbour type: numeric → `worksheet_write_number()`, date/datetime → `worksheet_write_datetime()`, logical → `worksheet_write_boolean()`, string → `worksheet_write_string()` with Ansi-to-UTF-8 conversion. Also accepts cell references like `"A1"` as row parameter.
`SayImage( nRow, nCol, cImage )`	Insert an image into a cell. Supports file paths and image buffers. Scales to fit `aImageSize` (default 200x200px).
`SayFormula( nRow, nCol, cFormula, oFormat, cPicture )`	Write a formula string to a cell via `worksheet_write_formula()`.
`MergeRange( r1, c1, r2, c2, cValue, oFormat )`	Merge a range of cells and write a value into the merged region.
`RowHeight( nRow, nHeight, oFormat, lPixel )`	Set row height (in character units or pixels).
`SetColumnSize( nColFrom, nColTo, nWidth, oFormat, lPixel )`	Set column width (in character units or pixels).
`Freeze( nRow, nCol )`	Freeze panes at the specified row and/or column.
`HideZero()`	Hide zero values in the sheet.
`SetStruct( aStruct, nRow, aGroup )`	Define headers and column structure for a data table with optional grouping.
`SetArrayData( aData, aStruct, aGroup )`	Write a 2D array as a formatted table with headers.
`CreateFrom( uSrc )`	Populate the sheet from the current DBF alias or an ADO record set.
`SetFooter( aFooter, nRow )`	Add a footer row with sum formulas to a structured table.
`SetBrush( cImage )`	Set a background image for the worksheet (PNG/JPEG).

TFormat Class

The TFormat class provides cell formatting for TWorkSheet:Say():

Method	Description
`Bold()`	Apply bold font
`Italic()`	Apply italic font
`Underline( n )`	Set underline style (1=single, 2=double)
`Align( n1, n2, ... )`	Set alignment flags (1=left, 2=center, 3=right, 10=vertical center, etc.)
`SetFont( cName, nSize, nColor, cStyles )`	Configure font name, size, color, and styles (B=bold, I=italic, S=strikeout, U=underline)
`SetColor( nFore, nBack )`	Set foreground (font) and background colors
`Picture( cPicture )`	Set number format (e.g., `"#,##0.00"` or predefined index). Use `cPicture` to store the active format code.
`SetBorder( nLine, nClr, cSide )`	Set border style. cSide: "T"=top, "B"=bottom, "L"=left, "R"=right, or omit for all sides
`TextWrap()`	Enable text wrapping within the cell
`FontRotate( nAngle )`	Rotate text by the given angle

sequenceDiagram participant App as "Application Code" participant TWS as "TWorkSheet:Say" participant CLIB as "xlsxlibhb (C)" App->>TWS: Say( row, col, value, oFormat ) TWS->>TWS: ValType(uValue) TWS->>CLIB: worksheet_write_number() TWS->>TWS: AnsiToUtf8(uValue) TWS->>CLIB: worksheet_write_string() TWS->>CLIB: worksheet_write_datetime() TWS-->>App: return Self

TXlsxReader - XML-Based Reading

The TXlsxReader class reads .xlsx files by treating the file as a ZIP archive and parsing the internal OpenXML components. No Excel installation is required.

Method	Description
`New( cXlsx, cSheet )`	Open an `.xlsx` file for reading. Optionally specify a sheet name. Returns nil if the file is not found or invalid.
`ReadData()`	Read all data into `aData` (2D array). Automatically detects types (N, D, T, L, C, M) and computes structure.
`ShowData()`	Display the spreadsheet in an XBrowse dialog with automatic type detection.
`Range( cRange )`	Read a specific range (e.g., `"A1:C10"`) as a 2D array.
`Cells( nRow, nCol )`	Read a single cell or a set of columns from a specific row.
`SaveAs( cSave )`	Save the loaded data to a new `.xlsx` file using the native XLSX library.
`Close()`	Release internal resources.

Data Members

DATA	Type	Description
`nRows`	Numeric	Number of rows in the sheet
`nCols`	Numeric	Number of columns in the sheet
`aHead`	Array	Header row (first row, if all character values)
`aStruct`	Array	Column structure (name, type, length, decimals)
`aData`	Array	All data rows (populated after `ReadData()`)
`nSecs`	Numeric	Elapsed seconds for reading (read-only)

The two convenience functions wrap the reader for one-liner usage:

Function	Description
`FW_ShowXlsx( cXlsx, cSheet )`	Open and display an `.xlsx` file in an XBrowse browser dialog.
`FW_OpenXlsx( cXlsx, cSheet )`	Open an `.xlsx` file and return a `TXlsxReader` object for programmatic access.

OLE Automation (FWDrXLSX)

For environments where Microsoft Excel is installed, the FWDrXLSX class wraps Excel's COM object model. The class is loaded dynamically via HB_FuncPtr() to avoid hard dependencies.

Method	Description
`New( cFile )`	Create a new Excel file via OLE. Prompts for a filename if not provided.
`Say( nRow, nCol, uValue, oFormat, cPicture )`	Write a value to a cell. Accepts both numeric coordinates and cell references (e.g., `"A1"`).
`Format( cPicture, cStyles, nAlign )`	Create a format object (`FWDrXLSXFormat`) for the OLE sheet.
`SayRow( nRow, aValues, oFormat, aPicture )`	Write an array of values to a row.
`SayHeader( nRow, aHead )`	Write a header row with bold formatting and bottom border.
`SetColSizes( aSizes )`	Set column widths from a structure array.
`SetStruct( aStruct, nRow, aGroup )`	Define a structured table with headers and column sizing.
`SetArrayData( aData, aStruct, aGroup )`	Write a 2D array as a formatted table.
`CreateFrom( uSrc )`	Import data from the current DBF alias or an ADO record set.

Runtime linking and selection logic:

Function	Description
`UseDrXlsx( lSet )`	Returns `.T.` if OLE automation should be used (drxl.prg linked and Excel not installed). Optionally set the preference with a logical parameter.
`DrXlsxLinked()`	Returns `.T.` if the `FWDrXLSX` class is available at runtime.
`DrXlsxObj( cFileXlsx )`	Create and return an `FWDrXLSX` object for the given file.
`DrXlsFormat( oXlsx )`	Create a format object attached to an `FWDrXLSX` instance.

Utility Functions

Function	Description
`XLSXLIBLinked()`	Returns `.T.` if the `xlsxlibhb` native library is available (checks for `XLSXLIB` or `TWORKBOOK` function pointers).
`UseXLSXLIB( lSet )`	Returns `.T.` if native `xlsxlibhb` should be preferred over OLE. Optionally set the preference.
`XlsxLibObj( cFile, cSheet )`	Create a `TWorkSheet` object using the native library.
`XlsxObj( cFile, cSheet )`	Smart factory: tries native library first, falls back to OLE, then back to native. Returns a usable worksheet object or nil.
`cTempXlsx()`	Generate a unique temporary filename with `.xlsx` extension in the system temp directory.
`cGetFileXlsx( lTmp )`	Open a standard file-save dialog for `.xlsx` files. If `lTmp` is `.T.` and the user cancels, returns a temporary filename.

Example: Create a Workbook and Write Cells

#include "FiveWin.ch"

function Main()

   local oBook, oSheet, oFmt, nRow := 1

   // Create a new workbook
   oBook := TWorkBook():New( "c:\temp\example.xlsx" )

   if oBook == nil
      MsgStop( "Cannot create workbook" )
      return nil
   endif

   // Add a worksheet
   oSheet := oBook:AddSheet( "Employees" )

   // Create a bold header format
   oFmt := oBook:Format()
   oFmt:Bold():Align( 2, 10 ):SetBottom( 2 )

   // Write headers
   oSheet:Say( nRow, 0, "ID",   oFmt )
   oSheet:Say( nRow, 1, "Name",     oFmt )
   oSheet:Say( nRow, 2, "Hired",    oFmt )
   oSheet:Say( nRow, 3, "Salary",   oFmt )

   // Write data rows (auto-typing: N, C, D, N)
   oSheet:Say( ++nRow, 0, 1001 )
   oSheet:Say( nRow, 1, "John Smith" )
   oSheet:Say( nRow, 2, STOD( "20240315" ) )
   oSheet:Say( nRow, 3, 75000.00 )

   oSheet:Say( ++nRow, 0, 1002 )
   oSheet:Say( nRow, 1, "Maria Garcia" )
   oSheet:Say( nRow, 2, STOD( "20240110" ) )
   oSheet:Say( nRow, 3, 82000.00 )

   // Freeze header row
   oSheet:Freeze( 1, 0 )

   // Finalize and save
   oBook:Close()

   // Display result
   FW_ShowXlsx( "c:\temp\example.xlsx" )

return nil

Example: Read an XLSX File

#include "FiveWin.ch"

function ReadXlsx()

   local oXlsx := FW_OpenXlsx( "c:\temp\example.xlsx" )

   if oXlsx == nil
      MsgStop( "Cannot open file" )
      return nil
   endif

   ? "Rows:", oXlsx:nRows, "Cols:", oXlsx:nCols
   ? "Header:", oXlsx:aHead[ 1 ], oXlsx:aHead[ 2 ], oXlsx:aHead[ 3 ]

   // Access specific cell
   MsgInfo( oXlsx:Cells( 2, 2 ), "Cell B2" )

   // Read a range
   XBROWSER oXlsx:Range( "A1:D3" ) TITLE "Range A1:D3"

   oXlsx:Close()

return nil

Notes

Native XLSX (TWorkBook) is the recommended approach for creating spreadsheets. It has no external dependencies, supports large files, and provides full formatting control through the TFormat class.
OLE Automation (FWDrXLSX) requires Microsoft Excel to be installed on the target machine. It is suitable for legacy code or when Excel-specific features (macros, pivot tables) are needed.
TXlsxReader automatically detects data types from the Excel XML metadata, handling strings (shared strings table), numbers, dates, datetimes, booleans, and images.
The cTempXlsx() and cGetFileXlsx() functions handle temporary file management and conflict detection (files currently open in Excel cannot be overwritten).
When both native and OLE libraries are available, UseXLSXLIB() and UseDrXlsx() control which strategy is preferred. By default, the native library is chosen when Excel is not installed.
String values in native XLSX are automatically converted from ANSI to UTF-8 for proper encoding in the .xlsx XML. Binary memo data is detected and replaced with "<binary data>".