阅读XLSX工作表以使用PHPExcel馈送MySQL表 [英] Reading a XLSX sheet to feed a MySQL table using PHPExcel

问题描述

我发现PHPExcel库非常适合使用PHP操作Excel文件(读，写等).

I found the PHPExcel library brilliant to manipulate Excel files with PHP (read, write, and so on).

但是文档中没有任何地方解释如何阅读XLSX工作表来填充MySQL表 ...

But nowhere in the documentation is explained how to read a XLSX worksheet to feed a MySQL table...

很抱歉这个愚蠢的问题，但我的工作需要它，但在网络上找不到答案.

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.

非常感谢.

已更新:

我明确了我的问题:

我在文档中找到的唯一可以帮助我的代码是读取Excel文件并将其显示在HTML表格中:

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 :

`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";`

我知道我可以使用循环来馈送我的MySQL表，但是我不知道如何...我不知道OOP ...

I know i can use the loop to feed my MySQL table, but i don't know how... I'm not aware in OOP...

请问有人可以帮我吗?

推荐答案

第一个for遍历行，第二个for遍历列. 因此，有很多解决您的问题"的方法.

The first for loops through rows, and the second one loops through columns. So, there are plenty of solutions to your "problem".

例如，您可以填充一个数组并为每行创建一条插入语句. 如下:

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.

这篇关于阅读XLSX工作表以使用PHPExcel馈送MySQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！