How to download an excel (.xls) file from a button in PHP

Question

deus d universo 0 Newbie Poster

1 Year Ago

So... I have a fully working code, but my boss asked me to add a button that downloads the table in excel format with the values from the database and I was like, I can do that?
I also asked chatGPT but it has a character limit so it never finished the code, sad.
I am still in school and had to learn PHP and MySQL from the scratch in the last 4 weeks, at least I knew a bit of HTML before.

//code

<?php

    $host = "localhost";
    $username = "root";
    $password = "";
    $dbname = "esercizi3";

    $con = mysqli_connect($host, $username, $password, $dbname);

    if (!$con)
    {
        die("Connection failed!" . mysqli_connect_error());
    }
    $sql = "SELECT
        *
    FROM
        courses";
    $result = $con->query($sql);
?>

<html>
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
    </head>
    <body>
        <?php
            if(!isset($_POST['delete']) && !isset($_POST['download']))
            {
        ?>
        <div style="width: 650px">
        <table class = "table table-bordered">
            <thead>
                <tr>
                    <td class="text-center" colspan="8">
                        <button type="button" name="download" class="btn btn-dark" style="height: 100%; width:33%;">Download Excel</button>
                        <a href="listStudent.php"><button type="button" class="btn btn-secondary" style="height: 100%; width:32%;">Students' List</button></a>
<?php //button ?>       <a href="addCourse.php"><button type="button" class="btn btn-primary" style="height: 100%; width:33%;">Add Course</button></a>
                    </td>
                </tr>
                <th class="text-center" colspan="8"><h2>Courses' List</h2></th>
                <tr>
                    <th class="text-center" scope="col">ID</th>
                    <th class="text-center" scope="col">Name</th>
                    <th class="text-center" scope="col">Students included</th>
                    <th class="text-center" class="text-center" colspan="2">Actions</th>
                </tr>
            </thead>
            <tbody>
            <?php
                while($row = $result->fetch_assoc())
                {
                    $ID_c = $row["ID_c"];
            ?>
                <tr>
                    <td class="text-center"><?php echo $row["ID_c"]; ?></td>
                    <td class="text-center"><?php echo $row["name_c"]; ?></td>
                    <?php
                        $sql2 = "SELECT
                            *
                        FROM
                            studentsCourses
                        WHERE
                            ID_course_sc = $ID_c";
                        $result2 = $con->query($sql2);
                    ?>
                    <td class="text-center">
                        <?php
                    echo "<ul>";
                            while($row2 = $result2->fetch_assoc())
                            {
                                $ID_student = $row2["ID_student_sc"];
                                $sql3 = "SELECT
                                    *
                                FROM
                                    students
                                WHERE
                                    ID = $ID_student";
                                $result3 = $con->query($sql3);
                                $row3 = $result3->fetch_assoc();
                                echo "<li>" .$row3["f_name"]. " " .$row3["l_name"]. "</li>";
                            }
                    echo "</ul>";
                        ?>
                    </td>
                    <td class="text-center">
                        <form action="editCourse.php" method="POST">
                            <input type="hidden" id="submit" name="ID_c" value="<?php echo $row['ID_c'];?>">
                            <button type="submit" class="btn btn-success" style="height: 100%; width:100%;">EDIT</button>
                        </form>
                    </td>
                    <td class="text-center">
                        <form method="POST">
                            <input type="hidden" id="ID_c" name="ID_c" value="<?php echo $row['ID_c'];?>">
                            <button name="delete" type="submit" class="btn btn-danger" style="height: 100%; width:100%;">DELETE</button>
                        </form>
                    </td>
                </tr>
            <?php } ?>
            </tbody>
        </table>
        </div>

        <?php
            }else
            {
                $ID_c = $_POST['ID_c'];
                $sql = "
                DELETE FROM 
                    courses
                WHERE 
                    ID_c = $ID_c";

                if ($con->query($sql) === TRUE) {
                    //entry deleted
                } else {
                    //error
                }
                $con->close();
                header('Location: http://localhost/esercizi3/listCourse.php');
            }
        ?>
    </body>
</html>

php

Edited 1 Year Ago by deus d universo because: to add information

2 Contributors
5 Replies
1K Views
1 Day Discussion Span
Latest Post 1 Year Ago Latest Post by Dani

Dani 4,329 The Queen of DaniWeb

1 Year Ago

So sorry for the triple posts! I also just want to clarify that this method lets you send a comma-delimited text file to the web browser for downloading, which can easily be opened by any version of Microsoft Excel or any other spreadsheet program. It does not write an Excel file, specifically. For that, there are a handful of third-party PHP libraries for creating Excel files. Here is a link to one of them, PHPSpreadsheet.

deus d universo commented: Right now, I have the phpspreadsheet installed (I think, it's strange to do it on mac) but I don't know how to proceed, I'll search a bit more, thanks +0

Dani 4,329 The Queen of DaniWeb

1 Year Ago

As I have no experience with phpSpreadsheet, any advice I offer would be the blind leading the blind. However, they do have official documentation here which includes this simple demo (copied and pasted from that page):

<?php

require 'vendor/autoload.php';

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

$spreadsheet = new Spreadsheet();
$activeWorksheet = $spreadsheet->getActiveSheet();
$activeWorksheet->setCellValue('A1', 'Hello World !');

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

It seems pretty feature rich, but I wonder if it's overkill for your needs. Is there a reason that the CSV format wouldn't work for you?

deus d universo commented: it would probably work for me, but my internship boss told me to use PHPExcel, but since its been permanently archived in 2019 Im using the"successor" +0

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Right now, I have the phpspreadsheet installed (I think, it's strange to do it on mac) but I don't know how to proceed, I'll search a bit more, thanks
it would probably work for me, but my internship boss told me to use PHPExcel, but since its been permanently archived in 2019 Im using the"successor"

Dani 4,329 The Queen of DaniWeb Administrator Featured Poster Premium Member · Answer 1 · 2023-03-21T16:55:58+00:00

You can use PHP to generate a text string of comma-delimited table columns (a .csv file) that can then be opened by Microsoft Excel or any other spreadsheet program.

You would start by populating a multidimensional PHP array with the table columns instead of spitting them out as HTML. Then, use fputcsv() to convert that array to a CSV file to be downloaded by the web browser. I’m on my phone right now typing this out, but I’ll give you a better code example in a couple of hours if you still need assistance.

Dani 4,329 The Queen of DaniWeb Administrator Featured Poster Premium Member · Answer 2 · 2023-03-21T19:36:51+00:00

OK, so I'm at a computer now, and I see that the official PHP documentation I linked you to in my previous post already includes a simple example of how it writes to a *.csv file one record at a time.

I'm using this functionality within my own code, and here's what I have (for my own use case):

// Start output buffering
ob_start();

// Write to the output buffer
$data = fopen('php://output', 'w');

// Loop through each record
for ($i = 0; $i < count($array); $i++)
{
    fputcsv($data, $array[$i]);
}

fclose($data);

// Retrieve contents of the buffer
$output = ob_get_clean();

Here is more information on why I'm using php://output. I'm doing it this way so there's no overhead of writing to a file in the file system, and then retrieving it for download.

deus d universo 0 Newbie Poster · Answer 3 · 2023-03-22T11:10:15+00:00

I want to do it with php Spreadsheet but I have no clue on how to do it and every second that passes I get more confused, can you explain step by step how to proceed? (It would be better if you updated the code too, but an explanation helps, I think)
And thank you for your replies.