php - 从上传的 excel 文件中检索值并将其存储到数据库中

Question

我正在尝试将每个员工的出勤详细信息作为批量出勤上传从 excel 文件保存到数据库中，下面的代码显示 Excel 数据导入成功，但没有将值存储到数据库中，行 $last_id= $this->mod_common->add_attendence_data($data_user);没有执行，这就是为什么它不起作用，如何让这一行执行？由于这段代码是由其他一些开发人员编写的，我发现它很难理解。

Controller :admin_list.php

	function upload_bulk_attendance(){
		$data['htmltext'] = $this->mod_common->get_allleaves();
	  $this->data['maincontent'] = $this->load->view('maincontents/upload_attendance_sheet', $data,true); 
	  $this->load->view('layout', $this->data);
	}
	
    function importExcel() {
	//$this->mod_common->delete_attendance();
	$this->load->library('excel');
	
    //microsoft excel 2007
	if(!$objReader = PHPExcel_IOFactory::createReader('Excel5')){
		die('Unable to create reader object');
	}
    //$objReader = PHPExcel_IOFactory::createReader('Excel2007');
    //set to read only
    $objReader->setReadDataOnly(false);
    //load excel file
	
	global $SITE;
	if(!$upload_val=$this->admin_init_elements->do_upload_docs('user_attendance',$SITE->client_logo_image_src,false)){
		$data['error'] = $this->upload->display_errors();
		$this->load->view('maincontents/upload_attendance_sheet', $data,true);	
	} else {
		
		$file_path = $upload_val['upload_data']['full_path'];
	}	
	
     $objPHPExcel  = $objReader->load($file_path);
     $objWorksheet = $objPHPExcel->setActiveSheetIndex(1);
	
/*---------------------------------------------------------------------------*/	
		$excelrow = 1;
		 $from_Date = $objWorksheet->getCellByColumnAndRow(4,1)->getValue();
		
		 $to_Date 	= $objWorksheet->getCellByColumnAndRow(6,1)->getValue();
		 $total_rows_in_sheet = $objPHPExcel->getActiveSheet()->getHighestRow();	
		
		if (PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell('E1'))) {
			$dateValue = PHPExcel_Shared_Date::ExcelToPHP($from_Date);
			$from_date = date('Y-m-d',$dateValue);
		}
		
		if (PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell('G1'))) {
			$dateValue = PHPExcel_Shared_Date::ExcelToPHP($to_Date);
			$to_date = date('Y-m-d',$dateValue);
		}

		$start = strtotime($from_date);
		$end = strtotime($to_date);
		$days_between = ceil(abs($end - $start) / 86400);
		$excelrow = $excelrow + 2;
		$emp_code_id_array = $this->mod_common->get_Details2('pr_users_details',array('userid','emp_code'));	
		foreach($emp_code_id_array as $e){ $emp_code_id[$e['emp_code']] = $e['userid']; }		
		
	do {
		$shift_excelrow 	= ++$excelrow; 
	/*--------------- Moving row wise for each employees data --------------*/		
		$emp_code	= $objWorksheet->getCellByColumnAndRow(0,$excelrow)->getValue();
		$emp_code	=($emp_code==null)?0:$emp_code;	
		
		if($emp_code<=0) continue;
		$emp_code = str_pad($emp_code, 3, '0', STR_PAD_LEFT);
		
		$emp_id 	=($emp_code_id[$emp_code]==null)?0:$emp_code_id[$emp_code];
		$emp_name	= $objWorksheet->getCellByColumnAndRow(1,$excelrow)->getValue();
	/*--------------- Moving column wise for each days data --------------*/
		$col_no=3;	
		for($i=0;$i<=$days_between;$i++) {
		 $dt  = date('Y-m-d',strtotime($from_date));
		 $day = date('d', strtotime($dt .' +'.$i.' day'));
		
		 $entry[] = $day."|".$objWorksheet->getCellByColumnAndRow($col_no,$shift_excelrow)->getValue();	
		 $col_no++;
		} 
		$present=$objWorksheet->getCellByColumnAndRow(34,$shift_excelrow)->getValue();
		$absent=$objWorksheet->getCellByColumnAndRow(35,$shift_excelrow)->getValue();
		$total=$objWorksheet->getCellByColumnAndRow(36,$shift_excelrow)->getValue(); 
			if($emp_id!=0){
				$emp_id_arr[]		= $emp_id;	
			}
			/* CREATING ARRAY FOR DB INSERT */	
			$data_user['emp_code']		= $emp_code;
			$data_user['emp_id']		= $emp_id;
			$data_user['name']			= $emp_name;
			$data_user['entry']			= implode(",",$entry);
			$data_user['from_date']		= $from_date;
			$data_user['to_date']		= $to_date;
			$data_user['present']		= $present;
			$data_user['absent']		= $absent;
			$data_user['total']			= $total; 
			$data_user['current_date']	= date('Y-m-d');
			
			$last_id=$this->mod_common->add_attendence_data($data_user);
			//Reset array
			$entry=array();
		}while($excelrow <=$total_rows_in_sheet);
		
	/*----------------------------------------------------*/
		if(count($emp_id_arr)==0){
			$emp_id_arr[]		= 0;	
		}
		$this->mod_common->save_attendance_notification($last_id,implode(",",$emp_id_arr),$data_user['from_date']);
		
		$this->session->set_flashdata('success', 'Excel Data Imported Succesfully');
		redirect(base_url().'admin_list/upload_bulk_attendance');
    }//importExcel() END 

模型:mod_common.php

 function add_attendence_data($datauser) {
	
	$emp_code = str_pad($datauser['emp_code'], 3, '0', STR_PAD_LEFT);
	$this->db_replace->select('*');
	$this->db_replace->from($this->myTables['attendance']);
	$this->db_replace->where('emp_code',$emp_code);
	$this->db_replace->where('from_date',$datauser['from_date']);
	$query=$this->db_replace->get();
	if($query->num_rows() <= 0)
	{
		$datauser['emp_code'] = $emp_code;
		
		$this->db_replace->insert($this->myTables['attendance'],$datauser);
		$instanceid = $this->db_replace->insert_id();
		
		return $instanceid;
	} else return false;
	
 } 	

查看:upload_attendance_sheet.php

<section class="content-header">
          <h1>
			Upload Attendance
          </h1>
          <ol class="breadcrumb">
            <li><a href="<?php echo base_url(); ?>"><i class="fa fa-home"></i> Home</a></li>
			 <li><a href="<?php echo base_url(); ?>admin_list"> Admin</a></li>
			 <li><a href="<?php echo base_url(); ?>admin_list#peopleconnect">Attendance</a></li>
			<li class="active">Upload Attendance</li>
            
          </ol>
    </section>
    <section class="content">
          <div class="container-fluid">
                    <div class="row"> 
                        <!-- Thought Day-->
                        <div class="panel wrapper clearfix m-b-none">
                <div class="box-header with-border">
<?php if($bulkUploadMsg!=''){?>
<?php echo $bulkUploadMsg;?>
<?php } ?>

<?php 
if($this->session->flashdata('success') == TRUE){ 
echo $this->session->flashdata('success');
}
?>


<br>

<?php 
echo form_open_multipart('admin_list/importExcel', array('method'=>'post','name'=>'uploadUserForm','id'=>'upload_file','class'=>"custom-form"));
?>
Please download attendance upload format by clicking <a href="<?php echo base_url(); ?>uploads/attendance_format.xls" class="text-info" style="color:#23b7e5">here</a><br><br>
<table  cellpadding="10px" style="background:none;width:100%;" RULES="ROWS">
<thead>
<tr>
	<td width="50px;">Upload</td>
	<td>:</td>
	<td>
		<?php echo form_upload(array("required"=>"required","name"=>"user_attendance","id"=>"user_attendance")); ?>
	</td>
</tr>
</table>
<h3>Instructions to Upload : </h3>
<ul>
	<li>Complete the sheet below and upload to update attendance records of all employees
</li><li>
You may add as many rows as needed - one for each employee
</li><li>Enter the start and end dates in the dd/mm/yyyy format only
</li><li>Only use the legend given to enter primary attendance details - Present at assigned office location-X, Absent without notification-A, On Official Travel-O, Present but not in office location-P, Week Off-W, Holiday-H, Half day working-D

</li><li>Use the following legends for entering Leave Details-<?php echo $htmltext; ?>

</li><li>As good practice, ensure that this sheet is uploaded at approximately the same interval each month

</li></ul><br>

</p>
<br>
<?php
echo form_submit(array('name'=>'submit','value'=>'Upload','class'=>'btn btn-info btn-submit pull-right add'));
echo form_close();
?>
</div>
</div>
</div>
</div>
<script type="text/javascript" src="<?php echo base_url(); ?>assets/plugins/data-tables/DT_bootstrap.js"></script>

表:pr_attendance

Answer 1

请尝试这种方式:-

<?php

public function importContacts($id)
{
  if(isset($_POST["Import"]))
    {
        $filename=$_FILES["file"]["name"];
        $f_extension = explode('.',$filename); 
        $f_extension = strtolower(end($f_extension));

        if($f_extension == 'csv')  // for import CSV file
            {
                $file_tmp_name=$_FILES["file"]["tmp_name"];
                if($_FILES["file"]["size"] > 0)
                  {
                    $file = fopen($file_tmp_name, "r");
                    $row = array();
                    while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
                    {
                        array_push($row, $emapData);
                    }
                    fclose($file);
                    unset($row[0]);
                    foreach(array_values($row) as $r){
                            $data = array(
                                    'gc_group_id' => $id,
                                    'gc_user_id' => $this->session->userdata('userId'),
                                    'gc_name' => @$r[1],
                                    'gc_number' => @$r[0],
                                    'gc_email' => @$r[2],
                                    'gc_added_date' => date('Y-m-d'),
                                );
                            $this->user_model->add('groups_contact',$data);
                            array_push($valid_no, $r[0]);
                    }
                    fclose($file);
                  }
            }

        if($f_extension == 'xlsx' || $f_extension == 'xls') 
            {
               /* PHPExcel library */
                set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
                require_once APPPATH.'third_party/PHPExcel/IOFactory.php';
                $file_tmp_name=$_FILES["file"]["tmp_name"];
                try {
                    $objPHPExcel = PHPExcel_IOFactory::load($file_tmp_name);
                } catch(Exception $e) {
                    die('Error loading file :' . $e->getMessage());
                }

                $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
                unset($sheetData[1]);
                foreach(array_values($sheetData) as $s){
                        $data = array(
                                    'gc_group_id' => $id,
                                    'gc_user_id' => $this->session->userdata('userId'),
                                    'gc_name' => @$s[B],
                                    'gc_number' => @$s[A],
                                    'gc_email' => @$s[C],
                                    'gc_added_date' => date('Y-m-d'),
                                );
                        $this->user_model->add('groups_contact',$data);
                        array_push($valid_no, $s[A]);
                }
            }
        $msg = 'Contacts Added Successfully ';
    }

}

?>

如果您发现任何问题，请告诉我。