👰🏻 👩🏾‍🎤 🆚 ExcelからMySQLへ。 PHPの小さな関数（修正済み） 🙆🏿 🧙 👩🏿‍🎤

はじめに

こんにちは$ habrauser ！

ExcelファイルをMySQLデータベースにインポートする必要がある場合がありますが、既製のソリューションはどこにもありません。だから、友人が簡単なインポート方法を探すように頼んだとき、私は最初に解決策の検索を~~グーグルで~~決めることにしました。残念ながら、 mysqlクエリに優れたphpには具体的なものは何もありませんでした。または、説明した方法はかなり不便でした。次に、PHPでExcelを操作するためのライブラリを見つけることにし、PHPExcelに出会いました。しかし、再び、失望が待っていました。mysqlへのphpexcelへのリクエストは、価値のあるものを何も与えませんでした（私は怠userなユーザーであり、1ページ目より先には行きません）。最後に、私はあなたと共有したい~~自転車~~スクリプトを作成することにしました。

開始する

だから、私はライブラリを見つけ、ダウンロードして理解し始めました。まず、ライブラリを接続し、データベースへの接続を作成する必要がありましたが、これはまったく難しくありません。

require_once "PHPExcel.php"; $connection = new mysqli("localhost", "user", "pass", "base"); $connection->set_charset("utf8");

次に、Excelファイルを開いて読み取ります。

 $PHPExcel_file = PHPExcel_IOFactory::load("./file.xlsx");

ファイルを開いた後、その中のすべてのシートをソートして、各シートをMySQLデータベースに追加する必要があります（特定の1つを追加することもできますが、それについては後で詳しく説明します）。

 foreach ($PHPExcel_file->getWorksheetIterator() as $worksheet) { // ... }

さて、今最も興味深い...

反復して追加

テーブルがない（または他のデータが存在する）ため、テーブルを作成する必要があるという事実から進めます。これを行うには、列の名前を取得する必要があります（友人からの要求に応じて、名前はテーブルの1行に入れることができます）。

 //      MySQL $columns_str = ""; //     Excel $columns_count = PHPExcel_Cell::columnIndexFromString($worksheet->getHighestColumn()); //    Excel        for ($column = 0; $column < $columns_count; $column++) { $columns_str .= ($columns_name_on1line ? "column" . $column : $worksheet->getCellByColumnAndRow($column, 1)->getCalculatedValue()) . ","; } //  ,     $columns_str = substr($columns_str, 0, -1);

次に、データベースからテーブルを削除し（存在する場合）、新しいテーブルを作成します。

 $connection->query("DROP TABLE IF EXISTS exceltable"); $connection->query("CREATE TABLE exceltable (" . str_replace(",", " TEXT NOT NULL,", $columns_str) . " TEXT NOT NULL)");

コードからわかるように、値はTEXT型になります。次に、セルをソートしてデータベースに追加します。もちろん、そのようなアルゴリズムはStack Overflowのオープンスペースで見つけることは難しくありませんが、結合されたセルの読み取り中にエラーが発生したことがわかりました（より正確には、クエリの列と値の数が一致しませんでした）。これを考慮に入れることにしました：

 //     Excel $rows_count = $worksheet->getHighestRow(); //    Excel for ($row = 1; $row <= $rows_count; $row++) { //         Excel $value_str = ""; //    Excel for ($column = 0; $column < $columns_count; $column++) { //       Excel $merged_value = ""; //   Excel $cell = $worksheet->getCellByColumnAndRow($column, $row); //      Excel foreach ($worksheet->getMergeCells() as $mergedCells) { //    - , if ($cell->isInRange($mergedCells)) { //      ,       //   $merged_value = $worksheet->getCell(explode(":", $mergedCells)[0])->getCalculatedValue(); break; } } // ,    :  ,    ,    //   $value_str .= "'" . (strlen($merged_value) == 0 ? $cell->getCalculatedValue() : $merged_value) . "',"; } //  ,     $value_str = substr($value_str, 0, -1); //     MySQL $connection->query("INSERT INTO exceltable (" . $columns_str . ") VALUES (" . $value_str . ")"); }

機能がすべてです！

もちろん、このスクリプトは、すべてが1つの関数に結合されている場合、はるかに便利です。したがって、最終結果は次のようになります。

Excel2mysql関数

 //   require_once "PHPExcel.php"; //    Excel   MySQL,      . //    . : // $worksheet -  Excel // $connection -   MySQL (mysqli) // $table_name -   MySQL // $columns_name_line -      MySQL (0 -   column + n) function excel2mysql($worksheet, $connection, $table_name, $columns_name_line = 0) { //    MySQL if (!$connection->connect_error) { //      MySQL $columns_str = ""; //     Excel $columns_count = PHPExcel_Cell::columnIndexFromString($worksheet->getHighestColumn()); //    Excel        for ($column = 0; $column < $columns_count; $column++) { $columns_str .= ($columns_name_line == 0 ? "column" . $column : $worksheet->getCellByColumnAndRow($column, $columns_name_line)->getCalculatedValue()) . ","; } //  ,     $columns_str = substr($columns_str, 0, -1); //   MySQL,    if ($connection->query("DROP TABLE IF EXISTS " . $table_name)) { //   MySQL if ($connection->query("CREATE TABLE " . $table_name . " (" . str_replace(",", " TEXT NOT NULL,", $columns_str) . " TEXT NOT NULL)")) { //     Excel $rows_count = $worksheet->getHighestRow(); //    Excel for ($row = $columns_name_line + 1; $row <= $rows_count; $row++) { //         Excel $value_str = ""; //    Excel for ($column = 0; $column < $columns_count; $column++) { //       Excel $merged_value = ""; //   Excel $cell = $worksheet->getCellByColumnAndRow($column, $row); //      Excel foreach ($worksheet->getMergeCells() as $mergedCells) { //    - , if ($cell->isInRange($mergedCells)) { //      ,       //   $merged_value = $worksheet->getCell(explode(":", $mergedCells)[0])->getCalculatedValue(); break; } } // ,    :  ,    ,    //   $value_str .= "'" . (strlen($merged_value) == 0 ? $cell->getCalculatedValue() : $merged_value) . "',"; } //  ,     $value_str = substr($value_str, 0, -1); //     MySQL $connection->query("INSERT INTO " . $table_name . " (" . $columns_str . ") VALUES (" . $value_str . ")"); } } else { return false; } } else { return false; } } else { return false; } return true; } //    MySQL $connection = new mysqli("localhost", "user", "pass", "base"); //   UTF-8 $connection->set_charset("utf8"); //   Excel $PHPExcel_file = PHPExcel_IOFactory::load("./file.xlsx"); //    Excel   MySQL $PHPExcel_file->setActiveSheetIndex(0); echo excel2mysql($PHPExcel_file->getActiveSheet(), $connection, "excel2mysql0", 1) ? "OK\n" : "FAIL\n"; //    Excel     MySQL foreach ($PHPExcel_file->getWorksheetIterator() as $index => $worksheet) { echo excel2mysql($worksheet, $connection, "excel2mysql" . ($index != 0 ? $index : ""), 1) ? "OK\n" : "FAIL\n"; }

おわりに

この記事がお役に立てば幸いです。さて、または、 ~~自転車~~を~~発明し~~たい~~が、モーター~~で独自のスクリプトを書く場合、この記事はあなたが始めるのに役立ちます。

PS

これが私の最初であり、最後の記事ではないと思います。したがって、ここで慣例となっているように、コメントであなたのアドバイスと修正を待っています。

更新する

結局のところ、私は小さな議論を作成することができましたが、なぜこれが行われたのか誰もが理解しているわけではありません。説明しようとします。

最初：高齢者はこれで作業する必要があり、データを失うことなくCSVでファイルを保存する方法を説明するのは難しいでしょう（そして、これは除外することはできません、彼らは上から来るXLSファイルの独自のフォーマットを持っている以外に）特にphpMyAdmin（ちなみに、バージョン3.4.5 はXLS / XLSXをサポートしていないため、理由を確認することをお勧めします）などを介してインポートされるためです。そのため、適合しません。

第二に、これらはすべてホスティングに配置する必要があり、サーバーとローカルプログラムの両方にモジュールをインストールすることは適切ではありません（一部の考えでは、WindowsではなくLinux以外）。

第三に、このビジネスは6か月に1回実行されますが、怠idleなため、インポートを一般化できるような関数を作成することにしました（突然、必要な人）。

良い点については、この関数をクラスで書き直し、何かを修正し、MySQLからExcelにエクスポートする機能を追加しました。ここから拾えます。

コメントに返信しないで申し訳ありませんが、記事自体が適切であると判断しました。

ExcelからMySQLへ。 PHPの小さな関数（修正済み）