How to export excel data by sheet name into json file in php
Hi guys, in this post, you will be learning how to export data by sheet names (multiple sheet names / tabs ) from excel and convert them into json format and then store in a dot json file in php using php-spreadsheet package. ( https://phpspreadsheet.readthedocs.io/ )
Example: here are 2 sheet names in excel. explained below in image.
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 by sheet name 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 by sheet name 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);
$usersData = $spreadsheet->getSheetByName('users')->toArray();
$hobbiesData = $spreadsheet->getSheetByName('hobbies')->toArray();
$userResponseData = [];
$count1 = "0";
foreach($usersData as $row)
{
if($count1 > 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
{
$count1 = "1";
}
}
$hobbiesResponseData = [];
$count2 = "0";
foreach($hobbiesData as $row)
{
if($count2 > 0 && $row['0'] != '' && $row['1'] != '')
{
$user_id = $row['0'];
$hobby = $row['1'];
$hobbiesObj = [
'user_id' => $user_id,
'hobby' => $hobby
];
array_push($hobbiesResponseData, $hobbiesObj);
$msg = true;
}
else
{
$count2 = "1";
}
}
$finalData = [
'users'=> $userResponseData,
'hobbies'=> $hobbiesResponseData
];
$newJsonString = stripslashes(json_encode($finalData));
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.