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; }
*/ header('Content-Type: text/plain'); $Filepath = "sample.xls"; // Excel reader from http://code.google.com/p/php-excel-reader/ require 'php-excel-reader/excel_reader2.php'; require 'SpreadsheetReader.php'; $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) { print_r($Row);
if (file_exists($xls_dir_name . $xls_file_name)) { /* to get a unique value */ $unique_id = time(); /* rename file: unique value - file name */ $xls_file_name = $unique_id . "-" . $xls_file_name; } else { if ($_FILES['file']['name'] == "") { $xls_file_name = ''; } } // upload the xsl file to this path xls/ move_uploaded_file($_FILES['file']['tmp_name'], $xls_dir_name . $xls_file_name); $file_path = $xls_dir_name . $xls_file_name; 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;
/** * @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"; } }
} $fr = new SpreadsheetReader($filedir); 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 = "";
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'); }