/** * 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; } /* //code for appending 254 to phone numbers if (isset($phone)) { $phone = preg_replace('/\s+/', '', $phone); $phone = ltrim($phone, '0'); // echo "<pre>".substr($phone, 0,3); if (substr($phone, 0,3) != '254') { $phone = '254'.$phone; } }else{ $phone = NULL; } */ $number_length = isset($phone) ? strlen($phone) : 0; // 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; } $fault_index = 2; //overriding both district and phone error as district is not necessarily necessary $status = 2; } /*commented out insertion below*/ $listing = array(); $listing_data = array('name' => $name, 'email' => $email, 'phone_number' => $phone, 'facility_code' => $new_facility_code, 'sub_county' => $new_district_id, 'county' => $new_county_id, 'usertype' => $usertype, 'date_uploaded' => $date_uploaded, 'status' => '0'); array_push($listing, $listing_data); // echo "<pre>";print_r($inv); $similarity_query = "SELECT * FROM email_listing_new WHERE phone_number = '{$phone}'"; $similarity = $this->db->query($similarity_query)->result_array(); //FACILITY CODE SEARCH if (empty($similarity)) { $insertion = $this->db->insert_batch('email_listing_new', $listing); // echo "QUERY SUCCESSFUL. ".$insertion." ".mysql_insert_id()."</br>"; } else { // echo "<pre> Dab on em"; } } // echo "<pre>";print_r($inv); // unlink($inputFileName); // echo "QUERY SUCCESSFUL. LAST ID INSERTED: ".mysql_insert_id(); exit; // redirect(base_url().'admin/report_listing'); // 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; } } if ($wpdb->insert($dbprefix . "dues_data", $rowData, array('%s', '%s', '%s', '%s', '%s'))) { $recordcount++; } } } $error_output = ob_get_clean(); if (!$error_output) { ?> <div class="updated"><p><strong>Import successful. Imported <?php esc_html_e($recordcount); ?> records.</strong></p></div><?php } else { ?> <div class="error"><p><strong>Import partially successful. Imported <?php esc_html_e($recordcount); ?> of <?php esc_html_e($row->getRowIndex() - 2); ?> records.</strong></p> <p>Errors follow:</p> <?php echo $error_output; ?> </div><?php } update_option('oadueslookup_last_update', $wpdb->get_var("SELECT DATE_FORMAT(MAX(dues_paid_date), '%Y-%m-%d') FROM {$dbprefix}dues_data")); } else { ?> <div class="error"><p><strong>Invalid file upload.</strong> Not an XLSX file.</p></div><?php } } // // HANDLE SETTINGS SCREEN UPDATES // if (isset($_POST[$hidden_field_name]) && $_POST[$hidden_field_name] == 'oadueslookup-settings') { $slug = $_POST['oadueslookup_slug']; $dues_url = $_POST['oadueslookup_dues_url']; $dues_register = $_POST['oadueslookup_dues_register']; $dues_register_msg = $_POST['oadueslookup_dues_register_msg']; $update_url = $_POST['oadueslookup_update_url']; $update_link_text = $_POST['oadueslookup_update_option_link_text']; $update_option_text = $_POST['oadueslookup_update_option_text']; $help_email = $_POST['oadueslookup_help_email']; # $help_email is the only one that throws an error if it doesn't # validate. The others we just silently fix so they're something # valid. The user will see the result on the form. if (!is_email($help_email)) { ?> <div class="error"><p><strong>'<?php esc_html_e($help_email); ?> ' is not a valid email address.</strong></p></div><?php } else { $foundchanges = 0; $slug = sanitize_title($slug); if ($slug != get_option('oadueslookup_slug')) { update_option('oadueslookup_slug', $slug); $foundchanges = 1; } $dues_url = esc_url_raw($dues_url); if ($dues_url != get_option('oadueslookup_dues_url')) { update_option('oadueslookup_dues_url', $dues_url); $foundchanges = 1; } if ($dues_register != get_option('oadueslookup_dues_register')) { update_option('oadueslookup_dues_register', $dues_register); $foundchanges = 1; } $dues_register_msg = sanitize_text_field($dues_register_msg); if ($dues_register_msg != get_option('oadueslookup_dues_register_msg')) { update_option('oadueslookup_dues_register_msg', $dues_register_msg); $foundchanges = 1; } $update_url = esc_url_raw($update_url); if ($update_url != get_option('oadueslookup_update_url')) { update_option('oadueslookup_update_url', $update_url); $foundchanges = 1; } $update_link_text = sanitize_text_field($update_link_text); if ($update_link_text != get_option('oadueslookup_update_option_link_text')) { update_option('oadueslookup_update_option_link_text', $update_link_text); $foundchanges = 1; } $update_option_text = sanitize_text_field($update_option_text); if ($update_option_text != get_option('oadueslookup_update_option_text')) { update_option('oadueslookup_update_option_text', $update_option_text); $foundchanges = 1; } $help_email = sanitize_email($help_email); if ($help_email != get_option('oadueslookup_help_email')) { update_option('oadueslookup_help_email', $help_email); $foundchanges = 1; } if ($foundchanges) { ?> <div class="updated"><p><strong>Changes saved.</strong></p></div><?php } } } // ============================ // screens and forms start here // ============================ // // MAIN SETTINGS SCREEN // echo '<div class="wrap">'; // header echo "<h2>" . __('OA Dues Lookup Settings', 'oadueslookup') . "</h2>"; // settings form ?> <h3 class="oalm">Import data from OALM</h3> <p>Export file from OALM Must contain at least the following columns:<br> BSA ID, Dues Yr., Dues Pd. Dt., Level, Reg. Audit Date, Reg. Audit Result<br> Any additional columns will be ignored.</p> <p><a href="http://github.com/justdave/oadueslookup/wiki">How to create the export file in OALM</a></p> <form action="" method="post" enctype="multipart/form-data"> <label for="oalm_file">Click Browse, then select the xlsx file exported from OALM's "Export Members", then click "Upload":</label><br> <input type="file" name="oalm_file" id="oalm_file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"> <input type="submit" class="button button-primary" name="submit" value="Upload"><br> <p><strong>Last import:</strong> <?php $last_import = get_option('oadueslookup_last_import'); if ($last_import == '1900-01-01') { echo "Never"; } else { esc_html_e($last_import); } ?> </p> </form> <h3 class="oalm">Lookup Page Settings</h3> <form name="oadueslookup-settings" method="post" action=""> <input type="hidden" name="<?php echo $hidden_field_name; ?> " value="oadueslookup-settings"> <table class="form-table"> <tbody> <tr> <th scope="row"><label for="oadueslookup_slug">Dues Page Slug</label></th> <td><code><?php echo esc_html(get_option("home")); ?> /</code><input id="oadueslookup_slug" name="oadueslookup_slug" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_slug")); ?> "> <p class="description">The name appended to your Site URL to reach the lookup page.</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_dues_url">Dues Payment URL</label></th> <td><input id="oadueslookup_dues_url" name="oadueslookup_dues_url" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_dues_url")); ?> "> <p class="description">The URL to send members to for actually paying their dues.</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_dues_register">Registration Required?</label></th> <td><input id="oadueslookup_dues_register" name="oadueslookup_dues_register" class="code" type="checkbox" value="1"<?php checked(1 == esc_html(get_option('oadueslookup_dues_register'))); ?> "> <p class="description">Does the dues payment site require the user to register before paying?</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_dues_register_msg">Registration Required Message</label></th> <td><input id="oadueslookup_dues_register_msg" name="oadueslookup_dues_register_msg" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_dues_register_msg")); ?> "> <p class="description">The instruction text to display informing the user that they need to register before paying dues.</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_update_url">Update Contact Info URL</label></th> <td><input id="oadueslookup_update_url" name="oadueslookup_update_url" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_update_url")); ?> "> <p class="description">The URL to send members to for updating their contact information.</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_update_option_link_text">Update Contact Link Info Text</label></th> <td><input id="oadueslookup_update_option_link_text" name="oadueslookup_update_option_link_text" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_update_option_link_text")); ?> "> <p class="description">The text to appear in the hyperlink to the Update Contact Information URL.</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_update_option_text">Section Label</label></th> <td><input id="oadueslookup_update_option_text" name="oadueslookup_update_option_text" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_update_option_text")); ?> "> <p class="description">The label or option on the Update Contact Information page to direct the user to.</p> </td> </tr> <tr> <th scope="row"><label for="oadueslookup_help_email">Help Email</label></th> <td><input id="oadueslookup_help_email" name="oadueslookup_help_email" class="regular-text code" type="text" value="<?php echo esc_html(get_option("oadueslookup_help_email")); ?> "> <p class="description">The email address for members to ask questions.</p> </td> </tr> </tbody> </table> <p class="submit"><input id="submit" class="button button-primary" type="submit" value="Save Changes" name="submit"></p> </form> <?php echo "</div>"; }
/** * 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); }