<?php

include 'PHPExcel/IOFactory.php';


if(isset($_POST["submit"])) {

    $inputFileName = $_FILES["fileToUpload"]["tmp_name"];
    $imageFileType = strtolower(pathinfo($_FILES["fileToUpload"]["name"],PATHINFO_EXTENSION));
 
    if($imageFileType != "csv" && $imageFileType != "xlsx" && $imageFileType != "xls") {
        echo 'wrong file type xlsx'; die;
    }
try {
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    $objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

//  Get worksheet dimensions
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();
$list = array('Tên KH','Địa chỉ','Nội dung tin nhắn','Phòng giao dịch','Đại lý','Mã KH','Số DT');
fputcsv($fp, $list);
$lists[] = $list;
$fp = fopen(__DIR__.'/'.$_FILES["fileToUpload"]["name"].'.csv', 'w');
//  Loop through each row of the worksheet in turn
for ($row = 2; $row <= $highestRow; $row++){ 
    //  Read a row of data into an array
    $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                    NULL,
                                    TRUE,
                                    FALSE);
    $list = $rowData[0];
   
    $dd = $rowData[0][$_POST["cot"]-1];
    $dd = str_replace(',',', ',$dd);
    $dd = str_replace(';',', ',$dd);
    $dd = str_replace(':',', ',$dd);
    $dd = str_replace('_',', ',$dd);
    $dd = str_replace('-',', ',$dd);
    $dd = trim($dd);
    $dd = explode(' ', $dd);
    $list[] = '';
    $list[] = '';
    foreach ($dd as $r){
        $r = str_replace(',','',$r);
        $l = strlen($r);
        $n = (float)$n;
        $s = substr($r, -1);
        $s1 = substr($r, 0 , 1);
        $s2 = substr($r, 0 , $l-1);
        $s3 = substr($r, 0 , 2);
        if( ((($s == 'A' || $s == 'B' || $s == 'C' || $s == 'M') && is_numeric($s2) ) || $s3 == 'TB' )  && $l > 8 && stripos($r,'/') === false ){
            $list[count((array)$list)-2] =  $r."\0";
        }
        if(($s1 == '0' || $s1 == '9' || $s1 == '2') && $l > 8 && is_numeric($r)  && stripos($r,'/') === false ){
           
            $list[count((array)$list)-1] =  '="'. $r . '"';// 
        }
    }
   
   
    $lists[] = $list;
    fputcsv($fp, $list);
    //  Insert row data array into your database of choice here
}

fclose($fp);
if(file_exists(__DIR__.'/'.$_FILES["fileToUpload"]["name"].'.csv')){
  
    $file_url = __DIR__.'/'.$_FILES["fileToUpload"]["name"].'.csv';
    header('Content-Type: application/octet-stream');
    header("Content-Transfer-Encoding: Binary"); 
    header("Content-disposition: attachment; filename=\"" . basename($file_url) . "\""); 
    readfile($file_url); 
    unlink(__DIR__.'/'.$_FILES["fileToUpload"]["name"].'.csv');
    
}


die;
}

?>


<form action="convert.php" method="post" enctype="multipart/form-data">
  Select excel to upload:
  <input type="file" name="fileToUpload" id="fileToUpload">
  <input type="text" name="cot" id="cot" value="3">
  <input type="submit" value="Upload" name="submit">
</form>