asked on 4/8/2020

Could you point how to make an Excel sheet to be correctly generated by using Laravel?

Hi Experts

Could you point how to make an Excel sheet to be correctly generated by using Laravel?

HTML code:

<div class="btn-group" role="group">
<button type="button" class="btn btn-primary" onclick="hotsite.relatorio.exportar();">
	<i class="fa fa-file-excel-o" aria-hidden="true"></i> Exportar Excel
  </button>
</div>

Open in new window

JS code:

Relatorio.prototype.exportar = function () {
  mesID = $("#filtroMesID").val();
  anoID = $("#filtroAnoID").val();
  trimestreID = $("#filtroTrimestreID").val();
  distribuidorID = $("#filtroDistribuidorID").val();
  
  console.log(mesID);
  console.log(anoID); 
  console.log(trimestreID);
  console.log(distribuidorID);
  
  var valores = {
    mesID: mesID,
    anoID: anoID,
    trimestreID: trimestreID,
    distribuidorID: distribuidorID
  };

  $.ajax({
    url: "/admin/relatorios/analiticoParticipante/exportar",
    method: "GET",
    data: valores,
    error: function (data) {
      $("#dlgAvisoTexto").html(data.retorno);
      hotsite.openModal("dlgAviso");
    },
    success: function (data) {
      $("#filtros").html(data);
      $(".selecttwo").select2();
    }
  });
};

Open in new window

Controller code pointed by the route above:

use Excel;

...
public function exportarAnaliticoParticipante(Request $request){
      $dados = [];
      $item = array();
      
      
      $mes = $request->input('mesID');
      $ano = $request->input('anoID');
      $trimestreID = $request->input('trimestreID');
      $distribuidor_id = $request->input('distribuidorID');
      
      
      $this->AnaliticoParticipante($mes, $ano, $trimestreID, $distribuidor_id, $request);
      
      
      $fullData = session('relatorio_analiticoparticipante');

      $dados[] = ['Razão Social','CNPJ','Status do Distribuidor','Nome GRV','CPF GRV', 'Email GRV' ,'Nome Assitente', 'CPF Assistente','Email Assistente', 'Trimestre','Ano','Mês da Compra','Meta (R$)','Grupo A (R$)','Grupo B (R$)', 'Grupo C (R$)' , 'Grupo D (R$)', 'Grupo E (R$)', 'Grupo F (R$)' , 'Grupo G (R$)', 'Total (R$)','% Atingimento Meta','Pontos Conquistados', 'Pontos Represados','Liberados?','Data da Distribuição','Status da Distribuição'];  
    
      
      
      foreach ($fullData as $data) {
        $item = [];
        foreach($data as $key=>$value){
          array_push($item, $value);
        }

          array_push($dados, $item);
      }


// Data is OK here to generate Excel !!!

//        print_r(json_encode($dados));
//        die;
//-------------------------------------------------


      Excel::create('relatorio_analiticodoparticipante', function($excel) use ($dados) {

          // Set the spreadsheet title, creator, and description
          $excel->setTitle('Relatorio Analítico dos Participantes');
          $excel->setCreator('IPremi')->setCompany('IPremi');
          $excel->setDescription('Lista Analítica dos Participantes');

          // Build the spreadsheet, passing in the users array
          $excel->sheet('sheet1', function($sheet) use ($dados) {
              $sheet->fromArray($dados, null, 'A1', false, false);
              $sheet->setOrientation('landscape');
              $sheet->setfitToPage(true);
          });

      })->download('xlsx');;
      
      
  }

Open in new window

I don't know the reason apparently the Excel sheet is been printed at the screen?!

Thanks in advance!

Laravel JavaScript PHP

Last Comment

Eduardo Fuerte

4/13/2020

Chris Stanyon

4/9/2020

Hey Eduardo,

The problem you have is because your AJAX success handler is inserting the excel data into the page:

success: function (data) {
      $("#filtros").html(data);

Open in new window

Generally, trying to download files via an AJAX call is tricky, so you may want to rethink your approach. You're likely to be better of just submitting a form to your Controller action directly. Then the server will respond with the correct headers and the file will be downloaded.

Eduardo Fuerte

4/9/2020

ASKER

Hi Chris

That mechanism works fine for another Excel download.

What I had obtained is different results to $fulldata

The incorrect result is involved with an extra array:

[
	[
		"Razão Social",
		"CNPJ",
		"Status do Distribuidor",
		"Nome GRV",
		"CPF GRV",
		"Email GRV",
		"Nome Assitente",
		"CPF Assistente",
		"Email Assistente",
		"Trimestre",
		"Ano",
		"Mês da Compra",
		"Meta (R$)",
		"Grupo A (R$)",
		"Grupo B (R$)",
		"Grupo C (R$)",
		"Grupo D (R$)",
		"Grupo E (R$)",
		"Grupo F (R$)",
		"Grupo G (R$)",
		"Total (R$)",
		"% Atingimento Meta",
		"Pontos Conquistados",
		"Pontos Represados",
		"Liberados?",
		"Data da Distribuição",
		"Status da Distribuição"
	],
....

Open in new window

The correct is:

	[
		"Razão Social",
		"CNPJ",
		"Status do Distribuidor",
		"Nome GRV",
		"CPF GRV",
		"Email GRV",
		"Nome Assitente",
		"CPF Assistente",
		"Email Assistente",
		"Trimestre",
		"Ano",
		"Mês da Compra",
		"Meta (R$)",
		"Grupo A (R$)",
		"Grupo B (R$)",
		"Grupo C (R$)",
		"Grupo D (R$)",
		"Grupo E (R$)",
		"Grupo F (R$)",
		"Grupo G (R$)",
		"Total (R$)",
		"% Atingimento Meta",
		"Pontos Conquistados",
		"Pontos Represados",
		"Liberados?",
		"Data da Distribuição",
		"Status da Distribuição"
	],

...

Open in new window

ASKER CERTIFIED SOLUTION

Chris Stanyon

4/9/2020

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Eduardo Fuerte

4/9/2020

ASKER

So

I used a submit button, with the method, like:

<form action="/admin/relatorios/analiticoParticipante/exportar"   method="get">
      </i><input type="submit" value="Submit">
</form>

Open in new window

This generates an error:

   public function exportarAnaliticoParticipante(Request $request){
      $dados = [];
      $item = array();
      
      //-------------------------------------------------------------
      $distribuidor_id = $_GET['filtroDistribuidorID'];
     //--------------------------------------------------------------

Open in new window

My problem here is how to send the form variables to the Controller method...

Chris Stanyon

4/9/2020

You need to change your form method to POST, and wrap the fields inside of the form. Then when you submit, all the values will be sent to the server.

Eduardo Fuerte

4/9/2020

ASKER

I didn't understand how to wrap the fields from the page.

The blade code:

<div class="row">
  <div class="panel panel-primary">
    <div class="panel-heading">
      <div class="pull-right">
        <a href="#" onclick="hotsite.formulario.filterToggle('filterAnalitico');" class="panelToggle"><i class="fa fa-dot-circle-o" aria-hidden="true"></i></a>
      </div>
      <h3 class="panel-title">Filtro</h3>
    </div>
    <div class="panel-body" id="filterCompras">
      <div class="row">
        <div class="col-md-6">
          <div class="form-group">
            <label for="filtroDistribuidorID">Distribuidor:</label>
            <select id='filtroDistribuidorID' class="form-control inputCadastro selecttwo">
                <option value=''>Todos os distribuidores</option>
                @foreach($distribuidores as $distribuidor)
                  @if($distribuidorDefault == $distribuidor->id)
                  <option value='{{ $distribuidor->id }}' selected>{{ $distribuidor->razaosocial }} ({{ CustomFuncs::formatarCnpj($distribuidor->cnpj) }})</option>
                  @else
                  <option value='{{ $distribuidor->id }}'>{{ $distribuidor->razaosocial }} ({{ CustomFuncs::formatarCnpj($distribuidor->cnpj) }})</option>
                  @endif
                @endforeach
              </select>
          </div>
        </div>
        <div class="col-md-6"></div>
      </div>
      <div class="row">
        <div class="col-md-3">
          <div class="form-group">
            <label for="filtroTrimestreID">Trimestre:</label>
            <select id='filtroTrimestreID' class="form-control inputCadastro" onchange="hotsite.relatorio.buildFilters()">
                  <option value="all">Todos</option>
                  
                  @foreach($trimestres as $trimestre)
                    @if($trimestreDefault == $trimestre->id)
                      <option value="{{ $trimestre->id}}" selected>{{ $trimestre->descricao }}</option>
                    @else
                      <option value="{{ $trimestre->id}}">{{ $trimestre->descricao }}</option>
                    @endif
                  @endforeach
                
                
                </select>
          </div>
        </div>
        <div class="col-md-3">
          <div class="form-group">
            <label for="filtroMesID">Mês:</label>
            <select id='filtroMesID' class="form-control inputCadastro" onchange="hotsite.relatorio.buildFilters()">
                <option value="all">Todos os meses</option>
                @foreach($meses as $mes)
                  @if($mes->mes === 1)
                    @continue;
                  @endif
                  @if($mesDefault == $mes->mes)
                    <option selected value="{{ $mes->mes}}">{{ $mes->nomeMes}}</option>
                  @else
                    <option value="{{ $mes->mes}}">{{ $mes->nomeMes}}</option>
                  @endif
                @endforeach
              </select>
          </div>
        </div>
        <div class="col-md-3">
          <div class="form-group">
            <label for="filtroAnoID">Ano:</label>
            <select id='filtroAnoID' class="form-control inputCadastro" onchange="hotsite.relatorio.buildFilters()">
                @foreach($anos as $ano)
                @if($ano->ano == 2019)
                  @continue
                @endif
                  @if($anoDefault == $ano->ano)
                    <option selected value="{{ $ano->ano}}">{{ $ano->ano}}</option>
                  @else
                    <option value="{{ $ano->ano}}">{{ $ano->ano}}</option>
                  @endif
                @endforeach
              </select>
          </div>
        </div>
      </div>
    </div>
    <div class='row'>
      <div class='col-md-12'>
        <div class='col-md-12' style="text-align:right">


            <form action="/admin/relatorios/analiticoParticipante/exportar" method="post">
                {{ csrf_field() }}
                <div class="col-xs-12 col-sm-12 col-md-12 col-lg-12 text-center">
                    <button type="submit" class='fa fa-file-excel-o'>Exportar Excel</button>
                </div>
            </form>

          <!--div class="btn-group" role="group">
            <a href='/admin/relatorios/analiticoParticipante/exportar' alt='' class="btn btn-primary">
                <i class="fa fa-file-excel-o" aria-hidden="true"></i> Exportar Excel
              </a>
          </div-->
          
        </div>
      </div>
    </div>
  </div>
</div>

Open in new window

And then

    public function exportarAnaliticoParticipante(Request $request){
      $dados = [];
      $item = array();

      // The values are NULL

        var_dump(request('filtroMesID'));
        var_dump(request('filtroAnoID'));
        var_dump(request('filtroTrimestreID'));
        var_dump(request('filtroDistribuidorID'));

Open in new window

Chris Stanyon

4/9/2020

Hey Eduardo,

You've coded your form after all your fields. The fields need to go inside the form, so in your code - right on line 1:

<form action="/admin/relatorios/analiticoParticipante/exportar" method="post">
<div class="row">
  <div class="panel panel-primary">
    <div class="panel-heading">
      <div class="pull-right">
      ...

Open in new window

And then close off your form at the end of your code.

Eduardo Fuerte

4/9/2020

ASKER

Ok. I did.

But how to receive the values inside the method after?

//Generates an error 
		$distribuidor_id = $_POST['filtroDistribuidorID'];


// Returns NULL
        var_dump(request('filtroMesID'));
        var_dump(request('filtroAnoID'));
        var_dump(request('filtroTrimestreID'));
        var_dump(request('filtroDistribuidorID'));

Open in new window

Chris Stanyon

4/9/2020

When you submit a form in Laravel, you'll receive the values as part of the Request

public function exportarAnaliticoParticipante(Request $request){
    $dados = [];
    $item = [];

    $filtroMesID = $request->filtroMesID;

Open in new window

This assumes that you've setup the route correctly, and mapped /admin/relatorios/analiticoParticipante/exportar to your exportarAnaliticoParticipante method.

Eduardo Fuerte

4/9/2020

ASKER

It's all correctly mapped.

The values is received as NULL.

Isn't it a good strategy to make AJAX to call an intermediate method and at this method the values to be obtained.

And then this method send the correct values to the one that will generate Excel?

Chris Stanyon

4/9/2020

OK - if you're going to that, then only the intermediary will have access to the request (whichever method you submitting the form to). If you're then passing that data onto another method, then you'll need to manually pass the data along as an argument to the new method:

public function HandleForm(Request $request) {
    $data = [
        'var1' => $request->var1,
        'var2' => $request->var2,
        'var3' => $request->var3,
    ];

    $this->Export($data);
}

public function Export($data) {
    // now you've got the data
}

Open in new window

But ... if you're doing any of this through AJAX, then you're back to where you started - you'll struggle to force a download, which is why I suggest a form POST instead.

Eduardo Fuerte

4/9/2020

ASKER

Really... the problem remains the same after interposing a method.

Returning to the POST...

Unfortunatelly the method only receives:

var_dump(json_encode($request));

Open in new window

string(90) "{"attributes":{},"request":{},"query":{},"server":{},"files":{},"cookies":{},"headers":{}}"

Eduardo Fuerte

4/9/2020

ASKER

Finally what I did and that works

-- At 1st line of the view
<form action="{{url('/admin/relatorios/analiticoParticipante/exportar')}}" method="GET">

-- At Controller
$mes = $request->input('filtroMesID');

-- And then the Excel is downloaded
$fullData = $this->AnaliticoParticipante($mes, $ano, $trimestreID, $distribuidor_id, $request);

// Stop using session, obtained the data directly - the way before was:

$this->AnaliticoParticipante($mes, $ano, $trimestreID, $distribuidor_id, $request);  // This returned a session
      
      
$fullData = session('relatorio_analiticoparticipante');

Open in new window

Chris Stanyon

4/9/2020

Hey Eduardo,

You shouldn't really be submitting form data over a GET request - you should be doing it with a POST request. You'd need to change your form method to POST and make sure your route is set up properly to accept a POST request:

Route::post('someurl', 'SomeController@someMethod');

Eduardo Fuerte

4/10/2020

ASKER

Hi Chris

I wish you a happy Easter!

The use of POST is mainly due security, isn't it?

Chris Stanyon

4/10/2020

Happy Easter to you too.

Yes - POST is a more secure way of sending data. The general guidelines are to use GET if all you're doing is retrieving data, and use POST if you're wanting to change data (INSERT / UPDATE etc).

Eduardo Fuerte

4/13/2020

ASKER

Hi Chris

Thank you for your help!