Как создать файл в Excel 97(2000, XP file) - XLS из Delphi / C++Builder / Lazarus?

О, это вечная тема - сделать таблицу Эксель из Delphi.
Таблицы Excel – сегодня один из мировых стандартов. А для программ, даже простых, частое требование – это вывод данных в виде таблиц и перенос отчёта в таблицу. Сразу отмечу, что XLS формат уже устаревший, это внутренний формат Microsoft Excel под названием Biff8 (целый мир со своими взаимосвязями между, с первого взгляда, никак не связанными сущностями, ячейками, бесконечными вкладками и страницами с участками файла, записанного в хардкорном бинарном формате), тем не менее - есть компании, заказчики, у которых именно такое требование. Таких заказчиков хорошо бы сразу предупредить, что для реальных больших данных этот формат НЕ ГОДИТСЯ - сами полюбуйтесь:

"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."

- там и число столбцов, и строк, и данных в них - ограничены. Лучше уж что-то новое использовать. В другой статье я расскажу, как сохранять из Delphi в XLSX XML (куда как более приятные форматы - хотя тоже Excel).

Но если уж захотелось странного, и недостатки формата вас не испугали, то пойдём пугаться дальше тут есть несколько возможностей:

1. Вывод таблицы XLS прямо сразу из StringGrid через вызовы OLE / OLE-container - у этого способа есть несколько неприятных моментов - вам всенепременно нужен установленный Microsoft Excel на компьютере (мы же не пираты какие - обязательно купить лицензию надо!), разрядность системы, установленного пакета MS Office и вашей скомпилированной программы должны совпадать (вы даже не представляете, сколько незабываемых часов отладки может сам доставить, например, 32-битный офис на 64-битной системе!), ваша табличка должна быть небольшой (на больших данных OLE, работая непосредственно в оперативной памяти, сразу упадёт  с громким треском, потянув за собою и Excel и вашу программку), и  даже для этой маленькой программки у вас должно быть достаточно времени. Процесс передачи серийных данных через OLE медитативен и не терпит суеты. Запрос “delphi ole excel container save file” скрасит вам не один вечер.
2. Окей, предположим, хочется делать всё по-взрослому - и отправлять в XLS большие объемы данных. Тогда приходят на помощь всевозможные библиотеки для записи сразу в XLS - например, TXLSFile. Есть и у этого подхода некоторые недостатки. Например - изображения, картинки, штрихкоды в ячейки поместить, да те же рамки-обрамления ячеек - не то чтобы невозможно, но будут результатом некоторых, скажем так, усилий.
3. Или TMS FlexCel. Вы можете сделать отчёт в TMS FlexCel с картинкаи и совсем без программирования. Если же вы хотите сделать это из кода, у них есть инструмент для его генерации! *
4. Так а что же делать? Выход, как обычно в этом блоге, есть - и это FastReport VCL! Во-первых, спокойно, используя максимум визуальных прелестей, делаете документ, отчёт (называйте, как хотите - хоть каталог для своих дилеров - и это не шутка, люди и не такое делают), потом экспортируйте готовый результат, как он есть - в Excel! Да, используйте рекомендации по подготовке отчёта - делайте его сразу ТАБЛИЧНЫМ, “аккуратненько, под линеечку” - FastReport, понятно, постарается наложенные друг на друга объекты вписать в таблицу - но так из пары объектов может получиться до 9(!) ячеек - вам же самим не понравится такой результат!

Записываем XLS из Delphi c помощью FastReport

Итак, ваш документ содержит большие таблицы, многоуровневые списки, иллюстрации, карты, штрих-коды и вы думаете, как бы это перенести в Excel?
Не буду тут повторно останавливаться на создании отчёта - бросили на форму проекта TfrxReport, TfrxBIFFExport и TButton, прописали на кнопку вызов

1

frxReport1.ShowReport();

- строим отчёт и запускаем окно предпросмотра того, что получилось.
Видим окно предварительного просмотра и кнопку “сохранить”

Кликаем  “Excel 97/2000/XP file...” (ниже есть вариант, как это реализовать с помощью кода, если не хочется или не требуется показывать предпросмотр и давать возможность отправить на печать, а сразу в biff 8). Появляется окошко настроек экспорта в XLS.
Мы же помним, что отчёты в FastReport всё-таки разбиваются на страницы, что с этим делать в Экселе? Да вот что!

Средства FastReport помогают выбрать, какие страницы нашего документа отправить в Excel, диапазон или только определённые страницы.

И, в принципе, как будет выглядеть результат: разбивать на страницы, оставив в изначальном виде, расположить всё на одной странице или же поделить на части с задаваемым количеством строк.

Можно указать, куда отправить Excel-файл (локально в память компьютера, на электронную почту или поместить в облако).

Открыть после экспорта – результирующий файл будет открыт сразу же после экспорта программой Microsoft Excel.

Можно сохранить в виде файла с расширением .xls в памяти компьютера, отправить в виде Email письма или загрузить в облачное хранилище (Dropbox, OneDrive, Box.com, GoogleDrive).

Служебная информация, которая также пойдёт в Excel-файл: название, автор, ключевые слова, версия документа, приложения, категория, менеджер и комментарий к файлу.

Безопасность — защита паролем документа (дополнительно можно указать подтверждение).

Если задать непустую строку пароля, то сгенерированный файл будет защищён паролем. Пароль пишется только символами Юникода и должен быть короче 256 символов.

Опции – настройка документа на большее визуальное соответствие с первоначальном вариантом (WYSIWYG), экспорт в таблицу  картинок-изображений, отображения границ ячеек, выставлять размер страницы, удаление пустых строк (для экономии места в этом конкретном формате очень важная опция), экспорт формул.

Если не нужно столь подробно выставлять параметры, то можно оставить всё по умолчанию.

Отправка из Delphi / Lazarus в Excel(Biff8) из кода

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
57

procedure TForm1.Button1Click(Sender: TObject);
begin
 {Формируем отчёт. Перед экспортом отчёт необходимо обязательно сформировать}
 frxReport1.PrepareReport();
 {Устанавливаем диапазон экспортируемых страниц. По умолчанию экспортируются все страницы
  сформированного отчёта}
 frxBIFFExport1.PageNumbers := '2-3';
 {Устанавливаем порядок разбивки документа:
  при установленной опции SingleSheet все страницы документа будут расположены на одном листе
  frxBIFFExport1.SingleSheet := True;
  Если установить значение ChunkSize, то на каждом листе будет заданное количество строк.
  SingleSheet в этом случае должно быть False
  frxBIFFExport1.ChunkSize := 50;
 
  Мы же установим порядок по умолчанию, когда каждому листу сформированного отчёта соответствует лист книги}
 frxBIFFExport1.SingleSheet := False;
 frxBIFFExport1.ChunkSize := 0;
 {Устанавливаем, нужно ли открывать результирующий файл после экспорта}
 frxBIFFExport1.OpenAfterExport := False;
 {Устанавливаем, нужно ли отображать прогресс экспорта
  (показывать, какая страница в данный момент экспортируется)}
 frxBIFFExport1.ShowProgress := False;
 {Устанавливаем, нужно ли отображать окно диалога с настройками фильтра экспорта}
 frxBIFFExport1.ShowDialog := False;
 {Устанавливаем имя результирующего файла.}
 {Обратите внимание на то, что если не установить имя файла и отключить показ диалогового окна фильтра экспорта,}
 {то всё равно будет отображён диалог выбора имени файла}
 frxBIFFExport1.FileName := 'C:\Output\test.xls';
 {Заполняем соответствующие поля вкладки Information}
 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';
 {Заполняем пароль на вкладке Protection}
 frxBIFFExport1.Password := 'User Password';
 {Устанавливаем свойства документа (вкладка Options)}
 {Свойство WYSIWYG включается при значении Inaccuracy <= 2, значение по умолчанию - 10}
 frxBIFFExport1.Inaccuracy := 10;
 {Устанавливаем, нужно ли экспортировать изображения}
 frxBIFFExport1.Pictures := True;
 {Устанавливаем, будет ли отображаться сетка в документе}
 frxBIFFExport1.GridLines := True;
 {Устанавливаем, нужно ли подгонять масштаб под размер страниц}
 frxBIFFExport1.FitPages := False;
 {Устанавливаем, нужно ли удалять пустые строки}
 frxBIFFExport1.DeleteEmptyRows := False;
 {Устанавливаем, нужно ли экспортировать формулы}
 frxBIFFExport1.ExportFormulas := True;
 {Экспортируем отчёт}
 frxReport1.Export(frxBIFFExport1);
end;

 Какие побочные эффекты у такого варианта создания Excel-листов из Delphi? Прежде всего, это на порядок быстрее и надёжнее, чем запись в XLS Biff8 через OLE-container (можете сами сравнить), да и возможности пошире (если, конечно, не нужно просто тупо выгнать StringGrid 100х100 в Excel, который гарантированно стоит на машинке без возможности обновлений), оно платформонезависимое (Linux-приложения, сделанные в Lazarus спокойно будут генерить XLS - и потом открыть в каком-нибудь Open Office / Libre Office), форматирование, свойства текста, цвета, картинки, штрих-коды, карты, графические примитивы из отчёта в результирующую Excel-таблицу будут сохранены (но учтите, Libre Office отказался показывать картинки, только MS Excel).

Вот так выгядит документ с иллюстрациями (рыбки) в Biff8 XLS. Каждая картинка в собственной ячейке.

Отчёт с картами после сохранения в формат Excel XLS (biff8). Некоторые ячейки были объединены.

Но  и ограничения есть - вызваны самим выбранным форматом! На количество выгоняемых на одном листе Excel строк и столбцов - вот вам прямо кусок из кода:

1
2

BiffMaxRow = $fffe;
BiffMaxCol = $fe; 

 Иначе бы оно при открытии сам MS Excel вываливался с ошибкой и не открывал бы таблицу. Формат, напомню, не развивается и уже давно морально устарел. Радует, что Microsoft не стоял на месте и (несколько позже, конечно, чем Fast Reports) понял таки преимущества XML в качестве базы для организации формата хранения. И о том, как сохранить из Delphi/Lazarus в Excel XML расскажем в следующей статье.

*Большое спасибо моему хорошему другу Bruno Fierens из TMS software team за помощь и корректировку в этой статье!

Вот так пример наших любимых "рыбок", без кодирования в Excel files
http://www.tmssoftware.biz/flexcel/doc/vcl/samples/delphi/reports/range-reports/index.html
 
Для использования из кода APIMate нужно проделать следующие манипуляции:
http://www.tmssoftware.biz/flexcel/doc/vcl/guides/getting-started.html#2-creating-a-more-complex-file-with-code