I recently asked about this topic on this website, and since I managed to import the data correctly, and after almost a week searching the Internet, I decided that it would be good to publish a question about it, since the biggest part of information about this is in English, I am sure that more than one person will serve. I must say that I am not an expert programming, and that recently I started with codeigniter (the community has helped me a lot), so if anyone has any suggestions, it is welcome.
The first thing is our database, in this case, I have a table called schedules:
The following is our EXCEL file
We will use a library called Spout, you can download it for free at the following link: Spout once you have downloaded it, unzip it in the third_party folder of your project
In this case we will only use a controller called cHorarios:
require_once APPPATH.'/third_party/Spout/Autoloader/autoload.php';
use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;
//Con las lineas anteriores cargamos la librería
class cHorarios extends CI_Controller{
function __construct()
{
parent::__construct();
//esto es de mi proyecto, no lo consideren
$this->load->model('mLogin');
$this->load->database();
$this->load->model('mHorarios');
}
public function index(){
//aquí solo estoy cargando el diseño no lo tomen en consideración
$this->load->view('layouts/header.php');
$this->load->view('layouts/menu.php');
$this->load->view('layouts/footer.php');
}
public function guardar_horario(){
if (!empty($_FILES['file']['name'])) {
$pathinfo = pathinfo($_FILES["file"]["name"]);
if (($pathinfo['extension'] == 'xlsx' || $pathinfo['extension'] == 'xls')
&& $_FILES['file']['size'] > 0 ) {
// Nombre Temporal del Archivo
$inputFileName = $_FILES['file']['tmp_name'];
//Lee el Archivo usando ReaderFactory
$reader = ReaderFactory::create(Type::XLSX);
//Esta linea mantiene el formato de nuestras horas y fechas
//Sin esta linea Spout convierte la hora y fecha a su propio formato
//predefinido como DataTime
$reader->setShouldFormatDates(true);
// Abrimos el archivo
$reader->open($inputFileName);
$count = 1;
//Numero de Hojas en el Archivo
foreach ($reader->getSheetIterator() as $sheet) {
// Numero de filas en el documento EXCEL
foreach ($sheet->getRowIterator() as $row) {
// Lee los Datos despues del encabezado
// El encabezado se encuentra en la primera fila
if($count > 1) {
$data = array(
//rut_usu, fecha_ini, y fecha_ter son imputs del formulario
//no se encuentran en el archivo EXCEL
//los estoy capturando por POST
'rut_usu' => $this->input->post('rut_usu'),
'hrs_ini' => $row[0],
'hrs_ter' => $row[1],
'lunes' => $row[2],
'martes' => $row[3],
'miercoles' => $row[4],
'jueves' => $row[5],
'viernes' => $row[6],
'sabado' => $row[7],
'fecha_ini' => $this->input->post('fecha_ini'),
'fecha_ter' => $this->input->post('fecha_ter')
);
$this->db->insert('horario',$data);
}
$count++;
}
}
// cerramos el archivo EXCEL
$reader->close();
} else {
echo "Seleccione un tipo de Archivo Valido";
}
} else {
echo "Seleccione un Archivo EXCEL";
}
}
}
Then in the view we need the corresponding inputs, I made a view for the tutorial, maybe a div is missing, but the important thing is the line of the form, when pressing the button the data written in the inputs and the data of the Excel file. I hope it serves you, it is my small contribution to the community, since I do not have so much knowledge. I say goodbye.
<html lang="es">
<head>
<title>Titulo de la web</title>
<meta charset="utf-8" />
</head>
<body>
<header>
<h1>Mi sitio web</h1>
<p>Mi sitio web creado en html5</p>
</header>
<section>
//abrimos el fromulario y llamamos a la función del controlador
<form action="<?php echo base_url(); ?>cHorarios/guardar_horario" method="POST" enctype="multipart/form-data">
//los imputs
<div class="col-md-3">
<div class="form-group">
<label>Sede</label>
<input type="text" class="form-control" id="rut_usu"
name="rut_usu">
</div>
</div>
<div class="col-md-3">
<div class="form-group">
<label>Sede</label>
<input type="text" class="form-control" id="fecha_ini" name="fecha_ini">
</div>
</div>
<div class="col-md-3">
<div class="form-group">
<label>Sede</label>
<input type="text" class="form-control" id="fecha_ter" name="fecha_ter">
</div>
</div>
<div class="col-md-3">
<div class="form-group">
<label>Seleccione un xlxs</label>
<input type="file" name="file" id="file" accept=".xlsx" class="form-control" >
</div>
</div>
<div class="col-md-3" style="top:25px; left: 30px;" >
<div class="form-group">
<button type="submit" id="Ingresar" name="Ingresar" class="btn bg-aqua">Guardar</button>
<button name="cancelar" id="cancelar" class="btn bg-red" style="width:80px;">Cancelar</button>
</div>
</div>
</div>
</form>
</section>
</body>
</html>