php 如何使用phpexcel读取数据并插入数据库？

How to use phpexcel to read data and insert into database?

提问by coder101

I have a phpapplication where I want to read data from excel, Insert into database and then generate pdf reports for specific users. I searched a lot but nothing specific given about both things.

我有一个php应用程序，我想从 excel 中读取数据，插入到数据库中，然后为特定用户生成 pdf 报告。我搜索了很多，但没有给出关于这两件事的具体信息。

回答by Mark Baker

Using the PHPExcel library to read an Excel file and transfer the data into a database

使用PHPExcel库读取Excel文件并将数据转入数据库

//  Include PHPExcel_IOFactory
include 'PHPExcel/IOFactory.php';

$inputFileName = './sampleData/example1.xls';

//  Read your Excel workbook
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();

//  Loop through each row of the worksheet in turn
for ($row = 1; $row <= $highestRow; $row++){ 
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                    NULL,
                                    TRUE,
                                    FALSE);
    //  Insert row data array into your database of choice here
}

Anything more becomes very dependent on your database, and how you want the data structured in it

更多的东西变得非常依赖于您的数据库，以及您希望在其中构建数据的方式

回答by Naser Gulzade

In order to read data from microsoft excel 2007 by codeigniter just create a helper function excel_helper.php and add the following in:

为了通过 codeigniter 从 microsoft excel 2007 读取数据，只需创建一个辅助函数 excel_helper.php 并添加以下内容：

      require_once APPPATH.'libraries/phpexcel/PHPExcel.php';
      require_once APPPATH.'libraries/phpexcel/PHPExcel/IOFactory.php';
      in controller add the following code to read spread sheet by active sheet
     //initialize php excel first  
     ob_end_clean();
     //define cachemethod
     $cacheMethod   = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
     $cacheSettings = array('memoryCacheSize' => '20MB');
     //set php excel settings
     PHPExcel_Settings::setCacheStorageMethod(
            $cacheMethod,$cacheSettings
          );

    $arrayLabel = array("A","B","C","D","E");
    //=== set object reader
    $objectReader = PHPExcel_IOFactory::createReader('Excel2007');
    $objectReader->setReadDataOnly(true);

    $objPHPExcel = $objectReader->load("./forms/test.xlsx");
    $objWorksheet = $objPHPExcel->setActiveSheetIndexbyName('Sheet1');

    $starting = 1;
    $end      = 3;
    for($i = $starting;$i<=$end; $i++)
    {

       for($j=0;$j<count($arrayLabel);$j++)
       {
           //== display each cell value
           echo $objWorksheet->getCell($arrayLabel[$j].$i)->getValue();
       }
    }
     //or dump data
     $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
     var_dump($sheetData);

     //see also the following link
     http://blog.mayflower.de/561-Import-and-export-data-using-PHPExcel.html
     ----------- import in another style around 5000 records ------
     $this->benchmark->mark('code_start');
    //=== change php ini limits. =====
    $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
    $cacheSettings = array( ' memoryCacheSize ' => '50MB');
    PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
    //==== create excel object of reader
    $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    //$objReader->setReadDataOnly(true);
    //==== load forms tashkil where the file exists
    $objPHPExcel = $objReader->load("./forms/5000records.xlsx");
    //==== set active sheet to read data
    $worksheet  = $objPHPExcel->setActiveSheetIndexbyName('Sheet1');


    $highestRow         = $worksheet->getHighestRow(); // e.g. 10
    $highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    $nrColumns          = ord($highestColumn) - 64;
    $worksheetTitle     = $worksheet->getTitle();

    echo "<br>The worksheet ".$worksheetTitle." has ";
    echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
    echo ' and ' . $highestRow . ' row.';
    echo '<br>Data: <table border="1"><tr>';
    //----- loop from all rows -----
    for ($row = 1; $row <= $highestRow; ++ $row) 
    {
        echo '<tr>';
        echo "<td>".$row."</td>";
        //--- read each excel column for each row ----
        for ($col = 0; $col < $highestColumnIndex; ++ $col) 
        {
            if($row == 1)
            {
                // show column name with the title
                 //----- get value ----
                $cell = $worksheet->getCellByColumnAndRow($col, $row);
                $val = $cell->getValue();
                //$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
                echo '<td>' . $val ."(".$row." X ".$col.")".'</td>';
            }
            else
            {
                if($col == 9)
                {
                    //----- get value ----
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    $val = $cell->getValue();
                    //$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
                    echo '<td>zone ' . $val .'</td>';
                }
                else if($col == 13)
                {
                    $date = PHPExcel_Shared_Date::ExcelToPHPObject($worksheet->getCellByColumnAndRow($col, $row)->getValue())->format('Y-m-d');
                    echo '<td>' .dateprovider($date,'dr') .'</td>';
                }
                else
                {
                     //----- get value ----
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    $val = $cell->getValue();
                    //$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
                    echo '<td>' . $val .'</td>';
                }
            }
        }
        echo '</tr>';
    }
    echo '</table>';
    $this->benchmark->mark('code_end');

    echo "Total time:".$this->benchmark->elapsed_time('code_start', 'code_end');     
    $this->load->view("error");

回答by Waqas Bukhary

Using the PHPExcellibrary, the following code will do.

使用PHPExcel库，以下代码将完成。

require_once dirname(__FILE__) . '/../Classes/PHPExcel/IOFactory.php';    
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true); //optional

$objPHPExcel = $objReader->load(__DIR__.'/YourExcelFile.xlsx');
$objWorksheet = $objPHPExcel->getActiveSheet();

$i=1;
foreach ($objWorksheet->getRowIterator() as $row) {

    $column_A_Value = $objPHPExcel->getActiveSheet()->getCell("A$i")->getValue();//column A
    //you can add your own columns B, C, D etc.

    //inset $column_A_Value value in DB query here

    $i++;
}

回答by Naser Gulzade

      if($query)
       {
        // try to export to excel the whole data ---
        //initialize php excel first  
        ob_end_clean();
        //--- create php excel object ---
        $objPHPExcel = new PHPExcel();
        //define cachemethod
        ini_set('memory_limit', '3500M');
        $cacheMethod   = PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
        $cacheSettings = array('memoryCacheSize' => '800MB');
        //set php excel settings
        PHPExcel_Settings::setCacheStorageMethod(
                $cacheMethod,$cacheSettings
              );


        $objPHPExcel->getProperties()->setTitle("export")->setDescription("none");

        $objPHPExcel->setActiveSheetIndex(0);

        // Field names in the first row
        $fields = $query->list_fields();
        $col = 0;
        foreach ($fields as $field)
        {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $field);
            $col++;
        }

        // Fetching the table data
        $row = 2;
        foreach($query->result() as $data)
        {
            $col = 0;
            foreach ($fields as $field)
            {
                $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $data->$field);
                $col++;
            }

            $row++;
        }

        $objPHPExcel->setActiveSheetIndex(0);
        //redirect to cleint browser
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=Provinces.xlsx');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
     }

回答by user1979239

    if($this->mng_auth->get_language()=='en')
          {
                    $excel->getActiveSheet()->setRightToLeft(false); 
          }
                else
                {  
                    $excel->getActiveSheet()->setRightToLeft(true); 
                }

       $styleArray = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THIN,
                            'color' => array('argb' => '00000000'),
                    ),
                ),
            );

          //SET property
          $objPHPExcel->getActiveSheet()->getStyle('A1:M10001')->applyFromArray($styleArray);


    $objPHPExcel->getActiveSheet()->getStyle('A1:M10001')->getAlignment()->setWrapText(true); 


$objPHPExcel->getActiveSheet()->getStyle('A1:'.chr(65+count($fields)-1).$query->num_rows())->applyFromArray($styleArray);  

$objPHPExcel->getActiveSheet()->getStyle('A1:'.chr(65+count($fields)-1).$query->num_rows())->getAlignment()->setWrapText(true); 

回答by AgnosticDev

Here is a very recent answer to this question from the file: 07reader.php

这是文件中对这个问题的最新回答：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;

回答by vinod maurya

Inci framework you can do download like so:

您可以像这样下载 Inci 框架：

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');