On a build server or a customer PC without Microsoft Office, CreateOleObject('Excel.Application') stops with EOleSysError and the message "Invalid class string", because there is no Excel to automate. The TXLSWorkbook class in HotXLS removes that dependency: it creates, reads and saves Excel workbooks directly from Delphi code, with no Excel process involved
HotXLS ships two engines, and the file format decides which one you want. TXLSWorkbook in unit lxHandle reads and writes the BIFF8 .xls format and imports CSV; TXLSXWorkbook in lxHandleX handles .xlsx and OpenDocument .ods. This guide stays with TXLSWorkbook, the class whose calls follow the Excel object model most closely. Sheets.Add, Cells[Row, Col].Value, Range['A1', 'D1'].Font.Bold, NumberFormat and SaveAs read almost exactly like the OLE code they replace, so porting an automation routine is mostly a matter of swapping late-bound Variant variables for typed ones. Where .xlsx enters the picture, the sections below show the hand-off
Creating a workbook and writing typed cells
A new TXLSWorkbook starts with no worksheets at all, so the first call after Create is Sheets.Add. Hold the workbook in a variable of the interface type IXLSWorkbook; the object is reference-counted and frees itself when the last reference goes away. From there every cell is reached through Sheet.Cells[Row, Col] with 1-based row and column numbers, exactly as in Excel, and the Variant type you assign to Value decides what kind of cell gets stored. The routine below builds a small order report with text, dates, numbers and a total formula
uses
SysUtils, Variants, lxHandle;
procedure BuildOrderReport(const FileName: string);
const
OrderNo: array[0..2] of string = ('000417', '000418', '000419');
Customer: array[0..2] of string = ('Northwind Traders', 'Contoso Ltd', 'Fabrikam Inc');
Amount: array[0..2] of Double = (1250.00, 384.50, 2210.75);
var
Book: IXLSWorkbook; // interface reference, never call Free
Sheet: TXLSWorksheet;
I, R: Integer;
begin
Book := TXLSWorkbook.Create;
Book.Title := 'Open orders';
Book.Author := 'Order service';
Sheet := Book.Sheets.Add; // a new workbook has no sheets yet
Sheet.Name := 'Orders';
Sheet.Cells[1, 1].Value := 'Order';
Sheet.Cells[1, 2].Value := 'Placed';
Sheet.Cells[1, 3].Value := 'Customer';
Sheet.Cells[1, 4].Value := 'Amount';
Sheet.Range['A1', 'D1'].Font.Bold := True;
for I := 0 to High(OrderNo) do
begin
R := I + 2;
Sheet.Cells[R, 1].NumberFormat := '@'; // text format, keeps leading zeros
Sheet.Cells[R, 1].Value := OrderNo[I];
Sheet.Cells[R, 2].Value := VarFromDateTime(EncodeDate(2026, 10, 20 + I));
Sheet.Cells[R, 3].Value := Customer[I];
Sheet.Cells[R, 4].Value := Amount[I];
end;
Sheet.Cells[5, 3].Value := 'Total';
Sheet.Cells[5, 4].Value := '=SUM(D2:D4)'; // leading '=' stores a formula
Sheet.Range['D2', 'D5'].NumberFormat := '#,##0.00';
Sheet.Range['A1', 'D1'].ColumnWidth := 18;
Sheet.FreezePanes(1, 0); // keep the header row in view
if Book.SaveAs(FileName) <> 1 then // BIFF8 .xls by default
raise Exception.CreateFmt('Could not save %s', [FileName]);
end;What gets stored depends on the Variant type you assign. Integers, Double and Currency become numeric cells, Boolean becomes TRUE or FALSE, and a string becomes text unless it starts with =, in which case TXLSWorkbook compiles it as a formula. To store literal text that begins with an equals sign, prefix it with an apostrophe the way you would when typing into Excel; the apostrophe is stripped and the rest is kept as text. Dates need one deliberate step. VarFromDateTime produces a varDate Variant, and when such a value lands in a cell still on the General format, TXLSWorkbook applies yyyy-mm-dd, or yyyy-mm-dd hh:mm:ss when there is a time part, so the cell displays as a date. Hand over the same moment as a plain Double, say the result of date arithmetic stored in a Double variable, and Excel shows 46315 instead of 20 October 2026, because nothing marked the cell as a date. Order numbers have the opposite problem: written as integers, 000417 turns into 417, so they go in as strings, and the @ text format stops Excel from converting the cell if someone retypes it later
Why does Sheets[0] return nil in TXLSWorkbook?
Because sheet positions are 1-based. Book.Sheets[1] is the first worksheet, matching Excel's Worksheets(1), and an index of 0 or anything past Sheets.Count returns nil instead of raising, so the access violation shows up one line later when you touch Name or Cells. Sheets.SheetByName follows the same rule and returns nil for an unknown name, which makes it the safer lookup in import code that has to cope with user-supplied workbooks. One boundary runs the other way. The FirstRow, LastRow, FirstCol and LastCol properties of an IXLSRange, including Sheet.UsedRange, are 0-based, while Cells stays 1-based, so code that walks the used area has to add 1 when it turns a bound into a cell address. An empty sheet reports a used range of A1:A1, which means the bounds alone never prove that a sheet holds data
The second trap comes from Delphi's own reference counting. TXLSWorkbook descends from TInterfacedObject, so a workbook held in a variable of type TXLSWorkbook sits at a reference count of zero. Pass that variable to any routine that takes an IXLSWorkbook parameter by value, SaveXLSWorkbookAsXLSX for example, and the count rises to one on entry and drops back to zero on exit, which destroys the workbook while your variable still points at it. Calling Free on a workbook that an interface variable also holds fails in the mirror-image way, as a double free. Declare the variable as IXLSWorkbook everywhere and both problems disappear, since the compiler then manages the lifetime for you. TXLSXWorkbook is meant to be used the ordinary way instead: held in an object variable, created and freed inside try..finally
Reading an existing .xls file and summing a column
TXLSWorkbook.Open returns 1 when the file loads, and after that every Value read hands back a typed Variant: varDate for cells whose number format is a date pattern, Null for empty cells, a string or number otherwise. A formula cell returns its result as computed by the built-in engine, falling back to the value Excel cached in the file; Formula returns the expression text with its leading =, and HasFormula tells the two kinds of cell apart. The function below reopens the report from the previous section, adds up the amounts for one month and skips the total row, which would otherwise count every order twice. Adding a name to SelectedSheets before Open limits parsing to that sheet, a cheap win on workbooks with many tabs; keep this for read-only jobs, because saving a workbook that was only partly loaded raises an exception
uses
SysUtils, Variants, DateUtils, lxHandle;
function SumMonth(const FileName: string; AYear, AMonth: Word;
out Orders: Integer): Double;
var
Book: IXLSWorkbook;
Sheet: TXLSWorksheet;
Used: IXLSRange;
R: Integer;
Placed, Amount: Variant;
begin
Result := 0;
Orders := 0;
Book := TXLSWorkbook.Create;
Book.SelectedSheets.Add('Orders'); // parse only this sheet
if Book.Open(FileName) <> 1 then
raise Exception.CreateFmt('Cannot open %s', [FileName]);
Sheet := Book.Sheets.SheetByName('Orders');
if Sheet = nil then
raise Exception.Create('Worksheet "Orders" not found');
Used := Sheet.UsedRange; // bounds are 0-based
// +1 converts a bound to a Cells row, another +1 skips the header
for R := Used.FirstRow + 2 to Used.LastRow + 1 do
begin
if Sheet.Cells[R, 4].HasFormula then // the total row
Continue;
Placed := Sheet.Cells[R, 2].Value;
Amount := Sheet.Cells[R, 4].Value;
if (VarType(Placed) <> varDate) or not VarIsNumeric(Amount) then
Continue; // blank or malformed row
if (YearOf(Placed) = AYear) and (MonthOf(Placed) = AMonth) then
begin
Result := Result + Amount;
Inc(Orders);
end;
end;
end;The date test in that loop depends on how Excel stores dates. A date cell holds a serial number, a count of days starting from 1 January 1900, and only its number format says "show this as a calendar date". TXLSWorkbook reads the format together with the number and converts the serial to TDateTime when the format is a date pattern, which is why VarType can tell an order date from an amount. Serials below 60 are shifted by one day during that conversion, because Excel's 1900 calendar includes a 29 February 1900 that never existed; from March 1900 onward the serial and Delphi's TDateTime carry the same number. Workbooks created on older Mac versions of Excel may count from 1904 instead, which Book.Date1904 reports. The article on Excel date serials, the 1904 epoch and number-format detection covers both epochs and the format patterns that mark a cell as a date
Can TXLSWorkbook open .xlsx files?
No, and it tells you so clearly. TXLSWorkbook.Open checks the first bytes of the file, and when they carry a ZIP signature, as every .xlsx and .ods package does, it raises an exception whose message names TXLSXWorkbook.Open and TXLSXWorkbook.OpenODS as the right entry points. That matters in practice, because .xlsx files renamed to .xls turn up regularly in mail attachments and old upload forms. Names ending in .csv or .tsv are routed to OpenCSV, which appends the parsed grid as a new worksheet. For import code that receives files from users, route on content rather than extension: DetectExcelContentFormat in unit lxXlsxExport inspects the leading bytes and reports which engine should load the file. Keep the Open result check on the BIFF branch, since ecfXls covers every OLE compound file and a password-encrypted .xlsx lives in that same container
uses
SysUtils, lxHandle, lxHandleX, lxXlsxExport;
// Normalizes an incoming spreadsheet of unknown origin to CSV
procedure ExportFirstSheetAsCsv(const Source, Target: string);
var
Kind: TExcelContentFormat;
Book: IXLSWorkbook;
XBook: TXLSXWorkbook;
Rc: Integer;
begin
Kind := DetectExcelContentFormat(Source);
case Kind of
ecfXls:
begin
Book := TXLSWorkbook.Create;
if Book.Open(Source) <> 1 then
raise Exception.CreateFmt('Cannot open %s', [Source]);
if Book.Sheets[1].SaveAs(Target, xlUnicodeCSV) <> 1 then
raise Exception.CreateFmt('Cannot write %s', [Target]);
end;
ecfXlsx, ecfOds:
begin
XBook := TXLSXWorkbook.Create; // plain object: Free it
try
if Kind = ecfOds then
Rc := XBook.OpenODS(Source)
else
Rc := XBook.Open(Source);
if Rc <> 1 then
raise Exception.CreateFmt('Cannot open %s', [Source]);
// Sheets[] and Cells are 1-based, this SheetIndex is 0-based
if XBook.SaveAsCSV(Target, 0, ',') <> 1 then
raise Exception.CreateFmt('Cannot write %s', [Target]);
finally
XBook.Free;
end;
end;
else
raise Exception.CreateFmt('%s is not a workbook', [Source]);
end;
end;SaveAs in Delphi: choosing .xls, CSV or .xlsx output
TXLSWorkbook.SaveAs(FileName) always writes BIFF8, the Excel 97-2003 .xls format, whatever extension the name carries. Saving to report.xlsx therefore produces an .xls file under the wrong name, and Excel either refuses it or warns that format and extension do not match when a user opens it. Routine save failures come back as return codes, so compare the result with 1 each time; a partly loaded workbook raises an exception on save, and file system errors still propagate as exceptions. Save without a name works only for a workbook that was opened from a file and returns -4 otherwise. Pick the output with the second SaveAs parameter or a dedicated method:
xlExcel97, the default: BIFF8.xls, which every Excel release since 97 and LibreOffice Calc open directlyxlCSVwrites comma-separated text in the process ANSI code page with no BOM, whilexlUnicodeCSVwrites UTF-16 LE with a BOM. On the workbook both save the active sheet, so call them on a specificTXLSWorksheetwhen there are severalxlHTMLandxlRTFfor document pipelines, andSaveAsPDFfor a single paginated PDF of all visible sheets; the HTML and PDF exporters are Windows features.xlsxthroughSaveXLSWorkbookAsXLSXinlxXlsxExport, which copies values, formulas, number formats, cell styling, column widths, row heights, merged ranges and print settings into a new OOXML package
uses
SysUtils, lxHandle, lxXlsxExport;
// One finished workbook, three deliverables
procedure PublishReport(const Book: IXLSWorkbook; const BaseName: string);
var
Orders: TXLSWorksheet;
begin
if Book.SaveAs(BaseName + '.xls') <> 1 then // BIFF8, the default
raise Exception.Create('Saving the .xls copy failed');
Orders := Book.Sheets.SheetByName('Orders');
if (Orders = nil) or
(Orders.SaveAs(BaseName + '.csv', xlUnicodeCSV) <> 1) then // one named sheet
raise Exception.Create('Saving the CSV copy failed');
if SaveXLSWorkbookAsXLSX(Book, BaseName + '.xlsx') <> 1 then
raise Exception.Create('Saving the .xlsx copy failed');
end;For CSV written by a service, pick the encoding on purpose and leave xlCSV for legacy importers that demand it. The ANSI code page of a Western Windows server is usually 1252, and customer names in Chinese, Greek or Polish do not fit into it, so a plain CSV cannot carry them; xlUnicodeCSV keeps every character, and TXLSXWorkbook.SaveAsCSV writes UTF-8 with a BOM when the consumer expects UTF-8. Formula cells go out as their computed values, and setting CSVEmitFormulas to True writes the formula text instead. Before a file leaves the machine, reopen it and assert the numbers. The QuickStart demo that ships with HotXLS does exactly that: it saves a sales order, loads it again, and fails unless the total cell still reads =SUM(E6:E9) and evaluates to 104.65. Set Title and Author too, since they land in the two OLE property-set streams that Excel shows under File > Info. TXLSWorkbook writes both streams on every save, because Excel can greet an .xls that lacks them with a "data may have been lost" warning, and an author you leave empty is recorded as HotXLS
When should you choose TXLSWorkbook over OLE automation?
For anything that runs unattended, choose a native library every time. Microsoft's support article on server-side Automation of Office (KB 257757) states that Microsoft neither recommends nor supports automating Office from unattended, non-interactive components, and in practice one hidden dialog or one orphaned EXCEL.EXE is enough to stall a service. The guide to generating Excel files without Office automation walks through those failure modes and the server-side design in detail. OLE automation still earns its place on a desktop where Excel is installed and the job is to drive Excel itself, such as opening a finished report in front of the user or running a macro the user owns. Between those two poles, format and file size decide:
TXLSWorkbookwhen the deliverable is.xls, when existing.xlsfiles have to be read and modified, or when ported OLE code should keep its familiar shapeTXLSXWorkbookwhen the deliverable is.xlsxwith charts or conditional formatting, or needs more than the 65,536 rows and 256 columns a BIFF8 sheet can hold- a forward-only reader when the file is too large to keep in memory as a workbook model; the streaming XLSX reader for huge files visits each cell once without building an object tree
HotXLS supports Delphi and C++Builder with both engines, the formula engine that computes formula results on read, and the QuickStart demo referenced above. If the report job from the first example is what you need, start from the QuickStart demo and the HotXLS Delphi spreadsheet component trial, then swap in your own query