👩‍🎨 👾 ✨ एक्सेल से लेकर MySQL तक। PHP में छोटा कार्य (निश्चित) 👂🏻 🤷🏾 🤹🏼

परिचय

हैलो $ habrauser !

ऐसा होता है कि आपको एक एक्सेल फ़ाइल को MySQL डेटाबेस में आयात करने की आवश्यकता होती है, लेकिन कहीं भी कोई तैयार समाधान नहीं है। इसलिए, जब एक दोस्त ने मुझे आयात करने के लिए एक आसान तरीका खोजने के लिए कहा, तो मैंने पहली बार एक समाधान के लिए खोज करने का निर्णय लिया। काश, mysql क्वेरी के लिए php एक्सेल कुछ भी ठोस नहीं देता, या वर्णित तरीके असुविधाजनक थे। तब मैंने PHP में एक्सेल के साथ काम करने के लिए एक पुस्तकालय खोजने का फैसला किया, और मैं PHPExcel में आया। लेकिन फिर से, निराशा ने मेरा इंतजार किया, mysql क्वेरी के लिए phpexcel ने कुछ भी सार्थक नहीं दिया (मैं एक आलसी उपयोगकर्ता हूं और 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) { // ... }

खैर, अब सबसे दिलचस्प ...

Iterate और जोड़ें

हम इस तथ्य से आगे बढ़ेंगे कि हमारे पास एक तालिका नहीं है (या वहाँ है, लेकिन अन्य डेटा के साथ) और हमें इसे बनाने की आवश्यकता है। ऐसा करने के लिए, हमें कॉलम के लिए नाम प्राप्त करने की आवश्यकता है (जैसा कि एक मित्र द्वारा अनुरोध किया गया है, नाम तालिका की 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 प्रकार के होंगे। अब हम कोशिकाओं के माध्यम से छांटने और उन्हें डेटाबेस में जोड़ने के लिए आगे बढ़ते हैं। बेशक, इस तरह के एल्गोरिथ्म स्टैक ओवरफ्लो की विशालता में खोजने के लिए मुश्किल नहीं है, लेकिन यह देखा गया कि संयुक्त कोशिकाओं को पढ़ने की कोशिश करते समय एक त्रुटि हुई (अधिक सटीक रूप से, क्वेरी में कॉलम और मानों की संख्या मेल नहीं खाती)। यह मैंने ध्यान में रखा:

 //     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 . ")"); }

यह सब फ़ंक्शन के बारे में है!

बेशक, यह स्क्रिप्ट बहुत सुविधाजनक होगी यदि सब कुछ एक फ़ंक्शन में जोड़ा गया। इसलिए, अंतिम परिणाम इस प्रकार है:

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

निष्कर्ष

खैर, मुझे उम्मीद है कि यह लेख आपकी मदद करता है। ठीक है, या, यदि आप ~~अपनी बाइक~~ का ~~आविष्कार~~ करना चाहते हैं ~~, लेकिन केवल एक मोटर के साथ~~ अपनी स्क्रिप्ट लिखें, यह लेख आपको आरंभ करने में मदद करेगा।

पुनश्च

यह मेरा पहला है, और मुझे लगता है कि अंतिम लेख नहीं है। इसलिए, मैं आपकी सलाह और संशोधनों की प्रतीक्षा कर रहा हूं, जैसा कि यहां प्रथागत है, टिप्पणियों में।

अद्यतन

मैं देखता हूं, आखिरकार, मैं एक छोटी सी चर्चा बनाने में कामयाब रहा, लेकिन हर कोई यह नहीं समझता कि ऐसा क्यों किया गया था। मैं समझाने की कोशिश करूंगा।

सबसे पहले: एक बुजुर्ग व्यक्ति को इसके साथ काम करना चाहिए था, यह समझाने में मुश्किल होगी कि डेटा खोए बिना, सीएसवी में फ़ाइल को कैसे बचाया जाए (और इसे बाहर नहीं किया जा सकता है, इसके अलावा उनके पास ऊपर से आने वाली एक्सएलएस फ़ाइल के लिए अपना स्वयं का प्रारूप है) और, विशेष रूप से यह phpMyAdmin के माध्यम से आयात किया जाता है (जो, वैसे, संस्करण 3.4.5 के बाद से XLS / XLSX का समर्थन नहीं करता है , मैं आपको यह देखने की सलाह देता हूं कि क्यों) या पसंद है। इसलिए यह फिट नहीं है।

दूसरी बात: यह सब होस्टिंग पर स्थित होना चाहिए, और सर्वर और स्थानीय कार्यक्रमों दोनों पर मॉड्यूल स्थापित करना उपयुक्त नहीं है (लिनक्स के अलावा, और विंडोज नहीं, जैसा कि कुछ सोचा गया है)।

तीसरा: यह व्यवसाय हर छह महीने में एक बार किया जाता है, लेकिन आलस्य से मैंने ऐसा कार्य लिखने का फैसला किया जो आयात को सामान्य कर सकता है (अचानक, जिसे इसकी आवश्यकता है)।

अब अच्छे के बारे में: मैंने इस फ़ंक्शन को एक कक्षा में फिर से लिखा, कुछ तय किया और MySQL से एक्सेल में निर्यात करने की क्षमता को जोड़ा। आप इसे यहां से उठा सकते हैं ।

टिप्पणियों में जवाब न देने के लिए क्षमा करें, मैंने तय किया कि लेख ही उचित होगा।

एक्सेल से लेकर MySQL तक। PHP में छोटा कार्य (निश्चित)