I am using an excel file as an import. After importing I want to save it in my database. The output is below
array(20) { [1]=> array(4) { ["A"]=> string(6) "ref_no" ["B"]=> string(9) "meter_msn" ["C"]=> string(10) "meter_type" ["D"]=> string(7) "sub_div" } [2]=> array(4) { ["A"]=> string(15) "20371110314300U" ["B"]=> string(12) "002999000071" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [3]=> array(4) { ["A"]=> string(15) "28371120086881U" ["B"]=> string(12) "002999000197" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [4]=> array(4) { ["A"]=> string(15) "28371120086883U" ["B"]=> string(12) "002999000008" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [5]=> array(4) { ["A"]=> string(15) "20371121675600U" ["B"]=> string(12) "002999000161" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [6]=> array(4) { ["A"]=> string(15) "20371121673500U" ["B"]=> string(12) "002999000170" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [7]=> array(4) { ["A"]=> string(15) "28371121678000U" ["B"]=> string(12) "002999000061" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [8]=> array(4) { ["A"]=> string(15) "28371120086882U" ["B"]=> string(12) "002999000181" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [9]=> array(4) { ["A"]=> string(15) "20371121674600U" ["B"]=> string(12) "002999000054" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [10]=> array(4) { ["A"]=> string(15) "28371110000050U" ["B"]=> string(12) "002999000003" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [11]=> array(4) { ["A"]=> string(15) "28371110312810U" ["B"]=> string(12) "002999000225" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [12]=> array(4) { ["A"]=> string(15) "04371110173300U" ["B"]=> string(12) "002999000065" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [13]=> array(4) { ["A"]=> string(15) "28371110185300U" ["B"]=> string(12) "002999000091" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [14]=> array(4) { ["A"]=> string(15) "28371110249803U" ["B"]=> string(12) "002999000002" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [15]=> array(4) { ["A"]=> string(15) "28371110079780U" ["B"]=> string(12) "002999000073" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [16]=> array(4) { ["A"]=> string(15) "20371110148700U" ["B"]=> string(12) "002999000070" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [17]=> array(4) { ["A"]=> string(15) "28371110173200U" ["B"]=> string(12) "002999000179" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [18]=> array(4) { ["A"]=> string(15) "28371110000236U" ["B"]=> string(12) "002999000272" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [19]=> array(4) { ["A"]=> string(15) "28371110000235U" ["B"]=> string(12) "002999000084" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } [20]=> array(4) { ["A"]=> string(15) "28371110883210U" ["B"]=> string(12) "002999000082" ["C"]=> string(7) "L.T.TOU" ["D"]=> float(37111) } }
Below is the code
public function actionExcel(){
$file_name = "excel_" . Yii::$app->user->id . ".xlsx";
$error = "";
if(isset($_FILES['file'])) {
$path_parts = pathinfo($_FILES["file"]["name"]);
$extension = $path_parts['extension'];
if(!in_array($extension,['xlsx','xls'])){
$error = "Invalid file";
}else {
if (move_uploaded_file($_FILES['file']['tmp_name'], 'uploads/' . $file_name)) {
$this->redirect(Url::to('process?file_name=' . $file_name . "&header_no=" . $_POST['header_no']));
}
}
}
return $this->render("excel",['error'=>$error]);
}
public function actionProcess(){
$file_name = $_GET['file_name'];
// $data = \moonland\phpexcel\Excel::import("uploads/test.xlsx"); // $config is an optional
try {
$header_index = $_GET['header_no'];
$data = \moonland\phpexcel\Excel::widget([
'mode' => 'import',
'fileName' => 'uploads/' . $file_name,
'setFirstRecordAsKeys' => false, // if you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel.
'setIndexSheetByName' => false, // set this if your excel data with multiple worksheet, the index of array will be set with the sheet name. If this not set, the index will use numeric.
'getOnlySheet' => 0, // you can set this property if you want to get the specified sheet from the excel data with multiple worksheet.
]);
if (isset($data[0])) {
$headers = $data[0][$header_index];
} else {
$headers = $data[$header_index];
}
}catch (Exception $x){
print_r($x->errorInfo);
}
return $this->render('excel_options',['headers'=>$headers,'file_name'=>$file_name,'header_index'=>$header_index]);
}
public function actionImport()
{
$file_name = $_POST['file_name'];
$header_index = $_POST['header_index'];
$fieldSet = $_POST['field'];
$data = \moonland\phpexcel\Excel::widget([
'mode' => 'import',
'fileName' => 'uploads/' . $file_name,
'setFirstRecordAsKeys' => false, // if you want to set the keys of record column with first record, if it not set, the header with use the alphabet column on excel.
'setIndexSheetByName' => false, // set this if your excel data with multiple worksheet, the index of array will be set with the sheet name. If this not set, the index will use numeric.
'getOnlySheet' => 0, // you can set this property if you want to get the specified sheet from the excel data with multiple worksheet.
]);
//loop therogh first sheet
$ok_count = 0;
$status_arr = [];
$final_data = isset($data[0]) ? $data[0] : $data;
$issued_to = 0;
$id = 0;
$operator ="";
$pupose="";
$loopcount=0;
var_dump($final_data);
die();
}
In the above code $final_data is what I have written the output at the start of the question. I have used php excel for it.
How can I read the excel file and then save the data into database?
Any help would be highly appreicated.
