PHP 将 Excel 导入数据库 (xls & xlsx)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11447918/
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
PHP import Excel into database (xls & xlsx)
提问by Ham
I tried to search for some plugins to import Excel file into MySQL database, one of them is http://code.google.com/p/php-excel-reader/
我试图搜索一些插件将 Excel 文件导入 MySQL 数据库，其中之一是http://code.google.com/p/php-excel-reader/
The tool is so powerful that it displays the entire excel content into html.
该工具非常强大，可以将整个 excel 内容显示为 html。
However, I think I just need to read the Excel file and extract the contents, for example, into an array, and then write a SQL statement for entering into the database.
但是，我觉得我只需要读取Excel文件并将内容提取出来，例如，放入一个数组，然后编写一个进入数据库的SQL语句即可。
Would there be any good codes and packages? Thanks!
有没有好的代码和包？谢谢！
回答by diEcho
This is best plugin with proper documentation and examples
这是具有适当文档和示例的最佳插件
https://github.com/PHPOffice/PHPExcel
https://github.com/PHPOffice/PHPExcel
Plus point:you can ask for help in its discussion forumand you will get response within a day from the author itself, really impressive.
加点：你可以在它的讨论区寻求帮助，你会在一天之内得到作者本人的回复，真的很令人印象深刻。
回答by shukshin.ivan
Sometimes I need to import large xlsx files into database, so I use spreadsheet-readeras it can read file per-row. It is very memory-efficientway to import.
有时我需要将大型 xlsx 文件导入数据库，所以我使用spreadsheet-reader它可以读取每行文件。这是一种非常节省内存的导入方式。
<?php
// If you need to parse XLS files, include php-excel-reader
require('php-excel-reader/excel_reader2.php');
require('SpreadsheetReader.php');
$Reader = new SpreadsheetReader('example.xlsx');
// insert every row just after reading it
foreach ($Reader as $row)
{
$db->insert($row);
}
?>
回答by user1259132
If you can convert .xls to .csv before processing, you can use the query below to import the csv to the database:
如果您可以在处理前将 .xls 转换为 .csv，则可以使用以下查询将 csv 导入数据库：
load data local infile 'FILE.CSV' into table TABLENAME fields terminated by ',' enclosed by '"' lines terminated by '\n' (FIELD1,FIELD2,FIELD3)
回答by Jon Taylor
If you save the excel file as a CSV file then you can import it into a mysql database using tools such as PHPMyAdmin
如果将 excel 文件另存为 CSV 文件，则可以使用 PHPMyAdmin 等工具将其导入 mysql 数据库
Im not sure if this would help in your situation, but a csv file either manually or programatically would be a lot easier to parse into a database than an excel file I would have thought.
我不确定这对您的情况是否有帮助，但是手动或以编程方式将 csv 文件解析到数据库中要比我想象的 excel 文件容易得多。
EDIT: I would however suggest looking at the other answers rather than mine since @diEcho answer seems more appropriate.
编辑：不过，我建议查看其他答案而不是我的答案，因为 @diEcho 答案似乎更合适。
回答by Ozsvar Istvan
I wrote an inherited class:
我写了一个继承的类：
<?php
class ExcelReader extends Spreadsheet_Excel_Reader {
function GetInArray($sheet=0) {
$result = array();
for($row=1; $row<=$this->rowcount($sheet); $row++) {
for($col=1;$col<=$this->colcount($sheet);$col++) {
if(!$this->sheets[$sheet]['cellsInfo'][$row][$col]['dontprint']) {
$val = $this->val($row,$col,$sheet);
$result[$row][$col] = $val;
}
}
}
return $result;
}
}
?>So I can do this:
所以我可以这样做：
<?php
$data = new ExcelReader("any_excel_file.xls");
print_r($data->GetInArray());
?>