Copy excel column formula in all rows when writing dynamic data from DB to PHP
I am using PHP (laravel)
to dynamically write data in an excel sheet from a database. I have an excel sheet in which I have filled the data in the first row with a multi-column formula.
Now what I want is I just want to dynamically copy this formula in each respective column as I write data dynamically. I am using maatwebsite to write data in an excel sheet. See below code.
Excel::selectSheetsByIndex(1)->load(public_path('uploads') . '/data.xlsx', function($reader) {
$reader->sheet('Sheetname', function($sheet) {
// Append row as very last
$sheet->appendRow(array(
'appended', 'appended' // Here I am appending the dynamic data in my code.
));
});
}, 'UTF-8')->store('xlsx', true);
Let's assume the column "M"
has a formula in the first row, so whenever a new record dynamically populates that column, the column must contain the same formula. How can I achieve this? Has anyone encountered this before?
source to share
you can use both $sheet->setCellValue('B5','=SUM(B2:B4)');
while writing data to excel file ... Hope this helps you ...
UPDATED CODE
for($k=1;$k>n;$k++){
$sheet->setCellValue('AC' . $k, '=SUM(N' . $k . ',O' . $k . ',Q' . $k . ',S' . $k . ',T' . $k . ',V' . $k . ',Y' . $k . ',AB' . $k . ')');
}
source to share
You can try something like this
$rule = ['appended', 'appended'];
Excel::selectSheetsByIndex(1)->load(public_path('uploads') . '/data.xlsx', function ($reader) use ($rule) {
$reader->sheet('Sheetname', function ($sheet) use ($rule) {
// Append row as very last
$sheet->appendRow($rule);
});
}, 'UTF-8')->store('xlsx', true);
source to share