0

I have a spreadsheet which I wanna read to get values to my database but when checking with var_dump() it reads every row as NULL. The Excel file is a XLSX.

Something odd about this spreadsheet is that it starts at row 11 but I need to get some values that are above as "mapped cells".

class ActaImport implements ToCollection, WithStartRow, 
        WithMappedCells
{
      public function mapping(): array
    {
        return [
            'fecha' => 'G2',
            'guia' => 'G3',
        ];
    }

    /**
    * @param Collection $rows
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function collection(Collection $rows)
    {
        foreach ($rows as $row)
        {
            if($row[1] == "Entregado por:"){
                break;
            }
            // busca el modelo
            $modelo = Modelo::where('name', $row[4])->first();
            if (!$modelo) {
                // si no está lo crea
                $modelo = $this->createModel($row);
            }
            // limpia el serial en caso de que tenga caracteres incorrectos
            $row[2] = preg_replace("/[^a-zA-Z0-9]/", "", $row[2]); 
            // busca el dispositivo a partir del serial (aqui no contamos con rótulo)
            $dispositivo = Device::where('serial', $row[2])->first();

            $datos = [
                // importa el acta
                    'serial' => $row[2],
                    'model_id' => $modelo->id,
                // formatea fecha
                    'fecha_recepcion' => date("Y-m-d H:i:s", ($row['fecha'] - 25569)*86400),
                    'guia_recepcion' => $row['guia'],
                    'customer_id' => 1, // datos genéricos
                    'str_id' => 1 // datos genéricos
                ];
            if (!$dispositivo) {
                // de no existir un equipo lo crea
                Device::create($datos);
            } else {
                // si existe lo actualiza con esta información
                Device::where('id', $dispositivo->id)->update($datos);
            }
        }   
    }


    public function createModel($row) 
    {
        // crea modelo en sistema en caso de no existir en la base
        $marca = Brand::where('name', $row[5])->first();
        $nuevoModelo = new Modelo;
        if($marca) {
            $nuevoModelo->brand_id = $marca->id;
            $nuevoModelo->name = $row[4];
            $nuevoModelo->save();
        }
        else {
            // si no existe la marca tiene que crearla
            $nuevaMarca = new Brand;
            $nuevaMarca->name = $row[5];
            $nuevaMarca->save();
            $nuevoModelo->brand_id = $nuevaMarca->id;
            $nuevoModelo->name = $row[4];
            $nuevoModelo->save();
        }

        return $nuevoModelo; 
    }

    public function startRow(): int
    {
        return 11;
    }



}

Also the spreadsheet has some formulas within. I've used WithCalculatedCells but it doesn't read the cells anyway.

ffuentes
  • 1,042
  • 5
  • 16
  • 36
  • 1
    As I understand the docs about the `WithMappedCells` concern the import will only read two cells now, G2 and G3. – piscator Mar 04 '19 at 13:39
  • The thing is it doesn't even read those ones. I'm gonna get rid of that but it ruins what I needed to do. I'll try it and tell you what happens. – ffuentes Mar 04 '19 at 13:41
  • I think it conflicts with `startRow` 11. I think you are best of by removing the `startRow` and the `mapping` methods, skip the rows manually and access G2 and G3 manually as well. All from the `collection` method. – piscator Mar 04 '19 at 13:45
  • Good idea. Anyway, I got rid of the WithMappedCells and got the params through the constructor and it works now. – ffuentes Mar 04 '19 at 13:55
  • 1
    Great, good luck with the import! – piscator Mar 04 '19 at 14:00

0 Answers0