PHP+Excel быстро генерируем XLS файлы


Создать простой xls файл

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

Excel открывает файл но перед этим ругается что он поврежден.
Вот такой пример получился:

30.10.2020, 15:59

Файл xls на скачивание
Всем привет, подскажите в чем беда, уже запарился, не могу понять в чем дело. Есть физический xls.

Создать программу импорта данных из *.xls на php
Здравствуйте. Хочу написать программу на php. Есть таблица *.xls из нее нужно вытащить столбец с.

Скачать xls-файл при нажатии на кнопку
Добрый день. Не получается при нажатии на кнопку скачивать файл. Выдает ошибку, как на сайте.

Как вставить документ Excel в страницу, так чтобы изменения вносить только в .xls файл?
Подскажите, как вставить документ Excel в страницу, так чтобы изменения вносить только в .xls файл?

Создать файл xls
Как программно создать файл xls, после создания я буду открыть его . ExcelФайл =.

30.10.2020, 22:39 2

Вручную?Зачем? Есть библиотеки пример

30.10.2020, 22:44 3 01.11.2020, 17:06 [ТС] 4

Если я правильно понял то для использования готовых решений, мне как то надо установить на сервер эту библиотеку. А я в этом не силен, да я вообще в php не силен, просто решил помочь товарищу =(

Добавлено через 53 секунды

01.11.2020, 17:06
01.11.2020, 17:55 5

Я не предлагал разбираться как сжимать. Это я к тому, что не всё так просто с форматом экселя. Есть вещи, которые нужно писать с нуля, но эксель того не стоит. Лучше не париться и ставить готовую библиотеку.

Сделаю исключение в разборе решения не для новичков. Предположим, что имеется php >=7.1
1. Переходим на страницу проекта PhpSpreadsheet.
2. Прокрутим ниже — там мы увидим ссылку на документацию по установке «Read more about it, including install instructions, in the official documentation», тыкаем по ссылке.
3. В разделе Installation мы видим команду, для установки composer require phpoffice/phpspreadsheet . А в разделе Hello World видим пример создания простейшего документа.
пример

Создаем отчеты в Excel на PHP

Не редко при разработке некоего проекта, возникает необходимость в формировании отчетной статистики. Если проект разрабатывается на Delphi, C# или к примеру, на С++ и под Windows, то тут проблем нет. Всего лишь необходимо воспользоваться COM объектом. Но дела обстоят иначе, если необходимо сформировать отчет в формате excel на PHP. И чтобы это творение функционировало на UNIX-подобных системах. Но, к счастью, не так все плохо. И библиотек для этого хватает. Я свой выбор остановил на PHPExcel. Я уже пару лет работаю с этой библиотекой, и остаюсь доволен. Поскольку она является кроссплатформенной, то не возникает проблем с переносимостью.

PHPExcel позволяет производить импорт и экспорт данных в excel. Применять различные стили оформления к отчетам. В общем, все на высоте. Даже есть возможность работы с формулами. Только необходимо учитывать, что вся работа (чтение и запись) должна вестись в кодировке utf-8.

Установка библиотеки

Для работы необходима версия PHP 5.2.0 или выше. А также необходимы следующие расширения: php_zip, php_xml и php_gd2. Скачать библиотеку можно отсюда.

С помощью библиотеки PHPExcel можно записывать данные в следующие форматы:

  • Excel 2007;
  • Excel 97 и поздние версии;
  • PHPExcel Serialized Spreadshet;
  • HTML;
  • PDF;
  • CSV.

Импорт данных из PHP в Excel

Рассмотрим пример по формированию таблицы умножения.

Далее нам необходимо получить наш *.xls файл. Здесь можно пойти двумя путями. Если предположим у вас интернет магазин, и клиент хочет скачать прайс лист, то будет лучше прибегнуть к такому выводу:

Здесь сформированные данные сразу “выплюнутся” в браузер. Однако, если вам нужно файл сохранить, а не “выбросить” его сразу, то не нужно выводить HTTP-заголовки и вместо “php://output” следует указать путь к вашему файлу. Помните что каталог, в котором предполагается создание файла, должен иметь права на запись. Это касается UNIX-подобных систем.

Рассмотрим еще на примере три полезные инструкции:

  • $sheet->getColumnDimension(‘A’)->setWidth(40) – устанавливает столбцу “A” ширину в 40 единиц;
  • $sheet->getColumnDimension(‘B’)->setAutoSize(true) – здесь у столбца “B” будет установлена автоматическая ширина;
  • $sheet->getRowDimension(4)->setRowHeight(20) – устанавливает четвертой строке высоту равную 20 единицам.

Также обратите внимание на следующие необходимые для работы с отчетом методы:

  • Методы для вставки данных в ячейку:
    • setCellValue([ $pCoordinate = ‘A1′ [, $pValue = null [, $returnCell = false]]]) принимает три параметра: координату ячейки, данные для вывода в ячейку и третий параметр эта одна из констант типа boolean: true или false (если передать значение true, то метод вернет объект ячейки, иначе объект рабочего листа);
    • setCellValueByColumnAndRow([ $pColumn = 0 [, $pRow = 1 [, $pValue = null [, $returnCell = false]]]]) принимает четыре параметра: номер столбца ячейки, номер строки ячейки, данные для вывода в ячейку и четвертый параметр действует по аналогии с третьим параметром метода setCellValue().
  • Методы для получения ячейки:
    • getCell([ $pCoordinate = ‘A1′]) принимает в качестве параметра координату ячейки;
    • getCellByColumnAndRow([ $pColumn = 0 [, $pRow = 1]]) принимает два параметра в виде номеров столбца и строки ячейки.


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

Оформление отчета средствами PHP в Excel

Очень часто возникает необходимость выделить в отчете некоторые данные. Сделать выделение шрифта или применить рамку с заливкой фона для некоторых ячеек и т.д. Что позволяет сконцентрироваться на наиболее важной информации (правда может и наоборот отвлечь). Для этих целей в библиотеке PHPExcel есть целый набор стилей, которые можно применять к ячейкам в excel. Есть конечно в этой библиотеке небольшой “минус” – нельзя применить стиль к нескольким ячейкам одновременно, а только к каждой индивидуально. Но это не создает дискомфорта при разработке web-приложений.

Назначить стиль ячейке можно двумя способами:

  • Применить метод applyFromArray, класса PHPExcel_Style. В метод applyFromArray передается массив со следующими параметрами:
    • fill — массив с параметрами заливки;
    • font — массив с параметрами шрифта;
    • borders — массив с параметрами рамки;
    • alignment — массив с параметрами выравнивания;
    • numberformat — массив с параметрами формата представления данных ячейки;
    • protection — массив с параметрами защиты ячейки.
  • Использовать метода класса PHPExcel_Style для каждого из стилей в отдельности. К примеру, назначить ячейке шрифт можно так: $sheet->getStyle(‘A1′)->getFont()->setName(‘Arial’) .

Заливка

Значением параметра fill является массив со следующими необязательными параметрами:

  • type — тип заливки;
  • rotation — угол градиента;
  • startcolor — значение в виде массива с параметром начального цвета в формате RGB;
  • endcolor — значение в виде массива с параметром конечного цвета в формате ARGB;
  • color — значение в виде массива с параметром начального цвета в формате RGB.

Стили заливки

FILL_NONE none
FILL_SOLID solid
FILL_GRADIENT_LINEAR linear
FILL_GRADIENT_PATH path
FILL_PATTERN_DARKDOWN darkDown
FILL_PATTERN_DARKGRAY darkGray
FILL_PATTERN_DARKGRID darkGrid
FILL_PATTERN_DARKHORIZONTAL darkHorizontal
FILL_PATTERN_DARKTRELLIS darkTrellis
FILL_PATTERN_DARKUP darkUp
FILL_PATTERN_DARKVERTICAL darkVertical
FILL_PATTERN_GRAY0625 gray0625
FILL_PATTERN_GRAY125 gray125
FILL_PATTERN_LIGHTDOWN lightDown
FILL_PATTERN_LIGHTGRAY lightGray
FILL_PATTERN_LIGHTGRID lightGrid
FILL_PATTERN_LIGHTHORIZONTAL lightHorizontal
FILL_PATTERN_LIGHTTRELLIS lightTrellis
FILL_PATTERN_LIGHTUP lightUp
FILL_PATTERN_LIGHTVERTICAL lightVertical
FILL_PATTERN_MEDIUMGRAY mediumGray

Пример указания настроек для заливки:

Или можно использовать следующие методы:

Вставка изображений

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

  • setPath([ $pValue = », [ $pVerifyFile = true]]) данный метод принимает два параметра. В качестве первого параметра указывается путь к файлу с изображением. А второй параметр имеет смысл указывать, если необходимо осуществлять проверку существования файла (может принимать одно из значений true или false).
  • setCoordinates([ $pValue = ‘A1′])) принимает на вход один параметр в виде строки с координатой ячейки.
  • setOffsetX([ $pValue = 0]) принимает один параметр со значением смещения по X от левого края ячейки.
  • setOffsetY([ $pValue = 0]) принимает один параметр со значением смещения по Y от верхнего края ячейки.
  • setWorksheet([ $pValue = null, [ $pOverrideOld = false]]) этот метод принимает на вход два параметра. Первый является обязательным, а второй нет. В качестве первого параметра указывается экземпляр объекта активного листа. Если в качестве значения второго параметра передать true, то если лист уже был назначен ранее – произойдет его перезапись и соответственно изображение удалится.

Код демонстрирующий алгоритм вставки изображения приведен ниже:

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

Шрифт

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

  • name — имя шрифта;
  • size — размер шрифта;
  • bold — выделять жирным;
  • italic — выделять курсивом;
  • underline — стиль подчеркивания;
  • strike — перечеркнуть;
  • superScript — надстрочный знак;
  • subScript — подстрочный знак;
  • color — значение в виде массива с параметром цвета в формате RGB.

Стили подчеркивания

UNDERLINE_NONE нет
UNDERLINE_DOUBLE двойное подчеркивание
UNDERLINE_SINGLE одиночное подчеркивание

Пример указания параметров настроек для шрифта:

Или воспользоваться следующими методами:

Рамка

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

  • тип рамки — (top|bootom|left|right|diagonal|diagonaldirection);
  • style — стиль рамки;
  • color — значение в виде массива с параметром цвета в формате RGB.


Стили линий

BORDER_NONE нет
BORDER_DASHDOT пунктирная с точкой
BORDER_DASHDOTDOT пунктирная с двумя точками
BORDER_DASHED пунктирная
BORDER_DOTTED точечная
BORDER_DOUBLE двойная
BORDER_HAIR волосная линия
BORDER_MEDIUM средняя
BORDER_MEDIUMDASHDOT пунктирная с точкой
BORDER_MEDIUMDASHDOTDOT утолщенная пунктирная линия с двумя точками
BORDER_MEDIUMDASHED утолщенная пунктирная
BORDER_SLANTDASHDOT наклонная пунктирная с точкой
BORDER_THICK утолщенная
BORDER_THIN тонкая

Пример указания параметров настроек для рамки:

Так же можно прибегнуть к использованию следующих методов:

$PHPExcel_Style->getBorders()->getLeft()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));

$PHPExcel_Style->getBorders()->getRight()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));

$PHPExcel_Style->getBorders()->getTop()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));

$PHPExcel_Style->getBorders()->getBottom()->applyFromArray(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));

$PHPExcel_Style->getBorders()->getDiagonal()->applyFromArray(array(‘style’ => PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′)));

$PHPExcel_Style->getBorders()->setDiagonalDirection(array(‘style’ =>PHPExcel_Style_Border::BORDER_DASHDOT,’color’ => array(‘rgb’ => ’808080′))).

Выравнивание

Значением параметра alignment является массив, который принимает на вход четыре необязательных параметра:

  • horizontal — константа горизонтального выравнивания;
  • vertical — константа вертикального выравнивания;
  • rotation — угол поворота текста;
  • wrap — разрешить перенос текста;
  • shrinkToFit — изменять ли размер шрифта при выходе текста за область ячейки;
  • indent — отступ от левого края.

Выравнивание по горизонтали

HORIZONTAL_GENERAL основное
HORIZONTAL_LEFT по левому краю
HORIZONTAL_RIGHT по правому краю
HORIZONTAL_CENTER по центру
HORIZONTAL_CENTER_CONTINUOUS по центру выделения
HORIZONTAL_JUSTIFY по ширине

Выравнивание по вертикали

VERTICAL_BOTTOM по нижнему краю
VERTICAL_TOP по верхнему краю
VERTICAL_CENTER по центру
VERTICAL_JUSTIFY по высоте

Пример параметров настройки стилей выравнивания:

Или использовать следующие методы:

Формат представления данных

Параметр numberformat представляет собой массив, который включает только один параметр: code — формат данных ячейки.

Список возможных форматов

FORMAT_GENERAL General
FORMAT_TEXT @
FORMAT_NUMBER
FORMAT_NUMBER_00 0.00
FORMAT_NUMBER_COMMA_SEPARATED1 #,##0.00
FORMAT_NUMBER_COMMA_SEPARATED2 #,##0.00_-
FORMAT_PERCENTAGE 0%
FORMAT_PERCENTAGE_00 0.00%
FORMAT_DATE_YYYYMMDD2 yyyy-mm-dd
FORMAT_DATE_YYYYMMDD yy-mm-dd
FORMAT_DATE_DDMMYYYY dd/mm/yy
FORMAT_DATE_DMYSLASH d/m/y
FORMAT_DATE_DMYMINUS d-m-y
FORMAT_DATE_DMMINUS d-m
FORMAT_DATE_MYMINUS m-y
FORMAT_DATE_XLSX14 mm-dd-yy
FORMAT_DATE_XLSX15 d-mmm-yy
FORMAT_DATE_XLSX16 d-mmm
FORMAT_DATE_XLSX17 mmm-yy
FORMAT_DATE_XLSX22 m/d/yy h:mm
FORMAT_DATE_DATETIME d/m/y h:mm
FORMAT_DATE_TIME1 h:mm AM/PM
FORMAT_DATE_TIME2 h:mm:ss AM/PM
FORMAT_DATE_TIME3 h:mm
FORMAT_DATE_TIME4 h:mm:ss
FORMAT_DATE_TIME5 mm:ss
FORMAT_DATE_TIME6 h:mm:ss
FORMAT_DATE_TIME7 i:s.S
FORMAT_DATE_TIME8 h:mm:ss
FORMAT_DATE_YYYYMMDDSLASH yy/mm/dd; @
FORMAT_CURRENCY_USD_SIMPLE «$»#,##0.00_-;@
FORMAT_CURRENCY_USD $#,##0_-
FORMAT_CURRENCY_EUR_SIMPLE [$EUR ]#,##0.00_-

Пример настройки для формата данных ячейки:

А можно и воспользоваться методом:

Защита ячеек

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

  • locked — защитить ячейку;
  • h >Пример настройки параметров для защиты ячейки:

Или использовать следующие методы:

Теперь мы знаем, какие есть настройки стилей и какие присутствуют параметры у каждого стиля. Сейчас мы к ячейкам таблицы применим стиль оформления, но проделаем это двумя методами. Первый метод заключается в создании массива настроек, который в качестве параметра мы передадим в метод applyFromArray, класса PHPExcel_Style.

Далее мы применим созданный нами стиль к ячейкам excel.

Сейчас применим тот же стиль, но используя другую методику.

Вот что у нас получилось:

Если требуется применять стиль многократно, то лучше подойдет первый метод, в другом же случае, лучше остановиться на втором. Для получения объекта (экземпляр класса PHPExcel_Style) ячейки отвечающего за стиль, необходимо использовать один из следующих методов:

  • getStyleByColumnAndRow([ $pColumn = 0 [, $pRow = 1]]) – применяется если требуется обратиться к ячейке по числовым координатам. Методу необходимо передать два параметра в виде номеров столбца и строки ячейки;
  • getStyle([ pCellCoordinate = ‘A1′]) – используется для обращения по строковой координате ячейки. Методу требуется передать один параметр, это строковое представление координаты.


Добавление комментариев

Я думаю, что не часто кто-то пользуется возможностью добавления комментариев к ячейкам, но это сугубо мое личное мнение, однако такая возможность имеется. Добавить комментарий к ячейке довольно просто, что видно из примера ниже:

Следует заметить, что при повторном вызове метода createTextRun() новый комментарий добавится к уже существующему, а не заменит его. Следует отметить, что данный метод возвращает объект класса PHPExcel_RichText_Run, у которого имеются методы для установки и получения параметров шрифта:

  • getFont() – возвращает объект класса для работы со шрифтами PHPExcel_Style_Font.
  • setFont([ $pFont = null]))]) – данному методу требуется передать в качестве параметра объект класса PHPExcel_Style_Font.

Вот какой комментарий мы должны получить:

Вставка ссылки

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

Так же в виде ссылки может быть использован, к примеру, email адрес: mailto:example@mail.com.

Чтение данных из Excel

Формировать отчеты и применять к ним стили это конечно отлично. Но на этом возможности библиотеки PHPExcel не заканчиваются. Ну что же, посмотрим на что она еще способна. А способна она еще и читать данные из файлов формата *.xls / *.xlsx.

С помощью библиотеки PHPExcel можно читать следующие форматы:

  • Excel 2007;
  • Excel 5.0/Excel 95;
  • Excel 97 и поздние версии;
  • PHPExcel Serialized Spreadshet;
  • Symbolic Link;
  • CSV.

Для работы нам понадобятся объекты двух классов:

  • PHPExcel_Worksheet_RowIterator – используется для перебора строк;
  • PHPExcel_Worksheet_CellIterator – используется для перебора ячеек.

Для демонстрации выведем данные из таблицы с информацией об автомобилях.

Пример чтения файла представлен ниже:

Первый вариант

Второй вариант

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

  • getHighestColumn() – возвращает символьное представление последнего занятого столбца в активном листе. Обратите внимание: не индекс столбца, а его символьное представление (A, F и т.д.);
  • getHighestRow() – возвращает количество занятых строк в активном листе.

А также нам не обойтись без помощи метода columnIndexFromString(), который входит в состав класса PHPExcel_Cell. Данный метод позволяет определить индекс столбца по его символьному представлению.

С помощью продемонстрированных возможностей, можно формировать и считывать любые отчеты в виде файлов, формата excel. А также были продемонстрированы почти все возможные методы для работы со стилями.

Комментариев: 194

Никита
Июн 24, 2020 @ 04:15:29

Здравствуйте, спасибо за статью!
Возникла проблема, буквально вчера все работало прекрасно, ничего не менял, сегодня в сохранившемся excel документе находятся только непонятные повторяющиеся символы, знаки, треугольники, и всякая такая дичь, причем если информацию я выгружаю на 2 строки, непонятных символов все равно на всю страницу. С кириллицей никак не связано.
Надеюсь на вашу помощь, спасибо!

admin
Июн 26, 2020 @ 11:39:52

Добрый день!
Сама по себе ничего не бывает. Может кодировка в модуле изменилась?

jeckson
Июл 07, 2020 @ 12:49:54

У меня файл на выходе получается какой то непонятный, одни каракули неразбитые по ячейкам. Не понимаю в чем дело.
http://joxi.ru/DrlVGp5FvOdJN2
Может кто-то совет дать?

irishmann
Фев 21, 2020 @ 15:38:12

Доброго времени суток, у вас до вывода заголовков EXCEL происходит вывод, может echo, var_dump, print затесались.

Richi
Сен 25, 2020 @ 00:43:31

Добрый день, можно ли чтобы скинули исходники(весь код из этой страницы), ну или код взаимодействия phpexcel на чтение из файла и вывод на страницу (по определенным параметрам список/чекбокс), или как легче это реализовать
и как оно будет работать из html?

admin
Сен 28, 2020 @ 11:33:01


Добрый день!
Так отдельных исходников нет, код прямо в тексте. А что вам не ясно?

Richi
Окт 18, 2020 @ 01:53:39

Есть одна страница и там 5 подстраниц, с выпадающими списками, для каждой свой excel
У меня не хочет нормально работать с выпадающим списком под HTML.

VsemP
Ноя 20, 2020 @ 15:11:39

$page->setCellValue(‘A1′, mb_convert_encoding(‘Текст’, ‘utf-8′, ‘windows-1251′)); как то так…

Александр
Окт 18, 2020 @ 10:00:08

А можно в ячейки формулу вписать? тоесть не результат вычислений а иммено формулу, как в обычном Excel, что бы потом пользователь мог копировать растягивать ячейки?

admin
Окт 23, 2020 @ 15:07:13

Думаю, что да. Так как библиотека по возможностям богата, но я лично не сталкивался с подобным.

Адам
Дек 04, 2020 @ 13:33:43

Как читать и перенести саму таблицу, а не просто текст с таблицы?

admin
Дек 04, 2020 @ 22:57:21

Не совсем понял, что вы имеете ввиду.

Михаил
Сен 06, 2020 @ 10:22:18

Необходимо, чтобы числа в столбце имели нули слева, например 01111. При выгрузке задаю ячейке текстовый формат, но в файле эксель, числа все-равно без нулей слева, т.е. 1111, хотя ячейка имеет формат текст.
Вот часть кода:
$objPHPExcel->getActiveSheet()->getStyle(‘B’.$j)
->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->setCellValue(‘B’.$j, $item[‘art_no’]);
Как сделать так, чтобы выгружались числа в ячейки с нулями слева и почему этого не происходит?

Добавить комментарий Отмена

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.

PHP библиотека PHPExcel для работы с Excel

Для работы с Excel использовал библиотеку PHPExcel. Установка простейшая – кладем папку Classes в нужную папку на сервере, указываем корректные пути в include/require.

Примеры кода по чтению/генерации файлов Excel можно посмотреть на github странице библиотеки.

Красивости

и этим не ограничивается функционал, это лишь то, что использовал:

  • mergeCells(“cell_range”) – Объединение указанных ячеек в одну. Данные должны лежать в первой ячейке, иначе они теряются.
  • setSize(16) – Делаем размер шрифта 16 для указанных ячеек.
  • setBold(true) – Делаем текст “жирным”
  • setWrapText(true) – Делаем перенос слов по умолчанию для всех ячеек
  • setAutoFilter – Включить фильтр по умолчанию
  • freezePane – Закрепить какие либо строки, например первую

  • borders – делается через создание стиля, а потом его применение на указанный диапазон ячеек
  • color – Аналогично с помощью стилей меняем цвет шрифта (Font)
  • setARGB – Изменить цвет ячейки, например
    • всей первой строки
    • конкретной ячейки (делал так цвет был переменным и задавался на основе данных – формировался разноцветный показательный Excel)
    • диапазона ячеек по диагонали
ЧТЕНИЕ

Код для чтения (два столбца):

Редактирование

Открываем файл test.xlsx, на его основе создаем новый new.xlsx с измененными парой ячеек.

ГЕНЕРАЦИЯ

Пример генерации на основе результата MySQL (не тестил, использовал универсальную функцию ниже).

Если нужно протестить базовую работу генерации на основе двумерного массива

Пример генерации xls из двумерного массива с настройками

  • имени (определяется на основе значения в переменной $_POST[‘filename’]),
  • ширины столбца (на основе $_POST[‘excelSettings’]),
  • bold первой строки (setBold),
  • переноса слов (setWrapText).

Пример вызова и код по генерации кнопки, добавлению к названию файла даты/времени (формат 20200123_003800_Название.xlsx) и переходу на страницу генерации xls.

Вызов функции (про функцию iconv_for_xls ниже):

Особенности

Мусор

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

Например, возможны кракозябры при генерации файла больше 20-40 строк, если оставить включенным debug типа print_r($_POST); (почему не воспроизводиться при генерации мелких файлов – это вопрос :)).

Аналогично, будет выдавать ошибку:

  1. Если в конце кода php нет “exit;”
  2. Если перед генерацией файла нет очистки буфера вывода через “ob_end_clean();”
  3. Если используется генерация xlsx (Excel2007), но не установлены xml и xmlwriter модули PHP. Так же может выдаваться ошибка “Fatal error: Class ‘XMLWriter’ not found in /

/XMLWriter.php on line 37” т.к. библиотека PHPExcel использует наследование класса от XMLWriter (“class PHPExcel_Shared_XMLWriter extends XMLWriter”), т.е. требует установленного модуля xmlwriter. Для “нормальных” дистрибутивов это делается простым sudo apt-get/yum install php-xmlwriter (или php5-xml) и перезагрузкой apache, для gentoo это делается через пересборку всего php с новым модулем.

Отправка array на другую страницу

Чтобы функция по генерации xlsx была универсальной, сделал отдельную страницу по генерации, но на эту страницу потребовалось передать двумерный массив. В интернете гуляет два варианта решения: сохранить массив в сессии/куках, передать его через json (лучше) или serialize (хуже).

Через сессии все передавалось, только значение почему то не апдейтилось корректно. Сходу не разобрался в причинах, использовал сначала serialize (полный пример см. в function create_xls), но потом мигрировал на json из-за периодических проблем со спец. символами serialize.

Кодировка

Если на сайте кодировка cp-1251, то при генерации обязательно нужно использовать iconv в utf-8. В противном случае вместо русских символов в ячейке будет бред (например, “ИСТИНА”).


PHPExcel не генерирует правильный файл XLS

Я использую PHPExcel в моем проекте для создания XLS файла данных, поступающих из базы данных. Я скачал библиотеку PHPExcel и использовать его в моем PHP класс, как дать ниже:

Этот код генерирует файл XLS, но этот файл полностью пуст. Я имею в виду, что я включил файлы PHPExcel в неправильном направлении в PHP классов. Может кто-нибудь сказать мне, где я делаю неправильно или пример того, как я могу сделать то же самое?

в первую очередь вам нужно прочитать файл:

Теперь получить данные из БД:

затем присвоить данные, чтобы исправить клетки:

в конце записи нового файла:

Вы должны передать имя файла в save() методе. Затем прочитать содержимое этого файлы и эхо в браузер.

Получение данных из Excel-файла xlsx через php

Рассмотрим, как с помощью языка php получить данные из Excel-файла, который в формате xlsx.

Нам потребуется библиотека PHPExcel, скачать её можно тут.

Из скаченного архива нам понадобится только папка Classes. Копируем её в наш проект.

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

Предположим, наш файл date.xlsx имеет путь site.ru/files/date.xlsx и содержит 3 столбца данных: имя, ключ, значение и 5 строк с заполненными данными. Тогда считывая файл нашей созданной функцией:

Требование к хостингу:

  • Версия PHP 5.2 или выше;
  • Включенное расширение PHP php_zip;
  • Включенное расширение PHP php_xml;
  • Включенное расширение PHP php_gd2.

Работа с Excel средствами PHP

PHPExcel — библиотека для создания и чтения данных из файлов формата OpenXML (который используется в MS Excel 2007). С ее помощью можно считывать из файлов, записывать в файлы, форматировать содержимое, работать с формулами и т.д. Для работы PHPExcel требуется версия PHP 5.2 или выше, с установленными библиотеками Zip, XML и GD2.

Установка PHPExcel

Первым делом библиотеку необходимо скачать. Для этого переходим на официальный сайт библиотеки и скачиваем архив PHPExcel-1.7.8.zip. После распаковки мы получим несколько файлов и папок:

  • Classes
  • Documentation
  • Tests
  • changelog.txt
  • install.txt
  • license.txt

Файлы — это различные описания по предыдущим версиям, лицензионное соглашение и очень краткая инструкция по установке. Далее, в папке Classes, содержится непосредственно сама библиотека PHPExcel — эту папку необходимо скопировать в корень нашего скрипта.

В папке Documentation содержится документация по библиотеке на английском языке. В папке Tests — примеры по использованию библиотеки.

Создание Excel-файла

Итак, давайте создадим файл makeexcel.php и начинаем работать с ним. Для начала нам необходимо подключить главный файл библиотеки PHPExcel.php (который находится в папке Classes) и создать объект класса PHPExcel:

Настройки листа книги Excel

Документ Excel состоит из книг, а каждая книга в свою очередь, состоит из листов. Далее лист состоит из набора ячеек, доступ к которым осуществляется по координатам. То есть у нас есть столбцы, которые имеют буквенные имена (А, В, С и т.д) и есть строки, которые пронумерованы. Значит, что бы получить доступ к первой ячейке нужно указать код А1. Точно также мы с помощью библиотеки будем получать доступ к каждой ячейке.

Итак, первым делом необходимо выбрать активный лист, на который мы будем выводить данные и получить объект этого листа:

С помощью метода setActiveSheetIndex(0) указываем индекс (номер) активного листа. Нумерация листов начинается с нуля. Далее с помощью метода getActiveSheet() получаем объект этого активного листа, то есть другими словами получаем доступ к нему для работы. И сохраняем этот объект в переменную $aSheet .

Если Вы захотите указать активным какой то другой лист, то вначале его необходимо создать, при помощи метода:

Затем, по аналогии, указываем индекс и получаем объект активного листа.

Вначале задаем ориентацию листа при помощи метода setOrientation() , которому передаем константу класса PHPExcel_Worksheet_PageSetup :

  • ORIENTATION_PORTRAIT — книжная
  • ORIENTATION_LANDSCAPE — альбомная

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


Далее вызываем метод SetPaperSize() , который позволяет задать размер страницы для печати. Ему передаем параметром константу PAPERSIZE_A4 класса PHPExcel_Worksheet_PageSetup . Что означает, что размер листа страницы будет установлен А4.

Далее устанавливаем поля документа, то есть отступы от краев документа. Отступы задаются в специальных символьных единицах. Вначале, обратите внимание, вызываем у объекта $aSheet метод getPageMargins() , который вернет объект класса, отвечающего за настройки полей страницы. Затем вызываем методы setTop() , setRight() , setLeft() и setBottom() .

Далее при помощи метода setTitle(‘Прайс лист’) задаем название нашего листа.

Если нужно, можно при печати выводить шапку и подвал листа:

Обратите внимание на передаваемые параметры:

  • для шапки передаем строку ‘&CТД ТИНКО: прайс-лист’ ; метка &C означает, что текст нужно расположить по центру.
  • для подвала передаем строку ‘&L&B’.$aSheet->getTitle().’&RСтраница &P из &N’ ; это означает, что нужно вывести слева и жирным шрифтом ( &L&B ) название листа (метод $aSheet->getTitle() ), затем справа ( &R ) вывести номер страницы ( &P ) из общего количества страниц ( &N ).

Затем указываем настройки шрифта по умолчанию:

  • setName(‘Arial’) — задаем имя шрифта;
  • setSize(8) — задаем размер шрифта.

Наполнение документа данными

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

Теперь заполним несколько ячеек текстом:

Здесь мы сначала объеденяем ячейки с А1 до E1 при помощи метода mergeCells() , далее задаем высоту строки: вначале получаем доступ к строке 1 при помощи метода getRowDimension(‘1’) , затем задаем высоту — setRowHeight(20) . Далее при помощи метода setCellValue(‘A1′,’ТД ТИНКО’) , устанавливаем значение ячейки А1.

Далее давайте в ячейку D4 запишем текущую дату:

Теперь, используя метод setCellValue() , а также цикл while() наполним данными наш прайс-лист:

Стилизация данных

Давайте немного украсим наш прайс-лист, то есть каждой ячейке добавим стилей. Для этого необходимо создать массив со стилями и при помощи метода applyFromArray() , применить этот массив к ячейке (или ячейкам):

Теперь, по аналогии, применим стили к остальным ячейкам:

Сохранение документа

Осталось только сохранить наш документ:

Если нужно вывести документ в браузер

Первый заголовок указывает браузеру тип открываемого контента — это документ формата Excel. Второй — говорит браузеру, что документ необходимо отдать пользователю на скачивание под именем simple.xlsx.

Добавление формул

Формула Excel — это математическое выражение, которое создается для вычисления результата и которое может зависеть от содержимого других ячеек. Формула в ячейке Excel может содержать данные, ссылки на другие ячейки, а также обозначение действий, которые необходимо выполнить.

Использование ссылок на ячейки позволяет пересчитывать результат по формулам, когда происходят изменения содержимого ячеек, включенных в формулы. Формулы Excel начинаются со знака =. Скобки ( ) могут использоваться для определения порядка математических операции.

Примеры формул Excel: =27+36 , =А1+А2-АЗ , =SUM(А1:А5) , =MAX(АЗ:А5) , =(А1+А2)/АЗ .

PHPExcel тоже поддерживает добавление формул в ячейки. Установить формулу можно так:

Чтение Excel-файла

Самый простой вариант — считать все таблицы (на всех листах) и записать данные в трехмерный массив:

Что использовать для генерации xls, в PHP (40 000 строк)?

Требуется генерировать прайс лист, порядка 40к строк. Важно чтобы была возможность группировки строк для создания дерева, поддержка старых версий Excel ( 2003 и выше), скорость и разумное потребление памяти.

Пробовал PHPexcel, выполняет все что нужно, но требует очень много памяти, и работает долго.(9 файлов генерируются около 9 минут)

Пробовал Spreadsheet_Excel_Writer (PEAR), работает быстро и памяти ест 30мб, но нет возможности группировать строки.(Возможно я не смог разобраться? Если подскажете как, будет круто).

Если это важно, php 7.0.27, CentOS Linux release 7.4.1708

Перенос данных из Excel в PHP и обратно

Сегодня расскажу как легко и быстро перенести любой объем данных из таблицы Excel в среду PHP и как с помощью PHP сгенерировать Excel файл и наполнить его данными. Такая задача часто возникает при разработке сайтов интернет магазинов и других площадок, где используются таблицы MySql. Так, например, мне нужно было перенести список, где было 5000 фамилий из Excel в Mysql, часто еще нужно вывести результаты опроса с сайта в Excel таблицу.

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


После загрузки распаковываем все файлы и создаем документ index.php, дальше будем работать в нем. Сначала напишем код для импорта данных из Excel. Вставляем следующие строчки, все пояснения есть в коде.

Copy Source | Copy HTML

  1. require_once ( ‘Excel_to_PHP/reader.php’ ); // подключаем файл reader.php из папки
  2. $Excel = new Spreadsheet_Excel_Reader(); // создаем объект
  3. $Excel ->setOutputEncoding( ‘utf-8’ ); // устанавливаем кодировку
  4. $Excel ->read( ‘example.xls’ ); // открываем нужный Excel файл
  5. $count = $Excel ->sheets[ 0 ][ ‘numRows’ ]; // узнаем количество строк в 1 листе
  6. for ( $rowNum = 1 ; $rowNum $count ; $rowNum ++) < echo $Excel ->sheets[ 0 ][ ‘cells’ ][ $rowNum ][ 1 ];> // циклом выводим все данные из первого с толбца
  7. for ( $rowNum = 1 ; $rowNum $count ; $rowNum ++) < echo $Excel ->sheets[ 0 ][ ‘cells’ ][ $rowNum ][ 2 ];> // циклом выводим все данные из второго с толбца
  8. ?>

Теперь в этом же документе напишем код для генерации Excel файла и создадим там две ячейки.

PHP+Excel быстро генерируем XLS файлы

Обсуждение статьи тематического каталога: Генерация XLS файлов на PHP (excel php web cgi)

Высказать мнение | Ответить | Правка | Cообщить модератору

Оглавление

  • Генерация XLS файлов на PHP (excel php web cgi), sil, 12:20 , 22-Янв-04, (1)
  • Генерация XLS файлов на PHP (excel php web cgi), уку, 23:41 , 06-Фев-04, (2)
  • Генерация XLS файлов на PHP (excel php web cgi), Andruhaa, 16:29 , 04-Ноя-04, (3)
    • Генерация XLS файлов на PHP (excel php web cgi), Виталий, 12:18 , 17-Янв-08, (5)
  • Генерация XLS файлов на PHP (excel php web cgi), Alexius, 14:30 , 15-Ноя-06, (4)
  • Генерация XLS файлов на PHP (excel php web cgi), thomas, 06:29 , 12-Мрт-08, (6)
  • Генерация XLS файлов на PHP (excel php web cgi), Pasha, 14:57 , 16-Сен-08, (7)
  • Генерация XLS файлов на PHP (excel php web cgi), SET, 17:18 , 28-Дек-08, (8)
  • Генерация XLS файлов на PHP (excel php web cgi), SET, 17:23 , 28-Дек-08, (9)
  • Генерация XLS файлов на PHP (excel php web cgi), Olfi, 22:04 , 17-Апр-09, ( 10 )
  • Генерация XLS файлов на PHP (excel php web cgi), nikalas9, 18:03 , 14-Май-09, ( 11 ) +1
  • Генерация XLS файлов на PHP (excel php web cgi), Sergey444, 17:49 , 17-Июн-10, ( 13 )
  • перенос текста по строкам, sterlibash, 19:06 , 20-Июл-10, ( 14 )

Сообщения по теме [Сортировка по времени | RSS]

извиените дурака, но вот я создал php файл с первым указанным здесь кодом.
в переменной $fname указал путь к файлу который я хочу получить в эксель а как это дело все запустить?
то есть мой фай называется excel.php и я его вызываю но ничего не происходит.
я раньше не работал с классами и не знаю как их вызывать к исполнению.
помогите полжалуйста

1. «Генерация XLS файлов на PHP (excel php web cgi)» + / –
Сообщение от sil on 22-Янв-04, 12:20
Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

2. «Генерация XLS файлов на PHP (excel php web cgi)» + / –
Сообщение от уку on 06-Фев-04, 23:41

Смотри здесь может подойдет там все довольно просто http://plati.ru/asp/pay.asp? >

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

А как форматировать ичейки например мне нужно увиличить длину поля

3. «Генерация XLS файлов на PHP (excel php web cgi)» + / –
Сообщение от Andruhaa on 04-Ноя-04, 16:29
Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

5. «Генерация XLS файлов на PHP (excel php web cgi)» + / –
Сообщение от Виталий (??) on 17-Янв-08, 12:18

или поставить «денежный» формат ячейки.

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

4. «Генерация XLS файлов на PHP (excel php web cgi)» + / –
Сообщение от Alexius (??) on 15-Ноя-06, 14:30

Достаточно простой класс, а то я тут с PEAR разбирался на эту тему . Если бы этот раньше нашёл, наверное склонился бы в его пользу но есть одно но . PEAR генерит файлы формата excel 95, а там ограничение на длину поля 255 символов :( . Этот класс генерит нормальный формат, но символы после 255 преврашаются в ячейке в `букозябры`. Может подскажете как этого избежать

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

6. «Генерация XLS файлов на PHP (excel php web cgi)» + / –
Сообщение от thomas on 12-Мрт-08, 06:29

Ребята, генерируйте xml. и любой формат поддерживается.

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

7. «Генерация XLS файлов на PHP (excel php web cgi)» + / –
Сообщение от Pasha (??) on 16-Сен-08, 14:57

А какнить тут можно страници разные в xsl создавать?

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

8. «Генерация XLS файлов на PHP (excel php web cgi)» + / –
Сообщение от SET on 28-Дек-08, 17:18

клас приведенный Евгением Климовым работает вполне сносно за что огромное спасибо ему. Единственное что хотелось бы ответить — кажется из методов добавления данных InsertNumber и
InsertText нужно убрать переход на след рядок:
$this->crow++;

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

9. «Генерация XLS файлов на PHP (excel php web cgi)» + / –
Сообщение от SET on 28-Дек-08, 17:23

простейший пример использования:

$excel = new PhpSimpleXlsGen();
$excel->default_dir = getcwd();
$excel->fname=’test.xls’;
$excel->InsertText(‘Kokashka’);
$excel->InsertText(‘Kokashka2’);
$excel->InsertText(‘Kokashka3’);
$excel->NewLine();
$excel->InsertText(‘Kokashka’);
$excel->InsertText(‘Kokashka2’);
$excel->InsertText(‘Kokashka3’);
$excel->NewLine();
$excel->InsertNumber(3);
$excel->InsertNumber(4);
$excel->InsertNumber(5);
$excel->SaveFile(‘test.xls’);

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

10 . «Генерация XLS файлов на PHP (excel php web cgi)» + / –
Сообщение от Olfi on 17-Апр-09, 22:04

а что за функция pack ? Это специальная функция для генерации excel ? Устанавливать куакие-нибудь библиотеки надо чтобы ексель генерировать?

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

11 . «Генерация XLS файлов на PHP (excel php web cgi)» +1 + / –
Сообщение от nikalas9 on 14-Май-09, 18:03

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

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

13 . «Генерация XLS файлов на PHP (excel php web cgi)» + / –
Сообщение от Sergey444 on 17-Июн-10, 17:49

вначале выдаем такие заголовки:

header(«Content-Disposition: attachment;filename=excel.xls»);
header(«Connection: Keep-Alive»);
header(«Content-Type: application/vnd.ms-excel»);

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

Высказать мнение | Ответить | Правка | ^ | Наверх | Cообщить модератору

14 . «перенос текста по строкам» + / –
Сообщение от sterlibash on 20-Июл-10, 19:06

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

PHPExcel Writer .xls файл генерирует ошибку Excel … как я могу изменить расширение .csv?

Я использую плагин WordPress, wpdatatables, который использует PHPExcel для экспорта файлов PDF, CSV и Excel.

В любом случае, при загрузке файла .xls с моего веб-сайта, когда я открываю его. Excel выдает ошибку «Формат файла и расширение файла« blahblah.xls »не совпадают. Файл может быть поврежден или небезопасен. Если вы не доверяете его источнику, не открывайте его ».

Конечно файл все равно открывается нормально, но я бы хотел избавиться от этой ошибки. Одна вещь, которую я заметил, это то, что экспорт .CSV, по-видимому, идентичен во всех отношениях, кроме расширения файла, и открывается без ошибок.

Как тот, кто не очень хорошо знаком с PHP, к сожалению, в каком направлении я должен искать, чтобы PHPExcel создавал только файлы .csv? Есть ли в PHPExcel определенная функция или каталог, отвечающий за вывод файла Excel?

Илон Маск рекомендует:  Что такое код show_source
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL