php - 使用 Phpspreadsheet 将列作为行从 Excel 插入到 mysql

Question

不知道如何格式化标题。对不起。
我正在使用PhpSpreadsheet将INSERTExcel 列插入 MySql 数据库。

我有的

我试过的

...

$activeSheet = $spreadsheet->getActiveSheet();
$sheetData = $activeSheet->toArray();

foreach($sheetData as $row) {
    $col1 = $row[0];

    $sql = "INSERT INTO rowascol (n1) VALUES ($col1)";
    $query = mysqli_query($con, $sql);
}

...

我得到了什么

+----+----+----+----+----+----+
| id | n1 | n2 | n3 | n4 | n5 |
+----+----+----+----+----+----+
|  1 |  4 |    |    |    |    |
|  2 |  6 |    |    |    |    |
|  3 | 45 |    |    |    |    |
|  4 |  5 |    |    |    |    |
|  5 |  7 |    |    |    |    |
+----+----+----+----+----+----+

我想要的是

+----+----+----+----+----+----+
| id | n1 | n2 | n3 | n4 | n5 |
+----+----+----+----+----+----+
|  1 |  4 |  6 | 45 |  5 |  7 |
|  2 |  9 |  3 |  5 |  8 | 12 |
+----+----+----+----+----+----+

标签： phpmysqlphpspreadsheet

Answer 1

下面的代码片段可能会有所帮助：

...

$highestColumn= ord($activeSheet->getHighestColumn())- ord('A') + 1;

for ($col = 0; $col < $highestColumn; $col++) {
    $col1 = array();
    foreach($sheetData as $row) {
        array_push($col1, $row[$col]);
    }
    $col1 = implode(',', $col1);

    $col1 = $col + 1 . ',' . $col1;  // add id
    $sql = "INSERT INTO rowascol VALUES ($col1)";
    // echo $sql;
    $query = mysqli_query($con, $sql);
}

...