Export data php to excel

Export from PHP to Excel

I need to export data from php to Excel, and be able to format various items in the excel spreadsheet. So far, the best library that I have found is PHPExcel. However, it seems very heavy, and somewhat slow. Granted, it is very powerful. Is there anything a bit more lightweight and faster, that allows me to export to excel and be able to apply simple formatting (bold, alignment, borders)?

2 Answers 2

I just got done with this yesterday. Using PHPExcel, I had no problems reading in a «master» document with formatting, writing 20-100 rows of content, and saving off the file (I save it «to screen» for immediate download. While some people on the forums complained about speed and overhead, I’m pushing a lot of data its way and it doesn’t have any problem at all doing what it advertises.

Note that somewhere I read to do styling in series as opposed to in loops when possible. For example, style a1:a50 as opposed to style->a1, style->a2 in a loop. Apparently, the two different scenarios have very different memory implications.

The only gotcha I found was a few quirks between outputting and reading Excel 2003 files. If you’re working entirely in XLSX files, it should function exactly as documented.

We always recommend applying styles to ranges rather than looping through individual cells. it’s faster and uses less memory, and the outputted Excel file is smaller because of the shared styles. Another useful style trick is applyFromArray(), defining a set of style features (bold, colour, background colour, borders, etc) in an array and then applying them to cells in a single call rather than setting each element individually.

Yep, I definately try format ranged instead of cells, but to be honest, there really isnt that much formatting in this sheet at all. At the moment, I’m exporting about 1000 rows and 6 columns, with some range formatting on 3 rows and 3 columns. It takes about 6 seconds (of which about 1 second is query time) to run on my localhost, which is usually a decent approximation of the server’s speed.

@JonoB — For writing your data to the PHPExcel object, have you looked at the PHPExcel_Worksheet’s fromArray() method rather than setting each cell’s value individually. This can be used with a Value Binder to force correct datatyping of cell values to speed up the process of building the workbook in memory.

Having worked in an enterprise shop for a major international bank, I can tell you that 6 seconds or processing is nothing compared to the convoluted online systems in place for Credit Cards. We would have been happy with 36 seconds.

Why is this the accepted answer when it doesn’t answer the question of using something other than PHPExcel?

Источник

Exporting Data to Excel using PHP & MySQL

Export Data to Excel is a popular feature of web applications to allow dynamic data export to Excel file to save locally for further use. If you’re a PHP developer and thinking to implement data export to excel, then you’re here at right place. You will learn here how to implement data export to excel using PHP & MySQL.

In our previous tutorial you have learned how to export data to excel in CodeIgniter, now in this tutorial we will explain how to export data to excel with PHP and MySQL.

We will cover this tutorial step by step to create live demo to implement data export to excel with PHP and MySQL. You can also download complete source code of live.

So let’s start implementing data export to excel with PHP and MySQL. Before we begin, take a look on files structure for this example.

Step1: Create MySQL Database Table

As we will cover this tutorial with example to export data to Excel file, so first we will create MySQL database table developers to store developer records.

CREATE TABLE `developers` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `skills` varchar(255) NOT NULL, `address` varchar(255) NOT NULL, `gender` varchar(255) NOT NULL, `designation` varchar(255) NOT NULL, `age` int(11) NOT NULL, `image` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will also insert few records to this table.

INSERT INTO `developers` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`, `image`) VALUES (1, 'Smith', 'Java', 'Newyork', 'Male', 'Software Engineer', 34, 'image_1.jpg'), (2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'), (3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30, 'image_2.jpg'), (4, 'Sara', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 25, 'image_2.jpg'), (5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35, 'image_2.jpg'), (6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'), (7, 'Cook', 'MySQL', 'Paris', 'Male', 'Web Developer', 26, 'image_2.jpg'), (8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 28, 'image_2.jpg'), (9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23, 'image_2.jpg'), (10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 28, 'image_2.jpg'), (11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38, 'image_2.jpg'), (12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 30, 'image_3.jpg');

Step2: Get Records from MySQL Database Table

In export.php file, we will get developer records from MySQL database table developers and store into an array.

include_once(«db_connect.php»); $sqlQuery = «SELECT name, gender, age, skills, address, designation FROM developers LIMIT 10»; $resultSet = mysqli_query($conn, $sqlQuery) or die(«database error:». mysqli_error($conn)); $developersData = array(); while( $developer = mysqli_fetch_assoc($resultSet) )

Step3: Display Records with Export to Excel Button

In index.php file, we will display developer records from $developersData array. We will also add data export button to export data.

 
 
 
 Export To Excel 
 
 
       ?>
 
Name
 
Gender
 
Age
 
Skills
 
Address
 
Designation
 
 
  
 
 
 

Step4: Implement Data Export to Excel

Now we will implement export data to excel when export button clicked. We will use $developersData array for data exported and saved into an xlsx file.

 echo implode("\t", array_values($developerInfo)) . "\n"; > > exit; > ?>

You may also like:

• User Management System with PHP & MySQL
• Datatables Add Edit Delete with Ajax, PHP & MySQL
• Build Helpdesk System with jQuery, PHP & MySQL
• Build Online Voting System with PHP & MySQL
• School Management System with PHP & MySQL
• DataTables Add Edit Delete with CodeIgniter
• Create RESTful API using CodeIgniter
• Build Reusable Captcha Script with PHP
• Product Search Filtering using Ajax, PHP & MySQL
• Image Upload and Crop in Modal with jQuery, PHP & MySQL
• Build Push Notification System with PHP & MySQL
• Project Management System with PHP and MySQL
• Hospital Management System with PHP & MySQL
• Build Newsletter System with PHP and MySQL
• Skeleton Screen Loading Effect with Ajax and PHP
• Build Discussion Forum with PHP and MySQL
• Customer Relationship Management (CRM) System with PHP & MySQL
• Online Exam System with PHP & MySQL
• Expense Management System with PHP & MySQL

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download

3 thoughts on “ Exporting Data to Excel using PHP & MySQL ”

Hello, first of all, good job and nice article, I have just one little problem, if you can help me, thanks!
i look the tutorial (export from php mysql to excel) and adapte the code to my database table, and works fine, but when i put “where id_form =’”.$id_form.”‘”,
the select from works fine, but the export, give me a white and clear excel file, i try put the “id_form” in the index field but, still not working. Any solution ? I dont change nothing in the code, just put the database connection for my website (not localhost), insert more data to export, and working, and the condition “where because, in the backoffice of the website i’m building, the client will select the content he wants, and export just that content.

hello Nice article.
In my case, the output of the SQL query which changes everytime by making few selections is displayed on the page- how can I download the result as excel?

Recommendations

Источник

Exporting Table Data To Excel in PHP Tutorial

In this tutorial, we will create a Export Table Data As Excel using PHP. This code will export your MySQLi data into a Microsoft Excel document. The code itself uses the header content function to translate the MySQLi data, then to be able to download as an excel format. This is a user-friendly program feel free to modify and use it in your system.

We will be using PHP as a scripting language that interprets in the web server such as XAMPP, WAMP, etc. It is widely used by modern website applications to handle and protect user confidential information.

Getting Started:

First you have to download & install XAMPP or any local server that run PHP scripts. Here’s the link for XAMPP server https://www.apachefriends.org/index.html.

And this is the link for the jquery that i used in this tutorial https://jquery.com/.

Lastly, this is the link for the bootstrap that i used for the layout design https://getbootstrap.com/.

Creating Database

Open your database web server then create a database name in it db_excel, after that click Import then locate the database file inside the folder of the application then click ok.

Or, you can simply copy/paste the SQL script below in your PHPMyAdmin SQL Page to create our database table and its column. To do this, navigate your database in PHpMyadmin to the SQL Tab. Then, paste SQL script in the provided text area and click the «Go» button.

Creating the database connection

Open your any kind of text editor(notepad++, etc..). Then just copy/paste the code below then name it conn.php.

Creating The Interface

This is where we will create a simple form for our application. To create the forms simply copy and write it into your text editor, then save it as index.php.

Creating PHP Query

This code contains the php query of the application. This code will store the student information to the MySQLi database server. To do that just copy and write this block of codes inside the text editor, then save it as save.php.

Creating the Main Function

This code contains the main function of the application. This code will convert your html table into a readable excel file when the button is clicked. To make this just copy and write these block of codes below inside the text editor, then save it as export_excel.php

DEMO Video

There you have it we successfully created Export Table Data As Excel using PHP. I hope that this simple tutorial helps you to what you are looking for. For more updates and tutorials just kindly visit this site.

Источник