Drupal reads Excel and imports database instances

Drupal reads Excel and imports database instances

This article mainly introduces how Drupal uses PHPExcel to read Excel and import the database. For more information, see

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

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.