Delphi создать файл excel
Когда задача стоит “Сохранить из Delphi или Lazarus в Excel” у разработчика просто огромный выбор - частично он описан в статье “Как записать в XLS Biff8 из Delphi”, там же я вкратце расписал, почему это плохой выбор. А что же тогда хорошо? А хорошо - сохранять в новый (ну, относительно новый) XML XLSX.
"XML является технологией, разработанной для управления структурированными данными и отображения этих данных в виде удобочитаемого текстового файла. Язык XML соответствует отраслевым стандартам и может быть обработан многими базами данных и приложениями. С помощью XML многие разработчики могут создавать собственные настроенные теги, структуры данных и схемы. В целом XML существенно облегчает определение, передачу, проверку и интерпретацию данных в различных базах данных, приложениях и организациях. "
Но Microsoft не был бы собой, если бы, традиционно не попытался сделать “свой собственный XML - c блэкджеком и ячейками!”. А потому у Excel на самом деле есть не один а целых два(!) XML-стандарта для хранения Excel-файлов: более ранний и более правильный.
Как я уже неоднократно указывал, FastReport позволяет сохранять из Delphi и Lazarus ваши документы, отчёты информацию в великое множество форматов. Сделать сам отчёт - легче лёгкого, можно посмотреть в прошлых статьях - единственное, опять остановлюсь-отмечу - следите за выравниванием ваших объектов, чтобы результирующие табличные форматы получались красивыми качественными! Так вот - для Excel XML у нас два разных(!) фильтра экспорта. Вот, полюбуйтесь!
Видим окно предварительного просмотра и кнопку “сохранить”
“Эй, уважаемый, а мне, простому программисту чем пользоваться?!” - спросите вы и будете совершенно правы. Короткий ответ - 2007 XML, более поздний. А ниже я расскажу, почему.
Понятно, что программист никогда не делает “потому, что захотелось” - хочется чего-либо пользователю. В чём же разница в этих двух форматах с точки зрения пользователя, жаждущего получить таблицу Excel из приложения? Со стороны пользователя разница примерно как между rft без картинок и полноценным файлом MS Word, и первый в реальной жизни почти не встречается. В простом XML нет, ни стилей ни картинок. Просто таблица в XML. Но в момент, когда его придумывали, и выбора-то особо не было - это был огромный шаг вперёд для MS Excel. А вот Microsoft Excel 2007 XML может хранить картинки , там есть стили и тд. Это контейнер OOML.
Углубиться в тему XML используемого с 2007 и далее можно тут - там целый мир!
А мы просто сравним два варианта сохранения в XML и XLSX XML и решим, какой использовать рациональнее и в каких случаях.
Экспорт в XML - более простой. Экспортируются только текстовые объекты. Изображения, графики, карты, штрихкоды, форматирование TfrxRichView, HTML-тэги и фоновое изображение в результирующий Excel XML не попадут.
Настройки экспорта – большее визуальное соответствие с начальным вариантом (WYSIWYG), разрыв страницы, и continuous - непрерывный документ с пропуском промежуточных заголовков и подвалов страниц.
Как будет выглядеть результат: без разделения, разделение на листы с использованием страниц отчёта, Use print on parent - каждой странице TfrxReportPage в шаблоне отчёта соответствует лист книги (при условии, что TfrxReportPage.PrintOnParent = False) или же поделить на части с задаваемым количеством строк.
Открыть после экспорта – результирующий файл будет открыт сразу же после экспорта программой Microsoft Excel (Ну или что у пользователя в системе ассоциировано с файлами расширения XLSX для их открытия).
Сравним получившиеся XLSX и Excel XML
Первый скриншот - это результат сохранения в формате XLSX и как мы видим, данный формат не поддерживает данные сложнее обычного текста. А вот второй скриншот показывает нам все возможности формата XML, т.е. полную поддержку штрихкодов, картинок. Если говорить про вес файлов, то результат ожидаемый: XLSX 48,0 КБ, XML 40,0 КБ.
Разберёмся подробнее почему XLSX весит больше XML. За основу взяли вот этих прекрасных рыбок. Данный документ содержит много текста, табличные данные и 30 фотографий. Для большей наглядности сравним ещё и нестареющий Excel 97, о котором мы говорили в другой статье.
Excel 97 - Формат двоичных файлов, бинарник (biff8), про сжатие тут не слышали и потому вес файла столь велик. В отличии от более позднего XML, формат 97 года поддерживает все виды изображений.
Excel table (XML) - Ранняя версия XLSX, где данные хранятся как простые одиночные монолитные XML-файлы, что делает их довольно большими, по сравнению с OOXML и устаревшими двоичными форматами Microsoft Office. Но на скриншоте вес минимален - возмутился бы каждый! Ответ прост, данный вес указан за документ, где отображаться будет только текст. А теперь представьте, если бы тут всё-таки была поддержка картинок, да он бы занимал кучу места, ведь о сжатии на этом моменте всё ещё никто не знает. Кроме того, встроенные элементы, такие как изображения, хранятся в виде двоичных закодированных блоков, но недоступны для отображения.
Excel 2007 XLSX (XML) - Microsoft взяли лучшее от прошлых форматов и внедрили сжатие файлов. Размер документа приблизительно на 50-75 процентов меньше, чем в предыдущих версиях.
Ограниченные возможности и возможные ограничения формата XLSX Excel 2007
Количество столбцов увеличилось с 256 до 16 384, количество строк в листе возросло с 65 536 до 1 048 576. Ускорены вычисления в больших листах, содержащих множество формул, благодаря поддержке Office Excel 2007 нескольких процессоров и многопоточных наборов микросхем.
В прошлые раз мы с Вами остановились на сохранении рабочей книги. В принципе, ничего сложного в запуске Excel, создании и сохранении рабочей книги нет. Сегодня заглянем немного глубже в работу с Microsoft Excel в Delphi — рассмотрим как можно работать с листами рабочей книги, изменять внешний вид ячеек и попробуем скопировать таблицу из своего приложения в открытую рабочую книгу.
План статьи:
Однако прежде, чем забираться в дебри Excel я хотел продемонстрировать Вам насколько глубоко мы сегодня заглянем.
1. Объекты Microsoft Excel
На рисунке я попробовал изобразить объектную модель Excel в миниатюре. Т.е. из этой модели исключены не требующиеся пока нам элементы, например такие, как свойства рабочей книги или выделенные объекты Selection — об этом речь пойдет позже.
Как Вы можете судить по представленной модели, а также и по собственному опыту работы, приложение Excel в общем случае может содержать коллекцию рабочих книг (WorkBooks Collection). Для доступа к конкретной рабочей книге мы с Вами использовали свойства Item коллекции:
где index — порядковый номер рабочей книги в коллекции WorkBooks.
И на этом наше первое знакомство с Excel закончилось. Между тем, каждая отдельная рабочая книга может содержать N листов, каждый из которых состоит из N ячеек, которые могут быть объединены в объекты Range.
На каждой из ступеней этой модели можно останавливаться и изучать её месяцами. Если будет особая необходимость в изучении каких-либо дополнительных свойств и методов — мы обязательно вернемся и изучим. А теперь, приступим к работе в Delphi с листами рабочей книги.
2. Как активировать лист рабочей книги Excel?
Как Вы знаете при создании пустой рабочей книги Excel автоматически добавляет в эту книгу 3 пустых листа. Чтобы работать с конкретным листом (WorkSheet или просто Sheet) этот лист необходимо активировать. Один из способов активации листа рабочей книги Excel в Delphi выглядит следующим образом:
Здесь в качестве параметров функции задается индекс рабочей книги (WBIndex) в коллекции WorkBooks и название листа.
Если хотите, то можете активировать тот же лист по индексу в коллекции WorkSheets — при этом сама функция немного упрощается (не требуется условие проверки названия листа).
Также, если вы уверены, что в данный момент времени активна необходимая Вам рабочая книга, то можно избежать использования лишней переменной (индекса рабочей книги WBIndex) и активировать лист вот так:
Теперь рассмотрим более подробно методы, используемые объектом WorkSheet.
3. Методы листа книги Excel
Теперь рассмотрим более подробно объект WorkSheet.
На рисунке представлены те методы, которые я когда-либо использовал в своей работе.
Всего объект WorkSheet насчитывает 30 различных методов, которые Вы можете использовать.
Метод | Описание |
Activate | Делает текущий лист активным. Работу метода мы уже с Вами рассмотрели |
Calculate | рассчитывает все открытые книги, конкретный лист в книге, или указанный диапазон ячеек на листе |
CheckSpelling | проверка орфографии на выбранном листе |
SaveAs | сохраняет все изменения в файле |
Delete | удаляет текущий лист WorkSheet |
Select | выделение листа |
Copy | копирует лист в другое место в рабочей книге |
Paste | вставляет содержимое буфера обмена на лист |
ChartObjects | возвращает объект, который представляет собой либо одну диаграмму (объект ChartObject) или совокупность всех диаграмм (объект ChartObjects) на листе |
Move | перемещение листа. Этот метод аналогичен методу Copy . Различие лишь в том, что после вcтавки копируемый лист удаляется из книги |
Рассмотрим теперь каждый из представленных выше методов.
Метод Calculate
Расчёт рабочей книги, листа или диапазона ячеек.
Стоит отметить, что вызов метода Calculate возможен не только для конкретного рабочего листа книги Excel. Рассмотрим варианты вызова метода Calculate.
Как рассчитать все открытые в данный момент книги Excel в Delphi?
Как рассчитать отдельный лист книги Excel в Delphi?
в приведенном ниже фрагменте кода рассчитывается активный лист
Как рассчитать диапазон ячеек листа Excel в Delphi?
Рассчитаем все формулы в столбцах A, B и C:
Метод CheckSpelling
Вызов метода выглядит следующим образом:
При этом в качестве expression должен выступать лист (WorkSheet).
Параметр | Тип | Описание |
CustomDictionary | Variant | строка, указывающая имя файла пользовательского словаря, который будет рассмотрен, если слово не найдено в основном словаре. Если этот аргумент опущен, то будет использоваться словарь по-умолчанию |
IgnoreUppercase | Variant | True, если необходимо, чтобы Microsoft Excel пропускал слова, которые написаны прописными буквами. False для того, чтобы Microsoft Excel проверить все слова. Если этот аргумент опущен, то используются текущие настройки Excel. |
AlwaysSuggest | Variant | True, для того, чтобы Microsoft Excel отображал список предложений других вариантов написания при обнаружении неправильного написанного слова. False, чтобы Microsoft Excel ожидал ввода правильного написание. Если этот аргумент опущен, то используются текущие настройки. |
SpellLang | Variant | язык словаря. Может принимать значения одной из констант MsoLanguageID, например для русского языка SpellLang = 1049, для английского (США) SpellLang = 1033 и т.д. Более подробно про идентификаторы языков см. эту статью. |
Как проверить грамматику на листе Excel в Delphi?
в этом случае MS Excel проверит текущий лист, включая проверку слов из прописных букв. Язык проверки — русский. Обратите внимание, что для того, чтобы вызвать метод мы явно указывали, какому параметру какое значение присвоить. Причём для того, чтобы пропустить какой-либо параметр, мы использовали EmptyParam (пустой параметр), который в Delphi представляет собой переменную типа OleVariant.
Понятно, что подобный способ вызова методов (с явным указанием всех параметров) для Вас не совсем удобен и широко не практикуется в программировании на Delphi, но тем не менее только так и никак иначе можно вызывать методы, используемые в Excel.
Метод SaveAs
Сохранение рабочего листа книги Excel.
Вызов метода:
Как сохранить активный лист рабочей книги Excel в Delphi?
В этом случае при сохранении будут использованы настройки по умолчанию.
Если Вам необходимо сохранить файл Excel в другом формате, то в параметре FileFormat можно использовать одно из значений перечислителя xlFileFormat. Некоторые значения xlFileFormat представлены в таблице ниже:
Имя | Значение | Описание | Расширение файла |
xlCSV | 6 | CSV | *.csv |
xlExcel8 | 56 | Книга Excel 97-2003 | *.xls |
xlHtml | 44 | Формат HTML | *.htm; *.HTML |
xlOpenDocumentSpreadsheet | 60 | Электронная таблица OpenDocument | *.ods |
xlOpenXMLWorkbook | 51 | Книга Open XML | *.xlsx |
Как сохранить активный лист рабочей книги Excel в заданном формате?
Напишем небольшую процедуру, позволяющую сохранять лист Excel в различных форматах:
На этой странице представлена подборка статей, посвященные работе с Excel в Delphi. Все статьи расположены по порядку изложения материала в блоге, то есть от самых основ работы с Excel в Delphi до выполнения сложных операций, вставки диаграмм в книгу и так далее.
Работа с Excel в Delphi. Основы основ
Это вводная статья по работе с excel в delphi, которая поможет вам разобраться с основами по взаимодействия вашего приложения Delphi и Microsoft Excel. В статье рассмотрены следующие вопросы:
Excel в Delphi. Методы объекта WorkSheet (лист)
В статье рассмотрены вопросы о том, как можно работать с листами рабочей книги, изменять внешний вид ячеек, копировать таблицу из своего приложения Delphi в открытую рабочую книгу:
Диаграммы Excel в Delphi. Общие сведения
В статье рассмотрен способ добавления диаграммы в лист Excel через объект ChartObjects. План статьи следующий:
Excel в Delphi. Работа с объектом Range (диапазон)
Статья дает более полное представление о том, как можно работать с данными листа Excel в Delphi, используя объект Range (диапазон). Рассмотрены свойства и методы объекта range. План статьи:
Excel в Delphi. Как изменить внешний вид ячеек?
В статье рассматриваются вопросы оформления ячеек таблицы Excel в Delphi:
Полная автоматизация. Редактируем объекты Excel, содержащиеся в документах Word и наоборот
В статье рассматривается достаточно нетривиальный вопрос: как добраться до диаграммы Excel, внедренной в документ Word и работать с ней в Delphi? Статья состоит из трех частей:
Быстрая обработка данных Excel в Delphi
Эта статья раскрывает вопросы, касающиеся быстрой обработки данных при работе с Excel в Delphi. Одной из проблем, с которой сталкиваются начинающие разработчики, является то, что рассмотренные в предыдущих статьях методы чтения/записи данных в Excel работают очень медленно при большом количестве данных. Статья позволяет понять то, как сократить время операций при работе с Excel в Delphi в несколько раз, используя вариантные массивы и библиотеку XLSReadWrite.
Пост-ответ. Работа с примечаниями в Excel
В статье рассматривается то, как, используя Delphi вставлять и редактировать примечания в ячейках Excel. План статьи:
Excel в Delphi. Работа со свойствами документа
Статья раскрывает вопросы изменения свойств документа Excel в Delphi и состоит из трех частей:
Создаем свои контролы на ленте Microsoft Office
Небольшая заметка о том, как получить доступ у ленте Excel и добавить на неё свою кнопку.
Эти и другие статьи, посвященные работе с Excel в Delphi вы всегда сможете найти в блоге по тегу Excel в Delphi
Книжная полка
Описание Описаны общие подходы к программированию приложений MS Office. Даны программные методы реализации функций MS Excel, MS Word, MS Access и MS Outlook в среде Delphi.
Видимо любители экономить килобайты оперативной памяти могут меня закидать помидорами или ещё чем по-хуже, но все-таки я скажу, что интеграция приложений (мегабайты оперативы) — это большой плюс нынешней разработки приложений.
Как ни крути, а время DOS и килобайтов оперативной памяти прошло. Вряд ли кто-то всерьез сейчас задумывается над тем куда это с винчестера пропал мегабайт? Да и использование в своих приложениях функциональности программ, которых ты не писал, но которые выполняют что-то лучше — это всё-таки больший прогресс, нежели корпеть год-два над программой, а потом узнать, что время-то прошло.
Введение
Итак, цель сегодняшней статьи — поделиться с Вами опытом работы с Microsoft Excel в приложениях, написанных на Delphi.
Вспомнился сейчас один случай. Когда я только начинал работать по своей специальности, пригласили меня написать программу-расчётник для экологов нашего нефтезавода. В принципе ничего серьёзного — программа считает выброс от нагревательной печи и выдает табличку результатов, которую необходимо распечатать и уложить в толстую папку с отчётами. Естественно, что в области разработки подобных приложения я далеко не пионер, поэтому дали взглянуть на аналог будущей программы, который работал ещё под DOS и печатались отчёты на дико скрипящем матричном принтере с 12-ю иголками. Ну посмотрел, элементарная таблица, расчёт немного запутан, но жить можно — начал по-тихоньку писать. И попалась мне тогда на глаза статейка про работу с Excel в Delphi. Вот я и решил попробовать выдавать отчёт не только на форму приложения, а ещё и скидывать весь ход расчёта с формулами и прочим делом в Excel…Надо сказать более сильно детской радости начальника отдела я не видел до сих пор :). Люди, всю жизнь проработавшие в DOS увидели как тот же самый расчёт может выглядеть в современных условиях. Вот теперь при определении технических заданий на каждую новую программу, обязательно присутствует пункт, гласящий, что программа должна передавать данные либо в MS Word либо в MS Excel.Соответственно и цена на разработку возрастает, иногда значительно.
Отсюда можно сделать простой и однозначный вывод — заказчики готовы пожертвовать лишними деньгами только для того, чтобы всё в программе было красиво. Excel может дать вашему приложению ту самую красоту и удобство.
Ну, а для того, чтобы каждый раз не утруждать себя выполнением однотипных операций, я разработал небольшой модуль для работы с Excel. Этот же модуль я в настоящее время дорабатываю под ещё одну задачу, но об этом после. Сегодня основы основ работы с Excel в Delphi.
1. Как проверить установлен ли Excel на компьютере пользователя?
Создаем новый модуль (unit) и подключаем в uses следующие модули:
О, это вечная тема - сделать таблицу Эксель из 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).
Но если уж захотелось странного, и недостатки формата вас не испугали, то пойдём пугаться дальше тут есть несколько возможностей:
- Вывод таблицы XLS прямо сразу из StringGrid через вызовы OLE / OLE-container - у этого способа есть несколько неприятных моментов - вам всенепременно нужен установленный Microsoft Excel на компьютере (мы же не пираты какие - обязательно купить лицензию надо!), разрядность системы, установленного пакета MS Office и вашей скомпилированной программы должны совпадать (вы даже не представляете, сколько незабываемых часов отладки может сам доставить, например, 32-битный офис на 64-битной системе!), ваша табличка должна быть небольшой (на больших данных OLE, работая непосредственно в оперативной памяти, сразу упадёт с громким треском, потянув за собою и Excel и вашу программку), и даже для этой маленькой программки у вас должно быть достаточно времени. Процесс передачи серийных данных через OLE медитативен и не терпит суеты. Запрос “delphi ole excel container save file” скрасит вам не один вечер.
- Окей, предположим, хочется делать всё по-взрослому - и отправлять в XLS большие объемы данных. Тогда приходят на помощь всевозможные библиотеки для записи сразу в XLS - например, TXLSFile. Есть и у этого подхода некоторые недостатки. Например - изображения, картинки, штрихкоды в ячейки поместить, да те же рамки-обрамления ячеек - не то чтобы невозможно, но будут результатом некоторых, скажем так, усилий.
- Или TMS FlexCel. Вы можете сделать отчёт в TMS FlexCel с картинкаи и совсем без программирования. Если же вы хотите сделать это из кода, у них есть инструмент для его генерации! *
- Так а что же делать? Выход, как обычно в этом блоге, есть - и это FastReport VCL! Во-первых, спокойно, используя максимум визуальных прелестей, делаете документ, отчёт (называйте, как хотите - хоть каталог для своих дилеров - и это не шутка, люди и не такое делают), потом экспортируйте готовый результат, как он есть - в Excel! Да, используйте рекомендации по подготовке отчёта - делайте его сразу ТАБЛИЧНЫМ, “аккуратненько, под линеечку” - FastReport, понятно, постарается наложенные друг на друга объекты вписать в таблицу - но так из пары объектов может получиться до 9(!) ячеек - вам же самим не понравится такой результат!
Записываем XLS из Delphi c помощью FastReport
Итак, ваш документ содержит большие таблицы, многоуровневые списки, иллюстрации, карты, штрих-коды и вы думаете, как бы это перенести в Excel?
Не буду тут повторно останавливаться на создании отчёта - бросили на форму проекта TfrxReport, TfrxBIFFExport и TButton, прописали на кнопку вызов
- строим отчёт и запускаем окно предпросмотра того, что получилось.
Видим окно предварительного просмотра и кнопку “сохранить”
И, в принципе, как будет выглядеть результат: разбивать на страницы, оставив в изначальном виде, расположить всё на одной странице или же поделить на части с задаваемым количеством строк.
Открыть после экспорта – результирующий файл будет открыт сразу же после экспорта программой Microsoft Excel.
Служебная информация, которая также пойдёт в Excel-файл: название, автор, ключевые слова, версия документа, приложения, категория, менеджер и комментарий к файлу.
Безопасность — защита паролем документа (дополнительно можно указать подтверждение).
Если задать непустую строку пароля, то сгенерированный файл будет защищён паролем. Пароль пишется только символами Юникода и должен быть короче 256 символов.
Опции – настройка документа на большее визуальное соответствие с первоначальном вариантом (WYSIWYG), экспорт в таблицу картинок-изображений, отображения границ ячеек, выставлять размер страницы, удаление пустых строк (для экономии места в этом конкретном формате очень важная опция), экспорт формул.
Если не нужно столь подробно выставлять параметры, то можно оставить всё по умолчанию.
Отправка из Delphi / Lazarus в Excel(Biff8) из кода
Какие побочные эффекты у такого варианта создания 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 строк и столбцов - вот вам прямо кусок из кода:
Иначе бы оно при открытии сам MS Excel вываливался с ошибкой и не открывал бы таблицу. Формат, напомню, не развивается и уже давно морально устарел. Радует, что Microsoft не стоял на месте и (несколько позже, конечно, чем Fast Reports) понял таки преимущества XML в качестве базы для организации формата хранения. И о том, как сохранить из Delphi/Lazarus в Excel XML расскажем в следующей статье.
Читайте также: