How do I install a library (Phpexcel) in a codeigniter application using NPM?

Question

I am developing an application that needs to export data from an array that received the result of a query in Excel format, in a dynamic way, and I wanted to know how to import the library, I started studying web last semester and resumed this year, excuse my ignorance, the application uses angular in front-end and codeigniter in back-end.

Answer 1

I used the fusonic/Spreadsheet-export lib https://github.com/fusonic/spreadsheet-export

To install the lib:

composer install fusonic/spreadsheet-export

In my case, I used the slim framework (I never used Codeigniter, but I think there are several similarities between them, from what I read). Here is a class that exported data from the database (accessed with the Doctrine ORM framework).

Here’s a class of mine, which was in production and worked perfectly if you want a model. But on github above you should have more examples of use.

<?php
namespace DarthEv\Core\cmd\etiquetagem;

use DarthEv\Core\cmd\AbstractCommand;
use DarthEv\Core\app\App;
use DarthEv\Core\dao\DAO;
use DarthEv\Core\dao\Etiqueta;
use Fusonic\SpreadsheetExport\Spreadsheet;
use Fusonic\SpreadsheetExport\ColumnTypes\TextColumn;
use Fusonic\SpreadsheetExport\Writers\OdsWriter;
use DarthEv\Core\Config;
use Fusonic\SpreadsheetExport\ColumnTypes\NumericColumn;
use Fusonic\SpreadsheetExport\ColumnTypes\DateColumn;
use Fusonic\SpreadsheetExport\Writers\CsvWriter;
/**
 * Lista as etiquetas disponíveis para a tarefa de etiquetagem.
 * @author monovox
 */
class ExportarPlanilhaTerrenaCommand extends AbstractCommand {

    public function __construct(\Slim\Http\Request $request, \Slim\Http\Response $response, $args){
        parent::__construct ($request, $response, $args );
    }

    public function process() {
        try {

            $etiquetas = DAO::em()->getRepository("\DarthEv\Core\dao\Etiqueta")->relEtiquetagemTerrena();

            $arr    = array();

            if(!empty($etiquetas)) {
                foreach( $etiquetas as $etiq ){
                    $one = array();
                    $one['etiquetaNumero'] = $etiq->getNumero();
                    $one['etiquetaDataVistoria'] = ( !empty($etiq->getDataVistoria()) )? $etiq->getDataVistoria()->format("d/m/Y H:i:s") : '';
                    $one['etiquetaDataVistoriaEn'] = ( !empty($etiq->getDataVistoria()) )? $etiq->getDataVistoria()->format("Y-m-d H:i:s") : '';

                    $one['entidadeNome'] = (empty($etiq->getEquipamento()))? '' : $etiq->getEquipamento()->getEntidade()->getNome();
                    $one['entidadeResponsavelNome'] = (empty($etiq->getEquipamento()))? '' : $etiq->getEquipamento()->getEntidade()->getResponsavel();
                    $one['entidadeResponsavelDocumento'] = (empty($etiq->getEquipamento()))? '' : $etiq->getEquipamento()->getEntidade()->getDocumento();
                    $one['entidadeResponsavelEmail'] = (empty($etiq->getEquipamento()))? '' : $etiq->getEquipamento()->getEntidade()->getEmail();
                    $one['entidadeResponsavelTelefone'] = (empty($etiq->getEquipamento()))? '' : $etiq->getEquipamento()->getEntidade()->getTelefone();
                    $one['entidadeResponsavelPais'] = (empty($etiq->getEquipamento()))? '' : $etiq->getEquipamento()->getEntidade()->getPais()->getNomeEn();

                    $one['usuarioCriadorEtiqueta']      = (!empty($etiq->getUserCriador()))? $etiq->getUserCriador()->getUsername() : '';
                    $one['usuarioDataCriacaoEtiqueta']  = (!empty($etiq->getDataCriacao()))? $etiq->getDataCriacao()->format("d/m/Y H:i:s") : '';
                    $one['usuarioDataCriacaoEtiquetaEn']    = (!empty($etiq->getDataCriacao()))? $etiq->getDataCriacao()->format("Y-m-d H:i:s") : '';
                    $one['operadorEtiquetagem']         = (!empty($etiq->getUserModificador()))? $etiq->getUserModificador()->getUsername() : '';
                    $one['operadorDataEtiquetagem']     = (!empty($etiq->getDataModificacao()))? $etiq->getDataModificacao()->format("d/m/Y H:i:s") : '';
                    $one['operadorDataEtiquetagemEn']   = (!empty($etiq->getDataModificacao()))? $etiq->getDataModificacao()->format("Y-m-d H:i:s") : '';

                    $one['etiquetaTipo'] = Etiqueta::TIPO[$etiq->getTipo()];
                    $one['etiquetaCancelada'] = (empty($etiq->getStatus())? 'S' : 'N');
                    $one['etiquetaCanceladaData'] = (empty($etiq->getStatus()))? $etiq->getDataCancelamento()->format("d/m/Y H:i:s") : '';
                    $one['etiquetaCanceladaDataEn'] = (empty($etiq->getStatus()))? $etiq->getDataCancelamento()->format("Y-m-d H:i:s") : '';
                    $one['etiquetaCanceladaMotivo'] = (empty($etiq->getMotivoCancelamento()))? '' : $etiq->getMotivoCancelamento() ;

                    $one['clusterNome'] = (empty($etiq->getCluster()))? '' : $etiq->getCluster()->getNome();
                    $one['clusterSigla'] = (empty($etiq->getCluster()))? '' : $etiq->getCluster()->getSigla();
                    $one['clusterNumeroEtiqMin'] = (empty($etiq->getCluster()))? '' : $etiq->getCluster()->getEtiquetaMin();
                    $one['clusterNumeroEtiqMax'] = (empty($etiq->getCluster()))? '' : $etiq->getCluster()->getEtiquetaMax();

                    $one['venueNome'] = (empty($etiq->getVenueEtiquetagem()))? '' : $etiq->getVenueEtiquetagem()->getNome();
                    $one['venueSigla'] = (empty($etiq->getVenueEtiquetagem()))? '' : $etiq->getVenueEtiquetagem()->getSigla();
                    $one['venueNumGrupo'] = (empty($etiq->getVenueEtiquetagem()))? '' : $etiq->getVenueEtiquetagem()->getGrupo();

                    $one['venueUsoPrincipalNome'] = (!empty($etiq->getVenueUsoPrincipal()))? $etiq->getVenueUsoPrincipal()->getNome() : '';
                    $one['venueUsoPrincipalSigla'] = (!empty($etiq->getVenueUsoPrincipal()))? $etiq->getVenueUsoPrincipal()->getSigla() : '';
                    $one['venueUsoPrincipalNumGrupo'] = (!empty($etiq->getVenueUsoPrincipal()))? $etiq->getVenueUsoPrincipal()->getGrupo() : '';

                    $one['eqptoDescricao'] = (empty($etiq->getEquipamento()))? '' : $etiq->getEquipamento()->getEquipamento();
                    $one['eqptoFabricante'] = (empty($etiq->getEquipamento()))? '' : $etiq->getEquipamento()->getFabricante();
                    $one['eqptoModelo'] = (empty($etiq->getEquipamento()))? '' : $etiq->getEquipamento()->getModelo();
                    $one['eqptoSerial'] = (empty($etiq->getEquipamento()))? '' : $etiq->getEquipamento()->getSerial();

                    $one['licencaNumAto'] = (empty($etiq->getLicenca()))? '' : $etiq->getLicenca()->getNumeroAto();

                    $terrena = (empty($etiq->getEquipamento()))? '' : $etiq->getEquipamento()->getEquipamentoTerrena();
                    $one["terrenaTipoAplicacao"]        = (!empty($terrena))? $terrena->getTipoAplicacao() : '';
                    $one["terrenaTipoAplicacaoOutra"]   = (!empty($terrena))? $terrena->getTipoAplicacaoOutra() : '';
                    $one["terrenaLatitude"]             = (!empty($terrena))? $terrena->getLatitude() : '';
                    $one["terrenaLongitude"]            = (!empty($terrena))? $terrena->getLongitude() : '';
                    $one["terrenaSatelite"]     = (!empty($terrena))? $terrena->getSatelite() : '';
                    $one["terrenaBanda"]        = (!empty($terrena))? $terrena->getBanda() : '';
                    $one["terrenaBandaOutra"]   = (!empty($terrena))? $terrena->getBandaOutra() : '';

                    $one['terrenaPortadoras']   = [];
                    $portadoraIndex = 1;
                    $portadoras = (empty($terrena->getPortadoras()))? [] : $terrena->getPortadoras();
                    foreach ($portadoras as $port){
                        $child = [];
                        $child['polarizacao'.$portadoraIndex]       = $port->getPolarizacao();
                        $child['polarizacaoOutra'.$portadoraIndex]  = $port->getPolarizacaoOutra();
                        $child['transponder'.$portadoraIndex]       = $port->getTransponder();
                        $child['frequenciaCentral'.$portadoraIndex] = $port->getFrequencia()->getFrequencia();
                        $child['bw'.$portadoraIndex]                = $port->getFrequencia()->getBw();
                        $child['modulacao'.$portadoraIndex]         = $port->getModulacao();
                        $child['fec'.$portadoraIndex]               = $port->getFec();
                        $child['localTransmissao'.$portadoraIndex]  = $port->getLocalTx();

                        array_push($one['terrenaPortadoras'], $child);
                        $portadoraIndex++;
                    }
                    array_push($arr, $one);
                }
            }

            $msgo = array("dados" => $arr );
            $this->data = $msgo;
            return $this;
        } catch (\Exception $e) {
            throw $e;
        }
    }

    public function respondWithJson() {
        return App::object()->getContainer()->view->render($this->response, 'json_response.html', array("response" => $this->data) );
    }

    public function respondWithHtml() {
        try {
            $plan   = new Spreadsheet();

            $plan->addColumn(new NumericColumn("Núm.Etiqueta"));
            $plan->addColumn(new DateColumn("Data Vistoria"));
            $plan->addColumn(new TextColumn("Entidade Nome"));
            $plan->addColumn(new DateColumn("Data Vistoria"));
            $plan->addColumn(new TextColumn("Entidade Nome"));
            $plan->addColumn(new TextColumn("Entidade Responsável"));
            $plan->addColumn(new TextColumn("Entidade Responsável Documento "));
            $plan->addColumn(new TextColumn("Entidade Responsável Email"));
            $plan->addColumn(new TextColumn("Entidade Responsável Telefone"));
            $plan->addColumn(new TextColumn("Entidade Responsável País"));
            $plan->addColumn(new TextColumn("Usuário Criador Etiqueta"));
            $plan->addColumn(new DateColumn("Data Criação Etiqueta"));
            $plan->addColumn(new TextColumn("Operador Etiquetagem"));
            $plan->addColumn(new DateColumn("Data Etiquetagem"));
            $plan->addColumn(new TextColumn("Etiqueta Tipo"));
            $plan->addColumn(new TextColumn("Etiqueta Cancelada"));
            $plan->addColumn(new DateColumn("Etiqueta Cancelada Data"));
            $plan->addColumn(new TextColumn("Etiqueta Cancelada Motivo"));
            $plan->addColumn(new TextColumn("Cluster Nome"));
            $plan->addColumn(new TextColumn("Cluster Sigla"));
            $plan->addColumn(new NumericColumn("Cluster Etiq. Mín."));
            $plan->addColumn(new NumericColumn("Cluster Etiq. Máx."));
            $plan->addColumn(new TextColumn("Local Etiquetagem Nome"));
            $plan->addColumn(new TextColumn("Local Etiquetagem Sigla"));
            $plan->addColumn(new TextColumn("Local Etiquetagem Grupo"));
            $plan->addColumn(new TextColumn("Venue Principal Nome"));
            $plan->addColumn(new TextColumn("Venue Principal Sigla"));
            $plan->addColumn(new TextColumn("Venue Principal Grupo"));
            $plan->addColumn(new TextColumn("Eqpto Fabricante"));
            $plan->addColumn(new TextColumn("Eqpto Modelo"));
            $plan->addColumn(new TextColumn("Eqpto Núm. Série"));
            $plan->addColumn(new TextColumn("Eqpto Descrição"));
            $plan->addColumn(new NumericColumn("Núm. Ato"));

            $plan->addColumn(new TextColumn("Tipo Aplicacao"));
            $plan->addColumn(new TextColumn("Tipo Aplicacao Outra"));
            $plan->addColumn(new TextColumn("Latitude"));
            $plan->addColumn(new TextColumn("Longitude"));
            $plan->addColumn(new TextColumn("Nome Satélite"));
            $plan->addColumn(new TextColumn("Banda"));
            $plan->addColumn(new TextColumn("Banda Outra"));

            $plan->addColumn(new TextColumn("Polarização"));
            $plan->addColumn(new TextColumn("Polarização Outra"));
            $plan->addColumn(new TextColumn("Transponder"));
            $plan->addColumn(new NumericColumn("Frequência Central (Hz)"));
            $plan->addColumn(new NumericColumn("BW (Hz)"));
            $plan->addColumn(new TextColumn("Modulação"));
            $plan->addColumn(new TextColumn("FEC"));
            $plan->addColumn(new TextColumn("Local Transmissão"));
            $plan->addColumn(new TextColumn("Período Transmissão"));
            foreach ( $this->data['dados'] as $dados ){
                //          foreach ( array_keys($dados) as $k ){
                unset($dados['etiquetaDataVistoria']);
                unset($dados['usuarioDataCriacaoEtiqueta']);
                unset($dados['operadorDataEtiquetagem']);
                unset($dados['etiquetaCanceladaData']);

                $portadoras = $dados['terrenaPortadoras'];
                unset($dados['terrenaPortadoras']);

                if(empty($portadoras)){
                    array_push($dados, null);
                    array_push($dados, null);
                    array_push($dados, null);
                    array_push($dados, null);
                    array_push($dados, null);
                    array_push($dados, null);
                    array_push($dados, null);
                    array_push($dados, null);
                    array_push($dados, null);
                    $plan->addRow(array_values($dados));
                } else {
                    foreach ($portadoras as $p){
                        $linha = $dados;
                        for($i=0; $i < count($p); $i++){
                            $val = array_values($p)[$i];
                            array_push($linha, $val );
                        }
                        $plan->addRow(array_values($linha));
                    }
                }
            }

            $writer = new CsvWriter();
            $writer->includeColumnHeaders = true;
            $writer->charset = CsvWriter::CHARSET_ISO;
            $plan->download($writer,  'relatorio_terrena.csv' );

        } catch (\Exception $e) {
            throw $e;
        }

    }
}