How to create a file in Excel 97 (2000, XP) – XLS from Delphi / C++Builder / Lazarus?

Oh, it’s an eternal topic – creating an Excel spreadsheet from Delphi.
Excel spreadsheets are one of the world’s standards today and for programs, even simple ones, it is frequently required to output the data as tables and transfer reports to the spreadsheet. One thing to notice is that the XLS format is already outdated and it is an internal Microsoft Excel format called Biff8 (the entire world with its own interconnections between, at the first glance, unrelated entities, cells, endless tabs and pages with file sections in a hardcore binary format). However, there are companies, customers who have such a requirement. It would be nice to immediately knock their heads warn these customers that this format is NOT suitable for really big data – check this out:

"Excel has limits on the amount of data a cell can hold: for Excel BIFF 8 files, that limit is 32,767 characters, so (in theory) 200+ characters should not be an issue. However, for longer strings, this data is maintained in the BIFF file across several blocks with continuation records, For BIFF 5 files (Excel 95) the limit is 2084 bytes per block; in BIFF 8 files (Excel 97 and above) the limit is 8228 bytes. Records that are longer than these limits must be split up into CONTINUE blocks."

– number of columns, lines and data there is limited. It’s better to use something new and in another article I will tell you, how to save in XLSX XML format from Delphi (much better formats – though still Excel).

But if you want something weird and format limitations didn’t scare you, let’s keep frightening ourselves check some solutions:

1. XLS spreadsheet output straight from StringGrid by OLE / OLE-container – this method has a few unpleasant moments: you certainly need Microsoft Excel installed on your computer (we are not pirates - you must buy a license!); the bit architecture of your system, MS Office package installed and your compiled program should match (you can’t even imagine how many unforgettable hours of debugging can deliver, for example, a 32-bit version of Office on a 64-bit system!); your spreadsheet should be small (working with big data directly in memory, OLE will crash immediately taking down both Excel and your application as well) and even for this small program you should have enough time. The data transmitting process with OLE is meditative and doesn’t like a rush. You will spend many evenings googling for “delphi ole excel container save file”.
2. Okay, let’s imagine that you want to do everything in an adult way – and send large amounts of data to the XLS. Then all sorts of libraries help us to write directly to XLS – for example, TXLSFile. This method has a few disadvantages, too. For example, it’s not like it is impossible to place images, pictures and barcodes in cells or apply the cell borders – but it will take, let’s say, some efforts.
3. Or TMS FlexCel. You can do a report in TMS FlexCel with an image and zero code.
   Even if you want to do it in code, they have a tool that generates the code! *
4. So what shall we do? As usual on this blog, we have a solution – and this is FastReport VCL! First, easily using the maximum of pleasant visual interface create a document or report (feel free to name it as you wish – even a catalog for your dealers – and this is not a joke, people do all sort of things). Then export the final result as it is – to Excel! Also use the report preparation recommendations – make it a TABLE right away, “neatly on the ruler” – of course, FastReport will try to fit the overlapping objects into the table – but you can get up to 9 (!) cells from a pair of objects, you won’t like the result!

Creating the XLS file from Delphi using FastReport

So, your document contains large tables, multi-level lists, illustrations, maps, barcodes and you think how to transfer them to Excel?
I will not dwell on creating a report here again – add TfrxReport, TfrxPDFExport and TButton components to the form, write

1

frxReport1.ShowReport();

for the button, build a report and run a result preview window.

We can see a preview window and Save button.

Click on Excel 97/2000/XP file (you can find another way of implementing this using a code  below if you want to save if directly as biff8 and don’t want to or don’t need to show a preview window and give the opportunity to send it for printing). The export to XLS settings window will appear.
We remember that reports in FastReport are divided into pages, right? What should we do with it in Excel? And here is what!



FastReport tools help you to choose which pages of our document to send to Excel, certain pages or a range.

And, basically, what the result will look like: divide into pages, leave the original form, arrange everything on one page or divide into parts with a given number of lines.

You can specify where to save the Excel file (local storage, send it as E-mail or upload it to the cloud).

Open after export – the resulting file will be opened by Microsoft Excel immediately after export.

You can save it as a file with the .xls extension in the local storage, send it as E-mail or upload it to the cloud (Dropbox, OneDrive, Box.com, GoogleDrive).

Service information which will also go to the Excel file: title, author, keywords, document version, applications, category, manager and file comment.

Protection – password protection of the document (you can optionally set the confirmation).

If you set a non-empty password string, the generated file will be password protected. The password is always written in Unicode characters and must be shorter than 256 Unicode characters.

Options – setting up the document for greater visual correspondence with the original version (WYSIWYG), exporting images and pictures to the spreadsheet, displaying cell borders, adjusting page size, deleting empty rows (very important option for saving space), exporting formulas.

If you don’t need to set such detailed parameters you can just leave everything by default.

Send to Excel(Biff8) from Delphi / Lazarus code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

procedure TForm1.Button1Click(Sender: TObject);
begin
 {Generate a report. The report must be generated before exporting}
 frxReport1.PrepareReport();
 {Set the range of pages to export. By default, all pages of the generated report are exported}
 frxBIFFExport1.PageNumbers := '2-3';
 {Set the splitting order:
  with the SingleSheet option on, all pages of the document will be located on one sheet
  frxBIFFExport1.SingleSheet := True;
  If you set the value to ChunkSize, each sheet will have a specified number of rows.
  In this case SingleSheet should be set to False
  frxBIFFExport1.ChunkSize := 50;
  We will set the default order when each sheet of the generated report corresponds to a sheet of the book}
 
 frxBIFFExport1.SingleSheet := False;
 frxBIFFExport1.ChunkSize := 0;
 {Set whether to open the resulting file after export}
 frxBIFFExport1.OpenAfterExport := False;
 {Set whether to display export progress
  (show which page is currently being exported)}
 frxBIFFExport1.ShowProgress := False;
 {Set whether to display a dialog box with export filter settings}
 frxBIFFExport1.ShowDialog := False;
{Set the name of the resulting file.} 
{Please note that if you do not set the file name and disable the export filter dialog box,}
{he file name selection dialog will still be displayed}
 frxBIFFExport1.FileName := 'C:\Output\test.xls';
{Fill in the corresponding fields of the Information tab}
 frxBIFFExport1.Title := 'Your Title';
 frxBIFFExport1.Author := 'Your Name';
 frxBIFFExport1.Keywords := 'Your Keywords';
 frxBIFFExport1.Revision := 'Your Revision';
 frxBIFFExport1.AppName := 'Your Application';
 frxBIFFExport1.Subject := 'Your Subject';
 frxBIFFExport1.Category := 'Category Name';
 frxBIFFExport1.Company := 'Company Name';
 frxBIFFExport1.Manager := 'Manager Name';
 frxBIFFExport1.Comment := 'Your Comment';
 {Fill in the password field of the Protection tab}
 frxBIFFExport1.Password := 'User Password';
 {Set the document properties (Options tab)}
 {The WYSIWYG property is enabled with Inaccuracy <= 2, the default value is 10}
 frxBIFFExport1.Inaccuracy := 10;
 {Set whether to export pictures}
 frxBIFFExport1.Pictures := True;
 {Set whether to display gridlines}
 frxBIFFExport1.GridLines := True;
 {Set whether to scale pages to fit}
 frxBIFFExport1.FitPages := False;
 {Set whether do delete empty rows}
 frxBIFFExport1.DeleteEmptyRows := False;
 {Set whether to export formulas}
 frxBIFFExport1.ExportFormulas := True;
 {Export the report}
 frxReport1.Export(frxBIFFExport1);
end;

What are the aftereffects of using this option to create Excel spreadsheets from Delphi? First of all, it’s much faster and more reliable than writing XLS Biff8 with OLE-container (you can compare yourself); it has more opportunities (unless, of course, you don’t need to simply export StringGrid 100x100 to Excel which is guaranteed to be installed on a computer without ability to update); it is platform-independent (Linux applications made in Lazarus will easily generate XLS – and then you can open them in something like Open Office / Libre Office); formatting, text properties, colors, pictures, barcodes, maps, graphic primitives exported from the report to resulting Excel spreadsheet will be saved (but note that Libre Office didn’t show the images, in contrast to MS Excel). 

Our demo-document with pictures (fishes) in Biff8 XLS. Every picture is placed to own cell.

Report with maps in Excel XLS (biff8). Some cells were merged.

But there are limitations – caused by the chosen format itself! The number of rows and columns that you can export on one page of Excel spreadsheet is limited – here is a piece of the code:

1
2

BiffMaxRow = $fffe;
BiffMaxCol = $fe; 

Otherwise it would crash when opening in MS Excel and the file wouldn’t open. Let me remind you that this format is not developed anymore and it has long been outdated. Gladly, Microsoft moved on and (of course, a bit later than FastReports) realized the advantages of XML as a basis for storage format. In the next article we will tell you how to save Excel XML file from Delphi / Lazarus.

*Thanks a lot to my good friend Bruno Fierens from TMS software team for help and correction in this article!

Codeless fishfacts demo, generates the fish fact pictures in the Excel files
http://www.tmssoftware.biz/flexcel/doc/vcl/samples/delphi/reports/range-reports/index.html
 
To do it with code, you would use APIMate as mentioned here:
http://www.tmssoftware.biz/flexcel/doc/vcl/guides/getting-started.html#2-creating-a-more-complex-file-with-code