Drupal reads Excel and Imports database instances

Source: Internet
Author: User
Tags php language php class require drupal

  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.  
Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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.