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;
// The .0000001 is to fix for php/excel fractional diffs
$totalseconds = floor(SPREADSHEET_EXCEL_READER_MSINADAY * $fractionalDay);
$secs = $totalseconds % 60;
$totalseconds -= $secs;
$hours = floor($totalseconds / (60 * 60));
$mins = floor($totalseconds / 60) % 60;
$dt = date('Y-m-d', mktime($hours, $mins, $secs, $dateinfo["mon"], $dateinfo["mday"], $dateinfo["year"]));
$record[$worktableColName] = $_CAMILA['db']->BindDate($dt);
} elseif ($orig_types[$k] == 'number' && $data->sheets[$sheetnum]['cellsInfo'][$i][$k2 + 1]['raw'] != '') {
$record[$worktableColName] = $data->sheets[$sheetnum]['cellsInfo'][$i][$k2 + 1]['raw'];
} elseif ($types[$k] == 'hyperlink' && $data->hyperlink($i, $k2 + 1, $sheetnum) != '') {
//$record[$worktableColName] = '<a href="' . $data->hyperlink($i, $k2+1, $sheetnum) . '" target="_blank">' . $data->value($i, $k2+1, $sheetnum) . '</a>';
$record[$worktableColName] = $data->hyperlink($i, $k2 + 1, $sheetnum);
} else {
$record[$worktableColName] = $data->value($i, $k2 + 1, $sheetnum);
}
if ($defVals[$k] != '' && $record[$worktableColName] == '') {
$record[$worktableColName] = camila_parse_default_expression($defVals[$k], '_camila_seq_num_', true);
}
if ($record[$worktableColName] != '') {
if ($forceCase[$k] == 'upper') {
$record[$worktableColName] = mb_strtoupper($record[$worktableColName], 'UTF-8');
}
if ($forceCase[$k] == 'lower') {
$record[$worktableColName] = mb_strtolower($record[$worktableColName], 'UTF-8');
}
$emptyrow = false;
}
}
} else {
if ($defVals[$k] != '') {
$record[$fields[$k]] = camila_parse_default_expression($defVals[$k], '_camila_seq_num_', true);
}
}
}
if (!$emptyrow) {
$now = $_CAMILA['db']->BindTimeStamp(gmdate("Y-m-d H:i:s", time()));
$id = $_CAMILA['db']->GenID('worktableseq', 100000);
foreach ($record as $k => $v) {
$record[$k] = str_replace('_camila_seq_num_', $id, $v);
}
$record['id'] = $id;
$record['created'] = $now;
$record['created_by'] = $_CAMILA['user'];
$record['created_src'] = 'import';
$record['created_by_surname'] = $_CAMILA['user_surname'];
$record['created_by_name'] = $_CAMILA['user_name'];
$record['last_upd'] = $now;
$record['last_upd_by'] = $_CAMILA['user'];
$record['last_upd_src'] = 'import';
$record['last_upd_by_surname'] = $_CAMILA['user_surname'];
$record['last_upd_by_name'] = $_CAMILA['user_name'];
$record['mod_num'] = 0;
$insertSQL = $_CAMILA['db']->AutoExecute($table, $record, 'INSERT');
if (!$insertSQL) {
//camila_information_text(camila_get_translation('camila.worktable.db.importerror'));
$failCount++;
$success = false;
} else {
$successCount++;
}
}
}
}
camila_information_text(camila_get_translation('camila.worktable.db.importedrows') . ': ' . $successCount);
camila_information_text(camila_get_translation('camila.worktable.db.skippedrows') . ': ' . $failCount);
@unlink(CAMILA_TMP_DIR . '/' . $filename);
} else {
$result = $_CAMILA['db']->Execute('select 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());
}
$filename = $result->fields['filename'];
// if ($filename != '') {
$myText = new CHAW_text(camila_get_translation('camila.wizard.choosefileforimport'));
$_CAMILA['page']->add_text($myText);
$form3->draw();
$success = false;
// }
}
if ($success) {
if ($worktablename != '') {
$myLink = new CHAW_link($worktablename, $worktablescript);
$myLink->set_br(0);
$_CAMILA['page']->add_link($myLink);
$myText = new CHAW_text(' - ' . camila_get_translation('camila.worktable.db.importok'));
$_CAMILA['page']->add_text($myText);
} else {
$myText = new CHAW_text(camila_get_translation('camila.wizard.configurationapplied'));
$_CAMILA['page']->add_text($myText);
}
}
}
<?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);
