alhimik1986 / php-excel-templator Goto Github PK
View Code? Open in Web Editor NEWPHP Spreadsheet extension to the export excel file from excel template
License: MIT License
PHP Spreadsheet extension to the export excel file from excel template
License: MIT License
They've changed the signature of public function insertNewBefore($pBefore, $pNumCols, $pNumRows, Worksheet $pSheet)
by adding return type hint of void
.
This projects should add a version restriction for the library:
"require": {
"phpoffice/phpspreadsheet": ">=1.4 <1.13"
}
Meanwhile problem can be solved by composer require phpoffice/phpspreadsheet:1.12.*
in one's own project which will override the broken version.
Форматирование ячеек в элементах даты не работает эффективно.
Например, когда yyyy / mm / dd установлен в ячейке, в которой задано {current_date},
В следующем коде это будет гггг - мм - дд.
Можно ли применить форматирование ячеек, установленное в Excel?
PhpExcelTemplator::saveToFile($import , $export, [
'{current_date}' => date('Y-m-d')
]);
Добрый день!
Очень хорошая, простая в освоении библиотека получилась.
Пробовал работать в связке с MS Excel - всё устраивает.
Но есть острая необходимость создавать файлы формата ODS из шаблона ODS...
Читал. что PhpExcel поддерживает эти и другие схожие форматы файлов. Где можно, если возможно, сделать такие настройки в Вашем проекте?
Спасибо!
Здравствуйте есть ли возможность добавлять изображения в таблицу?
I want to update a template with graph inside.
Then i use for loading
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setIncludeCharts(true);
$spreadsheet = $reader->load($xlsFile);
And i update/hack alhimik1986\PhpExcelTemplator\getWriter to
protected static function getWriter(Spreadsheet $spreadsheet)
{
$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
$writer->setIncludeCharts(true);
return $writer;
}
But graph still don't shown … :/ any idea ?
Данные:
$data = [
'android' => [
['lenovo 898t', 2, 50],
['htc one', 3, 100]
],
'apple' => [
['iphone 4s', 4, 100],
['iphone 5s', 6, 200]
]
];
В документации не нашел как сформировать объединяющую ячейку по вертикали или горизонтали. @alhimik1986
example.xlsx
I made a small template with your basic template as base and it's working
But if the fields are in the Header of the page in Excel, fields are not updated, while the same fields are updated if they are on the page.
Just to be sure I have put the same fields in the footer and in a normal cell
Добрый день.
А как можно ошибки отловить?
PhpExcelTemplator::saveToFile($templateFile, $fileName, $params); - нет проблем.
PhpExcelTemplator::saveToFile($templateFile, $fileName, $params, $callbacks); - нет файла.
And it is filled using the CellSetterArrayValueSpecial
setter, I would like it to carry the formula down with the rows too.
Currently you end up with:
Which is not great as the formula is then not used.
I was wondering if it was possible to do this using callbacks on say Threshold but it would need to know how many rows to fill it down and I can't see how to get that in the params.
Any suggestions
Вставленные рисунки пропадают.
I'm a beginner, and I'm not able to generate the result within the brackets []. Would anyone know what I should do?
foreach($sql as $sql){
$input = $sql['CGS_EMPRESA_CLIENTE_CTR_PPU_COD'];
}
PhpExcelTemplator::saveToFile('../excel_templates/'.$file_template, '../temp/'.$file_export,
[
'{Cliente}' => $row_ctr['CGS_EMPRESA_CLIENTE_NOME'],
'{Contrato_Num}' => $row_ctr['CGS_EMPRESA_CLIENTE_CTR_NUMERO'],
'{Contrato_Nome}' => $row_ctr['CGS_EMPRESA_CLIENTE_CTR_NOME'],
'[Codigo]' => [array($input)], //['Value1','Value2']
]);
//Download
header("Location: ../temp/$file_export");
}
Подскажите пожалуйста, в чем может быть проблема. Ajax'ом вызываю файл с таким содержимым
require_once('vendor/autoload.php');
$filename = date("Y-m-d");
$templateFile = $_SERVER['DOCUMENT_ROOT'].'templates/shabl_nacl.xlsx';
$path=$_SERVER['DOCUMENT_ROOT'];
$source=$_SERVER['DOCUMENT_ROOT']."/templates/shabl_nacl.xlsx";
$now = new DateTime();
$params = [
'{clinfo}' => new ExcelParam(CellSetterStringValue::class, $now->format('d-m-Y')),
'{docnm}' => new ExcelParam(CellSetterStringValue::class, 'Sales department'),
];
PhpExcelTemplator::saveToFile($templateFile, $fileName, $params);
по итогу должен сохраниться файл, но этого не происходит. Что я делаю не так?
Originally posted by @Liphanes in #1 (comment)
Currently, phpdoc comments are in Russian, make hard to understand.
Are you OK for an english translation ?
Здравствуйте.
Во-первых, файл увеличивается более чем в 2 раза после обработки библиотекой (5 кбайт шаблон, 12 кбайт обработанный, хотя в него вставляется всего пара слов).
Во-вторых, при открытии обработанного файла выскакивает следующая ошибка:
"Предупреждение при загрузке документа test2.xlsx:
Данные не могут быть загружены полностью, т.к. превышено максимальное количество столбцов в листе."
Но при этом файл все равно открывается, переменные заменяются, и никаких излишних столбцов нет.
Для открытия использую LibreOffice Calc
Hi, I receive this error message when I try to render a template with multiple array type columns at once..
Error message: Invalid cell coordinate A-1 at
[descriptions] [quantity] [cost]
example of payload.
$array = [
{descriptions}: ['first','second','third],
{quantity}: [1, 1, 1],
{cost}: [0, 0, 0],
]
If in the template is left only one column it is rendered correctly.
It happens in PhpOffice\PhpSpreadsheet::adjustRowDimensions
using atm alhimik1986/php-excel-templator 1.0.11,
phpoffice/phpspreadsheet : 1.22,
Hello,
In the function saveSpreadsheetToFile, you need to change
if (isset($events['beforeSave']) AND is_callable($events['beforeSave'])) {
$events['beforeSave']($spreadsheet, $writer);
}
to
if (isset($events[self::BEFORE_SAVE]) AND is_callable($events[self::BEFORE_SAVE])) {
$events[self::BEFORE_SAVE]($spreadsheet, $writer);
}
Otherwise BEFORE_SAVE event are never called.
Добрый день, возможно ли такое? А то например названия товаров бывают длинные и обрезаются.
I am generating an excel and in the parameters I pass an array with 60 items but the excel is generated with approximately 500 unnecessary rows
my code:
`$academicperiods = Academicperiod::where('status', 'Vigente')->firstOrFail();
$periodname=$academicperiods->period_name;
$periodcode = $academicperiods->period_code;
$finalperiodname='ASIGNATURAS A APERTURARSE EN EL PERÍODO '.$periodname.' ('.$periodcode.') ';
//ASIGNATURAS A APERTURARSE EN EL PERÍODO MARZO 2020 - JULIO 2020 (SI-2020)
$params = [
'{period}'=>new ExcelParam(CellSetterStringValue::class, $finalperiodname),
'[subjectgrade]' => new ExcelParam(CellSetterArrayValue::class, $subjectgrade),
'[subjectdepartment]' => new ExcelParam(CellSetterArrayValue::class, $subjectdepartment),
'[subjectname]' => new ExcelParam(CellSetterArrayValue::class, $subjectname),
'[subjectcredits]' => new ExcelParam(CellSetterArrayValue::class, $subjectcredits),
'[total]' => new ExcelParam(CellSetterArrayValue::class, $total),
'[spaces]' => new ExcelParam(CellSetterArrayValue::class, $spaces),
'[observations]' => new ExcelParam(CellSetterArrayValue::class, $observations)
];
$saveAs = 'storage/files/3/Proyecciones de Matrícula/ReporteProyecciónMatrícula.xlsx';
PhpExcelTemplator::saveToFile($documentTemplate, $saveAs, $params);`
all the arrays have 60 items but my excel is exported with 500+-
Allow (more easily) export graph
Currently : to import/export graph : a possible solution was using \PhpOffice\PhpSpreadsheet\IOFactory directly for writer.
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setIncludeCharts(true);
$spreadsheet = $reader->load($xlsFile);
$params = require('./params.php'); // Or any other way to do param
foreach($spreadsheet->getSheetNames() as $sheetName) {
$sheet = $spreadsheet->getSheetByName($sheetName);
$templateVarsArr = $sheet->toArray();
PhpExcelTemplator::renderWorksheet($sheet, $templateVarsArr, $params, array(), array());
}
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->setIncludeCharts(true);
header('Content-Disposition: attachment; filename="'.$fileName);
header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Pragma: public');
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate');
$writer->save('php://output');
Maybe adding an option on outputSpreadsheetToFile and saveSpreadsheetToFile ?
I don't wee how to do it in events, right ?
Есть ли процедура, чтобы сделать $ writer-> setPreCalculateFormulas (false) с PhpExcelTemplator :: saveToFile ()?
I tried to use your library, but with the xlsx template with an image inside, after saving the image disappears
Not sure if this bug has appeared/fixed somewhere, but I couldn't find any help, and I've been trying it all day:
I have this loop in my code:
foreach($report as $prod){ $sheetParams['{id_produto}'][$idx] = $prod['codigo']; $sheetParams['{product_name}'][$idx] = $prod['titulo']; $sheetParams['preco_compra'][$idx] = $prod['preco_compra']; $sheetParams['preco_venda'][$idx] = $prod['precoVenda']; $sheetParams['qde'][$idx] = (int)$prod['qde']; $sheetParams['[subtotalcompra]'][$idx] = '=E'. ($idx+6) . '*C'.($idx+6); $sheetParams['[subtotalvenda]'][$idx] = '=E'. ($idx+6) . '*D'.($idx+6); $sheetParams['[lucro_bruto]'][$idx] = '=G'. ($idx+6) . '-F'.($idx+6); $idx++; }
After filling the arrays, I send it using
PhpExcelTemplator::saveToFile($templateFile, $fileName, $sheetParams);
one of the arrays ([subtotalcompra]) gets the values:
[[subtotalcompra]] => Array ( [0] => '=E6*C6' [1] => '=E7*C7' )
whereas the other ([subtotalvenda]) is
[[subtotalvenda]] => Array ( [0] => '=E6*D6' [1] => '=E7*D7' )
The template file uses them in 2 columns, which should be the multiplication formulas for each row, but only the first row gets the correct values, '=E6*C6' and '=E6*D6'
The other cells get their values as '=E9*D9' and the cell to the right has '=E10*D10'
The indexes for each cell are changed as if the cell count somehow interferes in the string used for the formulas.
Even weirder is that the values are changed as if some regex acts on the string, changing every cell in the resulting spreadsheet.
I could even trace some data change into phpoffice/phpspreadsheet but was unable to find what could be happening before the data changed.
Вот пример разметки
A | B | C | D | E |
---|---|---|---|---|
[] | [] | [>] | [<] | [] |
C и D обеденены.
Если у нас больше одной строки, то все следующие строки будут разъеденные.
На выходе получим что колонка D будет пустая
A | B | C | D | E |
---|---|---|---|---|
[] | [] | [>] | [<] | [] |
[] | [] | [><] | [] | |
[] | [] | [><] | [] | |
[] | [] | [><] | [] |
Please check this template. The first merged cells work correctly while the send merged cells doesn't. The merged cells will be broked after exporting.
Thank you,
Зачем в конце этих функций exit?
PhpExcelTemplator::outputSpreadsheetToFile
PhpExcelTemplator::saveSpreadsheetToFile
Еще вопрос появился: есть возможность использовать стандартные формулы excel?
Например, одна ячейка заполняется по шаблону, соседняя вычисляется по формуле, заданной на этапе редактирования шаблона.
При вставке в шаблон вставляется X строк с данными и потом еще столько же пустых строк
Максимально простой пример, где видно этот баг
<?php
namespace App\controllers;
use alhimik1986\PhpExcelTemplator\PhpExcelTemplator;
/*
Шаблон Excel максимально простой
в ячейке A1 Имя
в ячейке A2 [NAME]
*/
class Excel
{
public function index(): void
{
$templateFile = './template/register.xlsx';
$fileName = './register.xlsx';
$data['[NAME]'] = [
'Ivan',
'Nata',
'Irina',
];
PhpExcelTemplator::saveToFile($templateFile, $fileName, $data);
}
}
Excel удалось открыть файл, восстановив или удалив нечитаемое содержимое.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.