我有一个 php 应用程序，想要从 Excel 中读取数据，插入到数据库中，然后为特定用户生成 PDF 报告。
我搜索了很多但没有得到关于这两件事情的具体信息。
如何使用PHPExcel读取数据并插入到数据库中？
111
- coder101
5
你有没有考虑将Excel数据导出为CSV格式？你的数据中是否有什么因素排除了CSV作为选项？ - Telmo Marques
2你看过PHPExcel库了吗？ - Mark Baker
@MarkBaker 我还没有看过这个库，虽然我尝试查看了函数参考文件，但由于我必须尽快让这个东西工作并处理项目的其他部分，所以文件太大了，无法深入研究。 - coder101
@TelmoMarques 我们曾考虑过通过CSV来完成，但想使用PHPExcel进行操作。否则，CSV这个选项始终存在。 - coder101
可以看一下这个教程。希望对你有所帮助- http://www.tisuchi.com/use-phpexcel-library/ - tisuchi
7个回答
232
使用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
}
对于任何更多的需求都非常依赖于您的数据库，以及您希望在其中如何结构化数据
- Mark Baker
5
我今天用版本1.7.9, 2013-06-02测试了一下，但它没有起作用。我不得不将
$highestColumn = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); 替换为简单的 $sheet->getHighestColumn()。你的代码可能存在缺陷，因为尝试从字符串中获取列索引，但是在循环中尝试通过 $highestColumn.$row 访问它（如果您不使用字符，则只会给出奇怪的链接整数）。 - user151496最高列应该是一个列字母值；所以你是对的 - 这应该只是设置
$highestColumn = $sheet->getHighestColumn(); 而不是 $highestColumn = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn()); 我也不是万无一失的。 - Mark Baker9代码已经过时，需要更新到1.8.0版本，更新后的代码可参考以下示例：https://github.com/PHPOffice/PHPExcel/blob/develop/Examples/28iterator.php。 - Lucas Serafim
2
getHighestColumn()每次返回255。将其替换为getHighestDataColumn()可以解决问题。这里有更多信息：https://dev59.com/mnDYa4cB1Zd3GeqPDbd0 - Martin Dzhonov@coder101 你好。能否给我你的PHP Pg_Query，将行数据数组插入到数据库中？谢谢。 - Shieryn
13
要通过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");
- Naser Gulzade
5
使用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++;
}
- Syed Waqas Bukhary
3
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');
}
- Naser Gulzade
2
谢谢你。实际上我几个月前就发布了这个问题，并且当时已经有了一个解决方法。但下次我处理这个功能时，我一定会使用你的建议。再次感谢。 - coder101
似乎是数据库对象。 - Muhammad Amjad
2
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);
- user1979239
0
这是来自文件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;
- AgnosticDev
1
我真的不确定这样回答是否以任何方式回答了原帖作者的问题。 - McAuley
0
在 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');
- vinod maurya
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接