Drupal reads Excel and imports it into MySQL database program code

Drupal calls PHPEXCL through the library
Download the Phpexcel and upload it to your Drupal directory: Sites/all/libraries/phpexcel

If you have a libraries module installed in your project, you can call it by Libraries_load ($name).

If the Libraries module is not 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 be in session for a long time.

So add in the beginning of the code:

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

To ensure that the running time is unrestricted.

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

Summed up a number of such points:

Drupal reads more than one row of Excel columns from 1 to n, and the number of rows is 1 to n.
Drupal uses the database structure n fields to store Excel 1 to n columns, and if you have a large number of columns in Excel, you can store n column values in 1 fields.
What I'm dealing with 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 submits the upload file:

The code is as follows

Copy Code

<?php function Excel_upload_form_submit ($form, & $form _state) { Set_time_limit (0); $timestamp = time (); Make sure that the Excel file is uploaded if ($file = file_save_upload (' file ')) { $row = 0; Parse number of rows $paseRows = 0; Skip rows with no values for the row count $insertRows = 0; Insert Number of 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); How many columns did/** get? $allColumn = $currentSheet->gethighestcolumn (); To get a total number of columns, if you do not use this static method, the $col is the largest English capital letter in the file column. $col = phpexcel_cell::columnindexfromstring ($currentSheet->gethighestcolumn ()); /** How many lines have been made? $allRow = $currentSheet->gethighestrow (); Iterates through the contents of each cell. Note that the row starts at 1 and the column starts with 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. " Data complete, add a total of ". $insertRows." Data, without the ". $paseRows of the question type ID." Piece of data. "); } else { Drupal_set_message (' File to import not found. '), ' error '); $form _state[' redirect '] = ' admin/content/db/import '; Return } } ?>

The Code section above notes some points:

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

Gets an array index of the uppercase letters in the English column.

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 starts at 0.
This code supports reading Excel 2007 and previous formats.