Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PHPでCSVのインポート/エクスポートに立ち向かう

 PHPでCSVのインポート/エクスポートに立ち向かう

PHPカンファレンス沖縄2021の発表資料です。
https://connpass.com/event/212058/

Takashi Kanemoto

May 29, 2021
Tweet

More Decks by Takashi Kanemoto

Other Decks in Programming

Transcript

  1. /32
    2021/05/29 #phpcon_okinawa @ttskch
    1
    PHPͰCSVͷΠϯϙʔτ/

    ΤΫεϙʔτʹཱͪ޲͔͏

    View full-size slide

  2. /32
    • ϦεςΟϯά޿ࠂͷձࣾͰWebαʔϏεΛ࡞ͬͯ·ͨ͠


    • CTOͱͯ͠ࡏ੶ͨ͠8೥ؒͰɺࣾһ਺2໊ˠ60໊ɺ

    ച্ن໛5000ສԁˠ27ԯԁʹ·Ͱ੒௕ 💪 💪 💪


    • ຊΛॻ͍ͨΓSchooͰߨٛͨ͠Γͱ͍ͬͨ׆ಈ΋
    2
    ͖ͨͭͪ
    ʙ2020/03


    (ג)ΧϧςοτίϛϡχέʔγϣϯζCTO


    📝 blog.ttskch.com/thank-you-quartet-communications
    @ttskch

    View full-size slide

  3. /32
    • डୗ։ൃͱࣾ֎CTO/ٕज़ސ໰ۀத৺Ͱ׆ಈͯ͠·͢


    • Symfony͕େ޷͖Ͱ͢ ✨


    • ڈ೥͸ΞυϕϯτΧϨϯμʔΛ1ਓͰ15೔ॻ͍ͨΓ

    10ສจࣈͷిࢠॻ੶Λແྉެ։ͨ͠Γ͠·ͨ͠💪💪💪
    3
    ͖ͨͭͪ
    2020/04ʙ


    ϑϦʔϥϯεWebΤϯδχΞ


    👨💻 https://kannade.jp
    @ttskch

    View full-size slide

  4. /32
    2021/05/29 #phpcon_okinawa @ttskch
    4
    PHPͰCSVͷΠϯϙʔτ/

    ΤΫεϙʔτʹཱͪ޲͔͏

    View full-size slide

  5. /32
    • ࣮͸Excel͸BOM෇͖UTF-8ͷCSVΛ։͚ΔͷͰSJISͰग़ྗ͢Δඞཁ͸ͳ͍


    • league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫΛ঺հ


    • Πϯϙʔτ/ΤΫεϙʔτʹಛԽͨ͠ttskch/bulkonyΛ࢖͑͹

    ΋ͬͱ؆୯/ॊೈʹ࣮૷Ͱ͖Δ
    5
    ✅͜ͷτʔΫͷཁ໿
    10෼͔͠ͳ͍ͷͰαϥοͱͰ͢🙏

    View full-size slide

  6. /32
    6
    Excel͸BOM෇͖UTF-8ͷ


    CSVΛ։͚Δ

    View full-size slide

  7. /32
    7
    ҙ֎ͱ஌ΒΕ͍ͯͳ͍ࣄ࣮
    https://ja.wikipedia.org/wiki/UTF-8#όΠτॱϚʔΫͷ࢖༻
    Excel͸BOM෇͖UTF-8ͷCSVΛ։͚Δ
    ٯʹݴ͏ͱɺBOM෇͖Ͱ͋Ε͹UTF-8Ͱ΋ී௨ʹ։͚Δ😳

    View full-size slide

  8. /32
    $fp = fopen('/path/to/output.csv', 'w+');ʊ


    fwrite($fp, "\xEF\xBB\xBF"); // ϑΝΠϧͷઌ಄ʹBOMΛૠೖ


    foreach ($data as $row) {ʊ


    fputcsv($fp, $row);ʊ





    fclose($fp);
    8
    PHPͰग़ྗ͢Δྫ
    https://ja.wikipedia.org/wiki/όΠτॱϚʔΫ
    ϑΝΠϧͷઌ಄ʹ ”EF BB BF” ͱ͍͏όΠφϦΛ෇Ճ͢Δ͚ͩ👌
    Excel͸BOM෇͖UTF-8ͷCSVΛ։͚Δ

    View full-size slide

  9. /32
    $fp = fopen('/path/to/output.csv', 'w+');ʊ


    fwrite($fp, "\xEF\xBB\xBF"); // ϑΝΠϧͷઌ಄ʹBOMΛૠೖ


    foreach ($data as $row) {ʊ


    fputcsv($fp, $row);ʊ





    fclose($fp);
    9
    PHPͰग़ྗ͢Δྫ
    https://ja.wikipedia.org/wiki/όΠτॱϚʔΫ
    ϑΝΠϧͷઌ಄ʹ ”EF BB BF” ͱ͍͏όΠφϦΛ෇Ճ͢Δ͚ͩ👌
    Excel͸BOM෇͖UTF-8ͷCSVΛ։͚Δ

    View full-size slide

  10. /32
    $fp = fopen('/path/to/output.csv', 'w+');ʊ


    fwrite($fp, "\xEF\xBB\xBF"); // ϑΝΠϧͷઌ಄ʹBOMΛૠೖ


    foreach ($data as $row) {ʊ


    fputcsv($fp, $row);ʊ





    fclose($fp);
    10
    PHPͰग़ྗ͢Δྫ
    https://ja.wikipedia.org/wiki/όΠτॱϚʔΫ
    ϑΝΠϧͷઌ಄ʹ ”EF BB BF” ͱ͍͏όΠφϦΛ෇Ճ͢Δ͚ͩ👌
    Excel͸BOM෇͖UTF-8ͷCSVΛ։͚Δ

    View full-size slide

  11. /32
    • ExcelͰ։͍ͯ΋Β͏ͨΊ͚ͩʹSJISͰग़ྗ͢Δඞཁ͸ͳ͍


    • UTF-8͔ΒSJISʹม׵͢ΔͱSJISʹଘࡏ͠ͳ͍จࣈ͕ ? ʹͳΔ໰୊


    • SJIS͔ΒͷΠϯϙʔτ͸΋ͬͱ໽հͳͷͰɺ

    ΞϓϦ͕ు͍ͨCSVΛΠϯϙʔτʹ΋࢖͏৔߹͸ಛʹBOM෇͖UTF-8Ұ୒
    11
    ✅ BOM෇͖UTF-8Λੵۃతʹ࢖͓͏
    Excel͸BOM෇͖UTF-8ͷCSVΛ։͚Δ
    ※ SJISͷCSVΛ҆શʹѻ͓͏ͱ͢Δͱߟྀ͢Δ͜ͱ͕৭ʑ͋Γ·͢ʢεϥΠυͷ࠷ޙͰࢀߟURLΛ͝঺հ͠·͢ʣ

    View full-size slide

  12. /32
    12
    league/csvΛ࢖ͬͨ


    Πϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

    View full-size slide

  13. /32
    • ۙ೥Α͘࢖ΘΕ͍ͯΔɺCSVΛ؆୯ʹѻ͏ͨΊͷPHPϥΠϒϥϦ


    • fgetcsvͱ͔Λ࢖ͬͯࣗྗͰ௿ϨΠϠʔͷίʔυΛॻ͔ͳͯ͘΋ɺ

    CSVΛ͍͍ײ͡ʹந৅Խͯ͘͠ΕΔ
    13
    league/csvͱ͸
    league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

    View full-size slide

  14. /32
    $csv = Reader::createFromPath('/path/to/input.csv');ʊ


    $csv->setHeaderOffset(0); // ϔομʔ͕0ߦ໨Ͱ͋Δ͜ͱΛએݴ


    $rows = $csv->getRecords();ʊ


    foreach ($rows as $row) {ʊ


    // ࣮ࡍͷΠϯϙʔτॲཧ


    }
    14
    Πϯϙʔτͷ࣮૷ྫ
    league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

    View full-size slide

  15. /32
    $csv = Reader::createFromPath('/path/to/input.csv');ʊ


    $csv->setHeaderOffset(0); // ϔομʔ͕0ߦ໨Ͱ͋Δ͜ͱΛએݴ


    $rows = $csv->getRecords();ʊ


    foreach ($rows as $row) {ʊ


    // ࣮ࡍͷΠϯϙʔτॲཧ


    }
    15
    Πϯϙʔτͷ࣮૷ྫ
    league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ
    શσʔλͷ഑ྻͰ͸ͳ͘


    1ߦͣͭऔಘͰ͖ΔΠςϨʔλͳͷͰ


    লϝϞϦ

    View full-size slide

  16. /32
    $csv = Reader::createFromPath('/path/to/input.csv');ʊ


    $csv->setHeaderOffset(0); // ϔομʔ͕0ߦ໨Ͱ͋Δ͜ͱΛએݴ


    $rows = $csv->getRecords();ʊ


    foreach ($rows as $row) {ʊ


    // ࣮ࡍͷΠϯϙʔτॲཧ


    }
    16
    Πϯϙʔτͷ࣮૷ྫ
    league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ
    ϔομʔߦͷҐஔΛએݴͨ͠ͷͰ
    [
    'ྻ໊' => '஋',
    'ྻ໊' => '஋',
    :
    ]
    ͱ͍͏ܗࣜͷ഑ྻσʔλ͕ಘΒΕΔ
    ͜ΕΛ࢖ͬͯΠϯϙʔτॲཧΛॻ͘

    View full-size slide

  17. /32
    $csv = Writer::createFromPath('php://temp');ʊ


    $csv->insertOne(['ྻ1', 'ྻ2', 'ྻ3']); // Ұߦૠೖ


    $csv->insertAll($data); // ೋ࣍ݩ഑ྻͰෳ਺ߦ·ͱΊͯૠೖ


    // ग़ྗ࣌ʹBOMΛ෇Ճ͢ΔΑ͏ઃఆ


    $csv->setOutputBOM("\xEF\xBB\xBF");ʊ


    $csv->output('μ΢ϯϩʔυ.csv');
    17
    ΤΫεϙʔτͷ࣮૷ྫ
    league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

    View full-size slide

  18. /32
    $csv = Writer::createFromPath('php://temp');ʊ


    $csv->insertOne(['ྻ1', 'ྻ2', 'ྻ3']); // Ұߦૠೖ


    $csv->insertAll($data); // ೋ࣍ݩ഑ྻͰෳ਺ߦ·ͱΊͯૠೖ


    // ग़ྗ࣌ʹBOMΛ෇Ճ͢ΔΑ͏ઃఆ


    $csv->setOutputBOM("\xEF\xBB\xBF");ʊ


    $csv->output('μ΢ϯϩʔυ.csv');
    18
    ΤΫεϙʔτͷ࣮૷ྫ
    league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ
    ࢦఆͨ͠ϑΝΠϧ໊Ͱ_


    μ΢ϯϩʔυͤ͞ΔͨΊͷ


    HTTPϨεϙϯεΛૹग़

    View full-size slide

  19. /32
    • ಛʹΠϯϙʔτ͸ɺ


    • ೖྗσʔλͷόϦσʔγϣϯ


    • Πϯϙʔτ࣮ߦલʹը໘ͰϓϨϏϡʔ


    ͳͲ͕ඞཁʹͳΔͱ్୺ʹ΍Δ͜ͱ͕૿͑ͯͻͨ͢Βେม😓
    19
    Ͱ΋ɺ·ͩ·ͩࣗલͰ࣮૷͢΂͖͜ͱ͕ଟ͍…
    league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

    View full-size slide

  20. /32
    21
    Ή͠Ζ͜͜Λָ͍ͨ͠…😓

    View full-size slide

  21. /32
    22
    ttskch/bulkony

    View full-size slide

  22. /32
    • PHPΞϓϦʹΠϯϙʔτ/ΤΫεϙʔτػೳΛ࣮૷͢Δࡍʹ

    ͍ͭ΋ॻ͍͍ͯͨ͋Ε͜ΕΛϥΠϒϥϦʹ੾Γग़͠·ͨ͠



    ⚡BOM෇͖UTF-8ͰͷΤΫεϙʔτ



    ⚡ϝϞϦޮ཰ʹ഑ྀͨ͠Πϯϙʔτ/ΤΫεϙʔτͷ౔୆Λఏڙ


    • ⚡Πϯϙʔτ಺༰ͷόϦσʔγϣϯͷ౔୆Λఏڙ


    • ⚡Πϯϙʔτ಺༰ͷϓϨϏϡʔػೳͷ౔୆Λఏڙ
    23
    ttskch/bulkonyͱ͸
    ttskch/bulkony

    View full-size slide

  23. /32
    use Ttskch\Bulkony\Export\Exporter;ʊ


    $exporter = new Exporter();ʊ


    $rowGenerator = new App\RowGenerator(); // ࣗ࡞Ϋϥε


    // ϑΝΠϧγεςϜʹग़ྗ


    $exporter->export('/path/to/output.csv', $rowGenerator);ʊ


    // HTTPϨεϙϯεΛૹग़


    $exporter->exportAndOutput('users.csv', $rowGenerator);
    24
    ར༻ΠϝʔδʢΤΫεϙʔτʣ
    ttskch/bulkony

    View full-size slide

  24. /32
    use Ttskch\Bulkony\Export\Exporter;ʊ


    $exporter = new Exporter();ʊ


    $rowGenerator = new App\RowGenerator(); // ࣗ࡞Ϋϥε


    // ϑΝΠϧγεςϜʹग़ྗ


    $exporter->export('/path/to/output.csv', $rowGenerator);ʊ


    // HTTPϨεϙϯεΛૹग़


    $exporter->exportAndOutput('users.csv', $rowGenerator);
    25
    ར༻ΠϝʔδʢΤΫεϙʔτʣ
    ttskch/bulkony
    ͜ͷΫϥεʹCSVߦͷੜ੒खॱΛॻ͘👨💻
    ※ ৄࡉ͸READMEΛ͝ࢀর͍ͩ͘͞🙏

    View full-size slide

  25. /32
    use Ttskch\Bulkony\Import\Importer;ʊ


    $importer = new Importer();ʊ


    $rowVisitor = new App\RowVisitor(); // ࣗ࡞Ϋϥε


    // CSVͷͲͷηϧʹόϦσʔγϣϯΤϥʔ͕͋Δ͔ɺ


    // ΠϯϙʔτʹΑͬͯͲ͜ͷ஋͕ߋ৽͞ΕΔ͔ɺͷ৘ใ͕ಘΒΕΔ


    $preview = $importer->preview('/path/to/input.csv', $rowVisitor);ʊ


    // ͦΕΛը໘ʹදࣔ͢Ε͹ϓϨϏϡʔػೳ׬੒


    render('some.template', ['preview' => $preview]);
    26
    ར༻ΠϝʔδʢΠϯϙʔτʣ
    ttskch/bulkony

    View full-size slide

  26. /32
    use Ttskch\Bulkony\Import\Importer;ʊ


    $importer = new Importer();ʊ


    $rowVisitor = new App\RowVisitor(); // ࣗ࡞Ϋϥε


    // CSVͷͲͷηϧʹόϦσʔγϣϯΤϥʔ͕͋Δ͔ɺ


    // ΠϯϙʔτʹΑͬͯͲ͜ͷ஋͕ߋ৽͞ΕΔ͔ɺͷ৘ใ͕ಘΒΕΔ


    $preview = $importer->preview('/path/to/input.csv', $rowVisitor);ʊ


    // ͦΕΛը໘ʹදࣔ͢Ε͹ϓϨϏϡʔػೳ׬੒


    render('some.template', ['preview' => $preview]);
    27
    ར༻ΠϝʔδʢΠϯϙʔτʣ
    ttskch/bulkony
    ͜ͷΫϥεʹߦ͝ͱͷΠϯϙʔτ΍


    όϦσʔγϣϯͷϩδοΫΛॻ͘👨💻
    ※ ৄࡉ͸READMEΛ͝ࢀর͍ͩ͘͞🙏

    View full-size slide

  27. /32
    28
    ΑΖ͚͠Ε͹࢖ͬͯΈ͍ͯͩ͘͞😇
    https://github.com/ttskch/bulkony

    View full-size slide

  28. /32
    • ࣮͸Excel͸BOM෇͖UTF-8ͷCSVΛ։͚ΔͷͰSJISͰग़ྗ͢Δඞཁ͸ͳ͍


    • league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫΛ঺հ


    • Πϯϙʔτ/ΤΫεϙʔτʹಛԽͨ͠ttskch/bulkonyΛ࢖͑͹

    ΋ͬͱ؆୯/ॊೈʹ࣮૷Ͱ͖Δ
    30
    ✅͜ͷτʔΫͷཁ໿

    View full-size slide

  29. /32
    • SJISͷCSVΛจࣈԽ͚ͤͣʹಡΈॻ͖͢Δํ๏ʹ͍ͭͯ͸ɺ

    PHPerKaigi 2021ͷए༿ষ͞Μͷηογϣϯ͕େมࢀߟʹͳΓ·͢


    • https://youtu.be/pc6ov8HWt1A
    31
    📝ؔ࿈͢Δ͓͢͢Ί৘ใ

    View full-size slide

  30. /32
    32
    @ttskch
    ʘThanks!ʗ

    View full-size slide