Drupal reads Excel and Imports database instances

  This article mainly introduces Drupal using Phpexcel to read Excel and import the database example, the need for friends can refer to the following

Phpexcel is a PHP class library that is used to manipulate office Excel documents, 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 2007 (Officeopenxml). xlsx, CSV, Libre/openoffice Calc. ODS, Gnumeric, PDF , HTML, and so on.   A, Drupal through the library call Phpexcel will phpexcel download, upload to the Drupal directory: Sites/all/libraries/phpexcel If your project installed libraries module, Can be invoked by Libraries_load ($name); If the Libraries module is not installed, you can simply use the following code to invoke:     Code as follows: Require ("sites/all/libraries/phpexcel/phpexcel/iofactory.php ");   Note In order to ensure that Excel is fully imported, the program can be in session for a very long time. So in the beginning part of the code add:   Code as follows: Set_time_limit (0); To ensure that the running time is unrestricted. Second, Drupal read Excel and import to the database Drupal implementation upload Excel file, read the Excel content, write to the database, print the import results message. Summed up a number of such points: 1.Drupal read multiple rows of Excel columns, the number of columns from 1 to n, the number of rows is 1 to n. 2.Drupal according to the database structure N fields to store Excel 1 to n columns, if there are many columns in Excel, you can put n column values in 1 fields. 3. Here I resolved 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:     Code as follows:<?php  function excel _upload_form_submit ($form, & $form _state) {    set_time_limit (0);    $timestamp = time ();   /ensure Excel File UploadThe     if ($file = file_save_upload (' file ')) {      $row = 0;//parse line number       $paseRows = 0; Skip rows with no value for rows       $insertRows = 0; Insert rows       $table = Array (        ' dbfield1′,        ' DBFIELD2′,&N Bsp       ' dbfield3,        ' dbfield4′,        ' dbfield5′,        ...        ' dbfieldn ',     );      require ("Sites/all/libr aries/phpexcel/phpexcel/iofactory.php ");      if (($handle = fopen ($file->filepath," R ")!== FALSE) {         $PHPExcel = new Phpexcel ();        $PHPReader = new Phpexcel_reader_excel 2007 ();        if (! $PHPReader->canread ($file->filepath)) {        &NBS P $PHPReader = new Phpexcel_reader_excel5 ();          if (! $PHPReader->canread ($file->filepath)) {            echo ' no Excel ';    & nbsp       return;         }       }        $ Phpexcel = $PHPReader->load ($file->filepath);        $currentSheet = $PHPExcel->getsheet ( 0;       /** get a total number of columns */        $allColumn = $currentSheet->gethighestcolumn ( ;       //Get total number of columns, if not using this static method, obtained $col is the largest English capital letter         $col = Phpexcel_cell: : Columnindexfromstring ($currentSheet->gethighestcolumn ());       /** get how many rows */        $allRow = $currentSheet->gethighestrow ();       //cycle reads the contents of each cell. Note that rows start at 1, columns start at a         for ($rowIndex = 2; $rowIndex <= $allRow; $rowIndex + +) {      &N Bsp   $token _db = $row _db = $field = ArraY ();          $i = 0;          $query = ";        &N Bsp for ($colIndex = 0; $colIndex <= $col; $colIndex + +) {           //$addr = $colIndex. $rowI ndex;           //$cell = $currentSheet->getcell ($addr)->getvalue ();    & nbsp       $cell = $currentSheet->getcellbycolumnandrow ($colIndex, $rowIndex)->getvalue ();            $cell = Trim ($cell);            if ($cell instanceof Phpex Cel_richtext) {             //Rich Text conversion string               $cell = $cell->__tostring ();           }            if ($ Colindex = ' A ' &&!intval ($cell)) {              $paseRows ++;    &NB Sp         break;           }            $field [] = $table [$i];& nbsp           $token _db[] = "'%s '";            $row _db[] = $cell;            $query. = $table [$i]. "= '%s ',";            $i ++;         }          $row ++;          if ($row _db) {& nbsp           db_query (' INSERT into {db_import} ('. Implode (', ', $field). ", created) VALUES ('. Impl Ode (', ', $token _db). ',%d ', Array_merge ($row _db, Array ($timestamp));            $insertR ows++;         }       }        fclose ($handle) &nbs P    }      drupal_set_message (' File @file import succeeded. ', array (' @file ' => $file->filename))) nbsp    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 (t (' File to import not found. '), ' Error '); &NB Sp     $form _state[' redirect '] = ' admin/content/db/import ';      return;   } }& nbsp.?>   Above Code section to note a few:     code as follows: $allColumn = $currentSheet->gethighestcolumn ();  //gets an array index of uppercase letters in English. $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.