Level 75
@vincej what code did you try when you mapped.
Feb 5, 2021
38
Level 15
Laravel Excel: How to export to excel with formulas
Laravel Excel docs are pretty sparse on the topic of embedding formulas in an export controller. Anyone know how this is done? My goal is to deliver a spreadsheet containing the correct formulas in the correct cells. I have scored the docs and SO and I have found only one tutorial at the bottom of this page which gave errors:
https://laravel-news.com/five-hidden-features-of-the-laravel-excel-package
Many thanks !
Level 15
Thank for helping out! As the docs are so sparse, I am guessing and my guess is wrong. I have an red error squiggle at the bottom of the headings method and also one on the public function map(). If you do not add a mapping method then you get big red errors under the class implements as well.
<?php
namespace App\Exports;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use DB;
Class BillingsExport implements FromCollection, WithHeadings, WithMapping
{
protected $startDate;
protected $endDate;
function __construct($startDate, $endDate)
{
$this->startDate = $startDate;
$this->endDate = $endDate;
}
public function collection()
{
return $child = DB::table('child')
->join('attendance', 'child.child_id', '=', 'attendance.child_id')
->select('child.child_id', 'child.childLastName', 'child.childFirstName', DB::raw('SUM(attendance.time) as time'), DB::raw('SUM(attendance.billable) as billable'))
->where('date', '>=', $this->startDate)
->where('date', '<=', $this->endDate)
->where('staff', '=', '0')
->groupBy('child.child_id')
->orderBy('child.childLastName')
->get();
}
// This generates the headings in the spreadsheet
public function headings(): array
{
$rate = DB::table('billingrate')
->latest()
->first();
$billingRate = number_format((float)$rate->rate, 2, '.', '');
return [
[
' ',
' ',
' ',
' ',
'$ Rate/Hour',
$billingRate
],
[' ',],
[
'Child Id',
'Last Name',
'First Name',
'Hours',
'Billable $',
'Hours',
'Amount Billed'
],
];
}
puplic function map(): array
{
return [
'A1' => '=A1*B2'
];
}
}
Level 33
You have a typo in puplic function map(): array, but I'm assuming that's not the problem, right?
- What happens if your
billingratetable is empty? - Does the export work when you remove the
map()method?
Level 33
I see the problem now. I think you you just have to return the array values from your map() method:
public function map(): array
{
return [
'=A1*B2'
];
}
Level 15
Thanks or that. I need to be able to specify the specific cell into which I will send a specific formula
Level 33
No problem. I think you're supposed to map all the cells there in order, so no need to specify the cell name.
Level 15
Fixing the typo did nothing. The headings method works. But that tutorial says I need to use mapping in order to add formulas. The tut cold be totally wrong, and there is very little on line or in the docs. I am thinking that a formula is just text. But the issue is getting it into the correct cell on the spreadsheet.
When hovering over function map PHPStorm tells me
Declaration must be compatible with
WithMapping->row(row:mixed)
To be honest, I have no idea what I am doing.
Level 33
The method map() must receive a $row parameter according to the contract:
public function map($row): array
{
return [
'=A1*B2'
];
}
See: https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/src/Concerns/WithMapping.php#L12
Level 15
Ok .... so .... I pass maybe $row = 3 is this what they want? The github is not obvious to me. And then I also need a column reference as well.
I am also not clear what you mean by this:
No problem. I think you're supposed to map all the cells there in order, so no need to specify the cell name.
Level 33
See my latest reply at the bottom of the thread.
Level 75
I would suggest doing a small easy spreadsheet at first with a formula until you work out why it's not working.
1 like
Level 15
@jlrdw You are right. My issue right now is that I can not find a tutorial which explains how to do it. I can't be the only guy on planet earth who has had this problem :o)
It's probably easy when you know how !
Anyway, I will do what you are suggesting.
Level 33
Here's how it should be implemented:
public function map($row): array
{
return [
$row->child_id,
$row->childLastName,
$row->childFirstName,
$row->time,
$row->billable,
'=A1*B2',
];
}
What I don't know is how you're supposed to determine the current row to apply it to the formula.
Level 122
think of map in the same way as a map() function in a collection
when you pass a collection into the export function then the map function is going to be called once for every item in the collection
so, say for instance you call the export function with a collection of 5 invoices then the map function is going to be called 5 times and in each call you are expected to return what you want in column 1, column 2, column 3 etc
you are not able to specify the row because this function just spreads your data across the rows and moves to the next row
so, specifying A1 makes no sense in the map function
Level 122
what is this formula to do, and why don't you calculate the value in the php code and just pass it to the output as a number?
Level 15
Ok A1 makes no sense, got it.
However, there is Major Progress! I have the formula appearing across each row next to the column marked $row->billable. This is in the wrong position. The column neighbouring $row->billable needs to be empty. Then there is a column titled $row->hours. That is where the formula needs to go.
However, is I make $row-> hours empty like this, I get a big fat error undefined property stdCass:$hours:
return [
$row->child_id,
$row->childLastName,
$row->childFirstName,
$row->time,
$row->billable,
$row->hours,
'=A1*B2',
];
Level 15
@snapey answer to your question: $row->hours is left empty as it is a user defined cell.
Level 33
$row->hours is not a part of your original DB query. If you need an empty column, use an empty string.
return [
$row->child_id,
$row->childLastName,
$row->childFirstName,
$row->time,
$row->billable,
'',
'=A1*B2',
];
Level 122
you need to write the formula in a way that does not care about the row, ie it is row independent
That's an excel issue not a laravel one
Level 75
@vincej does the formula adjust per row: B2, B3, etc
And shouldn't you start with A2, isn't A1 part of header.
Level 33
Regarding determining the current row (in order to compose the formula), check out the section "Mapping - Prepare rows" of the Laravel Excel documentation:
Level 15
@jlrdw yes the formula adjusts per row. And I only used A1 as a place holder, the actual cell is $F$3*G4 then on down to H4 etc
@snapey I don't quite follow your point. as you can see, one of the values is absolute $F$3 is specific to row three and can not move.
@guybrush_threepwood You are a Rock Star! I have an empty column
Last question: When you say Original Query, do you mean the collection query ?
1 like
Level 33
Yes @vincej, exactly.
If you wanted to reference $row->hours it should be part of your collection. If for some reason you wanted it to be empty, to could do:
DB::table('child')
->join('attendance', 'child.child_id', '=', 'attendance.child_id')
->select('child.child_id', 'child.childLastName', 'child.childFirstName', DB::raw('SUM(attendance.time) as time'), DB::raw('SUM(attendance.billable) as billable'), DB::raw('"" as hours'))
->where('date', '>=', $this->startDate)
->where('date', '<=', $this->endDate)
->where('staff', '=', '0')
->groupBy('child.child_id')
->orderBy('child.childLastName')
->get();
Level 122
what i mean is that your formula has to be different on every row and you also don't know how many rows there are
so your formula wants to be $f$3 * F? but you don't know what ? should be for any call to the map function
it might be able to use R1C1 notation, i'm not sure
https://www.engram9.info/excel-2003-vba-macros/using-rc-with-relative-references.html
alternatively, you may be able to do it with a simple row counter held in the class
private $current_row =4;
public function map($row): array
{
return [
$row->child_id,
$row->childLastName,
$row->childFirstName,
$row->time,
$row->billable,
'',
'=$f * f' . $this->current_row++,
];
}
fir some reason the code above does not show $f$3 but I hope you get the idea
2 likes
Level 15
@guybrush_threepwood @snapey @jlrdw
Thank you all for your help and all your efforts! I would have never got there without you.
Best reply to Guy.
1 like
Level 122
so what did you end up with?
Level 15
@snapey Everything as above just the mapping changed. I need to study your comments more to understand them better.
public function map($row): array
{
return [
$row->child_id,
$row->childLastName,
$row->childFirstName,
$row->time,
$row->billable,
'',
'=A1*B2',
];
}
Level 122
ok, so still =A1*B2 in every row. Not resolved the actual difficult bit yet?
1 like
Level 15
You are freaking brilliant. Ok, so I studied your posts and indeed you are 100% correct. Guy's answer did correctly give me 1 calculation in 1 cell, which in fairness is what I thought I needed, assuming that it would populate through to the other cells. Nope ! NO such luck,
But as the great Snapey always comes to the rescue you saw a problem which no one else saw. And what's more your answer works and is 100% correct. Personally I have not yet ever come across RC with relative references in Visual Basic. I mean like why would I? So - not for me, but for future readers of this post, it is only proper that they see Snapey's answer and so best will have to go to Snapey.
Deep apologies Guy - you got the ball rolling is absolutely the correct direction. And I thank you.
Thanks again to all of you. Now I have to figure out how to format my spreadsheet. Ugh :o)
Level 75
@vincej that's what I ment by formula adjusts per row.
So how are you making the formula do:
b
c
d
e
etc
Level 15
Sorry I don't understand
Level 15
I have taken on snapey's answer ie:
private $current_row =4;
public function map($row): array {
return [
$row->child_id,
$row->childLastName,
$row->childFirstName,
$row->time,
$row->billable,
'',
'=$F*F'. $this->current_row++,
];
}
Level 15
@jlrdw you are a mega star too .... top 10 leader board ... sheesh ... I could only wish. Thanks for all your help - you are always first t come through for me. Cheers!
Level 15
Level 75
This line:
'=$F*F'. $this->current_row++,
actually goes through the alphabet?
Level 15
@jlrdw I guess so ... to be honest ... I am so heads down in getting this just to work, that I am grateful that it just does work. The site is live, and I just need to fix this error of logic. I have not had any bandwidth to study the underlying logic of it all. However, I appreciate you bringing it to my attention. I will look at it after dinner :o)
Level 122
Anyone reading this needs to know that this forum does not like displaying =$F$3 in a code block. For some reason
So, the formula being written to the cell is =$F$3+F4, then =$F$3+F5, =$F$3+F6, =$F$3+F7
One part of the formula is fixed cell reference and the other increments the row number on each map call.
Glad you were able to get it to work Vince
Level 75
Thanks for the followup, I was curious about the incrementing.
Please or to participate in this conversation.