Универсальное чтение ячеек в PHPExcel / Хабр

Примеры по phpexcel. Создание excel-документа(.xlsx) на php

← разместить еще код

Пример создания excel-документа(.xlsx) на php с помощью класс PHPEXCEL. На странице представлен пример подключения класса и вызов методов для создания документа.

Также приведены примеры следующих возможностей: — заполнение ячеек — объединение ячеек — установка ширины колонки — выравнивание контента ячейки — заливка ячеек — рамки ячеек — размер шрифта в ячейках

Расшаренный код:

//———————————-Пример————————————— date_default_timezone_set(‘Europe/London’); if (PHP_SAPI == ‘cli’) die(‘This example should only be run from a Web Browser’); require_once $_SERVER[‘DOCUMENT_ROOT’]."/phpexcel/Classes/PHPExcel.php";/* подключаем класс */ $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties()->setCreator("афтор") ->setLastModifiedBy("Maarten Balliauw") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); $objWorkSheet = $objPHPExcel->createSheet(0);/* номер листа */ /* …. здесь заполняем лист …. */ header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’); header(‘Content-Disposition: attachment;filename="01simple.xlsx"’); header(‘Cache-Control: max-age=0’); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’); $objWriter->save(‘php://output’); //———————————————————————— //———————————-Примеры оформления листа————————————— /* заполняем ячейки */ $objWorkSheet->setCellValue(‘A1’, ‘1 этаж’); /* объединение ячеек */ $objWorkSheet->mergeCells(‘A1:A2’); /* устанавливаем ширину колонки */ $objWorkSheet->getColumnDimension(‘A’)->setWidth(9); /* выравниваем по центру (вертильно-горизонтально) */ $objWorkSheet->getStyle(‘A2’)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); /* заливаем ячейки нужным цветом — hex (b2b2b2) */ $objWorkSheet ->getStyle(‘A1:A2’) ->getFill() ->setFillType(PHPExcel_Style_Fill::FILL_SOLID) ->getStartColor() ->setRGB(‘hex’); /* устанавливаем бордер ячейкам */ $styleArray = array( ‘borders’ => array( ‘allborders’ => array( ‘style’ => PHPExcel_Style_Border::BORDER_THIN ) ) ); $objWorkSheet->getStyle(‘A1:A2’)->applyFromArray($styleArray); /* размер шрифта в ячейке */ $objWorkSheet->getStyle(‘A1’)->getFont()->setSize(13); /* Имя листа */ $objWorkSheet->setTitle(‘Прайс-лист ‘.$block_section);

PHPExcel и авто подбор высоты строки

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

Берём библиотеку PHPExcel версии 1.7.9 и начинаем ваять.

Пример «толстой» строки

<?php require_once ‘lib/PHPExcel/PHPExcel.php’; $objPHPExcel = new PHPExcel(); $objPHPExcel->getDefaultStyle()->getFont()->setName(«Calibri»)->setSize(11); $row = 1; // номер испытуемой строки $col = «A»; // идентификатор испытуемой колонки $width = 50; // ширина испытуемой ячейки // «толстый» текс $text = «Другими словами, чтобы большой текст полностью помещался в ячейке с фиксированной шириной, а не урезался до одной строки или выходил за рамки.»; $objPHPExcel->setActiveSheetIndex(0); $ActiveSheet = $objPHPExcel->getActiveSheet(); // задаём текст $ActiveSheet->setCellValue($col.$row, $text); // задаём ширину испытуемой ячейке $ActiveSheet->getColumnDimension($col)->setWidth($width); // отдаём файлом Excel 2007 header(«Pragma: public»); header(«Expires: 0»); header(«Cache-Control: must-revalidate, post-check=0, pre-check=0»); header(«Content-Type: application/force-download»); header(«Content-Type: application/octet-stream»); header(«Content-Type: application/download»); header(«Content-Disposition: attachment;filename=test1.xlsx»); header(«Content-Transfer-Encoding: binary «); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007’); $objWriter->save(‘php://output’); ?>

В итоге получаем

А хотелось бы

Решение для одинарных ячеек.

Загоняется текст в ячейку посредством автоматического подбора высоты строки и установки автоматического разбиения по строчкам текста:

// устанавливаем авто подбор высоты $objPHPExcel->getActiveSheet()->getRowDimension($row)->setRowHeight(-1); // и авто перенос текста $objPHPExcel->getActiveSheet()->getStyle($col.$row)->getAlignment()->setWrapText(true);

, где $objPHPExcel — объект класса PHPExcel, $row — номер строки, которой необходимо задать авто подбор высоты, $col — идентификатор столбца, например, А или Z.

Получаем в итоге то, что требовалось.

Всегда есть это «НО».

Что если ячейка у нас является объединением нескольких ячеек, а именно объединённых с помощью функции mergeCells()?

Получим следующее

А хотелось бы

Решение для авто подбора высоты строки для объединённых ячеек.

Для этого решения нам необходимо знать общую ширину ячейки, которая была собрана с помощью функции mergeCells. Если предыдущее решение работает только с одинарными ячейками, тогда почему бы нам не создать аналогичную, но уже одинарную ячейку в этой же строке, к примеру, в далёкой-далёкой колонке ZZ и задать ей авто перенос текста и скрыть. Тем самым мы заставим Excel установить высоту для данной строки.

$row = 1; // номер испытуемой строки $col1 = «A»; // идентификатор начальной колонки для объединения $col2 = «B»; // идентификатор конечной колонки для объединения $far_col = «ZZ»; // идентификатор «далёкой-далёкой» колонки $width = 50; // ширина испытуемой ячейки $default_width = 10; // ширина ячеек по умолчанию // «толстый» текст $text = «Другими словами, чтобы большой текст полностью помещался в ячейке с фиксированной шириной, а не урезался до одной строки или выходил за рамки.»; $ActiveSheet = $objPHPExcel->getActiveSheet(); // задаём текст для дубликата $ActiveSheet->setCellValue($far_col.$row, $text); // устанавливаем идентичную ширину дубликату и делаем невидимым $ActiveSheet->getColumnDimension($far_col)->setWidth($width + $default_width) ->setVisible(false); // задаём авто перенос дубликату $ActiveSheet->getStyle($far_col.$row)->getAlignment()->setWrapText(true);

Получаем в итоге то, что требовалось.

Осуждение статьи 🙂 б

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

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

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

Для решения задачи я нашел Microsoft Excel Spreadsheets и успешно его использовал. Сегодня расскажу и вам как просто это все дело организоввать. Microsoft Excel Spreadsheets состоит из 2 файлов oleread.inc и reader.php.

Кидаем эти файлы в папку с вашим проектом (файлы на всякий случай я приложу).

Далее в иполняем файле необходимо пишем следующий код:

require_once (‘reader.php’); // подключаем класс
$Excel = new Spreadsheet_Excel_Reader(); // создаем объект
$Excel->setOutputEncoding(‘utf-8’); // устанавливаем кодировку
$Excel->read(‘excel.xls’); // открываем файл
$line=$Excel->sheets[0][‘cells’];

 Собственно и все. Переменная $line будет содержать в себе массив строк и ячеек. К примеру вот так:

echo $line[1][1];

 мы выведем на экран ячейку у которой кодината равна x=1 y=1, хотя скорее всего я ошибаюсь уже сейчас не помню, вот так выведет содержимое ячейки у которой x=1 и y=1

echo $line[0][0];

 все же это массив и разумно предпологать что он начинается с нуля.

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

PHPExcel — хорошая и удобная библиотека для генерации Excel файлов из PHP.

При написании скрипта для генерации прайс-листа появилась задача выводить в том числе и колонку с ценами (сюрприз 🙂 ).

Самый простой способ — это преобразовать число через функцию number_format, приписать в конце «руб» и записать получившуюся строку в ячейку.
Способ простой, быстрый и неправильный.

Неправилен он потому что в таком случае значение ячейки воспринимается Excel как строка. А значит его уже нельзя использовать в формулах. Да и если вдруг кто-то захочет парсить этот прайс-лист, то такой подход добавит ему проблем.

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

Для задание формата чисел в PHPExcel есть ряд констант. Например константа PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1 позволяет показывать в ячейке число с двумя десятичными знаками, с точкой в виде разделителя и с запятой для разделителя тысяч.

С префиксом «руб». Все оказалось чуть сложнее. Хорошую услугу оказал LibreOffice — если в нем задавать формат ячейки, то тут же виден пользовательский тип формата — т.е. некоторая строка, задающая формат.

В результате я создал свой свобственный формат валюты (руб) для PHPExcel:

define(«PRICE_FORMAT», PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1. «[\$ руб.-419]»);

Используется он соответственно при задании стиля ячейки:

$sheet->setCellValue(«C» . $cnt, floatval($item[«price»])); $sheet->getStyle(«C» . $cnt)->getNumberFormat()->setFormatCode(PRICE_FORMAT);

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

Запись опубликована в рубрике Полезности, Работа, Фриланс с метками phpexcel, прайс. Добавьте в закладки постоянную ссылку.

После большого количества проб и ошибок, я все еще не могу найти способ обхода, чтобы объединить ячейки, чтобы иметь высоту AutoFit.

Я пробовал подход, основанный на немного кода VBA, который я нашел на этом сайте: https://groups.google.com/forum/?fromgroups=#!topic/microsoft.public.excel.programming/pcvg7o5sKhA

Следующий код вставляет текст, обертывает его и меняет ширину ячейки (A1) на общую ширину объединенных ячеек, которые я хочу.

Затем он объединяет ячейки и устанавливает столбец A обратно вниз к исходной ширине. $note — любая длинная строка текста. $vAlignTop — это массив, устанавливающий выравнивание текста в верхней части ячейки.

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

У кого-нибудь есть идеи? Я не против, чтобы жестко кодировать ширину столбца, я просто хочу, чтобы высота реагировала на текст.

Спасибо заранее.

phpphpexcel

задан Law Hop 09 нояб. '12 в 20:32

источникподелиться

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

Закрыть меню