phpexcel: писать данные в Excel с помощью PHP и читать из Excel

Задача писать данные в Excel с помощью PHP и читать из Excel

Выбор пал на библиотеку phpexcel

Как сказал Гагарин — "Поехали!"

//Скачать библиотеку — http://phpexcel.codeplex.com/
//Нашел русскую документацию — http://www.cyberforum.ru/php-beginners/thread1074684.html
//Подключаем скачаную библиотеку
include("Classes/PHPExcel.php");

if($_GET['do']==1){
    //Запрос и выборка данных из базы
    //В моем примере я подключаюсь к базе MS SQL
    //В Вашем примере вы указываете свое подключение MySQL\MS SQL\Postgree…

    include("../../base/ip_acct.php");

    $sql = "select TOP 5 id, name, info FROM ip_acct.dbo.points WHERE client_id = 780 AND info IS NOT NULL";
    $result = sqlsrv_query( $iconn, $sql );

    $l = array();

    while($r = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC)){
        //Результат выборки данных в массиве $l
        $l[] = $r;
    };

    //————————————
    //1 Часть: запись в файл

    //Создание объекта класса библиотеки
    $objPHPExcel = new PHPExcel();

    //Указываем страницу, с которой работаем
    $objPHPExcel->setActiveSheetIndex(0);

    //Получаем страницу, с которой будем работать
    $active_sheet = $objPHPExcel->getActiveSheet();

    //Создание новой страницы(пример)
    //$objPHPExcel->createSheet();

    //Ориентация и размер страницы
    // $active_sheet->getPageSetup()
        // ->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
    $active_sheet->getPageSetup()
        ->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
    $active_sheet->getPageSetup()
        ->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
        
    //Имя страницы
    $active_sheet->setTitle("Данные из docs");
        
    //Ширина стобцов
    $active_sheet->getColumnDimension('A')->setWidth(8);
    $active_sheet->getColumnDimension('B')->setWidth(10);
    $active_sheet->getColumnDimension('C')->setWidth(90);

    //Объединение ячеек
    $active_sheet->mergeCells('A1:C1');    

    //Высота строки
    $active_sheet->getRowDimension('1')->setRowHeight(30);

    //Вставить данные(примеры)
    //Нумерация строк начинается с 1, координаты A1 — 0,1
    $active_sheet->setCellValueByColumnAndRow(0, 1, 'Сегодня '.date('d-m-Y'));
    $active_sheet->setCellValue('A3', 'id');
    $active_sheet->setCellValue('B3', 'name');
    $active_sheet->setCellValue('C3', 'info');

    //Вставка данных из выборки
    $start = 4;
    $i = 0;
    foreach($l as $row_l){
        $next = $start + $i;
        
        $active_sheet->setCellValueByColumnAndRow(0, $next, $row_l['id']);
        $active_sheet->setCellValueByColumnAndRow(1, $next, $row_l['name']);
        $active_sheet->setCellValueByColumnAndRow(2, $next, $row_l['info']);
        
        $i++;
    };

    //Отправляем заголовки с типом контекста и именем файла
    header("Content-Type:application/vnd.ms-excel");
    header("Content-Disposition:attachment;filename='simple.xlsx'");

    //Сохраняем файл с помощью PHPExcel_IOFactory и указываем тип Excel
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

    //Отправляем файл
    $objWriter->save('php://output');
};

if($_GET['do']==2){
    //————————————
    //2 Часть: чтение файла
    //Файл лежит в директории веб-сервера!
    $objPHPExcel = PHPExcel_IOFactory::load("simple.xlsx");

    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet)
    {
        //Имя таблицы
        $Title              = $worksheet->getTitle();
        //Последняя используемая строка
        $lastRow         = $worksheet->getHighestRow();
        //Последний используемый столбец
        $lastColumn      = $worksheet->getHighestColumn();
        //Последний используемый индекс столбца
        $lastColumnIndex = PHPExcel_Cell::columnIndexFromString($lastColumn);
        
        echo $Title.'<table border="1" cellspacing="0"><tr>';
        for ($row = 1; $row <= $lastRow; ++$row)
        {
            echo '<tr>';
            for ($col = 0; $col < $lastColumnIndex; ++ $col) 
            {
                $val = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
                echo '<td>'.$val.'&nbsp;</td>';
            };
            echo '</tr>';
        };
        echo '</table>';
    };
};
    
exit();

На этом — все, видео выложу на своем канале в YouTube

Комментарии пользователей

Когда вы впервые создаете экземпляр $objPHPExcel, он уже имеет один лист (лист 0); вы добавите новый лист (который станет листом 1), но установив активный лист на лист $i (когда $i равен 0)… поэтому вы переименовываете и заполняете исходный лист, созданный при создании экземпляра $objPHPExcel а не тот, который вы только что добавили… это ваш заголовок «0».

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

Итак, первая итерация, у вас уже есть sheet0, добавьте новый лист в оба индекса 1 и 2 и отредактируйте/запишите лист 0.

Генерация прайс-листа в формате Excel при помощи PHP. Phpexcel

Вторая итерация, вы добавляете новый лист в оба индекса 3 и 4, а также редактируете/заголовок листа 1, но из-за того, что у вас один и тот же лист в индексах 1 и 2, это эффективно записывает на лист по индексу 2. Третья итерация, вы добавляете новый лист в индексы 5 и 6 и редактируете/заголовок 2, перезаписываете свое раннее редактирование/заголовок листа 1, который действовал против листа 2 вместо… и т.д.

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);

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

Примеры по 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);