In this tutorial you can find the solution of How to Export or Download the HTML Table Data in Excel Sheet by using JavaScript. Exporting Data to Excel is required feature in our web application. Because by export data functionality will helps to download data from web application to different file format for offline use of data and for this excel format is an ideal for exporting data in file for offline use. There many tutorial we have published for export data to excel at server side scripting using PHP. But if we can perform at client-side for export data into Excel sheet, so it will reduce load on server. So for this for export data to excel , here we will use JavaScript for perform client-side export data to excel sheet.
The client-side export feature will makes our web application more user-friendly. So with the help of JavaScript, we can export HTML table data to Excel format without refresh of web page. Under this tutorial, you can learn How to export HTML table data to excel using JavaScript. In this tutorial, we will use SheetJS JavaScript Library for export HTML table data to Excel.
Steps to Export HTML Table Data to Excel using JavaScript
HTML Table Data:
JavaScript Code:
1. HTML Table Data
For Export HTML data to Excel, here first we have to load some data in HTML table. So here we have make fetch employee table data and load in HTML table with table column like name, address, gender, designation and age. Here we have create HTML table with id employee_data. So this id we will use for fetch this HTML table data in JavaScript code. Under this HTML code we have make one button tag with id export_button, so when use has click on this button, then HTML table data will be download in Excel file format without refresh of web page using JavaScript.
<?php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
$query = "SELECT * FROM tbl_employee ORDER BY name ASC";
$result = $connect->query($query);
?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8" />
<title>Export HTML table data to excel using JavaScript</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
<script type="text/javascript" src="https://unpkg.com/xlsx@0.15.1/dist/xlsx.full.min.js"></script>
</head>
<body>
<div class="container">
<h2 class="text-center mt-4 mb-4">Export HTML table data to excel using JavaScript</h2>
<div class="card">
<div class="card-header">
<div class="row">
<div class="col col-md-6">Sample Data</div>
<div class="col col-md-6 text-right">
<button type="button" id="export_button" class="btn btn-success btn-sm">Export</button>
</div>
</div>
</div>
<div class="card-body">
<table id="employee_data" class="table table-striped table-bordered">
<tr>
<th>Name</th>
<th>Address</th>
<th>Gender</th>
<th>Designation</th>
<th>Age</th>
</tr>
<?php
foreach($result as $row)
{
echo '
<tr>
<td>'.$row["name"].'</td>
<td>'.$row["address"].'</td>
<td>'.$row["gender"].'</td>
<td>'.$row["designation"].'</td>
<td>'.$row["age"].'</td>
</tr>
';
}
?>
</table>
</div>
</div>
</div>
</body>
</html>
2. JavaScript Code
In this tutorial, we have use SheetJS JavaScript Library for export HTML table data to Excel using JavaScript. So first we have to include following SheetJS library link at header of this HTML web page.
In JavaScript code part, first we have make html_table_to_excel(type) function. This function has use SheetJS Library function and convert or Write HTML table data to Excel format and download in browser without refresh of web page.
Once function is ready then we have to called html_table_to_excel(type) function on button click event, so for trigger button click event, we have use addEventListener method. So when user has click on button then html_table_to_excel(type) function has been called with xlsx file type. So it will download HTML table data in .xlsx format Excel file in browser without refresh of web page at client-side.
function html_table_to_excel(type)
{
var data = document.getElementById('employee_data');
var file = XLSX.utils.table_to_book(data, {sheet: "sheet1"});
XLSX.write(file, { bookType: type, bookSST: true, type: 'base64' });
XLSX.writeFile(file, 'file.' + type);
}
const export_button = document.getElementById('export_button');
export_button.addEventListener('click', () => {
html_table_to_excel('xlsx');
});
Conclusion
This tutorial will helps you to add export feature of download HTML table data in Excel sheet without using third-party jQuery plugin or any server-side script. By follow this tutorial you can easily export HTML table data to Excel using minimal JavaScript code.
If you want to get complete source with .sql file, so please write your email address in comment box. We will send you complete source code file at your define email address.
This is one more post on Yajra Laravel Datatables package and in this post, we will learn How to add export buttons to Yajra Laravel Datatables package. If you are using Laravel framework as your web application development task. So, in Laravel framework if you want to use jQuery Datatable plugin for display data on web page in tabular format with searching of data, pagination and data sorting feature. For all those things you have to use Yajra Datatables package in Laravel framework. By using this package you can perform server-side processing of searching, sorting and pagination of data without writing any line of code.
So, If you want to export Yajra DataTable data in excel or csv file format, then you have to add csv and excel export button in your DataTable. In Laravel framework, Yajra Datatables package has provide DataTable Buttons plugin for add export button in your Laravel Datatable. After adding of export button, you can easily export your Laravel DataTable data to CSV file or Excel sheet file format without writing any line of code. So, If want to learn How to add export button in Yajra Laravel Datatable, then you have to follow below step. After following below steps you can easily integrate csv excel export button with your Yajra Laravel Datatable and you can simply export your datatable data in csv or excel file format.
Download Latest Version of Laravel Framework
Make MySql Database Connection
Generate Fake Data
Install Yajra DataTables Package
Create DataTable Class
Create Controller Class
Create View Blade File
Set Route
Run Laravel Application
1 - Download Latest Version of Laravel Framework
First we have to download latest version of Laravel framework, So If you have use window OS then you have to go command prompt in which Composer application must be installed and then after write following command.
This command will make export folder in define directory, download and install latest version of Laravel framework.
2 - Make MySql Database Connection
After downloading of Laravel framework, first want to make database connection. For this we have to open Laravel framework .env file and in that file we have to define Mysql database configuration.
After making of Mysql Database connection, now we want to make table in mysql databaes connection and then after we want to fill that table with fake data. For this we have to follow following steps.
Create Model Class
Migrate Table Defination to Database
Create Factory Class
Create Seed Class
Create Model Class
Here we will use Model class for perform database related operation. For this, we have to create model class file. For this we have to go command prompt and write following command.
php artisan make:model Export -m
This command will make Export.php model class file in app folder. In this file we have to define table column name which you can find below.
app/Export.php
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Export extends Model
{
protected $fillable = ['firstName', 'lastName'];
}
Migrate Table Defination to Database
When we have create model class, then at that time that model class migration table file has been created in database/migrations folder. In that file, we have to define table column defination which you can seen below.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateExportsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('exports', function (Blueprint $table) {
$table->id();
$table->string('firstName');
$table->string('lastName');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('exports');
}
}
After define table defination, now we want to migrate this table defination to mysql database and create exports table. For this we have go to command prompt and write following command.
php artisan migrate
Above command will make exports table in Mysql database. So, this way we can create table in mysql database from Laravel our laravel application.
Create Factory Class
For generate fake data and fill mysql exports table with fill data, we have to create one factory class. This class will allowed you to build fake data for your modelds. For create model factories, we have to go command prompt and write following command.
php artisan make:factory ExportFactory
This command will create model factories with name ExportFactory.php in database/factories folder. In this file, first we have import our model class and then after we have to define in which mysql table column we have to define fake data column name which can find below.
database/factories/ExportFactory.php
<?php
/** @var \Illuminate\Database\Eloquent\Factory $factory */
use App\Model;
use Faker\Generator as Faker;
use App\Export;
$factory->define(App\Export::class, function (Faker $faker) {
return [
'firstName' => $faker->firstName,
'lastName' => $faker->lastName
];
});
Create Seed Class
In Laravel Seed classes is used for seeding your database with test data. In Laravel framework all seed classes has been store in database/seeds folder. For create seed class, we have to command prompt and write following command.
php artisan make:seeder ExportTableSeeder
This command will create ExportTableSeeder.php seed class in database/seeds directory. In this class first we want to import our model class then after in this class you can find run() and under this method we can define how many number of fake data has been genrated by using model factory class. You can find source below it.
database/seeds/ExportTableSeeder.php
<?php
use Illuminate\Database\Seeder;
use App\Export;
class ExportTableSeeder extends Seeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
$count = 500;
factory(Export::class, $count)->create();
}
}
After this we have to open database/seeds/DatabaseSeeder.php file which is already created under this database/seeds directory. So we have to open this file and under this file we can find run() method and under that method we have to called ExportTableSeeder class.
database/seeds/DatabaseSeeder.php
<?php
use Illuminate\Database\Seeder;
class DatabaseSeeder extends Seeder
{
/**
* Seed the application's database.
*
* @return void
*/
public function run()
{
// $this->call(UserSeeder::class);
$this->call(ExportTableSeeder::class);
}
}
After this for generate fake data, we have to go command prompt and write following command.
composer dump-autoload
This command will regenerates the list of all classed which we have included in this Laravel project. Running of this command is required for generate fake data. And lastly for generate fake data we have to run following command.
php artisan db:seed
This command will seed your test data and it will fill exports mysql table with fake data. So, this is complete step by step process for creating fake data in Laravel framework.
Here we have use Yajra Datatables package has been used for load data in jQuery Datatable plugin in Laravel framework. For use Yajra Datatable package, we have to install in Laravel framework. For this we have go to command prompt and write following command.
composer require yajra/laravel-datatables-oracle
This command will download and install in latest version of Yajra DataTable package in Laravel framework. Here we want to add export button in Yajra Laravel Datatable, for this we have to download and intall Datatable Button plugin. For this we have to go command prompt and write followng command.
composer require yajra/laravel-datatables-buttons
This command will download and install DataTables Button plugin of Yajra Datatable package. By using this plugin we can add export button Yajra DataTables for export data for Datatable. Now we want to yajra datatable package service in Laravel framework. For this we have to open config/app.php file and under this file in provider array we have to define this two package service, which you can see below.
This command will publish datatables buttons plugin service in Laravel. Now we can use datatables buttons in Laravel framework for add export button Datatable.
5 - Create DataTable Class
In this tutorial, we will use DataTable class for load data in jQuery Datatable plugin and add export button in DataTable for export data in CSV or Excel file. For this we have to create DataTable class, for this we have go to command prompt and write following command.
php artisan datatables:make ExportDataTable
This command will make DataTables/ExportDataTable.php class file in app directory. So, we have to open this file, and in this class file we have to import model class. In this class file, we can see following method.
dataTable($query) - This method will build DataTable class. query(ExportDataTable $model) - This method is used for get the query source of DataTable. In this method we have define data fetch query. html() - This method will build your html code and Ajax jQuery script for initialize jQuery Datatable plugin. In this method we can define which export button you want to add in Datatable. getColumns() - In this method we have to define Mysql table column name. filename() - This method is used for get the file name for export.
app/DataTables/ExportDataTable.php
<?php
namespace App\DataTables;
use App\Export;
use App\DataTables\ExportDataTable;
use Yajra\DataTables\Html\Button;
use Yajra\DataTables\Html\Column;
use Yajra\DataTables\Html\Editor\Editor;
use Yajra\DataTables\Html\Editor\Fields;
use Yajra\DataTables\Services\DataTable;
class ExportDataTable extends DataTable
{
/**
* Build DataTable class.
*
* @param mixed $query Results from query() method.
* @return \Yajra\DataTables\DataTableAbstract
*/
public function dataTable($query)
{
return datatables()
->eloquent($query);
}
/**
* Get query source of dataTable.
*
* @param \App\ExportDataTable $model
* @return \Illuminate\Database\Eloquent\Builder
*/
public function query(ExportDataTable $model)
{
//return $model->newQuery();
$data = Export::select();
return $this->applyScopes($data);
}
/**
* Optional method if you want to use html builder.
*
* @return \Yajra\DataTables\Html\Builder
*/
public function html()
{
return $this->builder()
->columns($this->getColumns())
->minifiedAjax()
->dom('Bfrtip')
->orderBy(1)
->buttons(
Button::make('csv'),
Button::make('excel')
);
}
/**
* Get columns.
*
* @return array
*/
protected function getColumns()
{
return [
Column::make('id'),
Column::make('firstName'),
Column::make('lastName'),
Column::make('created_at'),
Column::make('updated_at'),
];
}
/**
* Get filename for export.
*
* @return string
*/
protected function filename()
{
return 'Export_' . date('YmdHis');
}
}
6 - Create Controller Class
In Laravel framework for handle http request, we have to ceate Controller class. In Laravel framework controller class file has been store in app/Http/Controllers follder. For create controller class file, we have to go command prompt and write following command.
php artisan make:controller ExportController
This command will create ExportController.php controller class file. In this file, first we want to import ExportDataTable.php class and then after we have to make index method which will render ExportDataTable class data and load in export.blade.php view file.
app/Http/Controllers/ExportController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\DataTables\ExportDataTable;
class ExportController extends Controller
{
public function index(ExportDataTable $dataTable)
{
return $dataTable->render('export');
}
}
7 - Create View Blade File
For display output in Laravel framework, it has been used blade templating engine for display HTML output in browser. In Laravel framework view files has been store in resources/views folder. In this directory we have already create export.blade.php file. This file will display Mysql table data in DataTable by using Yajra Datatable package. And with data you can also find export csv or excel button also.
In next we want to set the route of controller class method. For set route we have to open routes/web.php file and in this file we have to define route which you can find below.
routes/web.php
Route::resource('export', 'ExportController');
9 - Run Laravel Application
Now all set and we are ready to check output in browser. But before check output in browser, we have to first start Laravel application server. For this we have go to command prompt and write following command.
php artisan serve
This command will start Laravel server and provide base url of Laravel application. For test above code we have to write following url.
http://127.0.0.1:8000/export
So this is step by step processing of How to add export button in Laravel Yajra DataTable package. In this tutorial, we have first learn How to generate fake data in Laravel and then after we have seen how to load data in Datatable in Laravel framework by using Yajra Datatable package and then after we have seen how can we export Yajra DataTable data in CSV or Excel file format by using Yajra DataTable buttons plugin. So If you have found this tutorial helpful to you then do not forget to share it.
This is one more post on Exporting of Mysql data. In this post we will learn How to export Mysql data to CSV file by using PHP script. But here we add on feature like date range, that means here we will discuss how to export only those mysql data which has come between two define date to CSV file format by using PHP script. So, here you can learn How to export mysql data for specific date into Excel sheet or CSV file format via PHP.
If you have already learn How to Export Data to CSV file or Excel Sheet using PHP script. But suppose we do not want to export whole Mysql data into CSV file or Excel spread sheet but we want to Export those Data to CSV file with PHP which has come between select date range. So, at that time this turial will help you to learn How to export mysql data to CSV file or Excel sheet by using date range filter with PHP script. This is feature will add usability of your web application and use can freely to export those data which he want to export and he do not export whole unwanted data. This feature will reduce your website bandwidth and reduce load on your mysql database because only required or filtered data only has been exported into CSV file or Excel sheet.
In this post we want to Export date range filter data in CSV file using PHP then in PHP there are many PHP build in file system function is available for export data to CSV file. Here we have use some PHP function like fopen(), fputcsv(), fclose() for Export data to CSV file. Here fopen() function will open file in PHP stream. After this fputcsv() function will write data in open file and fclose() function will close open file. So, this basic PHP function has been used for export data to CSV file in PHP. But here we have not only export mysql data to csv file but also here we have export filtered data exported into csv file. So for filter data here we have use date range that means define two date and only export only those data which has come between this two define data. This we can done in mysql query which you can find below. For date range selection here we have use bootstrap date picker plugin. So, this simple tutorial on How to use Date Range Filter with Export Data to CSV File in PHP. Below you can find complete source code also.
DROP TABLE IF EXISTS `tbl_order`;
CREATE TABLE `tbl_order` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`order_customer_name` varchar(255) NOT NULL,
`order_item` varchar(255) NOT NULL,
`order_value` double(12,2) NOT NULL,
`order_date` date NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
/*Data for the table `tbl_order` */
insert into `tbl_order`(`order_id`,`order_customer_name`,`order_item`,`order_value`,`order_date`) values
(1,'David E. Gary','Shuttering Plywood',1500.00,'2019-06-14'),
(2,'Eddie M. Douglas','Aluminium Heavy Windows',2000.00,'2019-06-08'),
(3,'Oscar D. Scoggins','Plaster Of Paris',150.00,'2019-05-29'),
(4,'Clara C. Kulik','Spin Driller Machine',350.00,'2019-05-30'),
(5,'Christopher M. Victory','Shopping Trolley',100.00,'2019-06-01'),
(6,'Jessica G. Fischer','CCTV Camera',800.00,'2019-06-02'),
(7,'Roger R. White','Truck Tires',2000.00,'2019-05-28'),
(8,'Susan C. Richardson','Glass Block',200.00,'2019-06-04'),
(9,'David C. Jury','Casing Pipes',500.00,'2019-05-27'),
(10,'Lori C. Skinner','Glass PVC Rubber',1800.00,'2019-05-30'),
(11,'Shawn S. Derosa','Sony HTXT1 2.1-Channel TV',180.00,'2019-06-03'),
(12,'Karen A. McGee','Over-the-Ear Stereo Headphones ',25.00,'2019-06-01'),
(13,'Kristine B. McGraw','Tristar 10\" Round Copper Chef Pan with Glass Lid',20.00,'2019-05-30'),
(14,'Gary M. Porter','ROBO 3D R1 Plus 3D Printer',600.00,'2019-06-02'),
(15,'Sarah D. Hunter','Westinghouse Select Kitchen Appliances',35.00,'2019-05-29'),
(16,'Diane J. Thomas','SanDisk Ultra 32GB microSDHC',12.00,'2019-06-05'),
(17,'Helena J. Quillen','TaoTronics Dimmable Outdoor String Lights',16.00,'2019-06-04'),
(18,'Arlette G. Nathan','TaoTronics Bluetooth in-Ear Headphones',25.00,'2019-06-03'),
(19,'Ronald S. Vallejo','Scotchgard Fabric Protector, 10-Ounce, 2-Pack',20.00,'2019-06-03'),
(20,'Felicia L. Sorensen','Anker 24W Dual USB Wall Charger with Foldable Plug',12.00,'2019-06-04');
If you are using Laravel Framework for your web development, and if you are beginner in Laravel. Then this post will help you to learn something new in Laravel. Because in this post you can find how to import Excel spreadsheet data and insert into mysql database in Laravel. Here for importing Excel file data here we will use Laravel Maatwebsite package. By using this package we can easily import data from Excel sheet and insert into table in Laravel application.
In some of the businees houses in which there are many large number of data has been stored in Excel, and after storing that data, they want some application which helps them to store in web application database, that means import into web application database. Then at that time if your web application has been made in PHP Laravel framewor then this post will help you to make importing excel file data feature in Laravel application by using Maatwebsite package.
In this post we will see or learn how can we import any type of Excel speadsheet in .xls, .xlsx or CSV data imported into Mysql database in Laravel. For communicate excel file data in Laravel, here have use Maatwebsite Laravel Excel package, this package will help to communicate excel file data in Laravel application. Below you can find complete step by step process of how to use Laravel maatwebsite package for import Excel sheet data into database in Laravel.
First, we have to create table in Mysql Database, so run following SQL script, it will make tbl_customer table in your database.
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_customer`
--
CREATE TABLE `tbl_customer` (
`CustomerID` int(11) NOT NULL,
`CustomerName` varchar(250) NOT NULL,
`Gender` varchar(30) NOT NULL,
`Address` text NOT NULL,
`City` varchar(250) NOT NULL,
`PostalCode` varchar(30) NOT NULL,
`Country` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Indexes for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
ADD PRIMARY KEY (`CustomerID`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_customer`
--
ALTER TABLE `tbl_customer`
MODIFY `CustomerID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=146;
Step 2 - Mysql Database connection in Laravel
After this you have to make database connection. For this first you have to open database.php file from config. And in this file you have to define your database configuration.
If you want to import excel file data in Laravel, you have to first download Maatwebsite package, this package will communicate with Excel spreadsheet data. First for download package, you have to go to command prompt and write following command.
composer require maatwebsite/excel
This command will download this package in your Laravel working folder. After this we have to register this package in our Laravel application. For this we have to go to config/app.php file. And in this file you have to define providers and aliases.
This way we can register Maatwebsite package in Laravel application, now we can use this package for importing excel file data.
Step 4 - Controllers (ImportExcelController.php)
Now we have to make controller for handle http request for import data. In this controller we have use two use statement. First use DB is used for do mysql database operation, and second use Excel is for Maatwebsite package for import excel sheet data. In this controller, we have make two method.
index() - This is root method of this class, in this method it will fetch data from customer table and that data will be load in import_blade.php file in table format.
import() - This method has request for import excel file data. In this method first it has validate excel file format. If selected file other than excel sheet then it will return validation error. But suppose selected file is excel then it will proceed for import data. For import data here it has called Excel package class which has get data from excel file and convert into PHP array and then after insert into customer table. After successfully import of data it will return success message.
This file has been load by index() method of ImportExcelController, On this file we have make form for select excel file from local computer for import data. Below form it will display tbl_customer table data. And above form we have define for display validation error message and success message.
Lastly, we have to run Laravel application, for this we have to go to command prompt, and write following command.
php artisan serve
This command will Laravel application, it will return base url of Laravel application. Now for this application, we have to write following url in browser.
http://127.0.0.1:8000/import_excel
Above is the complete process of How to make import excel file data feature in Laravel using Maatwebsite Package.
