Drupal reads Excel and imports it into MySQL database program code

Source: Internet
Author: User
Tags drupal mysql database

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.

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.