function sheet($startAt) { // using the SpreadsheetReader class $Reader = new SpreadsheetReader($this->file); $Sheets = $Reader->Sheets(); foreach ($Sheets as $Index => $Name) { $Reader->ChangeSheet($Index); // to know when to get the data from file $number = 0; // name for the array. $name = ""; // within the billing excel for // gotobilling as of 12-17-2015 // within name area it have Total. $find = "Total"; foreach ($Reader as $Row) { // set the getting data once number is higher than $startAt if ($number > $startAt) { if ($Row[0] != "") { // to test the name not have total in side of it $pos = strpos($Row[0], $find); if ($pos === false) { // set the name $name = $Row[0]; } } // to see the row[1] is blanks or null if ($Row[1]) { // make an array of data from the file $export = array('name' => $name, 'entry_date' => $Row[1], 'description' => $Row[5], 'charge' => $Row[5], 'payment' => $Row[8], 'balance' => $Row[9]); // add to the data of the excelImport class array_push($this->data, $export); } } $number++; } } }
function get_sheet_reader($path_to_data_root) { $reader = new SpreadsheetReader($path_to_data_root . '/TBCPC-Regional-Programs-Lists-MASTER.xlsx'); $sheets = $reader->Sheets(); $reader->ChangeSheet(0); return $reader; }
echo '---------------------------------' . PHP_EOL; try { $Spreadsheet = new SpreadsheetReader($Filepath); $BaseMem = memory_get_usage(); $Sheets = $Spreadsheet->Sheets(); echo '---------------------------------' . PHP_EOL; echo 'Spreadsheets:' . PHP_EOL; print_r($Sheets); echo '---------------------------------' . PHP_EOL; echo '---------------------------------' . PHP_EOL; foreach ($Sheets as $Index => $Name) { echo '---------------------------------' . PHP_EOL; echo '*** Sheet ' . $Name . ' ***' . PHP_EOL; echo '---------------------------------' . PHP_EOL; $Time = microtime(true); $Spreadsheet->ChangeSheet($Index); foreach ($Spreadsheet as $Key => $Row) { echo $Key . ': '; if ($Row) { print_r($Row); $allxlsdata[] = $Row; } else { var_dump($Row); } $CurrentMem = memory_get_usage(); echo 'Memory: ' . ($CurrentMem - $BaseMem) . ' current, ' . $CurrentMem . ' base' . PHP_EOL; echo '---------------------------------' . PHP_EOL; if ($Key && $Key % 500 == 0) { echo '---------------------------------' . PHP_EOL; echo 'Time: ' . (microtime(true) - $Time); echo '---------------------------------' . PHP_EOL;
$po_box = ""; $customer = ""; $tag = ""; $results = array(); if (isset($_POST["po_box"])) { $po_box = $_POST["po_box"]; } if (isset($_POST["customer"])) { $customer = $_POST["customer"]; } if (isset($_POST["tag"])) { $tag = $_POST["tag"]; } foreach ($excel_sheets as $excel_sheet) { $reader = new SpreadsheetReader($directory . $excel_sheet); $reader->ChangeSheet(0); foreach ($reader as $row) { if ($customer != "" && strtolower($row[0]) == strtolower($customer) || $po_box != "" && in_array(strtolower($po_box), array_map("trim", explode("/", strtolower($row[7])))) || $tag != "" && in_array(strtolower($tag), array_map("trim", explode("/", strtolower($row[8]))))) { array_push($results, $row); } } } ?> <body> <div class="container-fluid"> <div class="row"> <div class="col-md-4"> <h1> <span class="text-primary"> Zaf </span> <span class="text-gray"> log </span> </h1> </div>
try { $spreadsheet = new SpreadsheetReader($file_path); $sheets = $spreadsheet->Sheets(); /* object contains all the excel sheet */ $data_type = array(); /* store the data type of the columns */ $size = sizeof($sheets); /* sheet numbers */ $string = ""; /* store sql statements */ $table_name = ""; /* store tables names */ /* for each sheet */ foreach ($sheets as $index => $name) { /* move to another sheet */ $spreadsheet->ChangeSheet($index); /* count to count the rows 0 is first row, 1 is second row ..etc */ $count = 0; $aux = 1; foreach ($spreadsheet as $Key => $row) { // Get the name of the table // first row if ($count == 0) { $string .= "CREATE TABLE " . $row[0] . "("; // first cell $table_name = $row[0]; } else { if ($count == 1) { // store the primary key $primary_key = ""; foreach ($row as $Key => $row1) {
function _importFileParseXLS(&$file, $nb_line_to_avoid) { global $conf; dol_include_once('/importdevis/lib/spreadsheet-reader/php-excel-reader/excel_reader2.php'); dol_include_once('/importdevis/lib/spreadsheet-reader/SpreadsheetReader.php'); $method = 'lineMapper_' . $conf->global->IMPORTPROPAL_FORMAT; $method_after = 'dataParserAfter_' . $conf->global->IMPORTPROPAL_FORMAT; $filename = sys_get_temp_dir() . '/' . $file['name']; copy($file['tmp_name'], $filename); $Reader = new SpreadsheetReader($filename); $Reader->ChangeSheet(0); //var_dump($Reader); $TData = array(); foreach ($Reader as $k => $line) { if ($k < $nb_line_to_avoid) { continue; } if (function_exists($method)) { $line = call_user_func($method, array($line)); } if (!empty($line)) { $TData[] = $line; } } if (function_exists($method_after)) { $TData = call_user_func($method_after, $TData); } return $TData; }
/** * @param string $path * @param string $id * @return null */ private function createCrewDocument($path, $id) { try { $reader = new SpreadsheetReader($path); $sheets = $reader->Sheets(); $parentId = $id; foreach ($sheets as $index => $name) { $page = $index; $reader->ChangeSheet($index); foreach ($reader as $row => $value) { // $childId = ''; if (empty($value[0])) { continue; } if ($value[0] == "Mariner's name") { continue; } if ($value[0] == "John Williams") { continue; } if ($value[0] == "Edward Jones") { continue; } else { // Create XML document $xml_main = new SimpleXMLElement('<add/>'); $xml = $xml_main->addChild('doc'); $id = $xml->addChild('field', $parentId . '_' . $page . '_' . $row); $id->addAttribute('name', 'id'); $id = $xml->addChild('field', 'sailor'); $id->addAttribute('name', 'type'); $parent = $xml->addChild('field', $parentId); $parent->addAttribute('name', 'parent'); $name = $xml->addChild('field', $this->cleanData($value[0])); $name->addAttribute('name', 'name'); $dob = $xml->addChild('field', $this->cleandob($value[1])); $dob->addAttribute('name', 'dob'); $age = $xml->addChild('field', $this->cleanAge($value[2])); $age->addAttribute('name', 'age'); $place_of_birth = $xml->addChild('field', $this->cleanData($value[3])); $place_of_birth->addAttribute('name', 'place_of_birth'); $home_address = $xml->addChild('field', $this->cleanData($value[4])); $home_address->addAttribute('name', 'home_address'); $name_of_ship = $xml->addChild('field', $this->cleanData($value[6])); $name_of_ship->addAttribute('name', 'name_of_ship'); $ship_port = $xml->addChild('field', $this->cleanData($value[7])); $ship_port->addAttribute('name', 'ship_port'); $date_leaving = $xml->addChild('field', $this->cleanData($value[8])); $date_leaving->addAttribute('name', 'date_leaving'); $joined_ship_date = $xml->addChild('field', $this->cleanData($value[9])); $joined_ship_date->addAttribute('name', 'joined_ship_date'); $joined_at_port = $xml->addChild('field', $this->cleanData($value[10])); $joined_at_port->addAttribute('name', 'joined_at_port'); $capacity = $xml->addChild('field', $this->cleanData($value[11])); $capacity->addAttribute('name', 'capacity'); $date_left = $xml->addChild('field', $this->cleanData($value[12])); $date_left->addAttribute('name', 'date_left'); $left_port = $xml->addChild('field', $this->cleanData($value[13])); $left_port->addAttribute('name', 'left_port'); $cause_of_leaving = $xml->addChild('field', $this->cleanData($value[14])); $cause_of_leaving->addAttribute('name', 'cause_of_leaving'); $sign_with_mark = $xml->addChild('field', $this->cleanSignWithMark($value[15])); $sign_with_mark->addAttribute('name', 'sign_with_mark'); $notes = $xml->addChild('field', $this->cleanData($value[16])); $notes->addAttribute('name', 'notes'); // Header('Content-type: text/xml'); $this->sendToSolr($xml_main->asXML()); // print($xml_main->asXML()); } } } } catch (Exception $e) { echo "Could not read file"; } }
if (!$fr) { update_log("Upload data gagal! File tidak bisa di buka", "import", 0); $_SESSION['errmsg'] = "Upload data gagal! File tidak bisa di buka"; ?> <meta http-equiv="refresh" content="0;URL=index.php?p=<?php echo $p; ?> "><?php } else { $c = 1; $date_from_text = "9999-99-99"; $date_until_text = "0000-00-00"; $Sheets = $fr->Sheets(); foreach ($Sheets as $Index => $Name) { $fr->ChangeSheet($Index); foreach ($fr as $Key => $Row) { $exec = false; $tanggal = ""; $batas_masuk = ""; $batas_keluar = ""; $jam_masuk = ""; $jam_keluar = ""; $status = ""; $kekurangan_masuk = ""; $kekurangan_keluar = ""; $kode_alasan_masuk = ""; $kode_alasan_keluar = ""; $keterangan_masuk = ""; $keterangan_keluar = ""; $hadir = "";
function import() { $logged_in = $this->session->userdata('logged_in'); if ($logged_in['su'] != "1") { exit('Permission denied'); return; } if (isset($_FILES['xlsfile'])) { if ($_SERVER['SERVER_ADDR'] == '127.0.0.1') { $targets = "C:/Users/Archit/Documents/GitHub/RapidInnovation/online-test/xls/uploads/"; } else { $targets = "/home/rapidovations/public_html/online-test/xls/uploads/"; } // $targets = 'xls/'; $targets = $targets . basename($_FILES['xlsfile']['name']); $docadd = $_FILES['xlsfile']['name']; if (move_uploaded_file($_FILES['xlsfile']['tmp_name'], $targets)) { $Filepath = $targets; $allxlsdata = array(); date_default_timezone_set('UTC'); $StartMem = memory_get_usage(); //echo '---------------------------------'.PHP_EOL; //echo 'Starting memory: '.$StartMem.PHP_EOL; //echo '---------------------------------'.PHP_EOL; try { $Spreadsheet = new SpreadsheetReader($Filepath); $BaseMem = memory_get_usage(); $Sheets = $Spreadsheet->Sheets(); //echo '---------------------------------'.PHP_EOL; //echo 'Spreadsheets:'.PHP_EOL; //print_r($Sheets); //echo '---------------------------------'.PHP_EOL; //echo '---------------------------------'.PHP_EOL; foreach ($Sheets as $Index => $Name) { //echo '---------------------------------'.PHP_EOL; //echo '*** Sheet '.$Name.' ***'.PHP_EOL; //echo '---------------------------------'.PHP_EOL; $Time = microtime(true); $Spreadsheet->ChangeSheet($Index); foreach ($Spreadsheet as $Key => $Row) { //echo $Key.': '; if ($Row) { $allxlsdata[] = $Row; } else { var_dump($Row); } $CurrentMem = memory_get_usage(); //echo 'Memory: '.($CurrentMem - $BaseMem).' current, '.$CurrentMem.' base'.PHP_EOL; //echo '---------------------------------'.PHP_EOL; if ($Key && $Key % 500 == 0) { //echo '---------------------------------'.PHP_EOL; //echo 'Time: '.(microtime(true) - $Time); //echo '---------------------------------'.PHP_EOL; } } // echo PHP_EOL.'---------------------------------'.PHP_EOL; //echo 'Time: '.(microtime(true) - $Time); //echo PHP_EOL; //echo '---------------------------------'.PHP_EOL; //echo '*** End of sheet '.$Name.' ***'.PHP_EOL; //echo '---------------------------------'.PHP_EOL; } } catch (Exception $E) { echo $E->getMessage(); } $this->qbank_model->import_question($allxlsdata); } else { echo "Not able to upload file"; } } else { echo "Error: " . $_FILES["file"]["error"]; } redirect('qbank'); }