Click here to Skip to main content
14,601,157 members
Rate this:
Please Sign up or sign in to vote.
How to coding to support import Excel file to auto insert Excel data into PHPExcel+HTML input form (parse this response Excel data into desired fields)?

Because I try coding to

1. send file to PHP.
2. parse EXCEL file with third-party library.
3. create response for AJAX/POST into HTML page.

but can't to parse this response Excel data into desired fields , I have send file to php and autofill Excel data with same id to HTML input tag (example : autofill input id="fname" to Hello and autofill input id="lname" to World).

What I have tried:

Sample code and file at the bottom.

1. excelimport.php

<pre><!DOCTYPE html>
<html>
<head>
<style>
h2 {display: inline;}
</style>
<script>
var _validFileExtensions = [".xls", ".xlsx", ".csv"];    
function ValidateSingleInput(oInput) {
    if (oInput.type == "file") {
        var sFileName = oInput.value;
         if (sFileName.length > 0) {
            var blnValid = false;
            for (var j = 0; j < _validFileExtensions.length; j++) {
                var sCurExtension = _validFileExtensions[j];
                if (sFileName.substr(sFileName.length - sCurExtension.length, 

sCurExtension.length).toLowerCase() == sCurExtension.toLowerCase()) {
                    blnValid = true;
                    break;
                }
            }
             
            if (!blnValid) {
                alert("Sorry, " + sFileName + " is invalid, allowed extensions are: " + _validFileExtensions.join(", 

"));
                oInput.value = "";
                return false;
            }
        }
    }
    return true;
}
</script>
</head>
<body>

<?php
if(isset($_FILES['excel']) && $_FILES['excel']['error']==0) {
		require_once "PHPExcel/Classes/PHPExcel.php";
		$tmpfname = $_FILES['excel']['tmp_name'];
		$excelReader = PHPExcel_IOFactory::createReaderForFile($tmpfname);
		$excelObj = $excelReader->load($tmpfname);
		$worksheet = $excelObj->getSheet(0);
		$lastRow = $worksheet->getHighestRow();
		
		echo "<table class=\"table table-sm\">";
		for ($row = 1; $row <= $lastRow; $row++) {
			 echo "<tr><td scope=\"row\">";
			 echo $worksheet->getCell('A'.$row)->getValue();
			 echo "</td><td>";
			 echo $worksheet->getCell('B'.$row)->getValue();
			 echo "</td><td>";
			 echo $worksheet->getCell('C'.$row)->getValue();
			 echo "</td><td>";
			 echo $worksheet->getCell('D'.$row)->getValue();
			 echo "</td><tr>";
		}
		echo "</table>";	
}
?>

<form action = "" method = "POST" enctype = "multipart/form-data">
	<h2 for="myfile1">Select files : </h2>
         <input type = "file" name = "excel" onchange="ValidateSingleInput(this)" />
         <input type = "submit"/><br><br>
</form>
	
	<h2 for="fname">First name : </h2><input type="text" id="fname" name="fname" 



value=""><br><br>
	<h2 for="lname">Last name : </h2><input type="text" id="lname" name="lname" 



value=""><br><br>
	<input type="submit" name="submit2">

</body>
</html>


2. excelimport.xlsx - http://doanga2007.github.io/excelimport.xlsx

3. PHPExcel with download file at https://github.com/PHPOffice/PHPExcel/ and I have file structure to image at bottom.

https://i.imgur.com/I45WwXk.png

Screenshot.

https://i.imgur.com/bNhop9T.png
Posted
Updated 11-Mar-20 3:49am
v4

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Good news : I have answer to coding to support import Excel file to auto insert Excel data into PHPExcel+HTML input form (parse this response Excel data into desired fields) with my full source code.

I have answer credit with link.

1. excelimport.xlsx (Excel file)

Download Link

2. excelimport.php (PHP Code)

<!DOCTYPE html>  
<html>  
<head>  
    <meta charset="UTF-8">  
    <title>Document</title>
    <style>
    h2 {display: inline;}
    </style>
</head>

<body>

<form action="" method="post" enctype="multipart/form-data" name="myform1" id="myform1">
    <h2 for="myfile1">Select files : </h2><input type="file" name="excelFile" id="excelFile" /><br><br>
    <h2 for="fname">First name : </h2><input type="text" id="fname" name="fname"><br><br>
    <h2 for="lname">Last name : </h2><input type="text" id="lname" name="lname"><br><br>
  <input type="submit" name="btnSubmit" id="btnSubmit" value="Submit" />
</form>

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>     
<script type="text/javascript">
$(function(){


    // เมื่อฟอร์มการเรียกใช้ evnet submit ข้อมูล        
    $("#excelFile").on("change",function(e){
        e.preventDefault(); // ปิดการใช้งาน submit ปกติ เพื่อใช้งานผ่าน ajax

        // เตรียมข้อมูล form สำหรับส่งด้วย  FormData Object
       var formData = new FormData($("#myform1")[0]);

        // ส่งค่าแบบ POST ไปยังไฟล์ read_excel.php รูปแบบ ajax แบบเต็ม
        $.ajax({
            url: 'read_excel.php',
            type: 'POST',
            data: formData,
            /*async: false,*/
            cache: false,
            contentType: false,
            processData: false
        }).done(function(data){
                console.log(data);  // ทดสอบแสดงค่า  ดูผ่านหน้า console
/*              การใช้งาน console log เพื่อ debug javascript ใน chrome firefox และ ie 
                http://www.ninenik.com/content.php?arti_id=692 via @ninenik         */
                $("#fname").val(data.A2);
                $("#lname").val(data.B2);
        });     

    });


});
</script>
</body>
</html>


3. read_excel.php (PHP Code)

<?php
header("Content-type:application/json; charset=UTF-8");    
header("Cache-Control: no-store, no-cache, must-revalidate");         
header("Cache-Control: post-check=0, pre-check=0", false); 
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Asia/Bangkok');
// http://php.net/manual/en/timezones.php
require_once("PHPExcel/Classes/PHPExcel.php");
?>
<?php 
if(isset($_FILES['excelFile']['name']) && $_FILES['excelFile']['name']!=""){
    $tmpFile = $_FILES['excelFile']['tmp_name'];  
    $fileName = $_FILES['excelFile']['name'];  // เก็บชื่อไฟล์
    $_fileup = $_FILES['excelFile'];
    $info = pathinfo($fileName);
    $allow_file = array("csv","xls","xlsx");
/*  print_r($info);         // ข้อมูลไฟล์   
    print_r($_fileup);*/
    if($fileName!="" && in_array($info['extension'],$allow_file)){
        // อ่านไฟล์จาก path temp ชั่วคราวที่เราอัพโหลด
        $objPHPExcel = PHPExcel_IOFactory::load($tmpFile);      


        // ดึงข้อมูลของแต่ละเซลในตารางมาไว้ใช้งานในรูปแบบตัวแปร array
        $cell_collection = $objPHPExcel->getActiveSheet()->getCellCollection();

        // วนลูปแสดงข้อมูล
        $v=1;
        $json_data = array();
        foreach ($cell_collection as $cell) {
            // ค่าสำหรับดูว่าเป็นคอลัมน์ไหน เช่น A B C ....
            $column = $objPHPExcel->getActiveSheet()->getCell($cell)->getColumn();
            // คำสำหรับดูว่าเป็นแถวที่เท่าไหร่ เช่น 1 2 3 .....
            $row = $objPHPExcel->getActiveSheet()->getCell($cell)->getRow();
            // ค่าของข้อมูลในเซลล์นั้นๆ เช่น A1 B1 C1 ....
            $data_value = $objPHPExcel->getActiveSheet()->getCell($cell)->getValue();          

            // เท่านี้เราก็สามารถแสดงข้อมูลจากการอ่านไฟล์ได้แล้ว และสามารถนำข้อมูลเหล่านี้
            // ทำการบันทักลงฐานข้อมูล หรือแสดงได้เลย
            $json_data["$column$row"] = $data_value;
//            echo $v." ----  ".$data_value."<br>";
             $v++;
        }       
         // แปลง array เป็นรูปแบบ json string  
        if(isset($json_data)){  
            $json= json_encode($json_data);    
            if(isset($_GET['callback']) && $_GET['callback']!=""){    
            echo $_GET['callback']."(".$json.");";        
            }else{    
            echo $json;    
            }    
        }        
    }
} 
?>


I use PHPExcel library to download with link.
   
Comments
Richard Deeming 11-Mar-20 10:13am
   
You should probably read the "readme" on the library you've chosen:

"PHPExcel last version, 1.8.1, was released in 2015. The project was officially deprecated in 2017 and permanently archived in 2019.

The project has not be maintained for years and must not be used anymore. All users must migrate to its direct successor PhpSpreadsheet, or another alternative."

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100