Drupal read Excel and import into MySQL database program code _php tutorial

This article introduces you to how to read excel in Drupal and import it into a MySQL database, here we describe a PHP class library that uses Excel plug-in phpexcel,phpexcel to manipulate 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 (Officeopenxml). xlsx, CSV, Libre/openoffice Calc. ODS, Gnumeric, PDF , HTML, etc.

Drupal calls PHPEXCL through the library
After downloading the phpexcel, upload it to the Drupal directory: Sites/all/libraries/phpexcel

If the Libraries module is installed in your project, it can be called by Libraries_load ($name).

If you do not have the Libraries module installed, you can simply use the following code to invoke:

The code is as follows	Copy Code
Require ("sites/all/libraries/phpexcel/phpexcel/iofactory.php");

Note To make sure that Excel is all imported, the program can take a long time to make the session.

So at the beginning of the code, add:

The code is as follows	Copy Code
Set_time_limit (0);

To ensure that there is no limit to run time.

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

There are several points to be summed up:

Drupal reads multiple rows and columns of Excel, the number of columns from 1 to n, and the number of rows is 1 to n.
Drupal is used to store Excel 1 to n columns according to the database structure n fields, and if Excel has a large number of columns, you can store the N column values in 1 fields.
What I've solved here is that Excel n column values are stored in MySQL n fields (n is not very large)

This is the function after Drupal finally commits the upload 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 rows resolved $paseRows = 0; Skip rows with no value for row count $insertRows = 0; Number of rows inserted $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); How many columns does the/** get altogether? * $allColumn = $currentSheet->gethighestcolumn (); How many columns are obtained, and if you do not use this static method, the $col is the largest English capital letter of the file column $col = phpexcel_cell::columnindexfromstring ($currentSheet->gethighestcolumn ()); How many lines does the/** get altogether? */ $allRow = $currentSheet->gethighestrow (); Iterates through the contents of each cell. Note that the row starts at 1 and the column starts 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] ', ARRA Y_merge ($row _db, Array ($timestamp))); $insertRows + +; } } Fclose ($handle); } Drupal_set_message (' File @file import succeeded. ', array (' @file ' = ' $file->filename)]); Drupal_set_message ("parsing". $row. " The data is complete, adding a total of ". $insertRows." The ". $paseRows of the data, without the question type ID." Bar data. "); } else { Drupal_set_message (t (' File to import not found. '), ' error '); $form _state[' redirect '] = ' admin/content/db/import '; Return } } ?>

The above Code section notes a few points:

The code is as follows	Copy Code
$allColumn = $currentSheet->gethighestcolumn ();

Gets an array index that is listed as an English capital letter.

The code is as follows	Copy Code
$col = phpexcel_cell::columnindexfromstring ($currentSheet->gethighestcolumn ());

Formats the English Capital index as a number, and the index value is calculated starting at 0.
This code supports reading in Excel 2007 and earlier formats.

http://www.bkjia.com/PHPjc/630716.html www.bkjia.com true http://www.bkjia.com/PHPjc/630716.html techarticle This article gives you an introduction to how to read excel in Drupal and import it into a MySQL database, where we describe using the Excel plugin Phpexcel,phpexcel is used to manipulate Office Excel documents ...

