How to export a report to Excel XML from Delphi / Lazarus

A developer has a huge choice of options when the task is to export to Excel from Delphi or Lazarus – it was partially described in the article “How to export a report to XLS Biff8 from Delphi” and there I also briefly explained why it is a bad choice. And what is a good choice, then? Well, it’s better to save a file in a new (well, relatively new) XML XLSX format.

" XML is a technology designed to manage structured data and display that data as a human-readable text file. XML conforms to industry standards and can be processed by many databases and applications. Using XML, many developers can create their own customized tags, data structures, and schemas. In general, XML greatly facilitates the definition, transfer, validation, and interpretation of data in various databases, applications, and organizations. "

But Microsoft wouldn’t be itself if it didn’t try to traditionally make “its own XML – with blackjack and cells!” And therefore, Excel actually works with not one but two (!) XML standards for storing Excel spreadsheets: an older and a more correct one.

As I mentioned many times before, FastReport allows you to save your documents, reports and data from Delphi and Lazarus in a great variety of formats. Creating the report itself is a piece of cake, you can see it in previous articles – the only thing I want to point out is that you need to watch the alignment of your objects so that the resulting spreadsheets are attractive and qualitative. So, we have two different (!) export filters for Excel XML. Look at it!

We can see a preview window and Save button.
“Hey, but what can a simple developer like me use here?” – you ask and you will be absolutely right. The short answer is 2007 XML, a later version, and below I will tell you why.

It is clear that the programmer never does anything ‘just because he wants to’ – the user is the one who wants something. So what’s the difference between these two formats from the user’s point of view when he wants to get an Excel spreadsheet from the application? For the user this difference is approximately the same as between rtf without any pictures and a full-fledged MS Word document – the first one hardly ever occurs in real life. In a simple XML file there are no styles or pictures – just a table in XML. But there was not much choice at the moment when it was invented – it was a huge step forward for MS Excel. Microsoft Excel 2007 XML can store pictures, it has styles etc. It is an OOML container.
You can delve into the topic of XML used from 2007 onwards here - it’s the entire world!

And we will just compare the two options for saving in XML and XLSX XML and decide which one it’s better to use and in what cases.

Exporting to XML is simpler: only text objects are exported. Images, graphs, maps, barcodes, TfrxRichView formatting, HTML tags and background image will not get into the resulting Excel XML file.
As usual, below I will describe how to implement saving in Excel XML format using a code, if you 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 XML settings window will appear. I put these two windows next to each other to give you the opportunity to compare the settings. It’s not just easy – it’s a piece of cake!

FastReport tools help you to choose which pages of our document to send to Excel, certain pages or a range.
Export settings: better visual correspondence with the original version (WYSIWYG), page breaks and continuous document which skips headers and footers.

How the result will look like: don’t split, use report pages, use print on parent – every TrfxReportPage in the report template corresponds to a sheet of the book (provided that TfrxReportPage.PrintOnParent = False) or split by a special number of rows.
Open after export: the resulting file will be opened by Microsoft Excel immediately after export (or by any other software associated with XLSX files).

The resulting XML / XLSX can be saved as a file with the .xml extension in the local storage, sent to FTP, sent via E-mail or uploaded to one of the cloud storages (Dropbox, OneDrive, Box.com, GoogleDrive).

Let’s compare the resulting XLSX and Excel XML files 

The first screenshot is the result of saving in XLSX format and as we can see this format doesn’t support data more complex than the plain text. But the second screenshot shows us all the possibilities of the XML format, such as full support of images and barcodes. Speaking about the weight of files, the result is quite predictable: XLSX - 48,0kB, XML - 40,0kB.

Let’s take a closer look at why XLSX weighs more than XML. These beautiful fish were taken as the basis. This document contains a lot of text, tabular data and 30 photographs. For greater clarity, let’s also compare it with the non-aging Excel 97, which we talked about in another article.

Excel 97 – it is a binary format (biff8) and it doesn’t have any compression so the file is so large. Unlike the later XML format, Excel 97 supports all kinds of images.

Excel table (XML) – an early version of XLSX, where the data is stored as simple, single, monolithic XML files, which makes them quite large compared to OOXML and MS Office legacy binary formats.

‘But the size of the file is minimal in the screenshot’, - you could say. The answer is simple: this size is indicated for a document where only the text will be displayed. Now imagine if this format supported the images – the document would take up a lot of space because there is still no compression. In addition, inline elements, such as images, are stored as binary encoded blocks which are not available for display.

Excel 2007 XLSX (XML) – Microsoft took the best from past formats and implemented the file compression. The size of the document is approximately 50-75% smaller than in previous versions.

Limited features and possible limitations of the XLSX Excel 2007 format

The number of columns increased from 256 to 16 384, the number of rows in a sheet increased from 65 536 to 1,048 576. Calculations in large sheets with many formulas were accelerated thanks to the Excel 2007 support of several processors and multithread chipsets. 

And now how to save in XLSX and XML Excel directly from Delphi / Lazarus:

Export to XML

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

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}
 frxXMLExport1.PageNumbers := '2-3';
 {Set whether to generate a continuous document which skips headers and footers.
  Enabling this option disables TfrxXMLExport.ExportPageBreaks}
 frxXMLExport1.SuppressPageHeadersFooters := True;
 {Set whether to export the page breaks within the sheet of the book so that when printing the pages correspond to the pages of the generated report}
 frxXMLExport1.ExportPageBreaks := True;
 {Set WYSIWYG}
 frxXMLExport1.Wysiwyg := True;
 {Set the document splitting order using Split property which can take the following values:
  ssNotSplit – create a continuous document;
  ssRPages – each sheet of the generated report corresponds to the sheet of the book;
  ssPrintOnPrev – each sheet of TfrxReportPage in the report template corresponds to a sheet of the book (provided that TfrxReportPage.PrintOnParent = False);
  ssRowsCount - each sheet will have the number of rows specified in the TfrxXMLExport.RowsCount property.}
 frxXMLExport1.Split := ssNotSplit;
 {Set whether to open the resulting file after export}
 frxXMLExport1.OpenAfterExport := False;
 {Set whether to display export progress
  (show which page is currently being exported)}
 frxXMLExport1.ShowProgress := False;
 {Set whether to display a dialog box with export filter settings}
 frxXMLExport1.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,}
 {the file name selection dialog will still be displayed}
 frxXMLExport1.FileName := 'C:\Output\test.xls';
 {Export the report}
 frxReport1.Export(frxXMLExport1);
end;

 Export to XLSX

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

procedure TForm1.Button2Click(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}
 frxXLSXExport1.PageNumbers := '2-3';
 {Set whether to generate a continuous document which skips empty rows, headers and footers (with EmptyLines = False).
  With EmptyLines = True the report will be exported as it was generated. Without skipping the empty rows, headers and footers.
  Enabling this option disables TfrxXLSXExport.SuppressPageHeadersFooters and vice versa)}
 frxXLSXExport1.EmptyLines := True;
 {Set whether to export the page breaks within the sheet of the book so that when printing the pages correspond to the pages of the generated report}
 frxXLSXExport1.ExportPageBreaks := True;
 {Set WYSIWYG}
 frxXLSXExport1.Wysiwyg := True;
 {Set whether to export only the content of data bands}
 frxXLSXExport1.DataOnly := False;
 {Set the document splitting order:
  with the SingleSheet option on, all pages of the document will be located on one sheet
  frxXLSXExport1.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
  frxXLSXExport1.ChunkSize := 50;
  We will set the default order when each sheet of the generated report corresponds to a sheet of the book}
 frxXLSXExport1.SingleSheet := False;
 frxXLSXExport1.ChunkSize := 0;
 {Set whether to open the resulting file after export}
 frxXLSXExport1.OpenAfterExport := False;
 {Set whether to display export progress
  (show which page is currently being exported)}
 frxXLSXExport1.ShowProgress := False;
 {Set whether to display a dialog box with export filter settings}
 frxXLSXExport1.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,}
 {the file name selection dialog will still be displayed}
 frxXLSXExport1.FileName := 'C:\Output\test.xlsx';
 {Export the report}
 frxReport1.Export(frxXLSXExport1);
end;

 Now we know how to save in XLSX and XML Excel 2007 (and newer) from Delphi and Lazarus and which format it is better to choose!