PHP Spreadsheet_Excel_Reader::value方法代码示例

function importToExistingTable($file, $tableName)
{
    $data = new Spreadsheet_Excel_Reader($file);
    $colcount = $data->colcount();
    $rowcount = $data->rowcount();
    //This gets the field names in the database so that they can be confirmed as matching those in the spreadsheet:
    $sql = mysql_query("SELECT * FROM `{$tableName}`");
    for ($no = 0; $no <= $colcount; $no++) {
        $tableColsArray[$no] = mysql_field_name($sql, $no);
    }
    //This goes through the spreadsheet and compares the columns with those in the database:
    for ($colno = 1; $colno <= $colcount; $colno++) {
        $cell = $data->value(1, $colno);
        if ($tableColsArray[$colno] != $cell) {
            die("Column {$colno} does not match its counterpart in the database");
        } else {
            echo "spreadsheet part ({$cell}) perfectly matches db part ({$tableColsArray[$colno]})<br />";
        }
    }
    //This goes through the whole sheet building the insert statement from the data:
    $actualInserts = 0;
    for ($rowno = 2; $rowno <= $rowcount; $rowno++) {
        $sql = "INSERT INTO `{$tableName}` (";
        for ($no = 1; $no < count($tableColsArray); $no++) {
            $sql = $sql . "`{$tableColsArray[$no]}`,";
        }
        $sql = substr($sql, 0, -1) . ") VALUES (";
        for ($colno = 1; $colno <= $colcount; $colno++) {
            $sql = $sql . "'" . mysql_real_escape_string($data->value($rowno, $colno)) . "',";
        }
        $sql = substr($sql, 0, -1) . ")";
        //This executes the insert and counts the successful inserts:
        if (!mysql_query($sql)) {
            echo mysql_error();
        } else {
            $actualInserts++;
        }
    }
    echo "<br />" . $actualInserts . " rows inserted";
}

 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;
//.........这里部分代码省略.........

<?php

ini_set("memory_limit", "10000M");
$conn = mysql_connect("localhost", "mahimagroup", "Mahima123") or die("Couldn't connect to server.");
$db = mysql_select_db("mahimagroup", $conn) or die("Couldn't select database.");
ini_set("display_errors", 1);
error_reporting(E_ALL);
require_once 'excel/excel_reader2.php';
$data = new Spreadsheet_Excel_Reader("itemMaster.xls");
for ($row = 2; $row <= $data->rowcount(); $row++) {
    $sql = "SELECT uom_id FROM ms_uom where name = '" . $data->value($row, 6) . "'";
    $result = mysql_query($sql) or die("Error in : " . $sql . "<br>" . mysql_errno() . " : " . mysql_error());
    if (mysql_num_rows($result) > 0) {
        $row_1 = mysql_fetch_array($result);
        $unit_id = $row_1['uom_id'];
    }
    $sql = "SELECT department_id  FROM ms_department where name = '" . $data->value($row, 2) . "'";
    $result = mysql_query($sql) or die("Error in : " . $sql . "<br>" . mysql_errno() . " : " . mysql_error());
    if (mysql_num_rows($result) > 0) {
        $row_1 = mysql_fetch_array($result);
        $department_id = $row_1['department_id'];
    }
    $sql = "SELECT machinary_id FROM ms_machinary where name = '" . $data->value($row, 3) . "'";
    $result = mysql_query($sql) or die("Error in : " . $sql . "<br>" . mysql_errno() . " : " . mysql_error());
    if (mysql_num_rows($result) > 0) {
        $row_1 = mysql_fetch_array($result);
        $machinary_id = $row_1['machinary_id'];
    }
    echo $sql_item = "insert into ms_item_master set\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tname = '" . addslashes($data->value($row, 1)) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tdepartment_id = '" . $department_id . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tuom_id = '" . $unit_id . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tmachinary_id = '" . $machinary_id . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tdrawing_number ='" . $data->value($row, 4) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tcatelog_number = '" . $data->value($row, 5) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\ttype_of_item = '" . $data->value($row, 8) . "'";
    //$result_ins = mysql_query($sql_item) or die("Error in query:".$sql_item."<br>".mysql_error().":".mysql_errno());
}

    if ($data->format($row, 3) != "") {
        $arr_test = explode(" ", $data->format($row, 3));
        $uom = str_replace('"', "", $arr_test[1]);
        if (!in_array($uom, $unit_master)) {
            $unit_master[] = $uom;
        }
    }
}
foreach ($unit_master as $unit_name) {
    $sql_unit_master = "insert into ms_uom set\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tname = '" . $unit_name . "'";
    $result_ins = mysql_query($sql_unit_master) or die("Error in query:" . $sql_unit_master . "<br>" . mysql_error() . ":" . mysql_errno());
}
$dep_id = 0;
for ($row = 5; $row <= $data->rowcount(); $row++) {
    if ($data->bold($row, 1, 0)) {
        $sql_department = "insert into ms_department set\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tname = '" . $data->value($row, 1, 0) . "'";
        $result_ins = mysql_query($sql_department) or die("Error in query:" . $sql_department . "<br>" . mysql_error() . ":" . mysql_errno());
        $dep_id = mysql_insert_id();
    } else {
        $unit_id = 0;
        if ($data->format($row, 3) != "") {
            $arr_test = explode(" ", $data->format($row, 3));
            $uom = str_replace('"', "", $arr_test[1]);
        }
        $sql = "SELECT uom_id FROM ms_uom where name = '" . $uom . "'";
        $result = mysql_query($sql) or die("Error in : " . $sql . "<br>" . mysql_errno() . " : " . mysql_error());
        if (mysql_num_rows($result) > 0) {
            $row_1 = mysql_fetch_array($result);
            $unit_id = $row_1['uom_id'];
        }
        $sql_item = "insert into ms_item_master set\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tname = '" . addslashes($data->value($row, 1)) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tdepartment_id = '" . $dep_id . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tuom_id = '" . $unit_id . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\topening_rate ='" . $data->raw($row, 4) . "',\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\topening_quantity = '" . $data->raw($row, 3) . "'";

 /**
  * Upload Excel method
  * Upload Access Details Template
  * @return void
  */
 public function uploadexcel()
 {
     $excelfile = $this->request->data['AccessDetail']['uploadexcelfile']['tmp_name'];
     $excelData = new Spreadsheet_Excel_Reader($excelfile, true);
     $accessDetailsModel = array();
     //starting with 2, 1 is header skipping it, all index for excel starts with 1
     $rowindex = 2;
     $arrindex = 0;
     while ($excelData->value($rowindex, 1) != '') {
         //accessid is auto increment
         $accessDetailsModel['AccessDetail'][$arrindex]['uniqueid'] = $excelData->value($rowindex, 1);
         $accessDetailsModel['AccessDetail'][$arrindex]['fname'] = $excelData->value($rowindex, 2);
         $accessDetailsModel['AccessDetail'][$arrindex]['lname'] = $excelData->value($rowindex, 3);
         $accessDetailsModel['AccessDetail'][$arrindex]['team'] = $this->getUserTeam();
         $accessDetailsModel['AccessDetail'][$arrindex]['systype'] = $excelData->value($rowindex, 4);
         $accessDetailsModel['AccessDetail'][$arrindex]['sysname'] = $excelData->value($rowindex, 5);
         $accessDetailsModel['AccessDetail'][$arrindex]['env'] = $excelData->value($rowindex, 6);
         $accessDetailsModel['AccessDetail'][$arrindex]['accresp'] = $excelData->value($rowindex, 7);
         $accessDetailsModel['AccessDetail'][$arrindex]['acctype'] = $excelData->value($rowindex, 8);
         $accessDetailsModel['AccessDetail'][$arrindex]['accprivilege'] = $excelData->value($rowindex, 9);
         $accessDetailsModel['AccessDetail'][$arrindex++]['accidassigned'] = $excelData->value($rowindex++, 10);
     }
     //debug($accessDetailsModel);
     foreach ($accessDetailsModel['AccessDetail'] as $value) {
         $accessDetailEntity['AccessDetail'] = $value;
         $this->AccessDetail->create();
         if ($this->AccessDetail->save($accessDetailEntity)) {
             $this->setFlash('Successfully uploaded from excel.', AppController::$SUCCESS);
         } else {
             $this->setFlash(AppController::$errorMessage, AppController::$DANGER);
             break;
         }
     }
     return $this->redirect(array('action' => 'index'));
 }

echo "<td>Pendidikan</td>";
echo "<td>TMT Lama</td>";
echo "<td>MKG Lama (Tahun)</td>";
echo "<td>MKG Lama (Bulan)</td>";
echo "<td>Gaji Pokok Lama</td>";
echo "<td>Jabatan</td>";
echo "<td>TMT Baru</td>";
echo "<td>MKG Baru (Tahun)</td>";
echo "<td>MKG Baru (Bulan)</td>";
echo "<td>Unit Kerja / SKPD</td>";
echo "<td>No SK</td>";
echo "<td>Tgl SK</td>";
echo "</tr>";
for ($i = 5; $i <= $data->rowcount(0); $i++) {
    // Cek Jika Pada Suatu Baris Nilainya Kosong atau Tidak
    if ($data->value($i, 1, 0) != "") {
        echo "<tr>";
        echo "<td>" . $data->value($i, 1, 0) . "</td>";
        echo "<td>" . $data->value($i, 2, 0) . "</td>";
        echo "<td>" . $data->value($i, 3, 0) . "</td>";
        echo "<td>" . $data->value($i, 5, 0) . "</td>";
        echo "<td>" . $data->value($i, 6, 0) . "</td>";
        echo "<td>" . $data->value($i, 8, 0) . "</td>";
        echo "<td>" . $data->value($i, 9, 0) . "</td>";
        echo "<td>" . $data->value($i, 10, 0) . "</td>";
        echo "<td>" . $data->value($i, 11, 0) . "</td>";
        echo "<td>" . $data->value($i, 12, 0) . "</td>";
        echo "<td>" . $data->value($i, 14, 0) . "</td>";
        echo "<td>" . $data->value($i, 15, 0) . "</td>";
        echo "<td>" . $data->value($i, 16, 0) . "</td>";
        echo "<td>" . $data->value($i, 19, 0) . "</td>";

 protected function RowtoContact(Spreadsheet_Excel_Reader $data, $row)
 {
     $contactRec = array();
     $contactRec['first_name'] = $data->value($row, 1);
     $contactRec['last_name'] = $data->value($row, 2);
     $contactRec['display_name'] = $data->value($row, 3);
     $contactRec['company'] = $data->value($row, 4);
     $contactRec['department'] = $data->value($row, 5);
     $contactRec['position'] = $data->value($row, 6);
     $contactRec['email'] = $data->value($row, 7);
     $contactRec['phone'] = $data->value($row, 8);
     $contactRec['mobile'] = $data->value($row, 9);
     $contactRec['fax'] = $data->value($row, 10);
     $contactRec['zipcode'] = $data->value($row, 11);
     $contactRec['province'] = $data->value($row, 12);
     $contactRec['city'] = $data->value($row, 13);
     $contactRec['street'] = $data->value($row, 14);
     $contactRec['country'] = $data->value($row, 15);
     $contactRec['webpage'] = $data->value($row, 16);
     $contactRec['qq'] = $data->value($row, 17);
     $contactRec['icq'] = $data->value($row, 18);
     $contactRec['skype'] = $data->value($row, 19);
     $contactRec['yahoo'] = $data->value($row, 20);
     $contactRec['photo'] = "";
     $contactRec['selected'] = '1';
     $contactRec['foreign_key'] = $this->getRawFileName() . '-' . $row;
     $contactRec['source'] = 'Excel2003 - ' . $this->getRawFileName();
     return $contactRec;
 }

include "../mysqli_connect.php";
require_once "../excel_reader2.php";
$nomor_sk = $_POST['no_sk'];
/*INFO OF FILES*/
$tmp = $_FILES['filename']['tmp_name'];
$fname = $_FILES['filename']['name'];
$DIR = "../../sys_files/scan_sk_kenpang/" . $fname;
// upload dahulu
move_uploaded_file($tmp, $DIR);
if (file_exists($DIR)) {
    $data = new Spreadsheet_Excel_Reader($DIR);
}
$sql = "";
for ($i = 5; $i <= $data->rowcount(0); $i++) {
    // Cek Jika Pada Suatu Baris Nilainya Kosong atau Tidak
    if ($data->value($i, 1, 0) != "") {
        //$data->value($i,1,0);
        $no_bkn = $data->value($i, 2, 0);
        $tgl_bkn = $data->value($i, 3, 0);
        $nip = $data->value($i, 5, 0);
        $pendidikan = $data->value($i, 6, 0);
        $tmt_lama = $data->value($i, 8, 0);
        $mkgt_lama = $data->value($i, 9, 0);
        $mkgb_lama = $data->value($i, 10, 0);
        $gapok_lama = $data->value($i, 11, 0);
        $jabatan = $data->value($i, 12, 0);
        $tmt_baru = $data->value($i, 14, 0);
        $mkgt_baru = $data->value($i, 15, 0);
        $mkgb_baru = $data->value($i, 16, 0);
        $skpd = $data->value($i, 19, 0);
        $no_sk = $data->value($i, 21, 0);