We help IT Professionals succeed at work.

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

Eduardo Fuerte
on
80 Views
Last Modified: 2020-04-13
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!
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 FuerteDeveloper and Analyst

Author

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Eduardo FuerteDeveloper and Analyst

Author

Commented:
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...
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 FuerteDeveloper and Analyst

Author

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 FuerteDeveloper and Analyst

Author

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 FuerteDeveloper and Analyst

Author

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 FuerteDeveloper and Analyst

Author

Commented:
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 FuerteDeveloper and Analyst

Author

Commented:
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

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 FuerteDeveloper and Analyst

Author

Commented:
Hi Chris

I wish you a happy Easter!

The use of POST is mainly due security, isn't it?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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 FuerteDeveloper and Analyst

Author

Commented:
Hi Chris

Thank you for your help!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.