php 使用PHPExcel将excel文件导入MySQL表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8221096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import an excel file into a MySQL table with PHPExcel
提问by Andres
Ok so I have been able to get php to show the data in excel .xls sheet but this same data I wanna be able to insert into my table. I can't seem to figure that part out, here's what I got so far:
好的，所以我已经能够让 php 显示 excel .xls 表中的数据，但是我希望能够插入到我的表中的相同数据。我似乎无法弄清楚那部分，这是我到目前为止所得到的：
$path = $_GET['file'];
include("../class/sql.php");
require '../class/PHPExcel.php';
require_once '../class/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load($path);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
echo '<br>Data: <table width="100%" cellpadding="3" cellspacing="0"><tr>';
for ($row = 1; $row <= $highestRow; ++ $row) {
echo '<tr>';
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
if($row === 1)
echo '<td style="background:#000; color:#fff;">' . $val . '</td>';
else
echo '<td>' . $val . '</td>';
}
echo '</tr>';
}
echo '</table>';
}
btw PHPExcel is awesome and I haven't had the time to read through all of it to fully understand :( I have to turn this in by wednesday.. Thanks in advance
顺便说一句，PHPExcel 很棒，我还没有时间通读所有内容以完全理解 :( 我必须在星期三之前将其交上来......在此先感谢
Edit: this is the idea that it should do..the values part is the one I am unsure about.
编辑：这是它应该做的想法......价值部分是我不确定的部分。
$sql = "insert into tablename (col1, col2, col3) values(...)";
//start at row 2 so headers are not inserted
for ($row = 2; $row <= $highestRow; ++ $row) {
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
//here's my prob..
echo $val;
}
$result = mysql_query($sql);
}
采纳答案by Daniel Ruf
You should create an array and store it in the database like this for example:
您应该创建一个数组并将其存储在数据库中，例如：
for ($row = 2; $row <= $highestRow; ++ $row) {
$val=array()
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val[] = $cell->getValue();
//here's my prob..
//echo $val;
}
$sql="insert into tablename (col1, col2, col3) values(`".$val[0]."`, `".$val[1]."`, `".$val[2].")";
$result = mysql_query($sql);
}
回答by Webmaster
if you want to use PHPExcel to do this :
如果你想使用 PHPExcel 来做到这一点：
<?php
//include the following 2 files
require 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
$SERVER = 'localhost';
$USERNAME = 'username';
$PASSWORD = 'password';
$DB = 'database';
$DSN = "mysql:host=".$SERVER.";dbname=".$DB."";
$connection = new PDO($DSN,$USERNAME,$PASSWORD);
$path = "test.xlsx";
$objPHPExcel = PHPExcel_IOFactory::load($path);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
echo "<br>The worksheet ".$worksheetTitle." has ";
echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
echo ' and ' . $highestRow . ' row.';
echo '<br>Data: <table border="1"><tr>';
for ($row = 1; $row <= $highestRow; ++ $row) {
echo '<tr>';
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
echo '<td>' . $val . '<br>(Typ ' . $dataType . ')</td>';
}
echo '</tr>';
}
echo '</table>';
}
for ($row = 2; $row <= $highestRow; ++ $row) {
$val=array();
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val[] = $cell->getValue();
}
$Connection="INSERT INTO `users` (name, family, type) VALUES ('".$val[1] . "','" . $val[2] . "','" . $val[3]. "')";
}
?>
回答by sonseiya
this is a good article using pear library Spreadsheet...
这是一篇使用梨库电子表格的好文章......
http://major.io/2008/11/07/importing-excel-files-into-mysql-with-php/
http://major.io/2008/11/07/importing-excel-files-into-mysql-with-php/
Check it
核实
