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

Source: Internet
Author: User
Tags drupal
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 ...

  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.