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);
}
