使用 PHPExcel 读取 XLSX 表以提供 MySQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7156228/
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
Reading a XLSX sheet to feed a MySQL table using PHPExcel
提问by mlh
I found the PHPExcel library brilliant to manipulate Excel files with PHP (read, write, and so on).
我发现 PHPExcel 库非常适合使用 PHP 操作 Excel 文件（读取、写入等）。
But nowhere in the documentation is explained how to read a XLSX worksheet to feed a MySQL table...
但是文档中没有任何地方解释如何读取 XLSX 工作表以提供 MySQL 表......
Sorry for this silly question, but i need it for my work and found no answer on the web.
抱歉这个愚蠢的问题，但我的工作需要它，但在网上找不到答案。
A small example could be very helpful.
一个小例子可能会很有帮助。
Thanks a lot.
非常感谢。
UPDATED :
更新 ：
I precise my question :
我精确我的问题：
The only part of code i found in the documentation that could help me is to read an Excel file and display it in a HTML table :
我在文档中找到的唯一可以帮助我的代码部分是读取 Excel 文件并将其显示在 HTML 表格中：
`require_once 'phpexcel/Classes/PHPExcel.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load("edf/equipement.xlsx");
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
echo '<table border="1">' . "\n";
for ($row = 1; $row <= $highestRow; ++$row) {
echo '<tr>' . "\n";
for ($col = 0; $col <= $highestColumnIndex; ++$col) {
echo '<td>' . $objWorksheet->getCellByColumnAndRow($col, $row)->getValue() . '</td>' . "\n";
}
echo '</tr>' . "\n";
}
echo '</table>' . "\n";`
I know i can use the loop to feed my MySQL table, but i don't know how... I'm not aware in OOP...
我知道我可以使用循环来提供我的 MySQL 表，但我不知道如何......我不知道在 OOP ......
Can somebody help me, please ?
有人可以帮我吗？
采纳答案by Imad Moqaddem
The first forloops through rows, and the second one loops through columns.
So, there are plenty of solutions to your "problem".
第一个for循环遍历行，第二个循环遍历列。因此，您的“问题”有很多解决方案。
You could, for example, populate an array and make an insert statement for each row. As the following :
例如，您可以填充一个数组并为每一行创建一个插入语句。如下：
$rows = array();
for ($row = 1; $row <= $highestRow; ++$row) {
for ($col = 0; $col <= $highestColumnIndex; ++$col) {
$rows[$col] = mysql_real_espace_string($objWorksheet->getCellByColumnAndRow($col, $row)->getValue());
}
mysql_query("INSERT INTO your_table (col1,col2) VALUES ($rows[1],$rows[2])");
}
Obviously, this code can be improved.
显然，这段代码可以改进。
回答by maestro416
Here is the code
这是代码
$inputFileName = $upload_path . $filename;
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$rows = array();
for ($row = 1; $row <= $highestRow; ++$row) {
for ($col = 0; $col <= $highestColumnIndex; ++$col) {
$rows[$col] = $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
}
mysql_query("INSERT INTO upload (`item_number`,`qty_sold`,`cost_home`) VALUES ($rows[1],$rows[2],$rows[3])");
}
?>
I have tried mysql_query("INSERT INTO upload (col1,col2) VALUES ($rows[1],$rows[2])"); as well but didn't work. The table stays empty
我试过 mysql_query("INSERT INTO upload (col1,col2) VALUES ($rows[1],$rows[2])"); 也是，但没有用。桌子一直空着
