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.