Avatar of Eduardo Fuerte
Eduardo FuerteFlag for Brazil asked on

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?!

img001

Thanks in advance!
LaravelJavaScriptPHP

Avatar of undefined
Last Comment
Eduardo Fuerte

8/22/2022 - Mon
Chris Stanyon

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.
ASKER
Eduardo Fuerte

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Eduardo Fuerte

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...
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Chris Stanyon

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.
ASKER
Eduardo Fuerte

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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Eduardo Fuerte

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

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.
ASKER
Eduardo Fuerte

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?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Chris Stanyon

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.
ASKER
Eduardo Fuerte

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":{}}"
ASKER
Eduardo Fuerte

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Stanyon

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');
ASKER
Eduardo Fuerte

Hi Chris

I wish you a happy Easter!

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

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).
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Eduardo Fuerte

Hi Chris

Thank you for your help!