Drupal read Excel and import a DB instance _php tutorial

Phpexcel is a PHP class library for working with Office Excel documents, based on the Microsoft 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, and so on.

One, Drupal calls Phpexcel 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:
Copy the Code code as follows: 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:
Copy the Code code as follows: Set_time_limit (0);
To ensure that there is no limit to run time.
Second, Drupal read Excel and import 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:
1.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.
2.Drupal based on the database structure N fields are used to store Excel 1 to n columns, if Excel has a large number of columns, you can store n column values in 1 fields.
3. 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:
Copy CodeThe code is as follows: 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:
Copy the Code code as follows:
$allColumn = $currentSheet->gethighestcolumn (); Gets an array index that is listed as an English capital letter.
$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/736822.html www.bkjia.com true http://www.bkjia.com/PHPjc/736822.html techarticle Phpexcel is a PHP class library for working with Office Excel documents, based on the Microsoft OPENXML Standard and PHP language. You can use it to read and write spreadsheets in different formats, such as Exc ...

