PHP Spreadsheet_Excel_Reader::val方法代码示例

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

 /**
  * 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;
 }

 /**
  * 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 proses()
 {
     require 'excel_reader.php';
     if (isset($_POST['submit'])) {
         $target = basename($_FILES['filepegawaiall']['name']);
         move_uploaded_file($_FILES['filepegawaiall']['tmp_name'], $target);
         $data = new Spreadsheet_Excel_Reader($_FILES['filepegawaiall']['name'], false);
         //    menghitung jumlah baris file xls
         $baris = $data->rowcount($sheet_index = 0);
         //    jika kosongkan data dicentang jalankan kode berikut
         if ($_POST['drop'] == 1) {
             //             kosongkan tabel pegawai
             $truncate = "TRUNCATE TABLE pegawai";
             $this->db->query($truncate);
         }
         //    import data excel mulai baris ke-2 (karena tabel xls ada header pada baris 1)
         for ($i = 2; $i <= $baris; $i++) {
             //       membaca data (kolom ke-1 sd terakhir)
             $nama = $data->val($i, 1);
             $tempat_lahir = $data->val($i, 2);
             $tanggal_lahir = $data->val($i, 3);
             echo $nama;
             echo " ";
             echo $tempat_lahir;
             echo " ";
             echo $tanggal_lahir;
             echo " ";
             echo "<br>";
             // setelah data dibaca, masukkan ke tabel pegawai sql
             // $query = "INSERT into pegawai (nama,tempat_lahir,tanggal_lahir)values('$nama','$tempat_lahir','$tanggal_lahir')";
             // $hasil = $this->db->query($query);
         }
         // if(!$hasil){
         //          jika import gagal
         // die(mysql_error());
         // }else{
         //          jika impor berhasil
         // echo "Data berhasil diimpor.";
         // }
         //    hapus file xls yang udah dibaca
         unlink($_FILES['filepegawaiall']['name']);
     }
 }

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

/**
 * 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 function import($file)
 {
     try {
         $users = array();
         $params = array(1 => 'username', 'password', 'first_name', 'last_name', 'email', 'admin', 'active');
         $excel = new Spreadsheet_Excel_Reader($file);
         $rows = $excel->rowcount($sheet_index = 0);
         $cols = $excel->colcount($sheet_index = 0);
         for ($row = 2; $row <= $rows; $row++) {
             for ($col = 1; $col <= $cols; $col++) {
                 $users[$row][$params[$col]] = $excel->val($row, $col);
             }
         }
         $this->userimport = new userimport();
         $users = $this->userimport->import($users);
         $_SESSION['message'] = $this->userimport->get_message();
         return $users;
     } catch (Exception $e) {
         display_page_error();
     }
 }

<?php

require_once "koneks.php";
error_reporting(E_ALL ^ E_NOTICE);
require_once 'excel_reader2.php';
$baris = $data->rowcount($sheet_index = 0);
// proses assigning baca data file 'data.xls'
//$data = new Spreadsheet_Excel_Reader("data.xls");
$data = new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
//-------- import dari sheet 1 ----------
// baca jumlah baris dalam sheet 1
$jmlbaris = $data->rowcount(0);
for ($i = 2; $i <= $jmlbaris; $i++) {
    // baca data pada baris ke-i, kolom ke-1, pada sheet 1
    $datakolom1 = $data->val($i, 1, 0);
    // baca data pada baris ke-i, kolom ke-2, pada sheet 1
    $datakolom2 = $data->val($i, 2, 0);
    // insert data ke tabel mhs
    $query = "INSERT INTO mhs (nim, namamhs) VALUES ('{$datakolom1}', '{$datakolom2}')";
    mysql_query($query);
}
//-------- import dari sheet 2 ----------
// baca jumlah baris dalam sheet 2
$jmlbaris = $data->rowcount(1);
for ($i = 2; $i <= $jmlbaris; $i++) {
    // baca data pada baris ke-i, kolom ke-1, pada sheet 2
    $datakolom1 = $data->val($i, 1, 1);
    // baca data pada baris ke-i, kolom ke-2, pada sheet 2
    $datakolom2 = $data->val($i, 2, 1);
    // insert data ke tabel dosen
    $query = "INSERT INTO dosen (kodedosen, namadosen) VALUES ('{$datakolom1}', '{$datakolom2}')";

if($_GET['aksi']=="import"){
	if(isset($_POST['submit'])){
	$jenis 		= $_POST['jenis'];
//nilai awal counter jumlah data yang sukses dan yang gagal diimport
 $sukses = 0;
 $gagal = 0;
$cell   = new Spreadsheet_Excel_Reader($_FILES['upfile']['tmp_name']);
$jum = $cell->rowcount($sheet_index=0);
 
$i = 2; // dimulai dari ke dua karena baris pertama berisi title
while( $i<=$jum ){
 
   //$cell->val( baris,kolom )
 
   $kode  = $cell->val( $i,1 );
   $nama = $cell->val( $i,2 );
   $jumlah = $cell->val( $i,3 );
    $hargabeli = $cell->val( $i,4 );
   $hargajual = $cell->val( $i,5 );

$sql ="INSERT INTO `po_produk` (`jenis`,`kode`,`nama`,`jumlah`,`hargabeli`,`hargajual`) VALUES ('$jenis','$kode','$nama','$jumlah','$hargabeli','$hargajual')";
$hasil = mysql_query( $sql );
setsaldoawal($kode);
if($hasil){
$sukses++;
}else{
$gagal++;
}
   $i++;
}

 function xls_import($id, $returl = '')
 {
     global $_CAMILA;
     require_once CAMILA_DIR . 'datagrid/form.class.php';
     require_once CAMILA_DIR . 'datagrid/elements/form/hidden.php';
     require_once CAMILA_DIR . 'datagrid/elements/form/filebox.php';
     require_once CAMILA_DIR . 'datagrid/elements/form/static_listbox.php';
     //if ($returl != '')
     //    $form3 = new phpform('camilastep4', $returl);
     //else
     $form3 = new phpform('camilastep4', 'cf_worktable_wizard_step4.php');
     $form3->submitbutton = camila_get_translation('camila.wizard.next');
     $form3->drawrules = false;
     new form_hidden($form3, 'custom', $id);
     if ($returl != '') {
         new form_hidden($form3, 'returl', $_REQUEST['camila_returl']);
     }
     new form_filebox($form3, 'filename', camila_get_translation('camila.worktable.xls.choose'), 50, CAMILA_TMP_DIR);
     $sheet_list = '';
     for ($i = 0; $i < 10; $i++) {
         if ($i > 0) {
             $sheet_list .= ',';
         }
         $sheet_list .= $i . ';' . ($i + 1);
     }
     new form_static_listbox($form3, 'sheetnum', camila_get_translation('camila.worktable.xls.sheetnum'), $sheet_list);
     $success = true;
     if ($form3->process()) {
         $filename = $form3->fields['filename']->value[0];
         $sheetnum = $form3->fields['sheetnum']->value;
         $result = $_CAMILA['db']->Execute('select short_title, scriptname, tablename, filename, sheetnum from ' . CAMILA_TABLE_WORKT . ' where id=' . $_CAMILA['db']->qstr($id));
         if ($result === false) {
             camila_error_page(camila_get_translation('camila.sqlerror') . ' ' . $_CAMILA['db']->ErrorMsg());
         }
         $table = $result->fields['tablename'];
         $worktablename = $result->fields['short_title'];
         $worktablescript = $result->fields['scriptname'];
         if ($filename == '' && $result->fields['filename'] != '') {
             $filename = $result->fields['filename'];
             $sheetnum = $result->fields['sheetnum'];
         }
         if ($filename != '') {
             require_once CAMILA_LIB_DIR . 'php-excel-reader/excel_reader2.php';
             $data = new Spreadsheet_Excel_Reader(CAMILA_TMP_DIR . '/' . $filename);
             $excelColNames = array();
             $i = 0;
             while ($data->val(1, $i + 1, $sheetnum) != '') {
                 $name = $data->val(1, $i + 1, $sheetnum);
                 $excelColNames[$i] = camila_strtoupper_utf8(isUTF8($name) ? $name : utf8_encode($name));
                 $i++;
             }
             $result = $_CAMILA['db']->Execute('select * from ' . CAMILA_TABLE_WORKC . ' where (wt_id=' . $_CAMILA['db']->qstr($id) . ' and is_deleted<>' . $_CAMILA['db']->qstr('y') . ') order by sequence');
             if ($result === false) {
                 camila_error_page(camila_get_translation('camila.sqlerror') . ' ' . $_CAMILA['db']->ErrorMsg());
             }
             $fields = array();
             $types = array();
             $defVals = array();
             $forceCase = array();
             $orig_types = array();
             $fieldMapping = array();
             $forceArr = camila_get_translation_array('camila.worktable.options.force');
             $count = 0;
             while (!$result->EOF) {
                 $colName = $result->fields['col_name'];
                 $name = camila_strtoupper_utf8($result->fields['name']);
                 $fieldMapping[$colName] = isUTF8($name) ? $name : utf8_encode($name);
                 $fields[$count] = $colName;
                 $types[$count] = $result->fields['type'];
                 $orig_types[$count] = $result->fields['orig_type'];
                 $defVals[$count] = $result->fields['default_value'];
                 $forceCase[$count] = $result->fields['force_case'];
                 $count++;
                 $result->MoveNext();
             }
             $successCount = 0;
             $failCount = 0;
             //db fields
             for ($i = 2; $i <= $data->rowcount($sheetnum); $i++) {
                 $record = array();
                 $emptyrow = true;
                 //db fields
                 reset($fields);
                 foreach ($fields as $k => $v) {
                     //k  Field position into database
                     //k2 Position in Excel file
                     $k2 = array_search($fieldMapping[$v], $excelColNames);
                     //Is it in Excel file?
                     if ($k2 !== false) {
                         $excelColName = camila_strtoupper_utf8($data->value(1, $k2 + 1, $sheetnum));
                         //$excelColName = $v;
                         $worktableColName = array_search($excelColName, $fieldMapping);
                         $worktableColName = $v;
                         if ($worktableColName != '') {
                             if ($types[$k] == 'date' && $data->val($i, $k2 + 1, $sheetnum) != '') {
                                 $numValue = $data->sheets[$sheetnum]['cellsInfo'][$i][$k2 + 1]['raw'];
                                 $utcDays = floor($numValue - ($data->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS));
                                 $utcValue = $utcDays * SPREADSHEET_EXCEL_READER_MSINADAY;
                                 $dateinfo = gmgetdate($utcValue);
                                 $fractionalDay = $numValue - floor($numValue) + 1.0E-7;
//.........这里部分代码省略.........

 $admin .= '<div class="bordermenu2"><a href="admin.php?pilih=importsiswa&amp;mod=yes">Import Siswa</a> | <a href="admin.php?pilih=importsiswa&amp;mod=yes&amp;aksi=delkelas">Hapus Siswa Per Kelas</a> | <a href="admin.php?pilih=importsiswa&amp;mod=yes&amp;aksi=inputsiswa">Input Siswa Satuan</a> | <a href="admin.php?pilih=importsiswa&amp;mod=yes&amp;aksi=daftarsiswa">Siswa tanpa Kelas</a>';
 $admin .= '</div>';
 $admin .= '<div class="panel panel-info">';
 if ($_GET['aksi'] == "") {
     if (isset($_POST['submit'])) {
         $kelas = $_POST['kelas'];
         //nilai awal counter jumlah data yang sukses dan yang gagal diimport
         $sukses = 0;
         $gagal = 0;
         $cell = new Spreadsheet_Excel_Reader($_FILES['upfile']['tmp_name']);
         $jumlah = $cell->rowcount($sheet_index = 0);
         $i = 2;
         // dimulai dari ke dua karena baris pertama berisi title
         while ($i <= $jumlah) {
             //$cell->val( baris,kolom )
             $nama = $cell->val($i, 2);
             $username = $cell->val($i, 1);
             $password = $cell->val($i, 1);
             $password = md5("{$password}");
             $nama = addslashes($nama);
             if ($username != '' and $password != '') {
                 $sql = "INSERT INTO `useraura` (`user`,`password`,`nama`) VALUES ('{$username}','{$password}','{$nama}')";
                 $hasil = mysql_query($sql);
                 $sql2 = "INSERT INTO `kelas_isi` (`kelas`,`siswa`) VALUES ('{$kelas}','{$username}')";
                 $hasil2 = mysql_query($sql2);
                 if ($hasil and $hasil2) {
                     $sukses++;
                 } else {
                     $gagal++;
                 }
             } else {

 $source = $_FILES['file']['tmp_name'];
 $t = "Subjective" . time() . "" . date('Ymd') . ".xls";
 $target = $upload_path . "/" . $t;
 $tempUploadedValue = move_uploaded_file($source, $target);
 if ($tempUploadedValue) {
     /*    echo "<script type='text/javascript'> alert('test1'); </script>";  */
     if (($handle = fopen($upload_path . '/' . $t, "r")) !== false) {
         $_FILES['file']['name'];
         $expiryDate = $_POST['expiryDate'];
         $data = new Spreadsheet_Excel_Reader($upload_path . '/' . $t);
         $data->dump(true, true);
         $data_array = array();
         for ($i = 1; $i <= $data->rowcount(); $i++) {
             $data_array[$i] = array();
             for ($j = 1; $j <= $data->colcount(); $j++) {
                 $data_array[$i][$j] = $data->val($i, $j);
             }
             // inner For
         }
         // outer For
         $myFile = $t;
         $url = "./excelfiles/" . $t;
         $newUploadedQuizURL = $newBaseURL . "/excelfiles/" . str_replace(' ', '%20', $myFile);
         $newQuizName = $_POST['questionFileName'];
         $newQuizFileName = $myFile;
         $duration = $_POST['hours'] * 3600 + $_POST['minutes'] * 60;
         $questionWeightage = $_POST['questionWeightage'];
         $questionQty = $_POST['questionQty'];
         $re_exam_date = $_POST['re_exam_date'];
         $cut_off = $_POST['cut_off'];
         mysql_query("CALL insertUploadedFile1('" . $t . "','" . $url . "','" . $user . "','" . $_POST['questionFileName'] . "','" . $questionWeightage . "','" . $questionQty . "','" . $cut_off . "','" . $duration . "','" . $_POST['expiryDate'] . "','" . $re_exam_date . "','Unpublished','subjective')");

        <?php 
// menggunakan class phpExcelReader
include "excel_reader2.php";
include "lib/koneksi.php";
// membaca file excel yang diupload
$data = new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
// membaca jumlah baris dari data excel
$baris = $data->rowcount($sheet_index = 0);
// nilai awal counter untuk jumlah data yang sukses dan yang gagal diimport
$sukses = 0;
$gagal = 0;
// import data excel mulai baris ke-2 (karena baris pertama adalah nama kolom)
for ($i = 2; $i <= $baris; $i++) {
    // membaca data bcf15 (kolom ke-1)
    $idbcf15 = $data->val($i, 1);
    $tahun = $data->val($i, 2);
    $bcf15no = $data->val($i, 3);
    $bcf15tgl = $data->val($i, 4);
    $suratpengantarno = $data->val($i, 5);
    $perintah = $data->val($i, 6);
    $suratperintahno = $data->val($i, 7);
    $suratperintahdate = $data->val($i, 8);
    $idtp2 = $data->val($i, 9);
    $idseksitp2 = $data->val($i, 10);
    $pemberitahuan = $data->val($i, 11);
    $suratno = $data->val($i, 12);
    $suratdate = $data->val($i, 13);
    $idtp3 = $data->val($i, 14);
    $idseksitp3 = $data->val($i, 15);
    $masuk = $data->val($i, 16);

 /**
  * Creates a new model.
  * If creation is successful, the browser will be redirected to the 'view' page.
  */
 public function actionCreate()
 {
     $model = new PhoneModel();
     $message = "";
     $errorList = array();
     // Uncomment the following line if AJAX validation is needed
     // $this->performAjaxValidation($model);
     if (isset($_POST['source_name'], $_POST['group_id'])) {
         $fileName = _APP_PATH_ . DS . "data" . DS . "tmp" . DS . $_POST['source_name'];
         $group_id = $_POST['group_id'];
         //$fileName = "D:\\chacha_cloud\\src\\trunk\chacha\data\\tmp\\20120713170547_phone_list.xls";
         try {
             require_once 'excel_reader2.php';
             $data = new Spreadsheet_Excel_Reader($fileName, true, "UTF-8");
             // khoi tao doi tuong doc file excel
             $rowsnum = $data->rowcount($sheet_index = 0);
             // lay so hang cua sheet
             $colsnum = $data->colcount($sheet_index = 0);
             // lay so cot cua sheet
             for ($i = 2; $i <= $rowsnum; $i++) {
                 // doc tu hang so 2 vi hang 1 la tieu de roi!
                 $phoneNum = $data->val($i, 1);
                 // xuat cot so 1 va cot so 2 tren cung 1 hang
                 // check so dien thoai xem co dung cua Vinaphone ko
                 try {
                     $phoneNum = Formatter::formatPhone($phoneNum);
                     if (Formatter::isVinaphoneNumber($phoneNum)) {
                         $model->phone = "{$phoneNum}";
                         $model->group_id = $group_id;
                         $model->status = 0;
                         $model->created_time = date("Y-m-d H:i:s");
                         var_dump($model->phone);
                         try {
                             if ($model->save()) {
                                 $message = yii::t('SpamModule', 'Upload thành công');
                             } else {
                                 print_r($model->getErrors());
                                 exit;
                             }
                         } catch (Exception $exc) {
                             echo $exc->getTrace();
                         }
                     } else {
                         //echo so dien thoai ko dung
                         $errorList[] = $phoneNum;
                     }
                 } catch (Exception $exc) {
                     echo $exc->getMessage();
                 }
             }
         } catch (Exception $exc) {
             echo $exc->getMessage();
         }
     }
     $uploadModel = new XUploadForm();
     $tmpArr = GroupModel::model()->findAll();
     $smsGroup = array();
     foreach ($tmpArr as $smsG) {
         $smsGroup[$smsG->id] = $smsG->name;
     }
     $this->render('create', array('model' => $model, 'uploadModel' => $uploadModel, 'message' => $message, 'smsGroup' => $smsGroup, 'errorList' => $errorList));
 }

<?php 
// menggunakan class phpExcelReader
include "excel_reader2.php";
include "lib/koneksi.php";
// membaca file excel yang diupload
$data = new Spreadsheet_Excel_Reader($_FILES['userfile']['tmp_name']);
// membaca jumlah baris dari data excel
$baris = $data->rowcount($sheet_index = 0);
// nilai awal counter untuk jumlah data yang sukses dan yang gagal diimport
$sukses = 0;
$gagal = 0;
// import data excel mulai baris ke-2 (karena baris pertama adalah nama kolom)
for ($i = 2; $i <= $baris; $i++) {
    // membaca data bcf15 (kolom ke-1)
    $tahun = $data->val($i, 1);
    $bcf15no = $data->val($i, 2);
    $Batal = $data->val($i, 3);
    $SuratBatalNo = $data->val($i, 4);
    $SuratBatalDate = $data->val($i, 5);
    $Pemohon = $data->val($i, 6);
    $AlamatPemohon = $data->val($i, 7);
    $ndkonfirmasino = $data->val($i, 8);
    $ndkonfirmasino2 = $data->val($i, 9);
    $ndkonfirmasidate = $data->val($i, 10);
    $ndkonfirmasito = $data->val($i, 11);
    $CacahNo = $data->val($i, 12);
    $CacahDate = $data->val($i, 13);
    $ndkonfirmasi = $data->val($i, 14);
    $recordstatuskonf = $data->val($i, 15);
    $idseksindkonfirmasi = $data->val($i, 16);