Drupal reads Excel and imports a database instance _php instance

Phpexcel is a PHP class library that is used to manipulate office Excel documents, 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, and so on.

One, Drupal calls Phpexcel 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:

Copy Code code as follows:

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:

Copy Code code as follows:

Set_time_limit (0);

To ensure that the running time is unrestricted.
Second, Drupal read Excel and import to 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:
1.Drupal reads more than one row of Excel columns from 1 to n and the number of rows is 1 to n.
2.Drupal according to the database structure N fields to store Excel 1 to n columns, if there are many columns in Excel, you can put n column values in 1 fields.
3. Here I resolved 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:

Copy Code code as follows:

<?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:

Copy Code code as follows:

$allColumn = $currentSheet->gethighestcolumn (); Gets an array index of the uppercase letters in the English column.
$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.