EXCEL to JSON Converter using PHP

How to Convert xlsx file to JSON format?

We are converting Excel file to JSON using PHPExcel library. PHPExcel library has working with SpreadSheet files. PHPExcel is a very stable library and simple in use. You just need a Download library and add it with your PHP Code.

How to Download PHPExcel Library?

There are many options available for download PHPExcel library. Checkout below links:

Flowchart:

How to read data of Excel file?

require_once "Classes/PHPExcel.php";
$excelReader = PHPExcel_IOFactory::createReaderForFile($file);
$excelObj = $excelReader->load($file);

Get Sheet Counts and their names:

$sheetCount = $excelObj->getSheetCount();
$sheetNames = $excelObj->getSheetNames();

Get Highest Row Number of Sheet:

$lastRow = $worksheet->getHighestRow();

Get Highest Column of Sheet:

$lastCol = $worksheet->getHighestColumn();

PHPExcel Column Loop:

for ($col = 'A'; $col <= $lastCol; $col++) {
    // code
}

FULL SOURCE CODE:

index.html
<html>
    <head>
        <script>
            function getJSON() {
                if(document.querySelector('#excelfile').files.length == 0) {
                    alert('Error : No file selected');
                    return;
                }

                var data = new FormData();
                data.append('file', document.querySelector('#excelfile').files[0]);
                var request = new XMLHttpRequest();
                request.open('post', 'exceltojson.php?action=filedata');
                request.addEventListener('load', function(e) {
                    var obj = JSON.parse(this.responseText);
                    var str1 = JSON.stringify(obj, undefined, 4);
                    output(syntaxHighlight(str1));
                    document.querySelector('pre').style.maxHeight = "280px";
                    document.querySelector('pre').setAttribute("id", "pre");
                    
                    document.getElementById('drag_name').innerText = "Drag your files here or click";
                    document.querySelector('#excelfile').value = '';
                });
                request.send(data);
            }
            
            function checkfile() {
                if(document.querySelector('#excelfile').files.length == 0) {
                        document.getElementById('drag_name').innerText = "Drag your files here";
                    } else {
                        document.getElementById('drag_name').innerText = "1 file has selected";
                    }
            }
            
            function syntaxHighlight(json) {
                json = json.replace(/&/g, '&').replace(/</g, '<').replace(/>/g, '>');
                return json.replace(/("(\\u[a-zA-Z0-9]{4}|\\[^u]|[^\\"])*"(\s*:)?|\b(true|false|null)\b|-?\d+(?:\.\d*)?(?:[eE][+\-]?\d+)?)/g, function (match) {
                    var cls = 'number';
                    if (/^"/.test(match)) {
                        if (/:$/.test(match)) {
                            cls = 'key';
                        } else {
                            cls = 'string';
                        }
                    } else if (/true|false/.test(match)) {
                        cls = 'boolean';
                    } else if (/null/.test(match)) {
                        cls = 'null';
                    }
                    return '<span class="' + cls + '">' + match + '</span>';
                });
            }
            
            function output(inp) {
                document.getElementById('result').appendChild(document.createElement('pre')).innerHTML = inp;
            }
        </script>
    </head>
    <body>
        <h1>Excel to JSON Converter</h1>
        <div class="form-group">
            <label>Upload File:</label>
            <form>
                <input id="excelfile" type="file" onchange="checkfile()">
                <p id="drag_name">Drag your files here</p>
            </form>
        </div>
        <button class="btn btn-primary" type="submit" onclick="getJSON()">CONVERT</button>
        <div class="form-group">
            <label>Result:</label>
            <div id="result" class="prettyprint" style="min-height: 40vh;max-height:300px;"></div>
        </div>
    </body>
</html>
exceltojson.php
<?php
if (isset($_FILES["file"])) {
    $file = "files/".basename($_FILES["file"]["name"]);
    move_uploaded_file($_FILES["file"]["tmp_name"], $file);
    
    require_once "Classes/PHPExcel.php";
    $excelReader = PHPExcel_IOFactory::createReaderForFile($file);
    $excelObj = $excelReader->load($file);
    $sheetCount = $excelObj->getSheetCount();
    $sheetNames = $excelObj->getSheetNames();
    
    $result = [];
    for ($sheet = 0; $sheet < $sheetCount; $sheet++) {
        $worksheet = $excelObj->getSheet($sheet);
        $lastRow = $worksheet->getHighestRow();
        $lastCol = $worksheet->getHighestColumn();
        
        $data = [];
        for ($row = 2; $row <= $lastRow; $row++) {
            $output = Array();
            for ($col = 'A'; $col <= $lastCol; $col++) {
                if ($worksheet->getCell($col.'1')->getValue() !== null && $worksheet->getCell($col.'1')->getValue() !== "") {
                    $output[$worksheet->getCell($col.'1')->getValue()] = $worksheet->getCell($col.$row)->getValue();
                }
            }
            $data[] = $output;
        }
        $result[$sheetNames[$sheet]] = $data;
    }
    echo json_encode($result);
}
?>