CODEWITHSUNDEEP
phplaravelexcel-formulaexcel-2007maatwebsite-excel
Raghbendra Nayak
Raghbendra Nayak

Reputation: 1646

Copy excel formula of column in all rows while writing dynamic data from DB in PHP

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?

Upvotes: 4

Views: 3840

Answers (2)

Amit Sahu
Amit Sahu

Reputation: 996

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 . ')'); }

Upvotes: 4

Sarnodeep Agarwalla
Sarnodeep Agarwalla

Reputation: 237

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);

Upvotes: 2

Related Questions