Как Получить Данные из Excel Файла не Открывая Его

Узнайте, как использовать поддержку XML в PHP для чтения данных, выгруженных из Microsoft® Excel® 2003 в XML-формате, и как экспортировать данные в XML-формате из PHP-приложений для использования в электронных таблицах Excel.

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

Создание базы данных

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

Синтаксис для создания схемы базы данных в MySQL выглядит следующим образом.

Листинг 1. SQL-код для создания схемы базы данных

Этот файл является простой базой данных, состоящей из одной таблицы с именем names, которая имеет пять полей: автоинкрементный идентификатор, имя, отчество, фамилия и адрес электронной почты.

Рисунок 2. Сохранение файла в виде электронной таблицы XML

После создания XML-файла можно приступать к написанию PHP-приложения.

Импорт данных

Импорт данных начинается с создания несложной страницы, на которой выбирается входной XML-файл Excel (рисунок 3).

Я присвоил файлу расширение .php, хотя на самом деле это вовсе не PHP. Это обычный HTML-файл, позволяющий пользователю указать файл и передать его на страницу import.php, на которой начинается все самое интересное.

Чтение XML-данных Excel

Для простоты я разделил написание страницы import.php на два этапа. На первом этапе выполняется разбор XML-данных и их вывод в форме таблицы. На втором этапе добавляется логика, в которой реализовано добавление записей в базу данных.

Первый объект Worksheet содержит реальные данные. Данные в этом объекте располагаются внутри тега Table, который в свою очередь содержит набор тегов Row и Cell. В каждом теге Cell располагается связанный с ним тег Data, содержащий данные ячейки. В нашем примере данные всегда представлены в виде строк (тип String).

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

В листинге 4 показана первая версия сценария import.php.

Сценарий начинается с чтения временного файла, загруженного в объект DOMDocument. Затем выполняется поиск всех тегов Row. Первая строка пропускается в соответствии с логикой обработки переменной $first_row. Далее для каждой строки выполняется циклический анализ каждого содержащегося в ней тега Cell.

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

В самом конце с помощью обычных функций PHP найденные данные выводятся в виде HTML-таблицы (рисунок 4).

Рисунок 4. Вывод данных в виде HTML-таблицы

Следующим шагом нужно загрузить информацию в базу данных.

Добавление информации в базу данных

После добавления полученного содержимого строки в структуру PHP необходимо занести его в базу данных. Для этого я добавил код, использующий модуль Pear DB (листинг 5).

На рисунке 5 показан вывод данных в браузере Firefox.

Результат выглядит не очень красиво, но это не важно. Важно то, что с помощью операторов prepare и execute можно добавить информацию в базу данных. Чтобы показать это, я создал еще одну страницу под названием list.php, отображающую информацию, полученную из базы данных (листинг 6).

Эта простая страница начинается с применения SQL-функции select к таблице names. После этого создается таблица, в которую с помощью метода fetchInto добавляются все строки.

На рисунке 6 показаны данные, отображаемые этой страницей.

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

Создание XML-файла для экспорта в Excel

Заключительным шагом является создание XML-файла Excel. Я начал с того, что скопировал XML-содержимое Excel в PHP-сценарий (листинг 7), поскольку это простейший способ получить XML-файл Excel, который будет корректно проанализирован (поскольку Excel очень требователен к своему XML-формату).

Сценарий начинается с того, что для результирующего вывода устанавливается формат XML. Это важно, поскольку в противном случае браузеры будут воспринимать этот код просто как некорректный HTML.

Я изменил часть кода, содержащую SQL-запрос, таким образом, чтобы результаты запроса сохранялись в массив. В данном случае необходимо поместить в атрибут ss:ExpandedRowCount количество строк плюс одну строку (отвечающую за заголовки). Если бы это была обычная страница отчета, дополнительная строка заголовков была бы не нужна.

На рисунке 7 показан результат открытия страницы в браузере.

Рисунок 7. Полученное XML-содержимое в браузере Firefox

Не очень впечатляет. Но посмотрите, что произойдет, когда я открою эту же ссылку в Internet Explorer (рисунок 8).

Рисунок 8. Полученное XML-содержимое в браузере Internet Explorer

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

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

Как поменять кодировку в Excel

Ниже приведен алгоритм, которым можно воспользоваться в том случае, если файл содержит множество колонок, но загрузить нужно лишь те, которые есть в документе 1С. Так же его можно использовать, когда есть необходимость проверить имена колонок на соответствие именам реквизитов для исключения ошибок.

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

Запись файла Excel с помощью модуля xlsxwriter

• Откройте программу и нажмите кнопку главного меню или меню «Файл» (зависит от версии).
• Перейдите в каталог с файлом и нажмите на имя файла в окне.
• Внизу найдите слово «Открыть». Щелкните по треугольнику около него и выберите «Открыть и восстановить».
• Нажмите «Восстановить».
• Если это не помогло, то вернитесь к окну путем повторного выполнения вышеуказанных действий, и нажмите «Извлечь данные».
• Далее выберите «Преобразовать формулы в значения» (все формулы пропадут) или «Восстановить формулы».
• После этого возможно данные будут восстановлены. Сохраните документ.

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