How to import JSON data into Excel file with PHP and MySQL?

How to import JSON data into Excel file with PHP and MySQL?

Exporting data and saving it as an Excel file is a common task. In this article, we will learn how to import JSON data into Excel file using PHP and MySQL. To achieve this goal, we will use the PHPExcel library, which is a powerful and easy-to-use PHP library.

First, we need to install the PHPExcel library. PHPExcel can be downloaded from the official website (https://github.com/PHPOffice/PHPExcel). Unzip the PHPExcel library into your PHP project directory.

Next, we will create a PHP file and import the PHPExcel library.

<?php
require_once 'PHPExcel/Classes/PHPExcel.php';

We will then connect to the MySQL database and execute a SQL query to get the JSON data.

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT json_data FROM table";
$result = $conn->query($sql);

$data = array();

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $data[] = $row['json_data'];
    }
}

$conn->close();

Then we will create a PHPExcel object and add the JSON data to the worksheet.

$objPHPExcel = new PHPExcel();

$objPHPExcel->setActiveSheetIndex(0);

$worksheet = $objPHPExcel->getActiveSheet();

$worksheet->setTitle('Sheet1');

$row = 1;

foreach ($data as $json) {
    $dataArray = json_decode($json, true);
    
    foreach ($dataArray as $col => $value) {
        $worksheet->setCellValueByColumnAndRow($col, $row, $value);
    }
    
    $row++;
}

Finally, we will save the Excel file.

$filename = 'data.xlsx';

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

$objWriter->save($filename);

echo "Excel file generated successfully.";

Now, we can run this PHP script and export the JSON data to an Excel file.

Summary:

In this article, we learned how to import JSON data into an Excel file using PHP and MySQL. We used the PHPExcel library to handle the creation and export of Excel files. By connecting to the MySQL database, executing the query and getting the JSON data, we were able to add the data to an Excel worksheet and save it as an Excel file.

I hope this article will be helpful to you and enable you to better deal with data export problems encountered in actual development. If you have any questions, please feel free to leave a message.

The above is the detailed content of How to import JSON data into Excel file with PHP and MySQL?. For more information, please follow other related articles on the PHP Chinese website!