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

Question

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?

Amit Sahu · Accepted Answer · 2017-05-08 11:16:59Z

4

+50

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

edited May 8, 2017 at 11:16

answered May 5, 2017 at 7:39

Amit Sahu

9966 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Raghbendra Nayak Over a year ago

But I need to copy the formula of above column to next one.

Amit Sahu Over a year ago

you can generate formula dynamically for each row, check my updated answer.

Raghbendra Nayak Over a year ago

So there is no way to copy the formula in next cell of excel?

Raghbendra Nayak Over a year ago

Let me try for this

Amit Sahu Over a year ago

May be there is some other option to achieve this... but my above solution fulfill your requirement...

Sarnodeep Agarwalla · Accepted Answer · 2017-05-01 06:34:17Z

2

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

answered May 1, 2017 at 6:34

Sarnodeep Agarwalla

2372 silver badges5 bronze badges

6 Comments

Raghbendra Nayak Over a year ago

In this way formula will be copy in my column?

Sarnodeep Agarwalla Over a year ago

According to you code, you wanted to append codes ` $sheet->appendRow(array( 'appended', 'appended' // Here I am appending the dynamic data in my code. ));` Now you can append the rule dynamically ... assign the rule to the variable ...

Raghbendra Nayak Over a year ago

Sorry for inconvenience but I just want to copy the formula of column in next dynamic columns data. See: 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.

Raghbendra Nayak Over a year ago

Any suggestion on this?

Sarnodeep Agarwalla Over a year ago

The formula of the column "M", are you getting it dynamically from the database????

|

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related