Drupal reads Excel and imports it into mysql database program code_PHP tutorial

This article will introduce to you how to read Excel in Drupal and import it into mysql database. Here we introduce the use of excel plug-in PHPExcel. PHPExcel is a PHP class library used to operate Office Excel documents. It is based on Microsoft's OpenXML standard and PHP language. You can use it to read and write spreadsheets in different formats, such as Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML, etc.

Drupal calls PHPExcl through Library
After downloading PHPExcel, upload it to the Drupal directory: sites/all/libraries/PHPExcel

If the libraries module is installed in your project, it can be called through libraries_load($name);.

If the libraries module is not installed, you can simply use the following code to call it:

The code is as follows

Copy code

代码如下	复制代码
require("sites/all/libraries/PHPExcel/PHPExcel/IOFactory.php");

require("sites/all/libraries/PHPExcel/PHPExcel/IOFactory.php");

Note that in order to ensure that Excel is fully imported, the program may take a long time to complete.

So add at the beginning of the code:

代码如下	复制代码
set_time_limit(0);

The code is as follows	Copy code
set_time_limit(0);

to ensure unlimited run time.

Drupal reads Excel and imports it into the database
After uploading the Excel file, Drupal reads the Excel content, writes it to the database, and prints the import result message.

To sum up, there are several points:

Drupal reads Excel multi-row and multi-column content, the number of columns is from 1 to n, and the number of rows is also 1 to n.
Drupal uses n fields to store Excel columns 1 to n according to the database structure. If Excel has many columns, the n column values ​​can be stored in 1 field.
What I solved here is to store Excel n column values ​​into MySQL n fields (n is not very large)

This is the function after Drupal finally submits the uploaded file:

The code is as follows

Copy code

function excel_upload_form_submit($form, &$form_state) { set_time_limit(0); $timestamp = time(); // Make sure the Excel file is uploaded if ($file = file_save_upload(‘file’)) { $row = 0; //Number of parsed rows $paseRows = 0; //Number of skipped rows Rows without value $insertRows = 0; //Number of inserted rows $table = array( ‘dbfield1′, ‘dbfield2′, ‘dbfield3, ‘dbfield4′, ‘dbfield5′, … ‘dbfieldn’, ); ​ require("sites/all/libraries/PHPExcel/PHPExcel/IOFactory.php"); If(($handle = fopen ( $file->filepath, "r" )) !== FALSE) { $PHPExcel = new PHPExcel (); $PHPReader = new PHPExcel_Reader_Excel2007 (); If (! $PHPReader->canRead ( $file->filepath )) { $PHPReader = new PHPExcel_Reader_Excel5 (); If (! $PHPReader->canRead ( $file->filepath )) { echo ‘no Excel’;            return;           }         } $PHPExcel = $PHPReader->load ( $file->filepath ); $currentSheet = $PHPExcel->getSheet (0); /**Get how many columns there are in total*/ $allColumn = $currentSheet->getHighestColumn(); //Get the total number of columns. If this static method is not used, the $col obtained is the largest English capital letter of the file column $col = PHPExcel_Cell::columnIndexFromString($currentSheet->getHighestColumn()); /**Get the total number of rows*/ $allRow = $currentSheet->getHighestRow(); //Loop to read the contents of each cell. Note that rows start from 1 and columns start from A for($rowIndex = 2; $rowIndex <= $allRow; $rowIndex++) {           $token_db = $row_db = $field = array();          $i = 0; $query = ”; for($colIndex = 0; $colIndex <= $col; $colIndex++) { //$addr = $colIndex.$rowIndex; //$cell = $currentSheet->getCell($addr)->getValue();                 $cell = $currentSheet->getCellByColumnAndRow($colIndex, $rowIndex)->getValue();              $cell = trim($cell); If($cell instanceof PHPExcel_RichText) { //Rich text conversion string $cell = $cell->__toString();             } If ($colIndex == ‘A’ && !intval($cell)) {                $paseRows++;              break;             }              $field[] = $table[$i];               $token_db[] = "’%s’";              $row_db[] = $cell; $query .= $table[$i]." = ‘%s’, ";             $i++;           }          $row++;             if ($row_db) { db_query('INSERT INTO {db_import} ('. implode(', ', $field) .', created) VALUES('. implode(', ', $token_db) .', %d)', array_merge($row_db , array($timestamp)));              $insertRows++;           }         }         fclose ( $handle ); } Drupal_set_message(t(‘File @file imported successfully.’, array(‘@file’ => $file->filename))); drupal_set_message("Parsing of ".$row." data is completed, a total of ".$insertRows." data is added, and there is no ".$paseRows." data with question type ID."); } else { Drupal_set_message(t(‘File to import not found.’), ‘error’); $form_state['redirect'] = ‘admin/content/db/import’; Return; } } ?>

Please note a few points in the above code:

The code is as follows

代码如下	复制代码
$allColumn = $currentSheet->getHighestColumn();

Copy code

代码如下	复制代码
$col = PHPExcel_Cell::columnIndexFromString($currentSheet->getHighestColumn());

$allColumn = $currentSheet->getHighestColumn();

The obtained column is the array index of English capital letters.

The code is as follows

Copy code

$col = PHPExcel_Cell::columnIndexFromString($currentSheet->getHighestColumn()); Format the English capital letter index as a number, and the index value starts from 0. This code supports reading Excel 2007 and earlier formats. http://www.bkjia.com/PHPjc/630716.html www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630716.htmlTechArticleThis article will introduce to you how to read Excel in Drupal and import it into a mysql database. Here we introduce Use excel plug-in PHPExcel, PHPExcel is used to operate Office Excel documents...