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

Transcript

1. /32 2021/05/29 #phpcon_okinawa @ttskch 1 PHPͰCSVͷΠϯϙʔτ/ 
 ΤΫεϙʔτʹཱͪ޲͔͏

2. /32 • ϦεςΟϯά޿ࠂͷձࣾͰWebαʔϏεΛ࡞ͬͯ·ͨ͠ • CTOͱͯ͠ࡏ੶ͨ͠8೥ؒͰɺࣾһ਺2໊ˠ60໊ɺ 
 ച্ن໛5000ສԁˠ27ԯԁʹ·Ͱ੒௕ 💪 💪 💪

   • ຊΛॻ͍ͨΓSchooͰߨٛͨ͠Γͱ͍ͬͨ׆ಈ΋ 2 ͖ͨͭͪ ʙ2020/03 (ג)ΧϧςοτίϛϡχέʔγϣϯζCTO 📝 blog.ttskch.com/thank-you-quartet-communications @ttskch

3. /32 • डୗ։ൃͱࣾ֎CTO/ٕज़ސ໰ۀத৺Ͱ׆ಈͯ͠·͢ • Symfony͕େ޷͖Ͱ͢ ✨ • ڈ೥͸ΞυϕϯτΧϨϯμʔΛ1ਓͰ15೔ॻ͍ͨΓ 
 10ສจࣈͷిࢠॻ੶Λແྉެ։ͨ͠Γ͠·ͨ͠💪💪💪

   3 ͖ͨͭͪ 2020/04ʙ ϑϦʔϥϯεWebΤϯδχΞ 👨💻 https://kannade.jp @ttskch

4. /32 2021/05/29 #phpcon_okinawa @ttskch 4 PHPͰCSVͷΠϯϙʔτ/ 
 ΤΫεϙʔτʹཱͪ޲͔͏

5. /32 • ࣮͸Excel͸BOM෇͖UTF-8ͷCSVΛ։͚ΔͷͰSJISͰग़ྗ͢Δඞཁ͸ͳ͍ • league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫΛ঺հ • Πϯϙʔτ/ΤΫεϙʔτʹಛԽͨ͠ttskch/bulkonyΛ࢖͑͹ 
 ΋ͬͱ؆୯/ॊೈʹ࣮૷Ͱ͖Δ 5

   ✅͜ͷτʔΫͷཁ໿ 10෼͔͠ͳ͍ͷͰαϥοͱͰ͢🙏

6. /32 6 Excel͸BOM෇͖UTF-8ͷ CSVΛ։͚Δ

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

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Λ։͚Δ

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Λ։͚Δ

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Λ։͚Δ

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Λ͝঺հ͠·͢ʣ

12. /32 12 league/csvΛ࢖ͬͨ Πϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

13. /32 • ۙ೥Α͘࢖ΘΕ͍ͯΔɺCSVΛ؆୯ʹѻ͏ͨΊͷPHPϥΠϒϥϦ • fgetcsvͱ͔Λ࢖ͬͯࣗྗͰ௿ϨΠϠʔͷίʔυΛॻ͔ͳͯ͘΋ɺ 
 CSVΛ͍͍ײ͡ʹந৅Խͯ͘͠ΕΔ 13 league/csvͱ͸ league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

14. /32 $csv = Reader::createFromPath('/path/to/input.csv');ʊ $csv->setHeaderOffset(0); // ϔομʔ͕0ߦ໨Ͱ͋Δ͜ͱΛએݴ $rows = $csv->getRecords();ʊ

    foreach ($rows as $row) {ʊ // ࣮ࡍͷΠϯϙʔτॲཧ } 14 Πϯϙʔτͷ࣮૷ྫ league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

15. /32 $csv = Reader::createFromPath('/path/to/input.csv');ʊ $csv->setHeaderOffset(0); // ϔομʔ͕0ߦ໨Ͱ͋Δ͜ͱΛએݴ $rows = $csv->getRecords();ʊ

    foreach ($rows as $row) {ʊ // ࣮ࡍͷΠϯϙʔτॲཧ } 15 Πϯϙʔτͷ࣮૷ྫ league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ શσʔλͷ഑ྻͰ͸ͳ͘ 1ߦͣͭऔಘͰ͖ΔΠςϨʔλͳͷͰ লϝϞϦ

16. /32 $csv = Reader::createFromPath('/path/to/input.csv');ʊ $csv->setHeaderOffset(0); // ϔομʔ͕0ߦ໨Ͱ͋Δ͜ͱΛએݴ $rows = $csv->getRecords();ʊ

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

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Λ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫ

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ϨεϙϯεΛૹग़

19. /32 • ಛʹΠϯϙʔτ͸ɺ • ೖྗσʔλͷόϦσʔγϣϯ • Πϯϙʔτ࣮ߦલʹը໘ͰϓϨϏϡʔ ͳͲ͕ඞཁʹͳΔͱ్୺ʹ΍Δ͜ͱ͕૿͑ͯͻͨ͢Βେม😓 19 Ͱ΋ɺ·ͩ·ͩࣗલͰ࣮૷͢΂͖͜ͱ͕ଟ͍…

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

20. /32 20

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

22. /32 22 ttskch/bulkony

23. /32 • PHPΞϓϦʹΠϯϙʔτ/ΤΫεϙʔτػೳΛ࣮૷͢Δࡍʹ 
 ͍ͭ΋ॻ͍͍ͯͨ͋Ε͜ΕΛϥΠϒϥϦʹ੾Γग़͠·ͨ͠ • ⚡BOM෇͖UTF-8ͰͷΤΫεϙʔτ • ⚡ϝϞϦޮ཰ʹ഑ྀͨ͠Πϯϙʔτ/ΤΫεϙʔτͷ౔୆Λఏڙ •

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

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); 24 ར༻ΠϝʔδʢΤΫεϙʔτʣ ttskch/bulkony

25. /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Λ͝ࢀর͍ͩ͘͞🙏

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]); 26 ར༻ΠϝʔδʢΠϯϙʔτʣ ttskch/bulkony

27. /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Λ͝ࢀর͍ͩ͘͞🙏

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

29. /32 29 ·ͱΊ

30. /32 • ࣮͸Excel͸BOM෇͖UTF-8ͷCSVΛ։͚ΔͷͰSJISͰग़ྗ͢Δඞཁ͸ͳ͍ • league/csvΛ࢖ͬͨΠϯϙʔτ/ΤΫεϙʔτͷ࣮૷ྫΛ঺հ • Πϯϙʔτ/ΤΫεϙʔτʹಛԽͨ͠ttskch/bulkonyΛ࢖͑͹ 
 ΋ͬͱ؆୯/ॊೈʹ࣮૷Ͱ͖Δ 30

    ✅͜ͷτʔΫͷཁ໿

31. /32 • SJISͷCSVΛจࣈԽ͚ͤͣʹಡΈॻ͖͢Δํ๏ʹ͍ͭͯ͸ɺ 
 PHPerKaigi 2021ͷए༿ষ͞Μͷηογϣϯ͕େมࢀߟʹͳΓ·͢ • https://youtu.be/pc6ov8HWt1A 31 📝ؔ࿈͢Δ͓͢͢Ί৘ใ

32. /32 32 @ttskch ʘThanks!ʗ