comment utiliser phpexcel pour lire les données et les insérer dans la base de données ?

Question

J'ai un php Une application où je veux lire les données d'Excel, les insérer dans la base de données et ensuite générer des rapports pdf pour des utilisateurs spécifiques. J'ai beaucoup cherché sur Internet mais rien de précis n'a été donné sur ces deux sujets. Si quelqu'un pouvait fournir un tutoriel ou quelque chose, ce serait une vraie aide.

Answer 1

Voici une réponse très récente du dossier à cette question : 07reader.php

<?php

error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

date_default_timezone_set('Europe/London');

/** Include PHPExcel_IOFactory */
require_once '../Classes/PHPExcel/IOFactory.php';

if (!file_exists("05featuredemo.xlsx")) {
    exit("Please run 05featuredemo.php first." . EOL);
}

echo date('H:i:s') , " Load from Excel2007 file" , EOL;
$callStartTime = microtime(true);

$objPHPExcel = PHPExcel_IOFactory::load("05featuredemo.xlsx");

$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;
echo 'Call time to read Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
// Echo memory usage
echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;

echo date('H:i:s') , " Write to Excel2007 format" , EOL;
$callStartTime = microtime(true);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

$callEndTime = microtime(true);
$callTime = $callEndTime - $callStartTime;

echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , EOL;
echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , EOL;
// Echo memory usage
echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , EOL;

// Echo memory peak usage
echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , EOL;

// Echo done
echo date('H:i:s') , " Done writing file" , EOL;
echo 'File has been created in ' , getcwd() , EOL;

Answer 2

Inci framework vous pouvez faire le téléchargement comme ça :

function clubDownload($clubname)
{

    $this->load->library("excel");

    $object = new PHPExcel();
    $object->setActiveSheetIndex(0);
    $this->load->model('Members_student_model');
    $query = $this->db->query("SELECT * FROM student WHERE $clubname!=''  order by id desc");
    $resultdatanew=$query->result_array();
    $page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 1;

    $object->getActiveSheet()->getStyle("A1")->getFont()->setBold(true)
                            ->setName('Verdana')
                            ->setSize(10)
                            ->getColor()->setRGB('330000');

    $object->getActiveSheet()->getStyle("B1")->getFont()->setBold(true)
                            ->setName('Verdana')
                            ->setSize(10)
                            ->getColor()->setRGB('330000');

    $object->getActiveSheet()->getStyle("C1")->getFont()->setBold(true)
                            ->setName('Verdana')
                            ->setSize(10)
                            ->getColor()->setRGB('330000');

    $object->getActiveSheet()->getStyle("D1")->getFont()->setBold(true)
                            ->setName('Verdana')
                            ->setSize(10)
                            ->getColor()->setRGB('330000');

    $object->getActiveSheet()->getStyle("E1")->getFont()->setBold(true)
                            ->setName('Verdana')
                            ->setSize(10)
                            ->getColor()->setRGB('330000');

    $object->getActiveSheet()->getStyle("F1")->getFont()->setBold(true)
                            ->setName('Verdana')
                            ->setSize(10)
                            ->getColor()->setRGB('330000');
    $object->getActiveSheet()->getStyle("G1")->getFont()->setBold(true)
                            ->setName('Verdana')
                            ->setSize(10)
                            ->getColor()->setRGB('330000');

    $object->getActiveSheet()->getStyle("H1")->getFont()->setBold(true)
                            ->setName('Verdana')
                            ->setSize(10)
                            ->getColor()->setRGB('330000');
    $object->getActiveSheet()->getStyle("I1")->getFont()->setBold(true)
                            ->setName('Verdana')
                            ->setSize(10)
                            ->getColor()->setRGB('330000');
    $headerStyle = array(
                'fill' => array(
                        'type' => PHPExcel_Style_Fill::FILL_SOLID,
                        'color' => array('rgb'=>'CCE5FF'),
                ),
                'font' => array(
                        'bold' => true,
                )
        );

    $object->getActiveSheet()->getStyle('A1:'.'I1')->applyFromArray($headerStyle);
    $table_columns = array("id", "studentid", "passport", "lastname", "firstname","university","commencing",$clubname,"added_date");
    $column = 0;
    foreach($table_columns as $field)
    {
    $object->getActiveSheet()->setCellValueByColumnAndRow($column, 1, $field);
    $column++;
    }
    $excel_row = 2;

    foreach($resultdatanew as $row)
    {

                $id=$row['id'];
                $studentid=$row['studentid'];
                $passport=$row['passport'];
                $lastname=$row['last_name'];
                $firstname=$row['first_name'];
                $passport=$row['university'];
                $commencing=$row['commencing'];
                $email_id=$row['email_id'];
                $added_date=$row['added_date'];

                $object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row,$id);

                $object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $studentid);
                $object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $passport);
                $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, $lastname);
                $object->getActiveSheet()->setCellValueByColumnAndRow(4, $excel_row, $firstname);
                $object->getActiveSheet()->setCellValueByColumnAndRow(5, $excel_row, $passport);
                $object->getActiveSheet()->setCellValueByColumnAndRow(6, $excel_row,  $commencing);
                $object->getActiveSheet()->setCellValueByColumnAndRow(7, $excel_row, $email_id);
                $object->getActiveSheet()->setCellValueByColumnAndRow(8, $excel_row, $added_date);

                $excel_row++;
}

$object_writer = PHPExcel_IOFactory::createWriter($object, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="club' .$clubname.'-'.date('Y-m-d') . '.xls');
$object_writer->save('php://output');