article

Monday, March 28, 2022

ReactJS PHP Mysqli Export Data Table into EXCEL Sheet

ReactJS PHP Mysqli Export Data Table into EXCEL Sheet


Install bootstrap

npm install react-bootstrap bootstrap@5.1.3

C:\reactdev\myreactdev>npm install react-bootstrap bootstrap@5.1.3

https://react-bootstrap.github.io/getting-started/introduction/

ReactHTMLTableToExcel
Provides a client side generation of Excel (.xls) file from HTML table element.

react-html-table-to-excel
https://www.npmjs.com/package/react-html-table-to-excel

Install
npm install --save react-html-table-to-excel
C:\reactdev\myreactdev>npm install --save react-html-table-to-excel

run
C:\reactdev\myreactdev>npm start

Database table

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `position` varchar(100) NOT NULL,
  `office` varchar(100) NOT NULL,
  `age` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `date_of_join` date NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `employee` (`id`, `name`, `position`, `office`, `age`, `salary`, `date_of_join`) VALUES
(1, 'Tiger Wood', 'Accountant', 'Tokyo', 36, 5689, '2020-01-09'),
(2, 'Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London', 56, 5648, '2020-02-15'),
(3, 'Jacob thompson', 'Junior Technical Author', 'San Francisco', 23, 5689, '2020-03-01'),
(4, 'cylde Ednalan', 'Software Engineer', 'Olongapo', 23, 54654, '2020-01-24'),
(5, 'Rhona Davidson', 'Software Engineer', 'San Francisco', 26, 5465, '2020-01-11'),
(6, 'Quinn Flynn', 'Integration Specialist', 'New York', 53, 56465, '2020-02-23'),
(8, 'Tiger Nixon', 'Software Engineer', 'London', 45, 456, '2020-03-04'),
(9, 'Airi Satou updated', 'Pre-Sales Support updated', 'New York', 25, 4568, '2020-04-28'),
(10, 'Angelica Ramos updated', 'Sales Assistant updated', 'New York', 45, 456, '2020-01-12'),
(11, 'Ashton updated', 'Senior Javascript Developer', 'Olongapo', 45, 54565, '2020-02-06'),
(12, 'Bradley Greer', 'Regional Director', 'San Francisco', 27, 5485, '2020-03-21'),
(13, 'Brenden Wagner', 'Javascript Developer', 'San Francisco', 38, 65468, '2020-04-14'),
(14, 'Brielle Williamson', 'Personnel Lead', 'Olongapo', 56, 354685, '2020-01-29'),
(15, 'Bruno Nash', 'Customer Support', 'New York', 36, 65465, '2020-02-22'),
(16, 'cairocoders', 'Sales Assistant', 'Sydney', 45, 56465, '2020-03-10'),
(17, 'Zorita Serrano', 'Support Engineer', 'San Francisco', 38, 6548, '2020-04-26'),
(18, 'Zenaida Frank', 'Chief Operating Officer (COO)', 'San Francisco', 39, 545, '2020-01-17'),
(19, 'Sakura Yamamoto', 'Support Engineer', 'Tokyo', 48, 5468, '2021-02-01'),
(20, 'Serge Baldwin', 'Data Coordinator', 'Singapore', 85, 5646, '2021-03-19'),
(21, 'Shad Decker', 'Regional Director', 'Tokyo', 45, 4545, '2022-03-09');

ALTER TABLE `employee`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `employee`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;


?
1
2
3
4
5
6
7
8
9
10
11
12
//src/index.js
import React from 'react';
import ReactDOM from 'react-dom';
import App from './App';
import 'bootstrap/dist/css/bootstrap.min.css'
import 'bootstrap/dist/js/bootstrap.min.js'
 
ReactDOM.render(
  <app>,
  document.getElementById('root')
)
</app>
public/index.html
?
1
2
3
4
5
6
7
8
9
10
11
12
//public/index.html
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <title>ReactJS </title>
  </head>
  <body>
    <div id="root"></div>
</body>
</html>
src/App.js
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
//src/App.js
import {useState,useEffect} from 'react';
import ReactHTMLTableToExcel from 'react-html-table-to-excel';
   
function App(){
  
    const[result,setResult]= useState([]);
  
    const getData = ()=>
    {
        fetch('http://localhost/devtest/reactjs/employee.php')
        .then(response => response.json())
        .then(res => setResult( res));
    }
     
    useEffect(() => {
        getData();
    },)
  
        return (
            <div className="container">
                <h3 className="mt-3 text-success"><center>ReactJS PHP Mysqli Export Data Table into EXCEL Sheet</center></h3>
                <div className="row mt-4">
                <ReactHTMLTableToExcel
                    id="test-table-xls-button"
                    className="download-table-xls-button btn btn-success mb-3"
                    table="table-to-xls"
                    filename="tablexls"
                    sheet="tablexls"
                    buttonText="Export Data to Excel Sheet"/>
                   <table className="table" id="table-to-xls">
                    <thead className="thead-dark">
                    <tr>
                        <th>Name</th>
                        <th>Position</th>
                        <th>Office</th>
                        <th>Age</th>
                        <th>Salary</th>
                        <th>Date Hire</th>
                    </tr>
                    </thead>
                    <tbody>
                    
                         {result.map((res)=>
                            <tr>
                            <td>{res.name}</td>
                            <td>{res.position}</td>
                            <td>{res.office}</td>
                            <td>{res.age}</td>
                            <td>{res.salary}</td>
                            <td>{res.date_of_join}</td>
                            </tr>
                          )}  
                        
                    </tbody>  
                </table>
             </div>
            </div>
        );
    }
   
export default App
employee.php http://localhost/devtest/reactjs/employee.php
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
//employee.php http://localhost/devtest/reactjs/employee.php
<?php
header("Access-Control-Allow-Origin: *"); //add this CORS header to enable any domain to send HTTP requests to these endpoints:
$host = "localhost";
$user = "root";
$password = "";
$dbname = "testingdb";
 
$con = mysqli_connect($host, $user, $password,$dbname);
 
$method = $_SERVER['REQUEST_METHOD'];
 
 
if (!$con) {
  die("Connection failed: " . mysqli_connect_error());
}
 
 
switch ($method) {
    case 'GET':
        $sql = "select * from employee";
      break;
}
 
// run SQL statement
$result = mysqli_query($con,$sql);
 
// die if SQL statement failed
if (!$result) {
  http_response_code(404);
  die(mysqli_error($con));
}
 
if ($method == 'GET') {
    echo '[';
    for ($i=0 ; $i<mysqli_num_rows($result) ; $i++) {
      echo ($i>0?',':'').json_encode(mysqli_fetch_object($result));
    }
    echo ']';
}else {
    echo mysqli_affected_rows($con);
}
 
$con->close();

Posted by ednalan at 12:00 AM . Under .

Related Post