PHP PHPExcel_Reader_Excel2007::setReadDataOnly方法代码示例

 /**
  * Reads the file into a PHPExcel object.
  * 
  * @param string $filename The file to parse.
  * 
  * @return \PHPExcel Returns the PHPExcel object for the file.
  * 
  * @throws ParseException when the reader encounters an error.
  */
 private static function readFile(string $filename) : \PHPExcel
 {
     try {
         $reader = new \PHPExcel_Reader_Excel2007();
         $reader->setReadDataOnly(true);
         $xlsx = $reader->load($filename);
     } catch (\Exception $e) {
         throw new ParseException($e->getMessage());
     }
     return $xlsx;
 }

 /**
  * Loads source data from local file into memory.
  */
 private function load_source_data_from_file()
 {
     require_once DOC_ROOT . '/vendor/PHPExcel/Classes/PHPExcel.php';
     // NOTE: In development it takes 24 Seconds to load and uses 318 MB of memory.
     $start = microtime(true);
     debug('Loading source file.');
     try {
         $reader = new \PHPExcel_Reader_Excel2007();
         $reader->setReadDataOnly(true);
         $this->source_loaded = $reader->load($this->source_file_path);
         unset($reader);
     } catch (\Exception $e) {
         throw new \Exception('Error loading source data from ' . "{$this->source_file_path}: {$e->getMessage()}");
     }
     $this->profile($start);
 }

 /**
  * @desc 读取excel数据
  * @param string $excelFilePath  excel的保存文件路径
  */
 public function readExcel($excelFilePath)
 {
     $return = array('status' => 0);
     $PHPReader = new PHPExcel_Reader_Excel2007();
     $PHPReader->setReadDataOnly(true);
     //设为只读
     //如果没有选择excel文件
     if (!$PHPReader->canRead($excelFilePath)) {
         $PHPReader = new PHPExcel_Reader_Excel5();
         if (!$PHPReader->canRead($excelFilePath)) {
             $return['msg'] = 'no Excel';
             return $return;
         }
     }
     $PHPExcel = $PHPReader->load($excelFilePath);
     //传入excel路径，载入该excel
     $currentSheet = $PHPExcel->getSheet(0);
     //sheet 0
     $allColumn = $currentSheet->getHighestColumn();
     //总列 //column Count
     $allRow = $currentSheet->getHighestRow();
     //总行//Row Count
     $blockStartEndRow = $this->getBloclStartEndRow($allColumn, $allRow, $currentSheet);
     $excelData = $this->getExcelData($blockStartEndRow, $allColumn, $allRow, $currentSheet);
     if (ISPRINTEXCELDATAARR) {
         echo '<pre>';
         echo '开始-结束行<br/>';
         print_r($blockStartEndRow);
         echo '<hr/>';
         echo 'excel数据数组<br/>';
         print_r($excelData);
         exit;
     }
     unlink($excelFilePath);
     //删除excel文件
     $return['data'] = $excelData;
     return $return;
 }

 public function upload_report_listing($file_name)
 {
     //  Include PHPExcel_IOFactory
     // include 'PHPExcel/IOFactory.php';
     // include 'PHPExcel/PHPExcel.php';
     // $inputFileName = 'excel_files/garissa_sms_recepients_updated.xlsx';
     // echo $category;exit;
     $inputFileName = 'print_docs/excel/uploaded_files/' . $file_name;
     $objReader = new PHPExcel_Reader_Excel2007();
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($inputFileName);
     // echo "<pre>";print_r($inputFileName);exit;
     $sheet = $objPHPExcel->getSheet(0);
     $highestRow = $sheet->getHighestRow() + 1;
     $highestColumn = $sheet->getHighestColumn();
     // echo "<pre>";print_r($highestRow);echo "</pre>";exit;
     $rowData = array();
     for ($row = 4; $row < $highestRow; $row++) {
         //  Read a row of data into an array
         $rowData_ = $sheet->rangeToArray('A' . $row . ':F' . $row);
         // echo "<pre>";print_r($rowData_);echo "</pre>";
         array_push($rowData, $rowData_[0]);
         //  Insert row data array into your database of choice here
     }
     // echo "<pre>";print_r($rowData);exit;//echo's array
     foreach ($rowData as $r_data) {
         // echo "<pre>";print_r($r_data);echo "</pre>";
         /*
         Result array key
         0 = name
         1 = phone
         2 = email
         3 = county
         4 = subcounty
         5 = mfl
         */
         $status = 1;
         $county_id = $district_id = $facility_code = 0;
         $new_county_id = $new_district_id = $new_facility_code = $usertype = null;
         $county_name = strtolower($r_data[3]);
         //lower case
         $county_name = str_replace(" ", "", $county_name);
         $county_name = str_replace("-", " ", $county_name);
         $county_name = ucwords($county_name);
         //upper first character
         $district = strtolower($r_data[4]);
         $district = ucfirst($district);
         $phone = preg_replace('/\\s+/', '', $r_data[1]);
         // echo "<pre>";print_r($phone);
         // echo "<pre>";print_r($county_name);
         $facility_code = !empty($r_data[5]) ? $r_data[5] : NULL;
         $name = !empty($r_data[0]) ? $r_data[0] : NULL;
         $email = !empty($r_data[2]) ? $r_data[2] : NULL;
         $date_uploaded = date('Y-m-d h:i:s');
         $fault_index = NULL;
         // echo $district;
         $query = "SELECT * FROM facilities WHERE facility_code = '{$facility_code}'";
         $result = $this->db->query($query)->result_array();
         //FACILITY CODE SEARCH
         // echo "<pre>";print_r($result);echo "</pre>";
         $sql = null;
         if (empty($result)) {
             //if no facility code then district
             $queryy = "SELECT * FROM districts WHERE district = '{$district}'";
             $resultt = $this->db->query($queryy)->result_array();
             if (empty($resultt)) {
                 //no district then county
                 $queryyy = "SELECT * FROM counties WHERE county = '{$county_name}'";
                 $resulttt = $this->db->query($queryyy)->result_array();
                 if (empty($resulttt)) {
                     echo "Empty county,subcounty and facility";
                 } else {
                     $county_id = $resulttt[0]['id'];
                     // echo "<pre>\t Only County ".$phone;
                     $new_county_id = $county_id;
                     $usertype = 10;
                 }
             } else {
                 //if district matches
                 $district_id = $resultt[0]['id'];
                 $county_id = $this->get_county_id_for_district($district_id);
                 // echo "<pre>\t District ".$phone;
                 $new_county_id = $county_id;
                 $new_district_id = $district_id;
                 $usertype = 3;
             }
             //district name match
         } else {
             //if facility_code_match
             // echo "<pre>";print_r($result);exit;
             $district_id = $result[0]['district'];
             $county_id = $this->get_county_id_for_district($district_id);
             // echo "<pre>\t Facility: ".$phone;
             $new_county_id = $county_id;
             $new_district_id = $district_id;
             $new_facility_code = $facility_code;
             $usertype = 5;
             // echo "<pre>"; print_r($county_id);exit;
         }
         /*
//.........这里部分代码省略.........

 public function uploadAction()
 {
     //     $data = $this->getRequest()->getPost();
     $file = $_FILES['xls'];
     if ($file['error'] == 4) {
         Mage::getSingleton('core/session')->addError(Mage::helper('maxfurniture')->__('Please choose file to upload.'));
         $this->_redirect('*/*/');
         return;
     } elseif ($file['error']) {
         Mage::getSingleton('core/session')->addError(Mage::helper('maxfurniture')->__('Upload error. Please try again.'));
         $this->_redirect('*/*/');
         return;
     } elseif (!preg_match('/\\.xlsx{0,1}$/', $file['name'])) {
         Mage::getSingleton('core/session')->addError(Mage::helper('maxfurniture')->__('You can only use XLS or XLSX files.'));
         $this->_redirect('*/*/');
         return;
     }
     $name = $file['name'];
     $ext = preg_match('/xlsx$/', $name) ? 'xlsx' : 'xls';
     $xls_file_name = Mage::getBaseDir() . "/custom/add_products/xls/import.{$ext}";
     @unlink($xls_file_name);
     move_uploaded_file($file['tmp_name'], $xls_file_name);
     try {
         require_once Mage::getBaseDir() . "/custom/include/PHPExcel/PHPExcel.php";
         $cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
         $cacheSettings = array('cacheTime' => 6000);
         PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
         if ($ext == 'xls') {
             $objReader = new PHPExcel_Reader_Excel5();
         } else {
             $objReader = new PHPExcel_Reader_Excel2007();
         }
         $objReader->setReadDataOnly(true);
         $names = $objReader->listWorksheetNames($xls_file_name);
         // ww($CUSTOM);
         $DIR = Mage::getBaseDir() . "/custom/add_products/";
         $files = array();
         foreach ($names as $key => $name) {
             $name = strtolower($name);
             $name = preg_replace('/\\d$/', '', $name);
             $files[$name] = $name;
         }
         foreach ($files as $file) {
             @unlink($DIR . 'csv/' . $file . '.csv');
         }
         $i = 0;
         foreach ($names as $key => $name) {
             $file_name = strtolower($name);
             $file_name = preg_replace('/\\d$/', '', $file_name);
             $i++;
             $file_path = $DIR . 'csv/' . $file_name . '.csv';
             $skip_first_row = false;
             if (file_exists($file_path)) {
                 //         wlog("Appending: $file_name.csv", false);
                 $skip_first_row = true;
             }
             /* else wlog("Creating: $file_name.csv", false);*/
             $fp = fopen($file_path, 'a');
             $objReader->setLoadSheetsOnly(array($name));
             $objPHPExcel = $objReader->load($xls_file_name);
             $objWorksheet = $objPHPExcel->getActiveSheet();
             $j = 0;
             foreach ($objWorksheet->getRowIterator() as $row) {
                 $j++;
                 if ($j == 1 && $skip_first_row) {
                     continue;
                 }
                 $cellIterator = $row->getCellIterator();
                 $cellIterator->setIterateOnlyExistingCells(false);
                 $fields = array();
                 foreach ($cellIterator as $cell) {
                     $value = $cell->getValue();
                     $fields[] = $value;
                 }
                 fputcsv($fp, $fields);
             }
             fclose($fp);
         }
     } catch (Exception $e) {
         Mage::getSingleton('core/session')->addError(Mage::helper('maxfurniture')->__('Error processing file.<br/>%s', $e->getMessage()));
         $this->_redirect('*/*/');
         return;
     }
     Mage::getSingleton('core/session')->addSuccess(Mage::helper('maxfurniture')->__('File uploaded and conferted to CSV files successfully.'));
     $this->_redirect('*/*/');
 }

function oadueslookup_options()
{
    global $wpdb;
    $dbprefix = $wpdb->prefix . "oalm_";
    $hidden_field_name = 'oalm_submit_hidden';
    if (!current_user_can('manage_options')) {
        wp_die(__('You do not have sufficient permissions to access this page.'));
    }
    // =========================
    // form processing code here
    // =========================
    if (isset($_FILES['oalm_file'])) {
        #echo "<h3>Processing file upload</h3>";
        #echo "<strong>Processing File:</strong> " . esc_html($_FILES['oalm_file']['name']) . "<br>";
        #echo "<strong>Type:</strong> " . esc_html($_FILES['oalm_file']['type']) . "<br>";
        if (preg_match('/\\.xlsx$/', $_FILES['oalm_file']['name'])) {
            /** PHPExcel */
            include plugin_dir_path(__FILE__) . 'PHPExcel-1.8.0/Classes/PHPExcel.php';
            /** PHPExcel_Writer_Excel2007 */
            include plugin_dir_path(__FILE__) . 'PHPExcel-1.8.0/Classes/PHPExcel/Writer/Excel2007.php';
            $objReader = new PHPExcel_Reader_Excel2007();
            $objReader->setReadDataOnly(true);
            $objReader->setLoadSheetsOnly(array("All"));
            $objPHPExcel = $objReader->load($_FILES["oalm_file"]["tmp_name"]);
            $objWorksheet = $objPHPExcel->getActiveSheet();
            $columnMap = array('BSA ID' => 'bsaid', 'Dues Yr.' => 'max_dues_year', 'Dues Pd. Dt.' => 'dues_paid_date', 'Level' => 'level', 'Reg. Audit Date' => 'reg_audit_date', 'Reg. Audit Result' => 'reg_audit_result');
            $complete = 0;
            $recordcount = 0;
            $error_output = "";
            foreach ($objWorksheet->getRowIterator() as $row) {
                $rowData = array();
                if ($row->getRowIndex() == 1) {
                    # this is the header row, grab the headings
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(FALSE);
                    foreach ($cellIterator as $cell) {
                        $cellValue = $cell->getValue();
                        if (isset($columnMap[$cellValue])) {
                            $rowData[$columnMap[$cellValue]] = 1;
                            #echo "Found column " . htmlspecialchars($cell->getColumn()) . " with title '" . htmlspecialchars($cellValue) . "'<br>" . PHP_EOL;
                        } else {
                            #echo "Discarding unknown column " . htmlspecialchars($cell->getColumn()) . " with title '" . htmlspecialchars($cellValue) . "'<br>" . PHP_EOL;
                        }
                    }
                    $missingColumns = array();
                    foreach ($columnMap as $key => $value) {
                        if (!isset($rowData[$value])) {
                            $missingColumns[] = $key;
                        }
                    }
                    if ($missingColumns) {
                        ?>
<div class="error"><p><strong>Import failed.</strong></p><p>Missing required columns: <?php 
                        esc_html_e(implode(", ", $missingColumns));
                        ?>
</div><?php 
                        $complete = 1;
                        # Don't show "may have failed" box at the bottom
                        break;
                    } else {
                        #echo "<strong>Data format validated:</strong> Importing new data...<br>" . PHP_EOL;
                        # we just validated that we have a good data file, nuke the existing data
                        $wpdb->show_errors();
                        ob_start();
                        $wpdb->query("TRUNCATE TABLE {$dbprefix}dues_data");
                        update_option('oadueslookup_last_import', $wpdb->get_var("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d')"));
                        # re-insert the test data
                        oadueslookup_insert_sample_data();
                        # now we're ready for the incoming from the rest of the file.
                    }
                } else {
                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(FALSE);
                    foreach ($cellIterator as $cell) {
                        $columnName = $objWorksheet->getCell($cell->getColumn() . "1")->getValue();
                        $value = "";
                        if ($columnName == "Dues Pd. Dt.") {
                            # this is a date field, and we have to work miracles to turn it into a mysql-compatible date
                            $date = $cell->getValue();
                            $dateint = intval($date);
                            $dateintVal = (int) $dateint;
                            $value = PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "YYYY-MM-DD");
                        } else {
                            if ($columnName == "Reg. Audit Date") {
                                # this is also a date field, but can be empty
                                $date = $cell->getValue();
                                if (!$date) {
                                    $value = get_option('oadueslookup_last_import');
                                } else {
                                    $dateint = intval($date);
                                    $dateintVal = (int) $dateint;
                                    $value = PHPExcel_Style_NumberFormat::toFormattedString($dateintVal, "YYYY-MM-DD");
                                }
                            } else {
                                $value = $cell->getValue();
                            }
                        }
                        if (isset($columnMap[$columnName])) {
                            $rowData[$columnMap[$columnName]] = $value;
                        }
//.........这里部分代码省略.........

/**
 * The possible scopes for $plugin_param are: 'table', 'database', and 'server'
 */
if (isset($plugin_list)) {
    $plugin_list['xlsx'] = array('text' => 'strImportXLSX', 'extension' => 'xlsx', 'options' => array(array('type' => 'bool', 'name' => 'col_names', 'text' => 'strImportColNames')), 'options_text' => 'strOptions');
    /* We do not define function when plugin is just queried for information above */
    return;
}
ini_set('memory_limit', '256M');
set_time_limit(120);
/* Append the PHPExcel directory to the include path variable */
set_include_path(get_include_path() . PATH_SEPARATOR . getcwd() . '/libraries/PHPExcel/');
require_once './libraries/PHPExcel/PHPExcel.php';
require_once './libraries/PHPExcel/PHPExcel/Reader/Excel2007.php';
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objReader->setLoadAllSheets();
$objPHPExcel = $objReader->load($import_file);
$sheet_names = $objPHPExcel->getSheetNames();
$num_sheets = count($sheet_names);
$tables = array();
$tempRow = array();
$rows = array();
$col_names = array();
for ($s = 0; $s < $num_sheets; ++$s) {
    $current_sheet = $objPHPExcel->getSheet($s);
    $num_rows = $current_sheet->getHighestRow();
    $num_cols = PMA_getColumnNumberFromName($current_sheet->getHighestColumn());
    if ($num_rows != 1 && $num_cols != 1) {
        for ($r = 1; $r <= $num_rows; ++$r) {
            for ($c = 0; $c < $num_cols; ++$c) {

 public function upload_recepients($file_name = NULL)
 {
     //  Include PHPExcel_IOFactory
     // include 'PHPExcel/IOFactory.php';
     // include 'PHPExcel/PHPExcel.php';
     $inputFileName = 'uploads/excel/' . $file_name;
     $objReader = new PHPExcel_Reader_Excel2007();
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($inputFileName);
     // echo "<pre>";print_r($inputFileName);exit;
     $sheet = $objPHPExcel->getSheet(0);
     $highestRow = $sheet->getHighestRow() + 1;
     $highestColumn = $sheet->getHighestColumn();
     // echo "<pre>";print_r($highestRow);echo "</pre>";exit;
     $rowData = array();
     for ($row = 3; $row < $highestRow; $row++) {
         //  Read a row of data into an array
         $rowData_ = $sheet->rangeToArray('A' . $row . ':H' . $row);
         // echo "<pre>";print_r($rowData_);echo "</pre>";
         array_push($rowData, $rowData_[0]);
         //  Insert row data array into your database of choice here
     }
     // echo "<pre>";print_r($rowData);echo "</pre>";exit;
     /*
     names
     facility_name
     mfl
     district
     id_number
     mobile
     email
     trainingsite
     */
     foreach ($rowData as $r_data) {
         // echo "<pre>";print_r($r_data);echo "</pre>";
         $status = 1;
         $district = strtolower($r_data[3]);
         $district = ucfirst($district);
         $fault_index = NULL;
         // echo $district;
         $facility_code = $r_data[2];
         $query = "SELECT * FROM facilities WHERE facility_code = '{$facility_code}'";
         // $query = "SELECT * FROM districts WHERE district = '$district'";
         $result = $this->db->query($query)->result_array();
         // $district_name = $result[0]['district'];
         // echo "<pre>";print_r($result);echo "</pre>";
         if (empty($result)) {
             $queryy = "SELECT * FROM districts WHERE district = '{$district}'";
             $resultt = $this->db->query($queryy)->result_array();
             // echo $r_data[0]."</br>";
             // $query = "";
             if (empty($resultt)) {
                 $fault_index = 1;
                 $status = 2;
                 $district_id = NULL;
             } else {
                 $district_id = $result[0]['id'];
                 $fault_index = 0;
                 // echo "<pre>";print_r($resultt); echo "</pre>";
             }
         } else {
             $district_id = $result[0]['district'];
         }
         $names = $r_data[0];
         $phone = $r_data[5];
         if (isset($phone)) {
             $phone = preg_replace('/\\s+/', '', $phone);
             $phone = ltrim($phone, '0');
             $phone = '254' . $phone;
         } else {
             $phone = NULL;
         }
         $email = $r_data[6];
         $number_length = isset($phone) ? strlen($phone) : 0;
         // echo $phone;
         echo "Number Length:  " . $number_length;
         if ($number_length != 12) {
             if (isset($fault_index)) {
                 $fault_index = 3;
                 //both error in phone and district
                 // $status = 2;
             } else {
                 $fault_index = 2;
             }
             $status = 2;
         }
         $sms_status = isset($status) ? $status : 1;
         $rec = array();
         $rec_data = array('fname' => $names, 'email' => $email, 'phone_no' => $phone, 'sms_status' => $sms_status, 'user_type' => 1, 'category_id' => 1, 'district_id' => $district_id, 'fault_index' => $fault_index);
         array_push($rec, $rec_data);
         $insertion = $this->db->insert_batch('recepients', $rec);
         echo "QUERY SUCCESSFUL. " . $insertion . " " . mysql_insert_id() . "</br>";
     }
     // };
     // echo "QUERY SUCCESSFUL. LAST ID INSERTED: ".mysql_insert_id(); exit;
     redirect(base_url() . 'users/recipients');
 }

 public function upload_redistribution($file_name)
 {
     $inputFileName = 'print_docs/excel/uploaded_files/' . $file_name;
     $objReader = new PHPExcel_Reader_Excel2007();
     $objReader->setReadDataOnly(true);
     $objPHPExcel = $objReader->load($inputFileName);
     $sheet = $objPHPExcel->getSheet(0);
     $highestRow = $sheet->getHighestRow() + 1;
     $highestColumn = $sheet->getHighestColumn();
     $sending_facility = $sheet->getCell('C8')->getValue();
     $facility_code = $this->session->userdata('facility_id');
     $current_date = date($format = "Y-m-d h:i:s", PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell('C9')->getValue()));
     // echo "$current_date";die;
     $today = date('Y-m-d h:i:s');
     $rowData = array();
     for ($row = 12; $row < $highestRow; $row++) {
         //  Read a row of data into an array
         $rowData_ = $sheet->rangeToArray('B' . $row . ':G' . $row);
         array_push($rowData, $rowData_[0]);
     }
     foreach ($rowData as $r_data) {
         $commodity_name = $r_data[0];
         $batch_no = $r_data[1];
         $manufacturer = $r_data[2];
         $expiry_date = date($format = "Y-m-d h:i:s", PHPExcel_Shared_Date::ExcelToPHP($r_data[3]));
         // $expiry_date =  date($format = "Y-m-d h:i:s", PHPExcel_Shared_Date::ExcelToPHP($expiry_date));
         $quantity_packs = $r_data[4];
         $quantity_units = $r_data[5];
         //Get commodity details from the name
         $commodity_details = commodities::get_details_name($commodity_name);
         foreach ($commodity_details as $key => $value) {
             $commodity_id = $value['id'];
             $commodity_code = $value['commodity_code'];
             $unit_size = $value['pack_size'];
             $commodity_source_id = $value['commodity_source_id'];
             //Convert the Packs to Units
             $received_quantity = null;
             if ($quantity_units != '' || $quantity_units != null) {
                 $received_quantity = $quantity_units;
             } else {
                 $unit_size = $unit_size == 0 ? 1 : $unit_size;
                 $received_quantity = $quantity_packs * $unit_size;
             }
             //Save the Data in a temporary table
             $received_data = array(array('facility_code' => $facility_code, 'sending_facility' => $sending_facility, 'commodity_id' => $commodity_id, 'batch_no' => $batch_no, 'manufacturer' => $manufacturer, 'quantity_received' => $received_quantity, 'expiry_date' => $expiry_date, 'date_received' => $current_date, 'status' => 1));
             //Save to receive redistributions table
             $this->db->insert_batch('receive_redistributions', $received_data);
         }
     }
     $this->session->set_flashdata('system_success_message', "File Upload Successful");
     redirect('issues/confirm_external_issue_offline');
 }

 /**
  * Reads the data from file in the DB and generates a wpDataTable
  */
 public function readFileData($table_data)
 {
     $columnTypes = array();
     if (!empty($table_data['file'])) {
         $xls_url = urldecode($table_data['file']);
         if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
             $xls_url = str_replace(site_url(), str_replace('\\', '/', ABSPATH), $xls_url);
         } else {
             $xls_url = str_replace(site_url(), ABSPATH, $xls_url);
         }
     } else {
         return _('Empty file', 'wpdatatables');
     }
     for ($i = 0; $i < count($table_data['columns']); $i++) {
         if ($table_data['columns'][$i]['orig_header'] == '%%NEW_COLUMN%%') {
             $table_data['columns'][$i]['orig_header'] = 'column' . $i;
         }
         $columnTypes[$table_data['columns'][$i]['orig_header']] = $table_data['columns'][$i]['type'];
     }
     $this->_id = $this->generateManualTable($table_data);
     require_once WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php';
     $objPHPExcel = new PHPExcel();
     if (strpos(strtolower($xls_url), '.xlsx')) {
         $objReader = new PHPExcel_Reader_Excel2007();
         $objReader->setReadDataOnly(true);
     } elseif (strpos(strtolower($xls_url), '.xls')) {
         $objReader = new PHPExcel_Reader_Excel5();
         $objReader->setReadDataOnly(true);
     } elseif (strpos(strtolower($xls_url), '.ods')) {
         $objReader = new PHPExcel_Reader_OOCalc();
         $objReader->setReadDataOnly(true);
     } elseif (strpos(strtolower($xls_url), '.csv')) {
         $objReader = new PHPExcel_Reader_CSV();
     } else {
         return _('File format not supported!', 'wpdatatables');
     }
     $objPHPExcel = $objReader->load($xls_url);
     $objWorksheet = $objPHPExcel->getActiveSheet();
     $highestRow = $objWorksheet->getHighestRow();
     $highestColumn = $objWorksheet->getHighestColumn();
     $headingsArray = $objWorksheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true);
     $headingsArray = $headingsArray[1];
     $r = -1;
     $insertArray = array();
     // Insert statement default beginning
     $insert_statement_beginning = "INSERT INTO " . $this->_name . " (" . implode(', ', array_values($this->_column_headers)) . ") ";
     $insert_blocks = array();
     for ($row = 2; $row <= $highestRow; ++$row) {
         // Set all cells in the row to their defaults
         foreach ($table_data['columns'] as $column) {
             $insertArray[$this->_column_headers[$column['orig_header']]] = "'" . esc_sql($column['default_value']) . "'";
         }
         $dataRow = $objWorksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true);
         if (isset($dataRow[$row]['A']) && $dataRow[$row]['A'] > '') {
             ++$r;
             foreach ($headingsArray as $dataColumnIndex => $dataColumnHeading) {
                 if (!in_array($dataColumnHeading, array_keys($this->_column_headers))) {
                     continue;
                 }
                 if ($columnTypes[$dataColumnHeading] != 'date') {
                     $insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . esc_sql($dataRow[$row][$dataColumnIndex]) . "'";
                 } else {
                     if ($objReader instanceof PHPExcel_Reader_CSV) {
                         $date = strtotime(str_replace('/', '-', $dataRow[$row][$dataColumnIndex]));
                     } else {
                         $date = esc_sql(PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]));
                     }
                     $insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . date('Y-m-d', $date) . "'";
                 }
             }
         }
         $insert_blocks[] = '(' . implode(', ', $insertArray) . ')';
         if ($row % 100 == 0) {
             $this->insertRowsChunk($insert_statement_beginning, $insert_blocks);
             $insert_blocks = array();
         }
     }
     $this->insertRowsChunk($insert_statement_beginning, $insert_blocks);
 }

 public function excelBasedConstruct($xls_url, $wdtParameters = array())
 {
     ini_set("memory_limit", "2048M");
     if (!$xls_url) {
         throw new WDTException('Excel file not found!');
     }
     if (!file_exists($xls_url)) {
         throw new WDTException('Provided file ' . stripcslashes($xls_url) . ' does not exist!');
     }
     require_once WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php';
     $objPHPExcel = new PHPExcel();
     if (strpos(strtolower($xls_url), '.xlsx')) {
         $objReader = new PHPExcel_Reader_Excel2007();
         $objReader->setReadDataOnly(true);
     } elseif (strpos(strtolower($xls_url), '.xls')) {
         $objReader = new PHPExcel_Reader_Excel5();
         $objReader->setReadDataOnly(true);
     } elseif (strpos(strtolower($xls_url), '.ods')) {
         $objReader = new PHPExcel_Reader_OOCalc();
         $objReader->setReadDataOnly(true);
     } elseif (strpos(strtolower($xls_url), '.csv')) {
         $objReader = new PHPExcel_Reader_CSV();
     } else {
         throw new WDTException('File format not supported!');
     }
     $objPHPExcel = $objReader->load($xls_url);
     $objWorksheet = $objPHPExcel->getActiveSheet();
     $highestRow = $objWorksheet->getHighestRow();
     $highestColumn = $objWorksheet->getHighestColumn();
     $headingsArray = $objWorksheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true);
     $headingsArray = $headingsArray[1];
     $r = -1;
     $namedDataArray = array();
     for ($row = 2; $row <= $highestRow; ++$row) {
         $dataRow = $objWorksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true);
         if (isset($dataRow[$row]['A']) && $dataRow[$row]['A'] > '') {
             ++$r;
             foreach ($headingsArray as $dataColumnIndex => $dataColumnHeading) {
                 $namedDataArray[$r][$dataColumnHeading] = $dataRow[$row][$dataColumnIndex];
                 if (WDT_DETECT_DATES_IN_EXCEL) {
                     $cellID = $dataColumnIndex . $row;
                     if (PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell($cellID))) {
                         $namedDataArray[$r][$dataColumnHeading] = PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]);
                     }
                 }
             }
         }
     }
     $namedDataArray = apply_filters('wpdatatables_filter_excel_array', $namedDataArray, $this->getWpId(), $xls_url);
     return $this->arrayBasedConstruct($namedDataArray, $wdtParameters);
 }