Drupal reads Excel and imports the database instance _ php instance

Source: Internet
Author: User
Tags drupal
This article mainly introduces how Drupal uses PHPExcel to read Excel and import the database. For more information, see PHPExcel, which is a PHP class library used to operate Office Excel documents, it is based on Microsoft's OpenXML standard and PHP language. You can use it to read and write workbooks in different formats, such as Excel (BIFF ). xls, Excel 2007 (OfficeOpenXML ). xlsx, CSV, Libre/OpenOffice Calc. ods, Gnumeric, PDF, HTML, etc.

1. Drupal calls PHPExcel through Library
Download PHPExcel and upload it to the Drupal Directory: sites/all/libraries/PHPExcel
If the libraries module is installed in your project, you can call it through libraries_load ($ name.
If the libraries module is not installed, you can simply use the following code to call it:

The code is as follows:

Require ("sites/all/libraries/PHPExcel/IOFactory. php ");

Note: to ensure that all Excel files are imported, the program can talk for a long time. Therefore, add the following content at the beginning of the code:

The code is as follows:

Set_time_limit (0 );


To ensure that the running time is not limited.
II. Drupal reads Excel and imports it to the database
Drupal uploads an Excel file, reads the Excel content, writes it to the database, and prints the import result message.
The following points are summarized:
1. Drupal reads multi-row and multi-column content in Excel. the number of columns ranges from 1 to n, and the number of rows is also 1 to n.
2. Drupal uses n fields in the database structure to store columns 1 to n in Excel. if there are many columns in Excel, you can store the n column values in 1 field.
3. here I solve the problem of storing n column values in Excel to n MySQL fields (n is not very large)

This is the function after Drupal finally submits the uploaded file:

The 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 parsed rows
$ PaseRows = 0; // skip rows with no value in the number of rows
$ InsertRows = 0; // Number of inserted rows
$ Table = array (
'Dbfield1 ′,
'Dbfield2 ′,
'Dbfield3,
'Dbfield4 ′,
'Dbfield5 ′,
...
'Dbfieldn ',
);
Require ("sites/all/libraries/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 );
/** Obtain the total number of columns */
$ AllColumn = $ currentSheet-> getHighestColumn ();
// Obtain the total number of columns. if this static method is not used, the obtained $ col is the maximum uppercase letter of the file column.
$ Col = PHPExcel_Cell: columnIndexFromString ($ currentSheet-> getHighestColumn ());
/** Obtain the total number of rows */
$ AllRow = $ currentSheet-> getHighestRow ();
// Read the content of each cell cyclically. Note that the row starts from 1 and the column starts from.
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)', array_merge ($ row_db, array ($ timestamp )));
$ InsertRows ++;
}
}
Fclose ($ handle );
}
Drupal_set_message (t ('file @ file imported successfully. ', array (' @ file' => $ file-> filename )));
Drupal_set_message ("parsing". $ row. "after the data entry is complete, a total of". $ insertRows. "data entries are added.". $ paseRows. "data entries with question ID are not displayed. ");
}
Else {
Drupal_set_message (t ('file to import not found. '), 'error ');
$ Form_state ['redirect'] = 'admin/content/db/import ';
Return;
}
}
?>

Pay attention to the following points in the code above:

The code is as follows:


$ AllColumn = $ currentSheet-> getHighestColumn (); // The array index of the obtained columns with uppercase English letters.
$ Col = PHPExcel_Cell: columnIndexFromString ($ currentSheet-> getHighestColumn (); // format an uppercase letter index as a number and the index value starts from 0.

This code supports reading Excel 2007 and earlier formats.

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.