Drupal reads Excel and imports the database instance

PHPExcel is an PHP library for manipulating Office and Excel documents. It is 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, etc.

1. Drupal calls PHPExcel via Library
After PHPExcel download, upload Drupal directory: sites/all/libraries/PHPExcel
If you install the ES34en module in your project, you can use ES35en_ES36en ($ES37en); To invoke.
If the libraries module is not installed, you can simply use the following code to call it:

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

Note that the program can run for a long time in order to ensure that Excel is fully imported. So at the beginning of the code add:

set_time_limit(0);

To ensure that the running time is not limited.
2. Drupal reads Excel and imports it into database
The Drupal implementation uploads the Excel file, reads the Excel content, writes to the database, and prints the import result message.
In summary, there are the following points:
1.Drupal reads Excel with rows and columns from 1 to n and rows from 1 to n.
2.Drupal is used to store columns from Excel 1 to n according to the database structure n. If the number of columns in Excel is large, you can store the value of the column in one field.
MySQL n (n is not very large)

This is the function after ES76en finally commits the upload file:

<?php 
function excel_upload_form_submit($form, &$form_state) { 
  set_time_limit(0); 
  $timestamp = time(); 
  //  Make sure that Excel The file has been uploaded  
  if ($file = file_save_upload('file')) { 
    $row = 0; // Analytical lines  
    $paseRows = 0; // Skip the number of rows   Rows that have no value  
    $insertRows = 0; // Insert the number of rows  
    $table = array( 
      'dbfield1 ' , 
      'dbfield2 ' , 
      'dbfield3, 
      'dbfield4 ' , 
      'dbfield5 ' , 
       ...  
      'dbfieldn', 
    ); 
    require("sites/all/libraries/PHPExcel/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 ); 
      /** achieve 1 How many columns are there */ 
      $allColumn = $currentSheet->getHighestColumn(); 
      // Get the total number of columns , If this static method is not used, the $col Is the largest uppercase letter in a file column  
      $col = PHPExcel_Cell::columnIndexFromString($currentSheet->getHighestColumn()); 
      /** achieve 1 How many rows are there */ 
      $allRow = $currentSheet->getHighestRow(); 
      // Loop through the contents of each cell. Pay attention to the line from 1 Start, columns start A start  
      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 strings  
            $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  Import success .', array('@file' => $file->filename))); 
    drupal_set_message(" parsing ".$row." Data completed, add total ".$insertRows." Bar data, no question type ID the ".$paseRows." The data. "); 
  } 
  else { 
    drupal_set_message(t('File to import not found.'), 'error'); 
    $form_state['redirect'] = 'admin/content/db/import'; 
    return; 
  } 
} 
?>

Note the following points in the code section 1 above:

$allColumn = $currentSheet->getHighestColumn();  // Gets the index of an array that lists English uppercase letters. 
$col = PHPExcel_Cell::columnIndexFromString($currentSheet->getHighestColumn()); // Formats an index of English uppercase letters as a number. The index value is set from 0 Let's start. 

This code supports reading Excel 2007 and earlier formats.