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

小开

最佳答案

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

//  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

小开

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

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

小开

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

小开

Here is a very recent answer to this question from the file: 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;

小开

Using the PHPExcel library, the following code will do.

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++;
}

小开

Inci framework you can do download like so:

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