How to export data from excel into json file in php | How to convert Excel to JSON

17-03-2023 | Share: Whatsapp

How to export data from excel into json file in PHP | How to convert Excel to JSON

How do I export data from excel sheet to JSON file?

Hi guys, in this post, you will be learning how to export excel file data's into json file in PHP using PHP-Spreadsheet-package. ( https://phpspreadsheet.readthedocs.io/ )

Example: Convert excel data to json. ( Excel file to JSON File )

Step 1: Install the Package via composer.

To install the package, open the terminal and change the directory (using cd command) to your folder.

$ composer require phpoffice/phpspreadsheet

After successful installation of this package, you will find few files in your folder like vendor, composer.json, etc.

Step 2: Create a file named index.php

<?php session_start(); ?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>How to Export Excel Data into json file in PHP</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
    
    <div class="container">
        <div class="row">
            <div class="col-md-12 mt-4">

                <?php
                if(isset($_SESSION['message']))
                {
                    echo "<h6 class='alert alert-success'>".$_SESSION['message']."</h6>";
                    unset($_SESSION['message']);
                }
                ?>

                <div class="card">
                    <div class="card-header">
                        <h4>How to Export Excel Data into json file in PHP</h4>
                    </div>
                    <div class="card-body">

                        <form action="code.php" method="POST" enctype="multipart/form-data">

                            <label>Upload Excel Sheet (Format - xlsx, xls, csv ) </label>
                            <input type="file" name="import_file" class="form-control" />
                            <button type="submit" name="save_excel_data" class="btn btn-primary mt-3">Import</button>

                        </form>

                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

Step 3: Lets create a file named users.json file ( in the same folder ) to store the excel data before upload excel sheet.

Step 4: Create a code.php file and paste the below code:

<?php
session_start();

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


$jsonFilePath = "users.json";

if(isset($_POST['save_excel_data']))
{
    $fileName = $_FILES['import_file']['name'];
    $file_ext = pathinfo($fileName, PATHINFO_EXTENSION);

    $allowed_ext = ['xls','csv','xlsx'];

    if(in_array($file_ext, $allowed_ext))
    {
        $inputFileNamePath = $_FILES['import_file']['tmp_name'];
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileNamePath);

        $data = $spreadsheet->getActiveSheet()->toArray();
        // var_dump($data);

        $userResponseData = [];
        $count = "0";
        foreach($data as $row)
        {
            if($count > 0 && $row['0'] != '' && $row['1'] != '' && $row['2'] != '' && $row['3'] != '')
            {
                $user_id = $row['0'];
                $first_name = $row['1'];
                $last_name = $row['2'];
                $email = $row['3'];
                $phone = $row['4'];

                $myUserObj = [
                    'user_id' => $user_id,
                    'first_name' => $first_name,
                    'last_name' => $last_name,
                    'email' => $email,
                    'phone' => $phone
                ];
                array_push($userResponseData, $myUserObj);
                $msg = true;
            }
            else
            {
                $count = "1";
            }
        }

        $usersArrayList = [
            'users'=> $userResponseData
        ];
        $newJsonString = stripslashes(json_encode($usersArrayList));
        file_put_contents($jsonFilePath, $newJsonString);

        if(isset($msg))
        {
            $_SESSION['message'] = "Excel Imported to JSON Successfully";
            header('Location: index.php');
            exit(0);
        }
        else
        {
            $_SESSION['message'] = "Something Went Wrong!";
            header('Location: index.php');
            exit(0);
        }
    }
    else
    {
        $_SESSION['message'] = "Invalid File";
        header('Location: index.php');
        exit(0);
    }
}

?>

Now you can start uploading / importing the excel file to convert excel data into json and store in json file in php.

Thanks for reading.