Issue
I am working on a CRM system and in it I need to create reports in excel, I need the option to export all the data of the database in excel and also the option of the user to filter the data they want and export only these options.
I already have the display and search method separate and working both use the same view to view the content (total or filtered) I also have the export method to Excel, but I can only export the whole database (without the filters ).
My initial idea was to make my view send to the get_excel method what is being displayed and then the method only generates the excel itself but I do not know how to pass that data array from the view to the present method on the controler.
Here are the codes I'm using:
Methods of controler:
Indexshows all DB data for the view
function index()
{
$this->template->set('title', 'Lista de Produtos');
$config = array(
"base_url" => base_url('produtos/p'),
"per_page" => 9,
"num_links" => 3,
"uri_segment" => 3,
"total_rows" => $this->model->countAll(),
"full_tag_open" => "<ul class='pagination'>",
"full_tag_close" => "</ul>",
"first_link" => FALSE,
"last_link" => FALSE,
"first_tag_open" => "<li>",
"first_tag_close" => "</li>",
"prev_link" => "Anterior",
"prev_tag_open" => "<li class='prev'>",
"prev_tag_close" => "</li>",
"next_link" => "Próxima",
"next_tag_open" => "<li class='next'>",
"next_tag_close" => "</li>",
"last_tag_open" => "<li>",
"last_tag_close" => "</li>",
"cur_tag_open" => "<li class='active'><a href='#'>",
"cur_tag_close" => "</a></li>",
"num_tag_open" => "<li>",
"num_tag_close" => "</li>"
);
$this->pagination->initialize($config);
$data['pagination'] = $this->pagination->create_links();
$offset = ($this->uri->segment(3)) ? $this->uri->segment(3):0;
$data['produtos'] = $this->model->listar('pcod','asc', $config['per_page'],$offset);
$this->template->load('layout', 'produtos_lista.phtml', $data);
}
Searh:
Use the filters to display only the requested data
public function pesquisar() {
$this->template->set('title', 'Resultado');
$data['pagination'] = "";
$data['produtos'] = $this->model->search();
$this->template->load('layout', 'produtos_lista.phtml', $data);
}
Get_excel:
Generates the report in excel
function get_excel(){
//$this->load->library('PHPExcel');
$contator = 1;
$arquivo = './planilhas/relatorio.xlsx';
$planilha = $this->phpexcel;
$planilha->setActiveSheetIndex(0)->setCellValue('A1','Codigo');
$planilha->setActiveSheetIndex(0)->setCellValue('B1','Nome');
$planilha->setActiveSheetIndex(0)->setCellValue('C1','Descrição');
$data['produtos'] = $this->model->listar();
//echo json_encode($data['produtos']);
//die('eieeiie');
foreach($data['produtos'] as $linha) {
$contator++;
$planilha->setActiveSheetIndex(0)->setCellValue('A'.$contator, $linha->pnome);
$planilha->setActiveSheetIndex(0)->setCellValue('B'.$contator, $linha->descricao);
$planilha->setActiveSheetIndex(0)->setCellValue('C'.$contator, $linha->pcod);
}
$planilha->getActiveSheet()->setTitle('planilha 1');
$objgravar = PHPExcel_IOFactory::createWriter($planilha, 'Excel2007');
$objgravar->save($arquivo);
$this->session->set_flashdata('mensagem', "<div class='alert alert-warning'> exportação salva com sucesso</div>");
redirect('produtos');
}
Finally my view:
<body>
<div class="row" >
<form action="/sistema/produtos/pesquisar" method="post">
<div class="col-sm-9">
<div class="form-group">
<input name="search" class="form-control" id="search" type="text"
placeholder="Filtrar produto " value="<?php echo $view_termo??null ;?>">
<span class="input-group-btn"></span>
</div>
</div>
<div class="col-sm-1">
<button class="btn btn-primary pull-left" type="submit">Filtrar</button>
</div>
</form>
<div class="col-sm-2">
<a data-toggle="modal" data-target="#new_produto" class="btn btn-primary ">Adicionar Produto</a>
</div>
</div>
<div id="list" class="row">
<div class="table-responsive col-md-12">
<table class="table table-striped" cellspacing="0" cellpadding="0">
<thead>
<tr>
<th>ID</th>
<th>Nome</th>
<th>Descrição</th>
<th class="actions">Ações</th>
</tr>
</thead>
<tbody>
<?php foreach ( $produtos as $produto ) {?>
<tr>
<td><?php echo $produto->pcod; ?></td>
<td><?php echo $produto->pnome; ?></td>
<td><?php echo $produto->descricao; ?></td>
<td class="actions">
<a title="Editar" class="btn btn-warning btn-xs" href="<?php echo base_url() . 'produtos/editar/' . $produto->pcod; ?>"> Editar</a>
<a title="Deletar" class="btn btn-danger btn-xs" href="<?php echo base_url() . 'produtos/deletar/' . $produto->pcod; ?>" onclick="return confirm('Confirma a exclusão deste registro?')">Deletar</a>
</td>
</tr>
<?php } ?>
</tbody>
</table>
<h3><?php echo $this->session->flashdata('mensagem');?></h3>
</div>
</div>
<div class="row">
<div class="col-sm-4" >
<?php echo $pagination; ?>
</div>
<div class="col-sm-2">
<a class="btn btn-primary" href="<?php echo base_url().'produtos/get_excel'?>">Export</a>
</div>
</div>
Solution
It's more easier to use javascript to take the html content and convert to csv with some plugin. If you do with backend, will need to take care of values, unset some vars and the form can be a mess with big arrays difficult to manipulate sometimes.
The visualization/filtering of content will be more easier too, because only setting some 'display: none' in css will do the trick. I know you tagged php/codeigniter/phpexcel, but I already did many projects that needed those features and I know how will be a pain in the ass if the content escalates quickly into a big data.
If you want to take my approach:
Some answers into Stack will recommend table2CSV from kunalbabre, but that plugin is discontinued/broken, so I suggest use excellentexport from Github, it's simple and has come with many approachs, one of them showed into README's git repository:
<table id="datatable">
<tr>
<td>100</td> <td>200</td> <td>300</td>
</tr>
<tr>
<td>400</td> <td>500</td> <td>600</td>
</tr>
</table>
<a download="somedata.xls" href="#" onclick="return ExcellentExport.excel(this, 'datatable', 'Sheet Name Here');">Export to Excel</a>
<a download="somedata.csv" href="#" onclick="return ExcellentExport.csv(this, 'datatable');">Export to CSV</a>
The columns that user want to filter the view, just identify with javascript and put display:none/visibility:hidden as you wish and you can easily order the columns content too by parsing with javascript or using plugins like sorttable, putting in your table:
<table class="sortable"></table>
The features with tables is so extensible and the users that will want to convert to CSV are so addicted with Excel features that I strongly suggest to give a try to implement into javascript.
Answered By - capcj Answer Checked By - Candace Johnson (PHPFixing Volunteer)
Posts
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.