Редактирование файла в PHPExcel

17.12.2022

4680

Иногда бывают случаи, когда нужно заполнить некий уже сформатированный xls-файл в PHP. Библиотека PHPExcel (PHPExcel.zip) тоже это умеет, включая дописывание, добавление и удаление строк.

Для примера заполним бланк счета на оплату в формате xls и xlsx:

Формат xls

schet.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();

PHP

Формат xlsx

schet.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();

PHP