How To Import .excel Data In WordPress DB

Here we will learn about how to import .excel data in WordPress DB.

How to Creating Custom Page Templates:

page-{slug}.php — WordPress uses a specialized template that uses the page’s slug.

write an opening PHP comment at the top of the template file that states the template’s name.

<?php /* Template Name: Example Template */ ?>

Download library for excel file read from click here.

This folder is put in child themes.

Custom code copied and paste into your child theme’s page template file.

Completed Code:

<?php
use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;

// Include Spout library 
require_once 'spout-2.4.3/src/Spout/Autoloader/autoload.php';

// this is working to import excelsheet data
if ( !empty( $_FILES['file']['name'] ) ) {
  // Get File extension eg. 'xlsx' to check file is excel sheet
  $pathinfo = pathinfo( $_FILES['file']['name'] );

  // check file has extension xlsx, xls and also check 
  // file is not empty
  if ( ( $pathinfo['extension'] == 'xlsx' || $pathinfo['extension'] == 'xls' ) && $_FILES['file']['size'] > 0 ) {
     
    // Temporary file name
    $inputFileName = $_FILES['file']['tmp_name']; 
    
    // Read excel file by using ReadFactory object.
    $reader = ReaderFactory::create(Type::XLSX);
    
    // Open file
    $reader->open( $inputFileName );
    $count = 1;
    // Number of sheet in excel file
    foreach ( $reader->getSheetIterator() as $sheet ) {
      
      // grab sheet name from existing file
      $existing_file_sheet_name = $sheet->getName();
      if( $existing_file_sheet_name ){
        // Number of Rows in Excel sheet
        foreach ( $sheet->getRowIterator() as $row ) {
          
          // It reads data after header. In the my excel sheet,
          // header is in the first row.
          if ( $count > 1 ) {
            //Here, You can insert data into database.
            global $wpdb;
            $tbl_name = $wpdb->prefix.'student';
            $kv_data = array(
              'student_id' => $row[0],
              'name'		 => $row[1],
              'address'	 => $row[2],
            );
            $new = $wpdb->insert( $tbl_name, $kv_data );
          }
          $count++;
        }
      }
    }
    // Close excel file
    $reader->close();
  } else {
    $erroe_msg = '';
    $erroe_msg = "Please Select Valid Excel File";
  }
}
?>
<div id="excelsucess"><?php echo $msg;?></div>
<div class="upload_error"><?php echo $erroe_msg;?></div>
<form action="#" method="post" name="myForm" enctype="multipart/form-data" class="upload_excel"> 
  <input type="file" name="file" id="upload_file">
  <input type= "submit" value ="upload" class="submit excel_btn">
</form>
<script>
jQuery( '.submit' ).click(function(){
  if( jQuery( '#upload_file' ).val().length == 0 ) {
    jQuery( '#excelsucess' ).html( 'Please select file' );			
    return false;
  }
});
</script>

OutPut: