php 通过网站将 Excel 工作表数据上传到 SQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17542300/
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
Uploading Excel sheet data into SQL database via a website
提问by RGriffiths
I am using phpto develop my website and I need to be able to browse for and upload an Excelsheet. I then need to store the information in an SQLdatabase.
我正在php开发我的网站，我需要能够浏览和上传工作Excel表。然后我需要将信息存储在SQL数据库中。
I realise this is an relatively open question but I am have looked for information on how to do this and have not got very far. Storing the data in the SQL database is fine but I am not sure how to upload the spreadsheet such that it can be read by the website.
我意识到这是一个相对开放的问题，但我一直在寻找有关如何做到这一点的信息，但并没有走多远。将数据存储在 SQL 数据库中很好，但我不确定如何上传电子表格以便网站可以读取它。
If anyone can point me in the right direction I would be grateful.
如果有人能指出我正确的方向，我将不胜感激。
回答by Bjoern
This question contains a lot of topics, I'll try a short summary on what has to be taken care of in order to solve your issue at hand.
这个问题包含很多主题，我将尝试对解决您手头的问题必须注意的事项进行简短总结。
(1) File Upload
(1) 文件上传
First you have to make an upload available. Basically you have at least two components.
首先，您必须使上传可用。基本上你至少有两个组件。
- The HTML form
- some (php) code to work with the file.
- HTML 表单
- 一些（php）代码来处理文件。
Example HTML code:
示例 HTML 代码：
<form action="your_upload_script.php" method="post" enctype="multipart/form-data">
Your XLS file: <input type="file" name="xlsfile" size="50" />
<input type="submit" name="submit" value="Submit" />
</form>
A simple, but solid example php code can be found here.
可以在此处找到一个简单但可靠的示例 php 代码。
Keep in mind, uploading files can have a few more issues, p.e. regulated upload sizes and such.
请记住，上传文件可能会有更多问题，如受监管的上传大小等。
(2) Parsing the Excel file
(2)解析Excel文件
Now that your file is uploaded, you have to parse it. I recommend using PHPExcel, which is a ...
现在您的文件已上传，您必须对其进行解析。我建议使用PHPExcel，这是一个......
... Project providing a set of classes for the PHP programming language, which allow you to write to and read from different spreadsheet file formats, like Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML, ...
... 项目为 PHP 编程语言提供了一组类，允许您写入和读取不同的电子表格文件格式，如 Excel (BIFF) .xls、Excel 2007 (OfficeOpenXML) .xlsx、CSV、Libre/OpenOffice Calc .ods、Gnumeric、PDF、HTML、...
(3) Storing the values in your tables
(3) 将值存储在表中
While or after parsing the spreadsheets you'll eventually have to store it in your tables. Whatever you do, please refrain from using the old mysql connector in php, since this one is old and deprecated. Use mysqlior PDOinstead.
在解析电子表格时或之后，您最终必须将其存储在表格中。无论您做什么，请不要在 php 中使用旧的 mysql 连接器，因为这个连接器已经过时且已弃用。请改用mysqli或PDO。
Again, this is only the short story. You'll eventually encounter more challenges and issues on the way.
再说一遍，这只是短篇小说。您最终会在途中遇到更多挑战和问题。
回答by Expedito
You could export your file as CSV, upload it and parse it with fgetcsv(). From the PHP manual:
您可以将文件导出为 CSV，上传并使用fgetcsv(). 从PHP 手册：
$row = 1;
if (($fh = fopen("excel.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
$num = count($data);
$row++;
for ($c=0; $c < $num; $c++) {
//make query and insert into database
}
}
fclose($handle);
}
回答by Goutam Pal
You can read the uploaded excel file using PHPExcel
您可以使用 PHPExcel 读取上传的 excel 文件
Download Link : https://github.com/PHPOffice/PHPExcel
回答by kmas
First, your Excel file has to be in CSV format, otherwise it will be a lot more difficult to insert data in your database (you have to parse it with PhpExcel for example).
首先，你的 Excel 文件必须是 CSV 格式，否则在你的数据库中插入数据会困难得多（例如你必须用 PhpExcel 解析它）。
If your Excel file is CSV, then you have to use LOAD DATA INFILE.
如果您的 Excel 文件是 CSV，那么您必须使用LOAD DATA INFILE。
You can see an example here : https://stackoverflow.com/a/10897669/1788704
你可以在这里看到一个例子：https: //stackoverflow.com/a/10897669/1788704
