public function main($UrlAppend = NULL, $get = NULL, $post = NULL) { if ($this->_isPost()) { $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $file = $this->_upload(); $data->read($file["path"]); $keyArr = array(); for ($a = 1; $a < 1000; $a++) { if ($data->sheets[0]['cells'][1][$a] != "") { array_push($keyArr, $data->sheets[0]['cells'][1][$a]); } } $dataList = array(); for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) { $valueArr = array(); for ($b = 0; $b < count($keyArr); $b++) { if ($keyArr[$b] == "key") { $key = $data->sheets[0]['cells'][$i][$b + 1]; } else { $valueArr[$keyArr[$b]] = $data->sheets[0]['cells'][$i][$b + 1]; } } $dataList[$key] = $valueArr; } $this->_f($_POST["FileName"], $dataList); } return $this->_assign; }
function getAll() { $reader = new Spreadsheet_Excel_Reader(); $reader->setUTFEncoder('iconv'); $reader->setOutputEncoding('UTF-8'); $reader->read($this->filename); $data = array(); $index_row = 1; if ($this->have_header) { $index_row = 2; } $iterator = -1; for ($i = $index_row; $i <= $reader->sheets[$this->sheet_index]['numRows']; $i++) { $iterator++; $data[$iterator] = array(); for ($j = 1; $j <= $reader->sheets[$this->sheet_index]['numCols']; $j++) { if ($this->have_header) { $data[$iterator][$this->headers[$j - 1]] = $reader->sheets[0]['cells'][$i][$j]; } else { $data[$iterator][$j - 1] = $reader->sheets[0]['cells'][$i][$j]; } } } return $data; }
public function importsiswa() { require_once 'application/controllers/base/excel_reader2.php'; echo '<html><head><title>Loading...</title></head><body></body></html>'; $xls = $_FILES['data-import']; if (!empty($_FILES['data-import']['name'])) { $this->load->library('upload'); //load upload lobrary $xls_name = str_replace(' ', '_', $xls['name']); $config['upload_path'] = './assets/xls/'; $config['allowed_types'] = '*'; $config['max_size'] = '2000000'; //2MB $config['overwrite'] = true; $this->upload->initialize($config); if (!$this->upload->do_upload('data-import')) { //Upload Failed echo $this->upload->display_errors(); } else { //Upload Complete $siswa = new Spreadsheet_Excel_Reader('./assets/xls/' . $xls_name); $rows = $siswa->rowcount(0); // echo $rows; //looping to insert database for ($i = 2; $i < $rows; $i++) { $nis = $siswa->val($i, 1, 0); //baris ke $i, kolom 1, sheet 1 $angkatan = $siswa->val($i, 2, 0); $nama = $siswa->val($i, 3, 0); $subkelas1 = $siswa->val($i, 4, 0); $subkelas2 = $siswa->val($i, 5, 0); $subkelas3 = $siswa->val($i, 6, 0); if (empty($subkelas1)) { $subkelas1 = null; } if (empty($subkelas2)) { $subkelas2 = null; } if (empty($subkelas3)) { $subkelas3 = null; } $status = $siswa->val($i, 7, 0); $kelamin = $siswa->val($i, 8, 0); $password = md5($nis); $data = array('nis' => $nis, 'angkatan' => $angkatan, 'nama_lengkap' => $nama, 'subkelas1' => $subkelas1, 'subkelas2' => $subkelas2, 'subkelas3' => $subkelas3, 'status' => $status, 'password' => $password, 'kelamin' => $kelamin); // echo '<br/>'; // print_r($data); //insert to table $this->db->insert('siswa', $data); } echo 'Berhasil Import Data Siswa <a href="' . site_url("admin/siswa") . '">kembali</a>'; } } else { echo 'XLS Not Found!'; } }
public function excelreader() { $this->load->library('Spreadsheet_Excel_Reader.php'); $data = new Spreadsheet_Excel_Reader("./data/example01.xls"); // $data = json_decode('{$data}',true); echo $data->dump(true, true); }
function upload_bulk_courses() { $data['user_id'] = $this->tank_auth->get_admin_user_id(); $config['upload_path'] = $this->excel_path; $config['allowed_types'] = 'xls'; //$config['max_size'] = '10000'; $this->load->library('upload', $config); if (!$this->upload->do_upload()) { $data['err_msg'] = $this->upload->display_errors(); $this->load->view('admin/show_error', $data); } else { $this->load->library('Spreadsheet_Excel_Reader'); $path = $this->excel_path . '/' . $_FILES['userfile']['name']; $data1 = new Spreadsheet_Excel_Reader(); $data1->read($path); error_reporting(E_ALL ^ E_NOTICE); for ($j = 1; $j < $data1->sheets[0]['numRows']; $j++) { $prog_data = array(); for ($i = 1; $i <= $data1->sheets[0]['numCols']; $i++) { $res = $data1->sheets[0]['cells'][$j + 1][$i]; //for finding program education level id by its name if ($i == 2) { $res = $this->courses->educ_lvl_id_by_name($data1->sheets[0]['cells'][$j + 1][$i]); } if ($i == 4) { $res = $this->courses->area_of_intrst_id_by_name($data1->sheets[0]['cells'][$j + 1][$i]); } $prog_data[] = $res; } $pro = array('prog_title' => $prog_data[0], 'educ_level_id' => $prog_data[1], 'course_name' => $prog_data[2], 'prog_parent_id' => $prog_data[3], 'createdby' => $data['user_id']); /*$pro=array('prog_title' =>$prog_data[0], 'educ_level_id'=>$prog_data[1], 'course_name'=>$prog_data[2], 'prog_parent_id'=>$prog_data[3], 'intake1'=>$prog_data[4], 'intake2'=>$prog_data[5], 'program_duration1'=>$prog_data[6], 'tution_fee1'=>$prog_data[7], 'program_duration2'=>$prog_data[8], 'tution_fee2'=>$prog_data[9], 'experience_required'=>$prog_data[10], 'gpa_required'=>$prog_data[11], 'per_required'=>$prog_data[12], 'program_detail'=>$prog_data[13], 'createdby'=>$data['user_id'] ); */ $this->db->insert('program', $pro); redirect('admincourses/manage_courses/bcus'); } //echo $data->sheets[0]['numRows']; } }
function updateDB($startIndex, $endIndex) { //need to copy the code that does the excel reading $analysisData = new Spreadsheet_Excel_Reader(); // Set output Encoding. $analysisData->setOutputEncoding('CP1251'); $inputFileName = 'ReverseGeoCodingForStopsVerification.xls'; $analysisData->read($inputFileName); error_reporting(E_ALL ^ E_NOTICE); $numRows = $analysisData->sheets[0]['numRows']; $numCols = $analysisData->sheets[0]['numCols']; //echo $numRows.",".$numCols; $strRoute = '<Routes>'; for ($i = $startIndex; $i <= $endIndex; $i++) { $stopId = $analysisData->sheets[0]['cells'][$i][1]; $StopName = $analysisData->sheets[0]['cells'][$i][2]; $Buses = $analysisData->sheets[0]['cells'][$i][3]; $latitude = $analysisData->sheets[0]['cells'][$i][4]; $longitude = $analysisData->sheets[0]['cells'][$i][5]; $strRoute = $strRoute . '<Route>'; $routeDetails = htmlentities(trim($StopName)) . ":" . $latitude . ":" . $longitude; //echo $routeDetails."<br/>"; $strRoute = $strRoute . '<RouteDetails>' . $routeDetails . '</RouteDetails>'; $strRoute = $strRoute . '</Route>'; } $strRoute = $strRoute . '</Routes>'; echo $strRoute; }
/** Helper for xls files. See fileToArray() */ public static function xlsToArray($filename, $limit) { if (!class_exists('Spreadsheet_Excel_Reader')) { include_once dirname(__FILE__) . '/../../src/Excel/xls_read/reader.php'; } $data = new \Spreadsheet_Excel_Reader(); $data->setOutputEncoding('ISO-8859-1'); $data->read($filename); $sheet = $data->sheets[0]; $rows = $sheet['numRows']; $cols = $sheet['numCols']; $ret = array(); for ($i = 1; $i <= $rows; $i++) { $line = array(); for ($j = 1; $j <= $cols; $j++) { if (isset($sheet['cells'][$i]) && isset($sheet['cells'][$i][$j])) { $line[] = $sheet['cells'][$i][$j]; } else { $line[] = ''; } } $ret[] = $line; if ($limit != 0 && count($ret) >= $limit) { break; } } return $ret; }
public function import($file) { try { $users = array(); $params = array(1 => 'user_name', 'user_password', 'user_first_name', 'user_last_name', 'user_email', 'user_group', 'user_vhost'); $excel = new Spreadsheet_Excel_Reader($file); $rows = $excel->rowcount($sheet_index = 0); $cols = $excel->colcount($sheet_index = 0); for ($row = 2; $row <= $rows; $row++) { if ($cols == 7) { for ($col = 1; $col <= $cols; $col++) { $users[$row][$params[$col]] = $excel->val($row, $col); $users[$row]['user_vhost'] = explode(',', $excel->val($row, 7)); $users[$row]['user_group'] = ''; } } } $this->userimport = new userimport(); $users = $this->userimport->import($users); $_SESSION['message'] = $this->userimport->get_message(); return $users; } catch (Exception $e) { display_page_error(); } }
/** * Importa la nomina de estudiantes desde una hoja excel */ public function import($archivo) { include_once 'system/application/libraries/excel_reader2.php'; $excel = new Spreadsheet_Excel_Reader("system/excel/alumnos/{$archivo}"); $i = 2; $this->db->trans_start(); while (intval($excel->val($i, 1))) { $codigo = intval($excel->val($i, 1)); $arr = array(); foreach ($this->columnas as $col => $pos) { $arr[$col] = $excel->val($i, $pos); } $arr['sexo'] = intval($arr['sexo']) == 1 ? 'M' : 'F'; if ($alumno = $this->alumnoExiste($codigo)) { // Actualizar $arr['id'] = $alumno['id']; $this->update($arr); } else { // Crear $this->create($arr); } $i++; } $this->db->trans_complete(); }
function do_upload() { $config['upload_path'] = './upload/'; $config['allowed_types'] = 'php|txt|xls|xml'; $config['max_size'] = '1000000'; //$config['max_width'] = '1024'; //$config['max_height'] = '768'; $this->load->library('upload', $config); if (!$this->upload->do_upload()) { $error = array('error' => $this->upload->display_errors()); //$this->load->view('header'); $this->load->view('upload_view', $error); //$this->load->view('footer'); } else { $data = array('upload_data' => $this->upload->data()); $this->load->library('Excel/spreadsheet_Excel_Reader'); //require_once 'Excel/reader.php'; // ExcelFile($filename, $encoding); $excel = new Spreadsheet_Excel_Reader(); // Set output Encoding. $excel->setOutputEncoding('CP1251'); //lecture du fichier excel //chmod('./upload/' . $data['upload_data']['file_name'], 0777); $excel->read('./upload/' . $data['upload_data']['file_name']); //$liste = array($excel->sheets[0]['numRows'],2); $array = $excel->sheets[0]['cells']; $temp = array_shift($array); var_dump($array); //$this->data_model->insert_entry($array); $this->load->view('upload_success', $data); } }
function ABSSAV() { $inFile = $_REQUEST['inFile']; echo "Filename is: {$inFile}<br>"; require_once 'Excel/reader.php'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('CP1251'); $data->read($inFile); error_reporting(E_ALL ^ E_NOTICE); $Target_Database = 'binawan'; $Target_Table = $Target_Database . '.ruang'; $Target_KodeID = "BINAWAN"; $s = "TRUNCATE TABLE {$Target_Table}"; $r = _query($s); for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) { $w = array(); $w['RuangID'] = trim($data->sheets[0]['cells'][$i][2]); $w['Nama'] = trim($data->sheets[0]['cells'][$i][3]); $w['Kapasitas'] = trim($data->sheets[0]['cells'][$i][4]); $w['KapasitasUjian'] = trim($data->sheets[0]['cells'][$i][4]); $w['KolomUjian'] = trim($data->sheets[0]['cells'][$i][5]); $w['KampusID'] = trim($data->sheets[0]['cells'][$i][6]); $w['Lantai'] = trim($data->sheets[0]['cells'][$i][7]); $w['RuangKuliah'] = trim($data->sheets[0]['cells'][$i][8]); $s = "insert into {$Target_Table}\r\n (RuangID, Nama, Kapasitas, KapasitasUjian, KolomUjian, KampusID, Lantai, KodeID, RuangKuliah, UntukUSM\r\n\t )\r\n values\r\n ('{$w['RuangID']}', '{$w['Nama']}', '{$w['Kapasitas']}', '{$w['KapasitasUjian']}', '{$w['KolomUjian']}', '{$w['KampusID']}', '{$w['Lantai']}', 'BINAWAN', '{$w['RuangKuliah']}', '{$w['RuangKuliah']}'\r\n\t )"; $r = _query($s); } echo "<script>window.location = '?{$mnux}={$_SESSION['mnux']}'</script>"; }
function readRoom($file, $table, $pos) { require_once "conn.php"; $sql = "TRUNCATE TABLE {$table}"; if ($mysqliObj->query($sql)) { echo "truncate success" . "</br>"; } require_once 'Excel/reader.php'; // ExcelFile($filename, $encoding); $data = new Spreadsheet_Excel_Reader(); // Set output Encoding. // $data->setOutputEncoding('CP936'); $data->setOutputEncoding('gbk'); $data->read($file); error_reporting(E_ALL ^ E_NOTICE); //$data->sheets[0]['numRows'] for ($i = $pos; $i <= $data->sheets[0]['numRows']; $i++) { $msg1 = $data->sheets[0]['cells'][$i][1]; $msg2 = $data->sheets[0]['cells'][$i][2]; $msg3 = $data->sheets[0]['cells'][$i][3]; $sql = "INSERT INTO {$table} (`roomId` ,`type` ,`price` )\n\t\t\t\tVALUES ('{$msg1}', '{$msg2}', '{$msg3}')"; if (!is_null($data->sheets[0]['cells'][$i][1])) { $mysqliObj->query($sql); } } $mysqliObj->close(); }
function ABSSAV() { $inFile = $_REQUEST['inFile']; require_once 'Excel/reader.php'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('CP1251'); $data->read($inFile); error_reporting(E_ALL ^ E_NOTICE); $Target_Database = 'binawan'; $Target_Table = $Target_Database . 'mhsw'; $Target_KodeID = "BINAWAN"; $s = "TRUNCATE TABLE "; $r = _query($s); for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) { $w = array(); $w['MhswID'] = trim($data->sheets[0]['cells'][$i][1]); $w['TahunID'] = trim($data->sheets[0]['cells'][$i][2]); $w['Nama'] = trim($data->sheets[0]['cells'][$i][3]); $w['Kelamin'] = trim($data->sheets[0]['cells'][$i][4]); $w['TempatLahir'] = trim($data->sheets[0]['cells'][$i][5]); $w['TanggalLahir'] = trim($data->sheets[0]['cells'][$i][6]); $w['Agama'] = trim($data->sheets[0]['cells'][$i][7]); $w['Alamat'] = trim($data->sheets[0]['cells'][$i][8]); $s = "insert into {$Target_Database}\r\n (MhswID, Login, LevelID, Password, PMBID, \r\n\t\t TahunID, KodeID, Nama, \r\n\t\t Foto, StatusMhswID, ProgramID, ProdiID, KelasID,\r\n\t\t Kelamin, TempatLahir, TanggalLahir, Agama, Alamat, \r\n\t\t Kota, RT, RW, KodePos, Propinsi, Negara, \r\n\t\t Telepon, Handphone \r\n\t )\r\n values\r\n ('{$w['MhswID']}', '{$w['Login']}', '120', '{$w['Password']}', '{$w['PMBID']}', \r\n\t\t '{$w['TahunID']}', '{$Target_KodeID}', '{$w['Nama']}', \r\n\t\t '{$w['Foto']}', '{$w['StatusMhswID']}', '{$w['ProgramID']}', '{$w['ProdiID']}', '{$w['KelasID']}',\r\n\t\t '{$w['Kelamin']}', '{$w['TempatLahir']}', '{$w['TanggalLahir']}', '{$w['Agama']}', '{$w['Alamat']}', \r\n\t\t '{$w['Kota']}', '{$w['RT']}', '{$w['RW']}', '{$w['KodePos']}', '{$w['Propinsi']}', '{$w['Negara']}', \r\n\t\t '{$w['Telepon']}', '{$w['Handphone']}' \r\n\t )"; $r = _query($s); } echo "<script>window.location = '?{$mnux}={$_SESSION['mnux']}'</script>"; }
function getDataFromExcel($filename) { $uimap = new Spreadsheet_Excel_Reader(); $uimap->read($this->path . "\\data\\uimap\\" . $filename . ".xls"); $s = 0; foreach ($uimap->boundsheets as $ar) { $i = 2; while ($i <= $uimap->sheets[$s]['numRows']) { if (!empty($uimap->sheets[$s]['cells'][$i][2]) && !empty($uimap->sheets[$s]['cells'][$i][3]) && strtolower($ar['name']) != 'datamap') { $this->sheets[strtolower($ar['name'])][strtolower($uimap->sheets[$s]['cells'][$i][2])] = $uimap->sheets[$s]['cells'][$i][3]; } elseif (!empty($uimap->sheets[$s]['cells'][$i][2]) && strtolower($ar['name']) == 'datamap') { $datafile = new Spreadsheet_Excel_Reader(); $datafile->read($this->path . "\\" . $uimap->sheets[$s]['cells'][$i][3]); $j = 2; while ($j <= $datafile->sheets[0]['numRows']) { if (!empty($datafile->sheets[0]['cells'][$j][2])) { if (empty($datafile->sheets[0]['cells'][$j][4])) { $this->datamap[strtolower($datafile->sheets[0]['cells'][$j][2])] = $datafile->sheets[0]['cells'][$j][3]; } else { $y = 3; while (!empty($datafile->sheets[0]['cells'][$j][$y])) { $this->datamap[strtolower($datafile->sheets[0]['cells'][$j][2])][] = $datafile->sheets[0]['cells'][$j][$y]; $y++; } } } $j++; } } $i++; } $s++; } $this->uimap = $this->sheets; }
public function add_in_mysql() { if (!empty($_FILES['efile']['name'])) { $exname = strtolower(substr($_FILES['efile']['name'], strrpos($_FILES['efile']['name'], '.') + 1)); $uploadfile = $this->getnames($exname); //上传后文件所在的路径 @move_uploaded_file($_FILES['efile']['tmp_name'], $uploadfile); } $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $data->read($uploadfile); //读取要导入数据库的文件 error_reporting(E_ALL ^ E_NOTICE); $Stu = D("Stu"); //$User->startTrans();//启动事务; $filed = C("excle"); //获取字段对应的列 for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) { $col = $data->sheets[0]['cells'][$i]; $arr = array("stuno" => $col[$filed["stuno"]], "stuname" => $col[$filed["stuname"]], "password" => $col[$filed["password"]], "sex" => $col[$filed["sex"]], "idcard" => $col[$filed["idcard"]], "classno" => $col[$filed["classno"]], "college" => "1"); $Stu->create($arr); echo $Stu->getError() . "<br/>"; if (!$Stu->add()) { $Stu->rollback(); //不成功,回滚 echo "导入失败回滚"; return; } echo $arr["stuno"] . "导入成功<br/>"; } $Stu->commit(); //成功提交 }
/** * Realiza la inserción o actualización de notas de un estudiante * @param string * @param integer */ function insertUpdateNotas($archivo, $anio) { include_once 'system/application/libraries/excel_reader2.php'; $excel = new Spreadsheet_Excel_Reader("system/excel/notas/{$archivo}"); $alumnos = $this->loadModel('Alumno_model')->getList(array('labelField' => 'id', 'valueField' => 'codigo')); $materias = $this->loadModel('Materia_model')->getList(array('labelField' => 'id', 'valueField' => 'nombre')); $i = 2; $this->errors = array(); $this->db->trans_start(); while (trim($excel->val($i, 1)) != '') { $codigo = trim($excel->val($i, 1)); if (!isset($alumnos[$codigo])) { array_push($this->errors, "No existe un alumno con código \"{$codigo}\" fila {$i} del archivo excel"); $i++; continue; } $alumno_id = $alumnos[$codigo]; $materia_nombre = trim($excel->val($i, 2)); if (!isset($materias[$materia_nombre])) { array_push($this->errors, "No existe la materia \"{$materia_nombre}\" en la fila {$i} del archivo excel"); $i++; continue; } $materia_id = $materias[$materia_nombre]; $this->setNota($alumno_id, $materia_id, $excel, $anio, $i); $i++; } $this->db->trans_complete(); return $this->errors; }
public function localUploadSNExcel() { $return = $this->localUpload(array('xls')); if ($return['error']) { $this->error($return['msg']); } else { $data = new Spreadsheet_Excel_Reader(); // 设置输入编码 UTF-8/GB2312/CP936等等 $data->setOutputEncoding('UTF-8'); $data->read(str_replace('http://' . $_SERVER['HTTP_HOST'], $_SERVER['DOCUMENT_ROOT'], $return['msg'])); chmod(str_replace('http://' . $_SERVER['HTTP_HOST'], $_SERVER['DOCUMENT_ROOT'], $return['msg']), 0777); // $sheet = $data->sheets[0]; $rows = $sheet['cells']; if ($rows) { $i = 0; foreach ($rows as $r) { if ($i != 0) { $db = M('Lottery_record'); $where = array('token' => $this->token, 'lid' => intval($_POST['lid']), 'sn' => trim($r[1])); $check = $db->where($where)->find(); if (!$check) { $where['prize'] = intval($r['2']); $db->add($where); } } $i++; } } $this->success('操作完成'); } }
function doimport() { $file_name = $_GET['province_id'] . ".xls"; if ($file_name != '') { //$ext = pathinfo($_FILES['filename']['name'], PATHINFO_EXTENSION); //echo $file_name = 'nursery.'.$ext; $uploaddir = 'import/tmp/'; $fpicname = $uploaddir . $file_name; ///move_uploaded_file($_FILES['filename']['tmp_name'], $fpicname); require_once 'include/Excel/reader.php'; $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('UTF-8'); $data->read($fpicname); error_reporting(E_ALL ^ E_NOTICE); $index = 0; for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) { $amphur = new Amphur(); $amphur = $amphur->where(" amphur_name ='" . trim($data->sheets[0]['cells'][$i][1]) . "'")->get(); $import[$index]['province_id'] = $_POST['province_id']; $import[$index]['amphur_id'] = $amphur->id; $import[$index]['name'] = trim($data->sheets[0]['cells'][$i][2]); $index++; if ($amphur->id > 0) { $_POST['province_id'] = $_GET['province_id']; $_POST['amphur_id'] = $amphur->id; $_POST['name'] = trim($data->sheets[0]['cells'][$i][2]); $nursery = new Nursery_Tmp(); $nursery->from_array($_POST); $nursery->save(); } } } }
function popola($file, $id_associazione) { $data = new Spreadsheet_Excel_Reader(); //$data->setOutputEncoding('CP1251'); //UTF-8 $data->setOutputEncoding('UTF-8'); $data->read($file); error_reporting(E_ALL ^ E_NOTICE); $counter = 0; $counterP = 0; $log_error = new log(); for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) { $counterP++; echo $counterP . " "; if ($counterP % 100 == 0) { echo "\n Processati: " . $counterP . "\n"; } ob_flush(); flush(); $query = "INSERT into soci (nome,mail) values "; $query .= "("; echo $data->sheets[0]['cells'][$i][1] . " ... \n"; $actual_element = trim($data->sheets[0]['cells'][$i][1], " \t\n\r\v,;"); echo $actual_element; $actual_element = mysql_real_escape_string($actual_element); $actual_element2 = trim($data->sheets[0]['cells'][$i][2], " \t\n\r\v,;"); $mail = mysql_real_escape_string($actual_element2); $query .= "'" . $actual_element . "','" . $mail . "'"; // Aggiungi user e password //$query.="'".(($email=="") ? getUniqueCode(10) : $email)."','".getUniqueCode(10)."',$id_associazione"; $query .= ")"; //validate email if (!Swift_Validate::email($actual_element2)) { //validate email, log error $log_error->scrivi_popola($actual_element . "\t" . $mail); } else { //insert into DB $result = mysql_query("SELECT id FROM soci WHERE mail='" . $mail . "'"); if ($riga = mysql_fetch_assoc($result)) { //if the mail is already in the database $int_id = $riga['id']; } else { if (!mysql_query($query)) { //insert new record echo "ti è andata male "; echo $query; return false; } $int_id = mysql_insert_id(); $counter++; } if ($id_associazione > 0) { mysql_query("INSERT into interessi_soci (id_socio,id_interesse) values ('{$int_id}','{$id_associazione}')"); } } } $log_error->close_popola(); //return $data->sheets[0]['numRows']-1; return "<BR>Letti " . $counterP . " e inseriti (non duplicati) " . $counter; }
function get_student_schedule($filename, $teacherSchedule) { $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('UTF8'); $data->setUTFEncoder('mb'); $data->read($filename); return get_result_student($data, $teacherSchedule); }
public function integrationExcel() { $controller = $this->config(); echo "<br />Excel Spreadsheet<br/>"; echo $spreadsheet = $controller->buildExcelTable(1, 1, 1, 1, 1, 1, array(1989, 1992)); $data = new Spreadsheet_Excel_Reader($spreadsheet); echo $data->dump(true, true); }
/** * Handle and read uploaded file * @param array - fileArray from form * @param array - postArray from form */ function handleUpload($_FILES, $_POST) { require_once "JotForm.php"; $path = mktime() . '_' . $_FILES['file']['name']; $key = $_POST['APIkey']; $form = $_POST['formID']; $jot = new JotForm($key); $error = ""; if (move_uploaded_file($_FILES['file']['tmp_name'], $path)) { $fileType = getFileType($path); $columns = array(); if ($fileType == 'csv') { if (($handle = fopen($path, "r")) !== FALSE) { if (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { foreach ($data as $title) { array_push($columns, $title); } } $error = 'File must contain at least two rows - the first represents the field titles'; while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $error = ''; $result = $jot->createFormSubmissions($form, writeData($data, $columns)); } fclose($handle); } else { $error = 'Could not open file'; } } else { require_once 'Excel/reader.php'; $excel = new Spreadsheet_Excel_Reader(); $excel->read($path); if ($excel->sheets[0]['numRows'] > 1) { for ($i = 1; $i <= $excel->sheets[0]['numCols']; $i++) { $title = $excel->sheets[0]['cells'][1][$i]; array_push($columns, $title); } for ($i = 2; $i <= $excel->sheets[0]['numRows']; $i++) { $data = array(); for ($j = 1; $j <= $excel->sheets[0]['numCols']; $j++) { array_push($data, $excel->sheets[0]['cells'][$i][$j]); } $jot->createFormSubmissions($form, writeData($data, $columns)); } } else { $error = 'File must contain at least two rows - the first represents the field titles'; } } } else { $error = 'No File Found'; } if (strlen($error) > 0) { return $error; } else { return 'none'; } }
function readxlsmain($file) { $d = new Spreadsheet_Excel_Reader(); $d->setOutputEncoding('utf-8'); $d->read($file); $data = array(); $data['boundsheets'] =& $d->boundsheets; $data['sheets'] =& $d->sheets; return $data; }
public function insertDataIntoRateChart() { if (empty($this->request->data['file_path']) || empty($this->request->data['broker']) || empty($this->request->data['gas_product'])) { return new CakeResponse(array('body' => json_encode(0))); } $data = new Spreadsheet_Excel_Reader("plupload/" . $this->request->data['file_path'], true); $temp = $data->dumptoarray(); $rowCount = count($temp); $head = $temp[1]; $finalData = array(); for ($i = 2; $i <= $rowCount; $i++) { $row = $temp[$i]; $data = array(); for ($j = 1; $j <= count($head); $j++) { $data[$head[$j]] = $row[$j]; } $finalData[] = $data; } $results = array(); foreach ($finalData as $r) { $r['broker_info_id'] = $this->request->data['broker']; $r['price_code'] = $r['PRICE CODE']; $consumption_range = $r['Consumption']; $c_array = explode("-", $consumption_range); $start_range = $this->tofloat($c_array[0]); $r['consumption_range_start'] = $start_range; if (isset($c_array[1])) { $end_range = $this->tofloat($c_array[1]); $r['consumption_range_end'] = $end_range; } else { $r['consumption_range_end'] = 10000000; } $r['region_code'] = $r['PES Region']; $r['standing_charge_per_year'] = $r['STANDING CHARGE p/year']; $r['standing_charge_per_day'] = $r['STANDING CHARGE p/day']; $r['unit_rate_per_kwh'] = $r['Unit Rate p/kwh']; $r['quote_gas_product_id'] = $this->request->data['gas_product']; $results[] = $r; } $flag = 0; $rateCharts = $this->QuoteGasRateChart->find('all', array('conditions' => array('quote_gas_product_id' => $this->request->data['gas_product'], 'broker_info_id' => $this->request->data['broker']))); foreach ($rateCharts as $chart) { $this->QuoteGasRateChart->id = $chart['QuoteGasRateChart']['id']; $this->QuoteGasRateChart->delete(); } if ($this->QuoteGasRateChart->saveAll($results)) { $flag = 1; $this->Session->setFlash('Uploaded Successfully', 'flash_success'); } else { $this->Session->setFlash('Error Occurred', 'flash_error'); } return new CakeResponse(array('body' => json_encode($flag))); }
function getMat($fileName) { error_reporting(E_ALL ^ E_NOTICE); require_once "excel_reader2.php"; try { $data = new Spreadsheet_Excel_Reader($fileName, false); $mat = $data->val(5, "D", 1); return $mat; } catch (Exception $e) { return "Errore in lettura"; } }
function process_file($filename, &$tempname) { global $_SGLOBAL; $isfile = 1; $data = new Spreadsheet_Excel_Reader(); //exit('adsf'); $data->setOutputEncoding('gbk'); $data->read($filename); $tempname = date("Y-m-d") . '_download_' . rand() . '.xls'; $filedownload = S_ROOT . './plugin/invite/download/' . $tempname; //初始化一个反馈结果的文件 require_once S_ROOT . './plugin/invite/phpwritexcel/Writer.php'; $workbook = new Spreadsheet_Excel_Writer($filedownload); $worksheet =& $workbook->addWorksheet('Sheet1'); $worksheet->setInputEncoding('utf-8'); $format_column =& $workbook->addformat(array('Size' => 9, 'Bold' => 1)); //读取结果文件的信息 $rows = 0; $recordindex = array('realname', 'sex', 'birthday', 'otheremail', 'academy', 'collegeid', 'class', 'mobile', 'unit'); $record = array('好友姓名(必填)', '好友性别', '生日(8位)', '邮箱(必填)', '学院', '学号', '班别', '手机(必填)', '所在单位', '备注'); $dataindexinfo = array(); encode_record(&$record); input_downloadfile($worksheet, $rows, $record, $format_column); for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) { //以下注释的for循环打印excel表数据 $datainfo = array(); //$datainfo = array('name', 'sex', 'birthday', 'otheremail', 'academy', 'collegeid', 'class', 'mobile', 'unit'); for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { $datainfo[$j - 1] = $data->sheets[0]['cells'][$i][$j]; $dataindexinfo[$recordindex[$j - 1]] = $data->sheets[0]['cells'][$i][$j]; } //var_dump($datainfo[0]); //检查记录的正确性 $nopass = checkinfo($datainfo[0], $datainfo[2], $datainfo[3], $datainfo[7], $isfile); if (!empty($nopass)) { //没有通过的记录加入到一个新文件中 $datainfo[$j - 1] = $nopass; encode_record(&$datainfo); //var_dump($datainfo); //var_dump($rows); input_downloadfile($worksheet, &$rows, $datainfo, $format_column); continue; } //邀请处理 //var_dump($dataindexinfo); inserttable('baseprofile', $dataindexinfo, 1); //var_dump($datainfo); invite_alumni($datainfo); } $workbook->close(); return $rows; }
/** * Method untuk membaca data pada file excel * * @param string $path_file path file excel * @param integer $baris_mulai_data * @return array */ function data_excel($path_file, $baris_mulai_data = 2) { include 'excel_reader2.php'; $file_excel = new Spreadsheet_Excel_Reader($path_file); # membaca jumlah baris dari data excel $baris = $file_excel->rowcount($sheet_index = 0); $kolom = $file_excel->colcount($sheet_index = 0); $data_return = array(); for ($i = $baris_mulai_data; $i <= $baris; $i++) { $row_data = array(); for ($k = 1; $k <= $kolom; $k++) { $row_data[] = $file_excel->val($i, $k); } $data_return[] = $row_data; } return $data_return; }
public static function leerMarcadoresExcel($fichero) { $leidos = array(); $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('CP1251'); $data->read($fichero); //Se comienza de indice 2 para un excel donde la primera row sea los titulos de las columnas for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) { $objeto = new Marcador(); $objeto->lat = $data->sheets[0]['cells'][$i][1]; $objeto->lng = $data->sheets[0]['cells'][$i][2]; $objeto->nombre = $data->sheets[0]['cells'][$i][3]; $objeto->direccion = $data->sheets[0]['cells'][$i][4]; $objeto->codPostal = $data->sheets[0]['cells'][$i][5]; array_push($leidos, $objeto); } return $leidos; }
public static function readXLS($file) { $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('UTF-8'); //设置输出的编码为utf8 $ret = $data->read($file); //要读取的excel文件地址 if ($ret == -1) { $array = false; } else { for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) { for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) { $array[$i - 1][$j - 1] = $data->sheets[0]['cells'][$i][$j]; } } } return $array; }
public function _batchDeal() { $data = new Spreadsheet_Excel_Reader(); $data->setOutputEncoding('utf-8'); $file = $this->_upload(); $data->read($file["path"]); for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) { $sendData = array('WorldID' => intval($data->sheets[0]['cells'][$i][1]), 'IsAllPlayer' => intval(0), 'PlayerID' => trim($data->sheets[0]['cells'][$i][2]), 'Title' => trim($data->sheets[0]['cells'][$i][3]), 'Content' => trim($data->sheets[0]['cells'][$i][4]), 'IsPack' => intval($data->sheets[0]['cells'][$i][5]), 'ItemID' => intval($data->sheets[0]['cells'][$i][6]), 'ItemNum' => intval($data->sheets[0]['cells'][$i][7]), 'IsBinded' => intval($data->sheets[0]['cells'][$i][8]), 'TimeDurableType' => intval($data->sheets[0]['cells'][$i][9]), 'TimeDurable' => intval(strtotime($data->sheets[0]['cells'][$i][10])), 'ImproveLevel' => intval($data->sheets[0]['cells'][$i][11]), 'AttachAttribute' => trim($data->sheets[0]['cells'][$i][12]), 'Money' => trim($data->sheets[0]['cells'][$i][13]), 'Exp' => intval($data->sheets[0]['cells'][$i][14])); $returnData .= "[" . $i . "]" . $this->addApply($sendData) . "<br/>"; } $URL_CsIndex = Tools::url('Apply', 'CsIndex'); $URL_CsAll = Tools::url('Apply', 'CsAll'); $showMsg = $returnData; $showMsg .= "<a href='{$URL_CsIndex}'>客服审核列表</a><br>"; $showMsg .= "<a href='{$URL_CsAll}'>客服审核列表(全部)</a>"; $this->jump($showMsg, 1, 1, false); die($returnData); }