как использовать phpexcel для чтения данных и вставки в базу данных?

Использование библиотеки 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 } 

Все больше становится очень зависимым от вашей базы данных и как вы хотите, чтобы структурированные в нем данные

Чтобы читать данные из microsoft excel 2007 с помощью codeigniter, просто создайте вспомогательную функцию 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(); // eg 10 $highestColumn = $worksheet->getHighestColumn(); // eg '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('Ym-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"); 

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

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

Вот очень недавний ответ на этот вопрос из файла: 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; 

Используя библиотеку 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++; }