CARA EXPORT FILE MYSQL KE EXCEL

When working with databases, it is important to be able to export data to various formats such as Excel for presentation and sharing purposes. In this article, we will be discussing the process of exporting data from MySQL to Excel with PHP.

Exporting Data from MySQL to Excel

First, let’s discuss why we would need to export data from MySQL to Excel. Excel provides an easy-to-use platform for creating and visualizing data, making it a popular choice for data analysis. Additionally, Excel has the ability to handle large amounts of data in a single workbook, making it an ideal tool for working with databases.

In order to export data from MySQL to Excel, we need to use PHP to create an Excel file. PHP provides various libraries for working with Excel files, such as PHPExcel and PHPSpreadsheet. In this article, we will be using PHPExcel to create our Excel file.

Using PHPExcel to Export MySQL Data to Excel

Before we can begin exporting data from MySQL to Excel, we need to first create a database and table to work with. For the purpose of this article, we will be creating a table called “users” with the following columns:

• id
• first_name
• last_name
• email

Once we have our database and table set up, we can begin writing our PHP code to export the data. First, we need to include the PHPExcel library:

require_once 'PHPExcel/Classes/PHPExcel.php';

Next, we need to establish a connection to the database:

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

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

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

Now that we have a connection to our database, we can retrieve the data from our “users” table:

$sql = "SELECT id, first_name, last_name, email FROM users";
$result = $conn->query($sql);

Now that we have our data, we can begin creating our Excel file. First, we create a new PHPExcel object:

$excel = new PHPExcel();

Next, we set the properties for our Excel file, such as the author and title:

$excel->getProperties()
    ->setCreator("Your Name")
    ->setLastModifiedBy("Your Name")
    ->setTitle("Export MySQL to Excel")
    ->setSubject("Export MySQL to Excel")
    ->setDescription("Export MySQL to Excel")
    ->setKeywords("mysql,excel,export")
    ->setCategory("Export");

Now, we create a new worksheet in our Excel file:

$sheet = $excel->getActiveSheet();

$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'First Name');
$sheet->setCellValue('C1', 'Last Name');
$sheet->setCellValue('D1', 'Email');

Now, we iterate through our result set and add each row to our Excel file:

$row = 2;
while($data = $result->fetch_array()) 
    $sheet->setCellValue('A'.$row, $data['id']);
    $sheet->setCellValue('B'.$row, $data['first_name']);
    $sheet->setCellValue('C'.$row, $data['last_name']);
    $sheet->setCellValue('D'.$row, $data['email']);
    $row++;

Finally, we save our Excel file:

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->save('export.xlsx');

And that’s it! Our data has been exported from MySQL to Excel with PHP.

FAQ

Q: What are some other formats that we can export MySQL data to?

A: Besides Excel, MySQL data can also be exported to formats such as CSV, PDF, and JSON. Exporting data to different formats allows for greater flexibility and easier integration with other systems.

Q: Is there a limit to the amount of data that can be exported from MySQL to Excel?

A: Yes, there is a limit to the amount of data that can be exported from MySQL to Excel. Excel has a limit of 1,048,576 rows and 16,384 columns per worksheet. If your dataset exceeds these limits, you may need to split the data into multiple worksheets or consider exporting to a different format.

Conclusion

In conclusion, exporting data from MySQL to Excel with PHP is a simple and straightforward process that can be accomplished using libraries such as PHPExcel. By exporting data to Excel, we can create visually appealing and easy-to-read reports that can be easily shared and analyzed. With some knowledge of PHP and MySQL, anyone can export data to Excel and unlock the full potential of their data.