Format fields with Laravel-Excel

Question

Format fields with Laravel-Excel

Navigation

#1 by (0 votes)

1

The following code allows you to export a .CSV file using the maatwebsite / laravel-excel library from Laravel 5.2 .

Excel::create('Filtro Productos Concentrado', function($excel) use($ini, $fin, $gr)
    {
        $granjas = Granja::all();
        $concentrados = Concentrado::all();
        $productos = PedidoConcentrado::all();
        $pedidos = ConsecutivoConcentrado::all();
        $prods = Pedidoconcentrado::whereBetween('fecha_entrega', [$ini,$fin])->get();
        foreach ($prods as $pr)  
        {
            foreach ($granjas as $g) 
            {
                if ($pr->granja_id == $g->id)
                {
                    foreach ($concentrados as $concentrado) 
                    {
                        if ($pr->concentrado_id == $concentrado->id) 
                        {
                            if ($gr == $g->id)
                            {
                                $productos_db[$pr->id]["granja"] = $g->descripcion_granja;
                                $productos_db[$pr->id]["ref"] = $concentrado->ref_concentrado;
                                $productos_db[$pr->id]["producto"] = $concentrado->nombre_concentrado;
                                $productos_db[$pr->id]["fecha_creacion"] = $pr->fecha_creacion;
                                $productos_db[$pr->id]["fecha_entrega"] = $pr->fecha_entrega;
                                $productos_db[$pr->id]["bultos"] = $pr->no_bultos;
                                $productos_db[$pr->id]["kilos"] = $pr->no_kilos;
                                $productos_db[$pr->id]["tipo_documento"] = $pr->tipo_documento;
                                $productos_db[$pr->id]["prefijo"] = $pr->prefijo;
                                $productos_db[$pr->id]["consecutivo"] = $pr->consecutivo_pedido;
                                $productos_db[$pr->id]["bodega"] = 'XXX';
                                $productos_db[$pr->id]["vendedor"] = '0';
                                $productos_db[$pr->id]["centro_costo"] = $g->centro_costo;
                                $productos_db[$pr->id]["forma_pago"] = $g->forma_pago;
                                $productos_db[$pr->id]["precio_concentrados"] = $g->precio_concentrados;
                            }
                            else if ($gr == '0') 
                            {
                                $productos_db[$pr->id]["granja"] = $g->descripcion_granja;
                                $productos_db[$pr->id]["ref"] = $concentrado->ref_concentrado;
                                $productos_db[$pr->id]["producto"] = $concentrado->nombre_concentrado;
                                $productos_db[$pr->id]["fecha_creacion"] = $pr->fecha_creacion;
                                $productos_db[$pr->id]["fecha_entrega"] = $pr->fecha_entrega;
                                $productos_db[$pr->id]["bultos"] = $pr->no_bultos;
                                $productos_db[$pr->id]["kilos"] = $pr->no_kilos;
                                $productos_db[$pr->id]["tipo_documento"] = $pr->tipo_documento;
                                $productos_db[$pr->id]["prefijo"] = $pr->prefijo;
                                $productos_db[$pr->id]["consecutivo"] = $pr->consecutivo_pedido;
                                $productos_db[$pr->id]["bodega"] = 'XXX'; 
                                $productos_db[$pr->id]["vendedor"] = '0'; 
                                $productos_db[$pr->id]["centro_costo"] = $g->centro_costo;
                                $productos_db[$pr->id]["forma_pago"] = $g->forma_pago;
                                $productos_db[$pr->id]["precio_concentrados"] = $g->precio_concentrados;
                            }
                        }
                    }
                }
            }
        }

        $productos_db = json_decode(json_encode($productos_db), true);
        $excel->sheet('Productos', function($sheet) use($productos_db)
        {

            foreach ($productos_db as $producto_db) 
            {
                $sheet->row(1, ['Fecha de Pedido', 'Tipo de Documento', 'Prefijo','Consecutivo', 'Granja', 'vendedor','Forma de Pago', 'codigo', 'Descripcion', 'Kilos', 'bodega', 'Centro de Costo', 'precio', 'Fecha de Entrega']);

                $row = [];
                $row[0] = $producto_db['fecha_creacion'];
                $row[1] = $producto_db['tipo_documento'];
                $row[2] = $producto_db['prefijo'];
                $row[3] = $producto_db['consecutivo'];
                $row[4] = $producto_db['granja'];
                $row[5] = $producto_db['vendedor'];
                $row[6] = $producto_db['forma_pago'];
                $row[7] = $producto_db['ref'];
                $row[8] = $producto_db["producto"];
                $row[9] = $producto_db['kilos'];
                $row[10] = $producto_db['bodega'];
                $row[11] = $producto_db['centro_costo'];
                $row[12] = $producto_db['precio_concentrados'];
                $row[13] = $producto_db['fecha_entrega'];

                $sheet->appendRow($row);
            }
        });
    })->export('csv');

I would like some cells to have the type of data as Text , the problem is that when exporting it, Excel recognizes them with General format and deletes the zero to the left you have to bring the data by default (leave 7 instead of 07 as it is in the BD). thanks for your help ...

javascript html php laravel excel

asked by Erick Echeverry Garcia 09.04.2018 в 21:46

source

1 answer

how to make a remote post from my wordpress site to an api in angular Checking PHP users by name and by id

score 0 · Answer 1

You could format the columns according to the documentation:

$sheet->setColumnFormat(array(
  'B' => '0',
  'D' => '0.00',
  'E' => '@',
  'F' => 'yyyy-mm-dd',
));

(Link to Documentation) , (Question in SO in English)

Issue 1: The export method of the Sheet class does not seem to work as you suppose, then you send 'csv' and is implemented waiting for something else, I think.

public function export($sheetExport)
{
$this->open($sheetExport);
if ($sheetExport instanceof FromView) {
    $this->fromView($sheetExport);
} else {
    if ($sheetExport instanceof FromQuery) {
        $this->fromQuery($sheetExport, $this->worksheet);
    }
    if ($sheetExport instanceof FromCollection) {
        $this->fromCollection($sheetExport, $this->worksheet);
    }
}
$this->close($sheetExport);
}

The documentation indicates that, to save with a defined writer, the% method could be used store of class Excel :
```
// Store on a different disk with a defined writer type. 
Excel::store(<datasource>, <nombre_archivo_destino>, <disco_destino>, Excel::CSV);
```
This is because there are defined the constants of the types of writers and eventually execute an export with the correct writerType .
```
$file = $this->export($export, $filePath, $writerType);
```