I am using PHP (laravel) for writing data dynamically in excel sheet from database. I have excel sheet in which I have filled data in first row with formula in few columns.
Now what I want, I just want to copy that formula in every respective column dynamically when I am writing data dynamically. I am using maatwebsite for writing the data in 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);
Suppose column "M" has formula in first row so whenever new record will fill in this column dynamically that column must contain the same formula. How can I achieve this? Anyone faced this before?
you can use like $sheet->setCellValue('B5','=SUM(B2:B4)'); at the time of writing data on excel file... Hope this will help 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 . ')');
}
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);