Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Экспорт в excel

Экспорт документов в Excel

Эта статья первая из цикла статей посвященных экспорту документов в MS Excel. В ней мы рассмотрим подключение к Excel, заполнению ячеек и простейшее оформление документа.

Я не буду углубляться в теорию, рассказывать о том как работает OLE механизм, начнем с самого главного.

Подключение.

Для подключения к Excel и работы с ним нам понадобится переменная типа Variant:

Далее создаем OLE объект:

Добавляем новую книгу:

Так же нам понадобятся константы:

Текст ячеек.

Теперь до любой ячейки мы можем добраться следующим образом:

Объект Range, выделение диапазона, объединение ячеек, выравнивание.

Представьте такую ситуацию: необходимо объединить несколько ячеек и выровнять текст в них по центру.

Границы и перенос по словам.

Для начала выделяем нужный диапазон а затем.

И включаем перенос по словам:

Пример.

Пример можно скачать здесь

Параметры страницы.

Начнем с полей страницы. Во первых для того чтобы добраться до параметров страницы у листа Excel имеется свойство объект PageSetup его мы и будем использовать. Для установки размеров полей необходимо изменить соответствующие свойства PageSetup, вот эти свойства:

  • LeftMargin — Левое поле
  • RightMargin — Правое поле
  • TopMargin — Верхнее поле
  • BottomMargin — Нижнее поле

Значение размеров полей необходимо указывать в пикселях, к чему мы не очень привыкли, поэтому воспользуемся функцией InchesToPoints объекта Application, которая переводит значение в дюймах в значение в пикселях. Теперь напишем процедуру которая подключит Excel и установит поля равные 0.44 дюйма (приблизительно 1 см):

Иногда полезно уметь установить и ориентацию страницы:

Здесь значение ориентации = 2, означает альбомную, при книжной ориентации присвойте Orientation значение 1.

Вы наверное не раз встречали такой отчет в котором таблица с большим количеством строк размещается на нескольких страницах в таких случаях очень удобны сквозные строки, они печатаются на каждой странице отчета:

Здесь мы указываем вторую и третью строки для печати на каждой странице.

Шрифты и цвета.

Для установки шрифта и размера текста выделите нужный диапазон и установите свойство Name объекта-свойства Font объекта Selection или свойство Size для изменения размера:

Если Вы хотите установить жирный или, например, наклонный стиль написания текста установите соответствующие свойства:

Для указания цвета текста измените свойство ColorIndex все того же объекта Font:

Вот несколько индексов цветов:

Для изменения цвета фона ячейки используйте объект Interior свойства Selection:

Колонтитулы.

Для добавления колонтитула к документу достаточно указать его содержание:

Для изменения размера шрифта добавьте к колонтитулу управляющий символ «&» и размер шрифта:

Экспорт данных в Excel

С помощью мастера экспорта можно экспортировать данные из базы данных Access в файл формата, распознаваемого приложением Excel. В этой статье описаны процедуры подготовки и экспорта данных в Excel, а также приведены рекомендации по поиску и устранению типичных неполадок, которые могут при этом возникнуть.

В этой статье

Экспорт данных в Excel: основы

При экспорте данных в Excel приложение Access создает копию выбранных данных, а затем сохраняет их в файле, который можно открыть в Excel. Если данные требуется копировать из Access в Excel достаточно часто, можно сохранить параметры операции экспорта для повторного использования и даже запланировать ее автоматический запуск через определенные интервалы времени.

Стандартные сценарии экспорта данных в Excel

В отделе или рабочей группе для работы с данными используется как Access, так и Excel. Данные хранятся в базах данных Access, но для анализа и распространения его результатов используется Excel. Группа выполняет экспорт данных в Excel по мере необходимости, но этот процесс требуется сделать более эффективным.

Вы используете Access, но ваш руководитель предпочитает работать с данными в Excel. Вам приходится регулярно копировать данные в Excel, но вам хотелось бы автоматизировать этот процесс ради экономии времени.

Об экспорте данных в Excel

В приложении Access нет команды «Сохранить как” с возможностью сохранения данных в формате Excel. Чтобы скопировать данные в Excel, необходимо воспользоваться функций экспорта, описанной в этой статье, либо скопировать данные Access в буфер обмена, а затем вставить их в таблицу Excel.

Можно экспортировать таблицу, запрос, форму или отчет, а также отдельные записи в представлении.

В приложении Microsoft Excel есть команда импорта данных из базы данных Access. Ее можно использовать вместо команды экспорта Access, однако с помощью команды импорта в Excel можно импортировать только таблицы и запросы. Дополнительные сведения см. в разделе справки Excel Подключение к внешним данных и их импорт.

Экспортировать макросы и модули нельзя. При экспорте формы, отчета или таблицы с подчиненными формами, отчетами или таблицами экспортируется только главная форма, отчет или таблица. Все подчиненные формы, отчеты и таблицы следует экспортировать в Excel отдельно.

В ходе операции экспорта можно экспортировать лишь один объект базы данных. Однако после нескольких операций экспорта можно выполнить в Excel слияние нескольких листов.

Подготовка к экспорту

Прежде чем выполнять операцию экспорта, следует проверить данные, которые будут экспортироваться, на предмет сообщений об ошибках и ошибочных значений. Ошибки рекомендуется устранить до экспорта данных в Excel. В противном случае в ходе экспорта могут возникнуть неполадки, а в ячейки листа Excel могут быть вставлены пустые значения. Дополнительные сведения о проблемах, которые могут возникнуть при экспорте данных в Excel, см. в разделе Решение проблем, связанных с отсутствующими и неверными значениями.

Читать еще:  Макрос для сохранения файла excel

Если исходным объектом является таблица или запрос, определите, как требуется экспортировать данные: с сохранением форматирования или без него. Выбранный способ экспорта влияет на два аспекта целевого файла: объем экспортируемых данных и формат их отображения. В приведенной ниже таблице представлены результаты экспорта форматированных и неформатированных данных.

Экспорт в excel

Наверняка многие думают, что Excel формата XLSX очень сложен для чтения и нужно обязательно использовать сложные скрипты и библиотеки. Так было со старым форматом XLS. Для нового экселя – все куда проще. XLSX – это зип архив, внутри которого в виде XML расположены данные по ячейкам и все остальные – в стандартной структуре с малыми хитростями.
Знания этого можно применять для настройки импорта данных из экселя. Любых данных. В дальнейшем мы разберем, как можно самостоятельно создавать эксель файл, не прибегая к супер библиотекам.
Нам понадобятся PHP 5, встроенный модуль ZIP и SimpleXML. Как вы думаете, сколько строк кода нам понадобится?

Для примера — расположим файл в папке со скриптом. Назовем его file.xlsx. Из данного файла нам понадобятся файл с данными по первому листу. Он расположен по адресу
file.xlsx/xl/worksheets/sheet1.xml
И файл с частыми строками, сделанный из соображений экономии повторяющихся фраз
file.xlsx/xl/sharedStrings.xml
Нужно считать все строки и вставлять их значения при нахождении ссылки на строку
Сказано – сделано. Создаем ассоциативный массив

Первой строкой – мы считываем полный текст распакованного файла внутри архива
Далее – собираем XML структуру в ассоциативный массив
После чего – пробегаемся по элементам массива, записывая значения в итоговый массив $sst. ICONV используется только в случае, если у вас кодировка – не UTF-8
Что дальше? Дальше чуть сложнее. Двумерный массив с возможными ассоциациями

Собираем первый лист. Формируем XML, после чего проходимся по каждой строке row и разбираем каждю ячейку. Для получения атрибутов ячейки – используем свойство attributes(). Если атрибут ‘t’==’s’ – значит значение ячейки – это строка в ассоциативном массиве, полученном ранее.
После сбора строки в массив – соединяем в наш общий массив $data. На самом деле – вместо этого можно было бы сразу добавлять в таблицу, или использовать дополнительные проверки.
Все.

DataExpress

Дело сделано. Первый лист прочитан и собран в массив. Сколько строк получилось? 20. Можно было бы сжать сильнее, оптимизировать. Если интересна структура файла – берите архиватор ZIP и смело пытайте XLSX. У вас обязательно получится

  • Автор: kosmom
  • Рейтинг: 0
  • Просмотров: 3879
  • Комментариев: 5
  • Создан: 19.01.2012 14:17

Комментарии ()

В режиме работы окна семантической информации Ответ или База предусмотрен экспорт соответствующего набор записей в лист Microsoft Excel для последующего анализа. Для экспорта данных в Microsoft Excel:

Предусмотрено создание шаблонов для экспорта данных в Excel как с использованием встроенного механизма системы ZuluGIS, так и с помощью файлов шаблонов программы MS Excel, формата .xlt. Последний вариант предполагает вывод отчетов в виде таблиц с произвольным оформлением с использованием основных средств оформления программы Excel.

Для создания нового шаблона нажмите кнопку Новый в мастере экспорта в Excel (Рисунок 444, «Окно мастера экспорта данных в Microsoft Excel»), в результате откроется диалог параметров вывода отчета.

Рисунок 445. Диалог параметров вывода отчета

Для создания шаблона на базе механизма отчетов ZuluGIS установите переключатель Использовать собственные настройки. Создание подобных отчетов освещено выше, в разделе «Просмотр и печать отчетов».

Для создания шаблона на базе файла шаблона MS Excel:

Установите переключатель Использовать настройки шаблона Excel (подробно см. «Файл шаблона в формате .xlt»).

В поле Файл шаблона укажите путь к файлу шаблона. Для этого, нажмите кнопку справа от поля и укажите в открывшемся диалоге выбора файлов путь к файлу шаблона в формате .xlt.

С помощью переключателя Тип отчета укажите тип отчета формируемого шаблоном, Табличный, либо Свободной формы (подробно см. «Файл шаблона в формате .xlt»), при выборе значения Свободной формы в поле Диапазон области данных также выбирается диапазон шаблона из которого формируется запись отчета.

В таблице Соответствие полей и диапазонов шаблона указывается в каких именованных диапазонах шаблона отображаются поля базы данных. В столбце Поля перечислены поля базы данных, а в столбце Диапазон указываются соответствующие им диапазоны шаблона. По умолчанию, для полей подставляются диапазоны с соответствующими названиями (знаки подчеркивания в названии диапазона рассматриваются как пробелы).

По завершению настройки шаблона нажмите кнопку ОК, чтобы сохранить изменения и закрыть диалог.

Существующий шаблон можно отредактировать, для этого в открывающемся списке в мастере экспорта в Excel выберите шаблон для редактирования и нажать кнопку Изменить, после внесения изменений нажмите кнопку ОК. Для удаления ненужного шаблона нажмите кнопку Удалить.

Создание и редактирование шаблонов экспорта данных в Excel

Шаблоны экспорта данных представляют собой файлы в формате шаблонов программы MS Excel (с расширением .xlt). Шаблоны можно создавать «с нуля», либо на базе уже существующих шаблонов.

В шаблонах для вывода данных из базы данных ZuluGIS применяются именованные ячейки и диапазоны ячеек листа Excel. В настройках вывода данных задается соответствие между полями базы данных и именами шаблона. При экспорте данных на базе такого шаблона поля базы данных слоя выводятся в места диапазонов шаблона с соответствующими именами.

Предусмотрены два режима вывода данных с помощью шаблона: табличный и в свободной форме, применяемый способ задается в настройках вывода данных (см. «Создание и редактирование шаблонов экспорта данных в Excel»).

Примечание

Для того чтобы задать имя для диапазона, выделите диапазон на листе MS Excel и введите в поле Имя (располагается с левого края строки формул, в нем отображается адрес текущей ячейки) название.

Для того чтобы изменить название или удалить имя диапазона, выполните команду меню MS Excel и в открывшемся диалоге выполните требуемые действия.

Примечание

В названиях полей и диапазонов следует использовать только буквы (латиницу и кириллицу), цифры и знаки подчеркивания (знаками подчеркивания следует использовать вместо пробелов).

Табличный вывод

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

Рисунок 446. Вывод отчета в табличном формате

Последовательность действий по созданию нового шаблона в табличном виде (на примере MS Excel 2003):

Создайте новую книгу MS Excel.

Добавьте в книгу постоянную информацию, которая должна присутствовать во всех книгах отчетов на базе данного шаблона (Листы с текстом, формулами, графиками и т.д.).

Создайте лист для вывода отчета. В верхней части создайте шапку отчета (Название отчета, заголовки столбцов отчета и т.д.). Под шапкой задайте строку именованных диапазонов (обычно одиночных ячеек), которые будут служить основой таблицы отчета.

Сохраните полученный шаблон в формате xlt.

Вывод в свободной форме

При выводе в свободной форме, для каждой записи отчета создается отдельная «карточка» произвольных размеров, Карточки в отчете выводятся одна под другой.

Как экспортировать любые данные из программы в Excel

Шаблон карточки создается в виде области листа Excel, в ячейках которой задается текст и оформление постоянной части карточки (одинаковой для всех формируемых карточек отчета) и именованными диапазонами отмечаются места для добавления полей из базы данных. Границы карточки задаются именованным диапазоном ячеек. Название этого диапазона указывается в поле Диапазон области данных настроек вывода данных.

Рисунок 447. Вывод отчета в свободной форме

Последовательность действий по созданию нового шаблона в свободной форме (на примере MS Excel 2003):

Создайте новую книгу MS Excel.

Добавьте в книгу постоянную информацию, которая должна присутствовать во всех книгах отчетов на базе данного шаблона (Листы с текстом, формулами, графиками и т.д.).

Создайте лист для вывода данных. В ячейки листа добавьте постоянные детали карточки отчета, постоянные для всех карточек Название отчета, названия полей отчета и т.д. Для ячеек, в которые должны выводится данные из полей отчета задайте имена.

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

Сохраните полученный шаблон в формате xlt.

Примеры экспорта в Excel приведены далее (см. «Примеры экспорта данных в Microsoft Excel»).

Файл шаблона в формате .xlt

Экспорт данных из Excel 2007

XLSX – представляет собой электронную книгу, созданную с помощью одной из версий программы Excel старше 2007 года. При этом данные имеют форму таблиц, состоящих из столбцов и строк. Каждая таблица разделена на ячейки, обладающие уникальным адресом, который состоит из цифирных и буквенных символов. Ячейки содержат данные, как введенные вручную, так и информацию, которая была получена в результате расчетов. Расширение допускает использование изображений, диаграмм и графической информации. Файлы XLSX форматируются на основе стандарта Open XML, позволяющего сжимать данные по типу ZIP. Архив сдержит файл для описания таблицы и отдельные файлы для каждого ее листа. Формат дает возможность разрабатывать и редактировать сложные многолистовые электронные таблицы со множеством формул и источников данных.

Использование расширения XLSX помогает оптимизировать содержимое файла, технологию управления и восстановления информации.

Формат существенно увеличивает возможности бинарных файлов устаревших вариантов программы Excel. Все приложения, отображающие XML, способны корректно работать с расширением XLSX. Благодаря сохранению данных в формате XML обеспечивается надежное хранение данных. Это дает возможность беспрепятственно передавать файлы по локальным сетям.

Простой экспорт в Excel XLSX

В продолжение темы, начатой в предыдущей статье, хочу поделиться своим опытом экспорта данных, в частности, в формате XLSX.

Итак, кому интересно, как заполнить XLSX без больших и сложных библиотек, прошу под кат.

Недавно передо мной возникла задача экспортировать непредсказуемый по размеру объем табличных данных в формате XLSX. Как любой здравомыслящий программист, первым делом полез искать готовые решения.
Почти сразу наткнулся на библиотеку PHPExcel. Мощное решение, с кучей разных функций и возможностей. Порывшись еще немного нашел отзывы программистов о ней. В частности, на форумах встречаются жалобы на скорость работы и отказ работать с большим объемом данных. Отметил библиотеку как один из вариантов решения и начал искать дальше.
Находил еще несколько библиотек для работы с XLSX, но все они были или забытыми, т.к. не обновлялись по 2-3 года, или обязательно тянули за собой сторонние библиотеки, или использовали DOM для работы с файлами, что мне не очень нравилось. Каждый раз, натыкаясь на очередную библиотеку и изучая механизмы ее работы, ловил себя на мысли, что все это «из пушки по воробьям». Не нужно мне такое сложное решение!
Признаюсь честно, изучив поверхностно каждое из найденных решений, не стал ставить и тестировать ни одного. Мне нужно было более простое и надежное, как танк, решение.

Задача

В общем, раз не нашел ничего подходящего, значит надо сформулировать технические требования к тому, что нужно. Требования, как и следовало ожидать, оказались тривиальными:

  • Оформить экспортирующий механизм в виде автономного класса
  • Реализовать в классе набор функций для записи значений ячеек и ряда
  • Возможность работы с неограниченным объемом данных
  • Распаковка и упаковка XLSX.

Отдельно остановлюсь только на последнем пункте. Как известно, XLSX представляет собой обычный zip-архив, который можно распаковать и увидеть, что он состоит из нескольких файлов и каталогов. Обратным образом его можно упаковать и переименовать в XLSX. Если все изменения правильные, то Microsoft Excel откроет файл без проблем.

Реализация

Изначально очень хотел создавать все файлы, из которых состоит XLSX, кодом, но, к счастью, быстро понял бессмысленность своей идеи. И родилось иное, более правильно и простое решение. Надо с помощью Microsoft Excel создать файл XLSX в таком виде, в каком он нужен в итоге, но без данных, иными словами — шаблон, а потом, с помощью кода, только добавить данные!
В таком случае, класс должен будет распаковывать шаблон в отдельный каталог, вносить изменения в /xl/worksheets/sheet1.xml и упаковывать содержимое каталога обратно в XLSX.

В объявлении класса присутствуют публичные переменные:
$templateFile – имя файла шаблона
$exportDir – папка, в которую будет распакован шаблон, разумеется с необходимыми правами доступа.

Конструктор класса принимает имя будущего файла, количество колонок и рядов. Потом проверяет, что имя файла корректно, папка для распаковки шаблона существует и формирует полное имя конечной папки для распаковки шаблона.
После создания класса можно распаковать шаблон и открыть на запись sheet1.xml. На самом деле я не просто дописываю в файл, а полностью его перезаписываю. Однажды взяв из него начальную строку, вношу в нее изменение в тэге dimension, который отражает размер экспортируемого диапазона, и записываю в файл.

Обеспечить скорость работы и возможность работы с большим объемом данных позволяют функции resetRow и flushRow. Они отвечают за очистку текущего ряда в памяти и запись текущего ряда на диск.
А вот сохранение значений ячеек с разными типами оказалось не такой простой задачей.

Запись строки

Казалось бы, что сложного записать строковое значение в файл. Однако, в XLSX все не так просто. Все строки внутри XLSX хранятся в отдельном файле /xl/sharedStrings.xml. В ячейки записываются не строковые значения, а их порядковые номера — индексы. Разумное решение с точки зрения сокращения размера файла.

Но такое решение неудобно с точки зрения программного заполнения шаблона. Если выполнять указанное требование, то мне бы пришлось выполнять отдельный проход по всем строковым значениям в массиве данных, исключать повторяющиеся, сохранять их в sharedStrings.xml, проиндексировать и вместо значений в исходном массиве вписать их индексы. Медленно и неудобно.

Оказывается, можно обойти требование и сохранять строковые значения ячеек прямо в ячейках. Но в этом случае формат записи будет иной:

Запись числа

Никаких сложностей с записью целых или дробных чисел не возникло. Все просто:

Запись даты и времени

Дата и время хранятся в виде количества секунд прошедших с 01.01.1970 поделенных на количество секунд в сутках. Причем, в вычислении допущена ошибка с определением високосного года. В общем, не вдаваясь в подробности, которые несложно найти в сети, чтобы корректно вычислять дату пришлось объявить в классе две константы:
ZERO_TIMESTAMP – смещение даты в формате Excel от UNIX_TIMESTAMP
SEC_IN_DAY – секунд в сутках.
После вычисления значения даты и времени, целая часть дроби – это дата, дробная часть – время:

После записи всех данных остается закрыть рабочий лист и рабочую книгу.

Применение

Как и раньше, использование описанного класса основано на экспорте данных с помощью провайдера CArrayDataProvider. Предполагая, что объем экспортируемых данных может оказаться очень большим, применен специальный итератор CDataProviderIterator, который перебирает возвращаемые данные по 100 записей (можно указать иное число записей).

Кому интересно, может получить исходный код моего класса AlxdExportXLSX совершенно безвозмездно.

Ссылка на основную публикацию
Adblock
detector
Внимание