Содержание
PhpSpreadsheet: Работа с excel файлами (запись и чтение данных)
На смену PHPExcel пришла мощная библиотека PhpSpreadsheet от тех же разработчиков.
Создание exсel файла
use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setTitle('Worksheet 1'); $sheet->setCellValue('A1', 'Hello World !'); // Writer можно создать так: $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); //$writer = new Xlsx($spreadsheet); $writer->save($path . '/export/worksheet__' . date('Ymd_h:i:s') . '.xlsx'); $spreadsheet->disconnectWorksheets();Запись данных в exсel с помощью итератора
Более информативный пример со стилями документа, строк, ячеек по условию.
use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Style\Color; use PhpOffice\PhpSpreadsheet\Style\ConditionalFormatting\Wizard; use PhpOffice\PhpSpreadsheet\Style\Fill; use PhpOffice\PhpSpreadsheet\Style\Style; try < $path = $path = realpath(__DIR__); // Способ #1. Стили для шапки $headerStyles = [ 'font'=>[ 'color'=>[ 'rgb' => '000' ], 'bold' => true, 'size' => 13 ], 'fill'=>[ 'fillType' => Fill::FILL_SOLID, 'startColor' => [ 'rgb' => Color::COLOR_CYAN ] ], ]; // Способ #2. Стили для данных $redStyle = new Style(false, true); $redStyle->getFill() ->setFillType(Fill::FILL_SOLID) ->getStartColor()->setARGB(Color::COLOR_RED); // Создание документа, листа и "писателя" $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); // Название листа $sheet->setTitle('Worksheet 1'); $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // Стили документа по умолчанию $spreadsheet->getDefaultStyle() ->getFont() ->setName('Arial') ->setSize(12); // Дополнительные стили // . // Выравнивание по центру в строке //$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); // Установка стилей для шапки $spreadsheet->getActiveSheet()->getStyle('A1:F1')->applyFromArray($headerStyles); // Шапка $headerItems = [ 'First Name', 'Last Name', 'Email', 'Gender', 'Age', 'Class' ]; // Добавление шапки $sheet->fromArray($headerItems); // A1 start // Данные из файла для примера (чаще всего из базы данных) $file = file_get_contents('student-data.json'); $studentData = json_decode($file,true); // Расчёт крайней правой точки листа $extremeCellValue = $sheet ->getCellByColumnAndRow( count($headerItems), count($studentData) + 1 ) ->getCoordinate(); // Форматирование ячеки по условию (если студенту меньше 18 лет) $conditionalStyles = []; $wizardFactory = new Wizard("A1:$extremeCellValue"); $textWizard = $wizardFactory->newRule(Wizard::CELL_VALUE); $textWizard->lessThan(18) ->setStyle($redStyle); $conditionalStyles[] = $textWizard->getConditional(); $spreadsheet->getActiveSheet() ->getStyle($textWizard->getCellRange()) ->setConditionalStyles($conditionalStyles); // Данные вставлять со 2-й строки $row = 2; foreach ($studentData as $student): unset($student['id']); // Исключаем id из данных (если необходимо) $sheet->fromArray($student, null, 'A'.$row); // A2 start $row++; endforeach; // Сохранение файла $writer->save($path . '/export/students_list__' . date('Ymd_h:i:s') . '.xlsx'); > catch (Exception $e) < $error = date('Y/m/d H:i:s') . ': ' . $e->getMessage() . PHP_EOL; error_log($error, 3, $path . '/runtime/app.log'); die($e->getMessage() . PHP_EOL); >Чтение данных из файла exсel
use PhpOffice\PhpSpreadsheet\IOFactory; $reader = IOFactory::createReader('Xlsx'); $spreadsheet = $reader->load('students_list.xlsx'); // Только чтение данных $reader->setReadDataOnly(true); // Количество листов $sheetsCount = $spreadsheet->getSheetCount(); // Данные в виде массива $data = $spreadsheet->getActiveSheet()->toArray(); foreach ($data as $item): var_dump($item); endforeach;Редактирование файла в PHPExcel
Иногда бывают случаи, когда нужно заполнить некий уже сформатированный xls-файл в PHP. Библиотека PHPExcel (PHPExcel.zip) тоже это умеет, включая дописывание, добавление и удаление строк.
Для примера заполним бланк счета на оплату в формате xls и xlsx:
Формат xls
//spl_autoload_unregister('autoload'); require_once __DIR__ . '/PHPExcel/Classes/PHPExcel.php'; require_once __DIR__ . '/PHPExcel/Classes/PHPExcel/Writer/Excel5.php'; require_once __DIR__ . '/PHPExcel/Classes/PHPExcel/IOFactory.php'; $objReader = PHPExcel_IOFactory::createReader('Excel5'); $objPHPExcel = $objReader->load(__DIR__ . '/schet.xls'); $sheet = $objPHPExcel->getActiveSheet(); // Дописываем номер накладной $sheet->setCellValue('B13', $sheet->getCell('B13')->getValue() . '123 от ' . date('d.m.Y')); // Покупатель $sheet->setCellValue('F19', 'ООО "Рога и копыта"'); // Функция для копирования строк function copyRowFull(&$sheet, $from, $to) < $sheet->getRowDimension($to)->setRowHeight($sheet->getRowDimension($from)->getRowHeight()); $lastColumn = $sheet->getHighestColumn(); ++$lastColumn; for ($c = 'A'; $c != $lastColumn; ++$c) < $cell_from = $sheet->getCell($c . $from); $cell_to = $sheet->getCell($c . $to); $cell_to->setXfIndex($cell_from->getXfIndex()); $cell_to->setValue($cell_from->getValue()); $cell_from = $sheet->getCell($c . $from); if($cell_from->isMergeRangeValueCell()) < $col = PHPExcel_Cell::coordinateFromString(PHPExcel_Cell::splitRange($cell_from->getMergeRange())[0][1])[0]; $sheet->mergeCells($c . $to . ':' . $col . $to); > > > // Заполняем таблицу с товарами $prods = array( array( 'sku' => '8545775', 'name' => 'Боксерские перчатки GREEN HILL Super Star (без марки AIBA)', 'price' => '6060', 'count' => '2' ), array( 'sku' => '865645', 'name' => 'Боксерский мешок 120X35, 46 кг', 'price' => '9900', 'count' => '1' ), array( 'sku' => '865643', 'name' => 'Кронштейн для боксерского мешка', 'price' => '4800', 'count' => '3' ), ); $line = 22; $total = 0; $n = 1; foreach ($prods as $row) < $i = $line + 1; $sheet->insertNewRowBefore($i, 1); copyRowFull($sheet, $line, $i); $sheet->setCellValue('B' . $line, ++$n); $sheet->setCellValue('D' . $line, $row['name']); $sheet->setCellValue('S' . $line, $row['count']); $sheet->setCellValue('W' . $line, 'шт'); $sheet->setCellValue('Z' . $line, $row['price']); $sheet->setCellValue('AF' . $line, $row['price'] * $row['count']); $total += $row['price'] * $row['count']; $line++; > // Удаляем лишнюю строку т.к. первая строка уже была $sheet->removeRow($line); // Итого $sheet->setCellValue('AF' . ($line + 1), $total); // В том числе НДС $sheet->setCellValue('AF' . ($line + 2), ''); // Всего к оплате: $sheet->setCellValue('AF' . ($line + 3), $total); // Всего наименований $sheet->setCellValue('B' . ($line + 4), 'Всего наименований ' . count($prods) . ', на сумму ' . $total . ' руб.'); // Сумма прописью function str_price($value) < $value = explode('.', number_format($value, 2, '.', '')); $f = new NumberFormatter('ru', NumberFormatter::SPELLOUT); $str = $f->format($value[0]); $str = mb_strtoupper(mb_substr($str, 0, 1)) . mb_substr($str, 1, mb_strlen($str)); $num = $value[0] % 100; if ($num > 19) < $num = $num % 10; >switch ($num) < case 1: $rub = 'рубль'; break; case 2: case 3: case 4: $rub = 'рубля'; break; default: $rub = 'рублей'; >return $str . ' ' . $rub . ' ' . $value[1] . ' копеек.'; > $sheet->setCellValue('B' . ($line + 5), str_price($total)); // Сохранение в файл //$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); //$objWriter->save(__DIR__ . '/Счет.xls'); // Вывод в браузер header("Expires: Mon, 1 Apr 1974 05:00:00 GMT"); header("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT"); header("Cache-Control: no-cache, must-revalidate"); header("Pragma: no-cache"); header("Content-type: application/vnd.ms-excel"); header("Content-Disposition: attachment; filename=Счет.xls"); $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); $objWriter->save('php://output'); exit(); Формат xlsx
//spl_autoload_unregister('autoload'); require_once __DIR__ . '/PHPExcel/Classes/PHPExcel.php'; require_once __DIR__ . '/PHPExcel/Classes/PHPExcel/Writer/Excel2007.php'; require_once __DIR__ . '/PHPExcel/Classes/PHPExcel/IOFactory.php'; $objReader = PHPExcel_IOFactory::createReader('Excel2007'); $objPHPExcel = $objReader->load(__DIR__ . '/schet.xlsx'); $sheet = $objPHPExcel->getActiveSheet(); // Дописываем номер накладной $sheet->setCellValue('B13', $sheet->getCell('B13')->getValue() . '123 от ' . date('d.m.Y')); // Покупатель $sheet->setCellValue('F19', 'ООО "Рога и копыта"'); // Функция для копирования строк function copyRowFull(&$sheet, $from, $to) < $sheet->getRowDimension($to)->setRowHeight($sheet->getRowDimension($from)->getRowHeight()); $lastColumn = $sheet->getHighestColumn(); ++$lastColumn; for ($c = 'A'; $c != $lastColumn; ++$c) < $cell_from = $sheet->getCell($c . $from); $cell_to = $sheet->getCell($c . $to); $cell_to->setXfIndex($cell_from->getXfIndex()); $cell_to->setValue($cell_from->getValue()); $cell_from = $sheet->getCell($c . $from); if($cell_from->isMergeRangeValueCell()) < $col = PHPExcel_Cell::coordinateFromString(PHPExcel_Cell::splitRange($cell_from->getMergeRange())[0][1])[0]; $sheet->mergeCells($c . $to . ':' . $col . $to); > > > // Заполняем таблицу с товарами: $prods = array( array( 'sku' => '8545775', 'name' => 'Боксерские перчатки GREEN HILL Super Star (без марки AIBA)', 'price' => '6060', 'count' => '2' ), array( 'sku' => '865645', 'name' => 'Боксерский мешок 120X35, 46 кг', 'price' => '9900', 'count' => '1' ), array( 'sku' => '865643', 'name' => 'Кронштейн для боксерского мешка', 'price' => '4800', 'count' => '3' ), ); $line = 22; $total = 0; $n = 1; foreach ($prods as $row) < $i = $line + 1; $sheet->insertNewRowBefore($i, 1); copyRowFull($sheet, $line, $i); $sheet->setCellValue('B' . $line, ++$n); $sheet->setCellValue('D' . $line, $row['name']); $sheet->setCellValue('S' . $line, $row['count']); $sheet->setCellValue('W' . $line, 'шт'); $sheet->setCellValue('Z' . $line, $row['price']); $sheet->setCellValue('AF' . $line, $row['price'] * $row['count']); $total += $row['price'] * $row['count']; $line++; > // Удаляем лишнюю строку т.к. первая строка уже была $sheet->removeRow($line); // Итого $sheet->setCellValue('AF' . ($line + 1), $total); // В том числе НДС $sheet->setCellValue('AF' . ($line + 2), ''); // Всего к оплате $sheet->setCellValue('AF' . ($line + 3), $total); // Всего наименований $sheet->setCellValue('B' . ($line + 4), 'Всего наименований ' . count($prods) . ', на сумму ' . $total . ' руб.'); // Сумма прописью function str_price($value) < $value = explode('.', number_format($value, 2, '.', '')); $f = new NumberFormatter('ru', NumberFormatter::SPELLOUT); $str = $f->format($value[0]); $str = mb_strtoupper(mb_substr($str, 0, 1)) . mb_substr($str, 1, mb_strlen($str)); $num = $value[0] % 100; if ($num > 19) < $num = $num % 10; >switch ($num) < case 1: $rub = 'рубль'; break; case 2: case 3: case 4: $rub = 'рубля'; break; default: $rub = 'рублей'; >return $str . ' ' . $rub . ' ' . $value[1] . ' копеек.'; > $sheet->setCellValue('B' . ($line + 5), str_price($total)); // Сохранение в файл //$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); //$objWriter->save(__DIR__ . '/Счет.xlsx'); // Вывод в браузер header("Expires: Mon, 1 Apr 1974 05:00:00 GMT"); header("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT"); header("Cache-Control: no-cache, must-revalidate"); header("Pragma: no-cache"); header("Content-type: application/vnd.ms-excel" ); header("Content-Disposition: attachment; filename=Счет.xlsx"); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007"); $objWriter->save('php://output'); exit();Edit XLSX in PHP
Hi there is a way to edit uploaded Excel (xlsx) document in PHP. Document is very simple (table with item names, price, quantity). I suppose PHPExcel able only read document but not edit? Any advices?
Why do you think PHPExcel will be unable to edit it? They have a PHPExcel_Writer_Excel2007 class that seems to handle xlsx correctly.
Simply reading the PHPExcel site front page should have told you that it’s read and write: phrases like «allow you to write to and read from» are the clue we give.
3 Answers 3
You can use PHPExcel to edit a document as well, check out these threads for more information:
Oh thanks! Its can be confusing when using different documents for upload. I’ll try to explain crealry. There is a system which generates excel documents (there is three templates with different layouts but generates same table in different style) so in provided examples the edited cells was chosen manualy ->setCellValue(‘A1’, ‘Hello’) , So how to find out which field has the information, and after editing how to find out which cell was edited and save the changes?
@DeividasJuškevičius: You can look at official docs of PHPExcel or their support forum for more info actually.
OpenTBS can edit XLSX documents using the technique of templates.
By the way, it can also edit DOCX, PPTX, ODT, ODS, .
Please try this approach. For Example, I want to edit/update the column(expiryDate) in the sheet (sheet1).
$localFilePath = "C:/xampp/htdocs/Projects/abc.xlsx"; $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); $reader->setLoadSheetsOnly(["sheet1"]); $spreadsheet = $reader->load($localFilePath); $worksheet = $spreadsheet->setActiveSheetIndex(0); $column = NULL; foreach($worksheet->getRowIterator() as $index => $row) < $cellIterator = $row->getCellIterator(); $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells, foreach($cellIterator as $subIndex => $cell) < $value = $cell->getValue(); if($index == 1 && $value == "expiryDate") < $column = $subIndex; >if((empty($value) || $value == "") && $column == $subIndex && $index != 1)< $cell->setValue("2050-12-31 00:00:00"); > > > $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Xlsx"); $writer->save($localFilePath);