Laravel Excel Export with Vlookup formula multiple sheets

[tomekhun]

Hello All,

Thank you for this wonderful set of features for importing and exporting, I love to use your features.

I have a working environment with many exports and imports.

In an export I would like to achieve the following. I have multiple sheets and would like to have a sheet where i would use a Vlookup formula that searches values from another sheet.

The expected result would be the following:
in Column A user will type(scan) an SKU and in Colunm B the Vlookup formula would search the given sku from another sheet for the product name.

But in my export I have #N/A in all cells that contains the formula, not even have the formula in the field only #N/A as text. In my config file the 'pre_calculate_formulas' => false is set.
in the export which based on FromArray I use WithMapping function and for the specifig cell I use the following:
"=VLOOKUP("B{$thisRow}",#NAMEOFSHEET#::B:C,2,FALSE)",

where #NAMEOFSHEET# is the proper name of the other sheet and $thisRowId represents the currentRow.

if I put and simpler formula to a cell, such as SUM it creates the export with the proper formula in the cell and not with the calculated value.

Is there any idea what am I doing wrong?

thank you for your kind help!

regs

Tomi

[MarkBaker]

What is the :: in #NAMEOFSHEET#::B:C?

PhpSpreadsheet expects a ! separator between a worksheet and cell/row/column reference.

[MarkBaker]

I can't understand why it shouldn't leave the formula in the cell: that formula is that actual cell value, so it should always be maintained; and I can't recreate a situation where it is replaced by the calculated value.

[tomekhun]

sorry for my confusement...

so if i use a SUM function it will show the result in the cell in excel but if i click the cell it will show that the value is produced by a function summing n cells.
if i use VLOOKUP during export it will put the actual value in the field and there is no sign of that is produced by a function, only a value.

I have multiple sheets and this specific sheet where i would like to use the vlookup function is empty, the user has to fill it in, so I would like to achive that when a user enters an SKU it will vlookup the sku from another sheet and produces the producrt name in the field where i put the vlookup.

So i would like to have the function in the field and not the produced value.

I hope this is understandable, sorry for my poor english.

thank you in advance.

[MarkBaker]

PhpSpreadsheet should not be replacing a formula with the calculated result from that formula. The cell value should always contain the formula. If a formula with VLOOKUP is being replaced with the calculated value, then it is a bug somewhere in the PhpSpreadsheet code that needs fixing.

I am trying to create a spreadsheet based on what you have said, and the formula that you posted previously, to see the problem for myself, but am unable to recreate your problem.

And without being able to see what is happening with your spreadsheet, to trace what the code is doing, there is nothing that I can do about it. Can you provide an example that demonstrates this problem.

[tomekhun]

ok. Thank you for your patience in this matter! I know that I do something wrongly so excuse this from my side. and thanks again!!!

So I'am using a laravel environment with laravel-excel.

I'm creating an export with multiple sheets.
here is the basic export for creating the sheets:
Basically what happens here is that I create 3 sheets with inventory data and muiltiple (based on my model) "empty" sheets based on some other criteria. on these empty sheets created by ExportInventoryDataSheet I create 1000 dummy rows with empty data and only in 2 columns i would like to have a vlookup formula that is referenced to a sheet created by this ExportInventoryProductsSheet.
(sorry for the confusing build-up)
`<?php

namespace App\Exports;

use App\Models\StoragePlaces;
use App\Models\WarehousingInventory;
use Illuminate\Support\Facades\Date;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Falust\EzrosysReporting\EzrosysReportingGeneral;

class ExportInventoryToExcel implements FromArray, WithMultipleSheets
{
use Exportable;

protected $reportType;
public function __construct()
{
   
    $this->sheets = [];
    
}

public function model(int $model)
{
    $this->model = $model;
    
    return $this;
}


public function array(): array
{
    
    return $this->sheets;
   
   

}

public function sheets(): array
{

    $inventory = WarehousingInventory::find($this->model);
    $dataSheets = [];
    foreach ($inventory->getItemsData->groupBy('storage_place_id') as $key=>$invData) {
        
        $storagePlaceName = StoragePlaces::find($key);
        $dataSheets[] = new ExportInventoryDataSheet($this->model,"#RAKHELY_{$storagePlaceName->name}_{$key}");
    }
  
        $sheets = [
            new ExportInventoryProductInInventorySheet($this->model),
            new ExportInventoryProductSerialSheet($this->model),
            new ExportInventoryProductsSheet($this->model),
           
        ];
   
        $sheets = array_merge($dataSheets,$sheets);
    
    return $sheets;
}   

}`

and for the specific sheet i use ExportInventoryDataSheet

`<?php

namespace App\Exports;

use App\Http\Repositories\AdminStockRepository;
use App\Models\Products;
use App\Models\WarehousingInventory;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\RemembersRowNumber;

class ExportInventoryDataSheet implements FromArray, WithMapping,WithEvents,ShouldAutoSize,WithHeadings,WithTitle
{
use Exportable;
use RemembersRowNumber;

protected $index = 0;

public function __construct($model,$storagePlaceName)
{
    $this->model = $model;
    $this->storagePlaceName = $storagePlaceName;
    $this->products = [];

}
/**
* @return \Illuminate\Support\Collection
*/
public function array(): array
{
    $arr = [];
    for ($i=0;$i<1000;$i++) {
        $arr[] = [];
    }
    return $arr;
    
}
public function headings(): array
{
    
        return [
            
           
           'Sor ID',
           'Termék Barcode',
           'Serial',
           'Név',
           'Kód',
           'Mennyiség'
           
             
        ];
}
public function map($data): array
{
    $thisRow = ++$this->index;
    $thisRowId = $thisRow+1;
    $tmpArr = [
        $thisRow,
        "",
        "",
    
       "=VLOOKUP(B{$thisRowId},Termekek!B:C,2,FALSE)",
       "=VLOOKUP(B{$thisRowId},Termekek!B:D,3,FALSE)",
        
        ''
        
    ];
    


    return $tmpArr;

    


    
    
    
}

public function registerEvents(): array
{
    
    return [    
        
        AfterSheet::class => [self::class, 'afterSheet']
    ];
   
}

public static function afterSheet(AfterSheet $sheet) 
{

   // $sheet->sheet->setCellValue('G2', 210000);
  //  $sheet->sheet->setCellValue('D2', '=VLOOKUP(B2;Termekek!B:C;2;FALSE)');
   // $sheet->sheet->setCellValue('E2', '=VLOOKUP(B2;Termekek!B:D;3;FALSE)');
    //$sheet->sheet->getDelegate()->getStyle("D2")->setQuotePrefix(true);
    //$sheet->sheet->getDelegate()->getStyle("E2")->setQuotePrefix(true);

   
    
}

public function title(): string
{
    return $this->storagePlaceName;
}

}
`

I hope that this might be able to demonstrate my problem.

thanks

[tomekhun]

@MarkBaker

When I'm creating an export directly with ExportInventoryDataSheet only one sheet, the formula is present in my export. But when I'm running through another export class to create multiple sheets using WithMultipleSheets, that cell becomes TEXT and not a formula. Other simpler formulas such as IF() or SUM() behaves as i expect and they are present as formulas in the sheet, but VLOOKUP becomes a simple TEXT

[MarkBaker]

I don't know what the Laravel wrapper might be doing, but this works as expected in PhpSpreadsheet:

$spreadSheet = new Spreadsheet();
$workSheet = $spreadSheet->getActiveSheet();
$dataSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadSheet, 'Data');
$spreadSheet->addSheet($dataSheet);

$data = [['XYZ', 'ABC'], ['WXY', 'DEF'], ['VWX', 'GHI'], ['UVW', 'JKL']];

$formula = '=VLOOKUP(A1,Data!B:C,2,false)';

$workSheet->getCell('A1')->setValue('VWX');
$workSheet->getCell('A2')->setValue($formula);
$dataSheet->fromArray($data, null, 'B1');

$writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadSheet);
$writer->save("{$fileName}.xlsx");

[tomekhun]

Yes. it seems that it fails at the wrapper. Again. thank you for your precious time, and sorry, that I did not came to this conclusion.

Thanks again!!!