Drupal reads Excel and imports it to the mysql database. code _ PHP Tutorial

Drupal reads the Excel file and imports it to the mysql database program code. This article introduces How to Read Excel from Drupal and import it to the mysql database. here we will introduce the use of the excel plug-in PHPExcel, PHPExcel is used to operate the OfficeExcel document. This article introduces How to Read Excel from Drupal and import it to the mysql database. here we will introduce the use of the excel plug-in PHPExcel, 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.

Drupal calls PHPExcl through the 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.

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:

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.
Drupal is used to store columns 1 to n in Excel based on the database structure. if there are many columns in Excel, you can store the n column values in one field.
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 ();

Returns an array index with uppercase letters.

The code is as follows:
$ Col = PHPExcel_Cell: columnIndexFromString ($ currentSheet-> getHighestColumn ());

Format an uppercase letter index as a number. The index value starts from 0.
This code supports reading Excel 2007 and earlier formats.

Sheet is used to operate Office Excel documents...